Mysql performance issues? Try this.

I manage all of Bitsum’s network infrastructure, and became discouraged when I saw that some recent changes (we are migrating to the cloud like everyone else) caused spikes in mysqld CPU consumption. I traced it down to a configuration variable that defaults to a surprisingly small number (8Mb).

If you have a large site and thus a large number of indices on any tables, changing the below variable will yield massive improvement. Now, definitely don’t go crazy, never more than 1/4 of your RAM, but this will help.

In Debian (oe at least Ubuntu 16.04) Linux you can find it at /etc/mysqld/mysql.conf/mysqld.cnf

key_buffer_size=512MB

The above decreased mysqld CPU consumption by 2/3rd’s in our case, and I’m sure gave lifespan to the HDDs!

Official documentation

Note this number may be well in excess of what was required. We will work to determine the optimal value as we progress in our cloud systems.

%d bloggers like this: