Server Config for DB Server for Multisite

Here's my first question of the sorts for you guys, I've looked online, high and low for suggestion but not sure what to do here.

Looking for help from the WPMU guru's out there. We currently have a multisite setup with Nginx Webserver (4GB | 2 CPU) and a DB Server (2GB | 2 CPU) all running Ubuntu 12.04. I've noticed incredibly slow speeds regarding updating posts with the db server. I currently use digitalocean to host both servers. My gut feeling is that the config we have currently set for the db server could be increased to use more resources and improve load times with updating subsites in the backend. The loading of the front-end is fast having nginx especially with caching enabled, but backend administration may take 20+ seconds to load a page or longer.

I'm using the Multi-DB Plugin as well with 16 DB's setup FYI. Don't plan on having more than 100 sites but they'll be using alot of the webservers resources in sending email campaigns etc. I'm curious if there is a resource I could have my developer read on understanding how we should configure the DB server and down the road setup replication, and load balancing when its necessary.

Here's the config I have for mysql DB server-

[mysqld]
local-infile = 0
max_connections = 250
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
max_heap_table_size = 16M
table_cache = 5000
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 7000
connect_timeout = 15
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 16M
[mysqld_safe]
open_files_limit = 4096
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout

Appreciate your time!

  • Aaron
    • CTO

    Do you have any details on the slow queries from your slow log? A few optomizations we make is global tables should be converted to innodb as they are very write heavy.

    Also if there are specific blog tables that are super high traffic and write heavy we might convert those on a case by case basis.

    But most importantly, to improve admin speed you need to keep away from the database as much as possible. That means using a persistent in-memory cache. We use a memcached server for that. http://wordpress.org/plugins/memcached/

    I think W3 total cache might have similar option in it, not sure. If digital ocean's new private networking is available for your site, then you could setup a memcached droplet (lock it down with firewall though as it's not truly private!). Probably easier as you only have one app server is just a local memcached instance on it, you might need to resize your instance to get more RAM. A little bit of cache size goes a long way, though with multisite the more the better. I would start with whatever RAM you have available, then monitor your cache evictions to adjust up as you grow.

    That will take a whole lot of read load off your DB, making those writes much less contested.

  • phillipfx
    • Design Lord, Child of Thor

    Those are some good suggestions. I'll talk to my developer and relay this....

    Any opinions with making updates to the my.conf i copied/pasted above? I understand caching the backend of WP is not a smart approach, only best for the front-end.

    Any other considerations you can think of?

    Your insight is invaluable!

    Thanks,
    Phil

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.