Hi
I have a lot of sites (30, with plans of adding many times that amount) that use the same MySQL server, with 1,6 million products.
My host has stopped my service as it uses to many ressources.
The exact line they wrote about was "SELECT id,name,normalised_name,image_url,description,price,rating,MIN(price) AS minPrice, COUNT(id)" from products.php
Now I can either move to an external database service like Google SQL or try to optimize. I don't know much about databases, so a few questions:
1) Besides removing the related products (which are not that important to me) are there any other things I should remove or optimize?
2) Is is possible to say anything about how big an impact the above changes will make? Like, "if you remove related products you should cut usage by x%"
3) If I can't optimize / remove to stay within my limits, would there be any problems with moving just the database to google sql instead?
Thanks a lot in advance
Thank you, unfortunately I'm on shared hosting so I don't think I'll be able to change much about the settings.
So I will look into either having an external database in order to keep the site and traffic handling with my host (I have unlimited traffic), or move both sites and database to something like digitalocean
Hi,
I'm afraid I don't have any experience of running the script against external cloud database - latency could become an issue however I would first look at the MySQL configuration of your server. There is some discussion of MySQL server configuration for large sites in this thread...
Note in particular changes to key_buffer_size and sort_buffer_size as the defaults are very small. Allocating around 1/4 of total available server memory normally gives good results; e.g. for a 2GB RAM VPS, for example;
key_buffer_size=384Msort_buffer_size=128M
If you don't have access to the MySQL configuration (normally /etc/my.cnf - changes would need to be applied as root and MySQL restarted) then your host should be able to make the changes for you...
Hope this helps!
Cheers,
David.
--
PriceTapestry.com