I have a Drupal multisite installation with one RackSpace Cloud Server for the web server and another Cloud Server for the database. The web server has Varnish, APC, Memcache and Apache while the database server is running MySQL. Internetworking between the web server and database server is via the RackSpace private network.
I have been adding Drupal multisites to the web server and creating the corresponding database tables.
The Cloud Server for the database has 256MB RAM with no swap. $top shows 23MB free on restart and goes down to about 5MB free on prolonged use.
My questions are:
-
What should I be checking on the MySQL server to let me know that things are going well? So far for a half dozen multisites, everything is working swimmingly, but I know this cannot possible last as I continue to add databases and query them.
-
What will a symptom be that I am starting to have problems?
Thank you in advance.
Comments
Use the Drupal dbtuner
You want as much of the cache in memory as possible:
A) Make sure all large queries are handled by memcache, then tune query cache limit for the 'smaller' intensive queries. Redirect memory savings to memcache.
B) Increase query cache limit after inspecting your slow log, then repeat. It is likely that you will see a few HUGE dumps in there. That is what you are trying find a place for or clean up. Probably variable, menu_router, og_ancestry, node_access.. and then your custom views.
C) Reduce larger query sizes by investigating the nature of the tables and the data.
By default, Apache and MySQL are configured to accept 150 connections. Most PHP applications such as Drupal will open only one connection per thread, so you can safely set the number of connections on both sides to the same value. One note, MySQL actually allows n+1 connections, so you have an extra connection available for management.
Drupal websites usually require over 32 MB of RAM per request, and you can eyeball the average value by looking at the results returned by command top.
For MySQL, set or adjust this value: max_connections = 150.
Paging (swapping) needs to eliminated!
--
Linux: Web Developer
Peter Bowey Computer Solutions
Australia: GMT+9:30
(¯`·..·[ Peter ]·..·´¯)
dbtuner
Peter,
Thank you for the recommendation to install the dbtuner module. I will install it and take a look at the queries as you recommended.
There will be further questions, I am certain.
graphs help too
"What should I be checking on the MySQL server to let me know that things are going well?"
I run Cacti to graph and monitor servers which will help answer this question. Google has some great MySQL Cacti templates - http://code.google.com/p/mysql-cacti-templates/
demo: http://graphs.phamilydev.net/ guest/guest
Cacti templates
I didn't use the Google Code mysql-cacti-templates, however, I am using Cacti to monitor the following MySQL items:
From your statistics, it looks like I am not monitoring:
Should I monitor any of those additional ones?
What specifically should I be looking for in some of these graphs that will tell me that I am running into problems in MySQL?
It's easy to see where there are Network Interface Errors or when an Interface has 100% utilization. I am not sure what the equivalent problem areas in MySQL are.