database performance of MySQL

[rbrabe]

Hi all,

 

I'm currently testing osCommerce 2.2 MS2 with round about 1000 categories and 3000 (mostly deactivated) products.

On my linux systems (PIII 1GHz and PII 233MHz) each click in the shop takes time between 20s (PIII) / 30s (PII) and 65s (PIII) / 90s (PII).

A closer look to the system pointed at MySQL which needs nearly most of the time to perform the request(s).

An update to MySQL 4.1 with enabled cache didn't brought a better performance.

 

Does anybody have experience with a similar count of categories and products on an equal hardware?

And does anybody have suggestions how to improve the (database) performance?

Till now I didn't tried any cache mechanism.

 

Thanks!

 

rb

[Guest]

it could be that your images are dragging it down. i have many sites with 5000 or more products and have no problem as you are experiencing.

 

if we were experiencing that, there would be no one using the app . . .

[rbrabe]

it could be that your images are dragging it down.  i have many sites with 5000 or more products and have no problem as you are experiencing.

 

if we were experiencing that, there would be no one using the app . . .

<{POST_SNAPBACK}>

 

A look at the process list (with top) shows, that the mysqld is burning 90-97% of the CPU cycles during the waiting time. This behavior happens with MySQL 3.25.55 and 4.1. PHP-Connections to other table spaces of the same database instance don't show this behaviour.. So I wouldn't point at a problem of the MySql installation.

 

Images shouldn't be the problem, because after MySQL finished all SQL-statements, the HTML-page will be displayed immediately.

 

Of course nobody would use osCommerce, if this would be normal! That's why I hope for qualified answer. ;-)

 

rb

[OceanRanch]

No direct experience with numbers that high....however you might try turning category counts off

 

Administration->Configuration->My Store->Show Category Counts

 

set it to false

 

Also, you should try using the osCommerce caching facility to allow for the memory storage of Category Box display etc.

 

Administration->Configuration->Cache

 

set this to true and make sure you have set a writable directory for the cache.

 

HTH

Tom

[Guest]

Turn on the category cache...

[rbrabe]

Thank you! :thumbsup:

 

Category count was the problem.

I'll try the cache too!

 

rb

[Guest]

The category count is an extremely resource intensive loop...and if there are a great number of categories the cache will help as well.

 

Good luck!