Guest Posted March 20, 2006 Posted March 20, 2006 While setting up and testing, I populated 6 tables with data for 75 of 8,000 actual products, the only 75 which have associated images. The performance was snappy over my local network. With 12 categories, 0.5 to 1 second would show the results of a category query. When I populated the full 8,000 products, it now takes 9 seconds to query a category. The full data export produces over 300 categories. I'm concerned that this would be much slower yet when run over the Internet. Does this slow down seem normal when increasing the number of records from 75 to 8,000? The following are the 6 tables used in populating with real product data: products products_description manufacturers categories_description categories products_to_categories Could I have some kind of mismatch between these 6 tables populated to 8,000 compared to only 75? Or would this be normal performance for osCommerce?
custodian Posted March 20, 2006 Posted March 20, 2006 While setting up and testing, I populated 6 tables with data for 75 of 8,000 actual products, the only 75 which have associated images. The performance was snappy over my local network. With 12 categories, 0.5 to 1 second would show the results of a category query. When I populated the full 8,000 products, it now takes 9 seconds to query a category. The full data export produces over 300 categories. I'm concerned that this would be much slower yet when run over the Internet. Does this slow down seem normal when increasing the number of records from 75 to 8,000? The following are the 6 tables used in populating with real product data: products products_description manufacturers categories_description categories products_to_categories Could I have some kind of mismatch between these 6 tables populated to 8,000 compared to only 75? Or would this be normal performance for osCommerce? What are your system spec's? Ram, Swap space, CPU, OS? Do you having caching enabled? What other (if any) services/processes are running on the server? Is it a shared host or dedicated? Shared DB or dedicated? Finding the cause may take a closer look at the over-all picture. My Contributions Henry Smith
♥bruyndoncx Posted March 20, 2006 Posted March 20, 2006 and you did turn-of category counts - right ? KEEP CALM AND CARRY ON I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support). So if you are still here ? What are you waiting for ?! Find the most frequent unique errors to fix: grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt
Jan Zonjee Posted March 20, 2006 Posted March 20, 2006 and you did turn-of category counts - right ? and read the A Store Speed Optimization in Progress, Step by step from a vanilla install! thread?
Guest Posted March 20, 2006 Posted March 20, 2006 and you did turn-of category counts - right ? Thanks, bruyndoncx and custodian. For my configuration, turning off category counts made the largest, single difference. With category counts off instead of on and cache turned off, category queries over my local network were 1.5 seconds. My test server and Internet access has only 256K upload speed so these same category queries from the Internet are between 2.5 and 3 seconds. Internet queries seem responsive, rather than slow. That same relative performance with commercial level upload bandwidth should be good. Enabling the cache and with category counts on produces comparable performance. But since our tables will be updated daily, enabling the cache could result in presenting no longer current product information. After deleting the category cache file from admin tools, the category cache must be created for that category. "Priming the pump" for that first time run category then takes 9 seconds again over my local network. I did not test cache enabled over the Internet.
kgt Posted March 20, 2006 Posted March 20, 2006 Also keep in mind that MySQL really does need quite a bit of memory to sort large datasets. There's no way around this problem. Even if the query is as optomized as can be, if the dataset is too large MySQL has to do some juggling between harddisk and memory to sort it. The default installation doesn't really allow for enough memory when you start to put more demands on the mysql server. It requires tuning before you can get decent performance on large datasets. You will want to increase sort_buffer_size, key_buffer_size, table_cache, and read_buffer_size in my.ini/my.cnf. The default size for these parameters is usually well less than a megabyte. It should be 1 or more megabytes for all of these parameters. http://dev.mysql.com/doc/refman/4.1/en/opt...the-server.html Most hosts have MySQL running on a dedicated server with these parameters properly tuned so unless they have high demand (if it's a shared server) MySQL will perform better on a hosted solution than your local PC or a server running an out-of-the-box install of MySQL. Contributions Discount Coupon Codes Donations
Recommended Posts
Archived
This topic is now archived and is closed to further replies.