Perfromance issue - looks like MySQL

[kopoba11]

Hello All, I am having performance issues. Let me explain.

It takes 9 sec parse time when I click on some of the menu items. When in PHPadmin and I look for process, I can see that it takes 9-10 seconds to run the query, i.e copy to temp table - status message Copying to tmp table . Status message shows me this partial query “SELECT DISTINCT p.products_id, p.products_image, p.products_tax_class_id, IF (s.status, s.specials_ne “

I have only 4000 products with 8000 attributes and 4000 specials. Looks like my problem is DB
I am running OSCMax 1.7 on My SQL 2.5.6. Any ideas to improve performance. I have dedicated hosting environment so I am free to do anything.

Thank you

[jpf]

I have only 4000 products with 8000 attributes and 4000 specials. Looks like my problem is DB

8000 attribs would be some of the reasons.... each item must have on average 2 attribs that are not use by any other item...... Is there a way to reduce the attribs? As well each item is a SPECIAL???

[kopoba11]

Sorry some corrections:
I am running MySQL 3.23.58
I have around 16 attributes. Some of my products associated to as much as 5 attributes. Therefore my products attributes table has 8000 records for 4000 products. (well about 2 attributes per product)
I saw some performance difference going from 800 to 3000 products. It was acceptable. Ihave to note that only 800 products had specials. As soon as I gave specials to all products, performance tanked big time. I can see performance problems only when I drill down through category menu . I have only 10 categories. Each category may have only 5 sub-categories. My whole menu structure only 2-3 level deep.

Thank you

[jpf]

Hate to say it - one way to SPEED up MYSQL is to host on LUNIX not Windows PC....

Also in Admin change

* Category Counts Off
* Cache On!

Check mySQL setting on:

key_buffer_size Pool for index pages; Can be made very big (default is only 8MB)
table_cache Change if you have many tables or simultaneous connections (default 64)
key_buffer_size (on MySQL version 4.1+ will gain some proformance)

Use --skip-locking (default on some OSes) if possible. This will turn off external locking and will give better performance.

For optimizing SELECT Statements and Other Queries - Using simpler MySQL permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead when clients execute statements. For example, if you don't grant any table-level or column-level privileges, the server need not ever check the contents of the tables_priv and columns_priv tables. Similarly, if you place no resource limits on any accounts, the server does not have to perform resource counting. If you have a very high query volume, it may be worth the time to use a simplified grant structure to reduce permission-checking overhead.

MySQL can be fast - you just need to have the proper indexing set up. If you need a good MySQL reference, pick up a copy of Paul DuBois' book, which is currently the bible for MySQL. O'Reilly also recently released a reference by Monty and the MySQL AB team.

The way to check if the indices are already there are with the SHOW INDEX command. For instance, to check the tcphdr table, you would run:

+ []mysql> show index from tcphdr;

If you need to create the index, you can run:

+ []CREATE INDEX idx_tcp_sport ON tcphdr(tcp_sport);


This is just some of the info out there on the subject - hopfully it will help you....

[kopoba11]

Thank you. I will work on it. I will try to post any updates I will have.

[dewed]

You might also look into one of the caching contributions on oscommerce.com We are using one contributed by username Chemo.

[kopoba11]

Thanks for all the help. Join query on products and specials was running very slow becasue of index problem. After proper index on both tables all to normal. I am sure I will have to look into perfromance more.