Database Optimization Experience – Rackspace Cloud
Conversation:
Hello,
It has come to our attention that the database xyz has been running some queries that take an extremely long time to complete and have started to become detrimental to the shared resource that the database is hosted on. At this time, we have not imposed any restrictions on the database, however, if the issue persists, such restrictions may become necessary in order to preserve the shared resource in question.
Here are the queries in question that have been running slowly.
If you have any questions, please feel free to contact us!
Thank you,
Cloud Sites Operations
Previously the script was querying to 4 or more tables for single record or list of records. It is corrected by DE-normalization. Now only tbl_content table and sometimes the favorite articles table will be using in JOIN query but most of the queries will be depending on single table. For this, we have implemented a cron job script which query to the required tables via JOIN and update that single tbl_content table. We have checked the facebook URL as well. Please check the website at your end and update us if any further improvement is required. Thanks for the cooperation regarding database privileges reversion.
If, in case, you think its necessary to disable the database like it was on 27 July 2012. You can put “die();” or “exit();” in index.php instead of disabling the database. Because our cron jobs script is continuously fetching the news from API and it does matter for us. We are standby with you to resolve the queries problem. I think only 1 query is left which you requested to review. Can you please check it again because that is updated as well.
Thank you,
Fahad Mahmood
Hello,
I haven’t noticed any load spikes on this MySQL host since re-enabling this database, however I do see some extremely long running queries. For example, a query examines over 55 million rows and runs for several minutes:
Please look into indexing and optimizing these. I don’t see any issues at the moment but if several of these queries were executed simultaneously it could create excessive load and performance issues for other customers. For a large, high resource database like this you might also consider looking into Cloud Servers or a dedicated solution where you would have full control over server configuration and resources.
Regards,
Systems Operations
Rackspace Cloud Sites
Hello,
I have reviewed both queries, please have a look on the first one again. I have implemented LIMIT on it even for pagination count.
The second query is in our CRON JOB script which will not be running by any user or user related activity. It runs on weekly basis and picks limited no. of records from different categories to FORM a weekly newsletter. I will ask the CTO about this query but i am sure that it will not create problem in future because its frequency will remain the same as it is user independent activity.
We are still looking for improvement of the queries if you feel any of them?
Thanks,
Fahad Mahmood
Hello Fahad,
Thanks for your work in optimizing this database. I don’t see any slow queries from our slow logs today so I went ahead and restored the connection limit for your database users. We will continue to monitor over the next 24 hours and let you know if we run into any issues.
Regards,
Systems Operations
Rackspace Cloud Sites
Hello,
I haven’t seen any recent long running queries and there haven’t been any load spikes on this MySQL server over the past few hours. I’m going to go ahead and mark this ticket as solved. We’ll continue monitoring and let you know if we come across any further issues.
Regards,
Systems Operations
Rackspace Cloud Sites
Last updated: March 24, 2014
