New to the community? Start here

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!

Fri, 10 Jan 2014 0:43:40
  • Aaron
    • Ex Staff

    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