MySQL Server 5.6 only runs when making swapfile

Question

I am new to MySQL server. I have set up a dedicated MySQL server. I am aiming to install MySQL server version 5.6 and since version 5.6 is not available on the Ubuntu repository, I have to upgrade it myself. Unfortunately, upgrading the MySQL server is a pain.

Everything went smoothly up until starting the MySQL by using the command:

sudo service mysql.server start

It will issue an error:

The server quit without updating PID file (/var/run/mysqld/mysqld.pid)

I've searched far and wide on what's causing this error. Unfortunately, I have found none. Not until a colleague of mine suggested making a swapfile. And thankfully enough it worked! Right now the MySQL server is running smoothly.

I was just wondering what caused the error and what are the drawbacks in this kind of setup in the long run?

Answer 1

MySQL 5.6 wants more memory

It's likely that MySQL 5.6 just needs more memory than your previous version.

MySQL 5.6 tries to allocate more memory per connection than previous versions - I believe a lot of this is attributable to the performance schema, which is now enabled by default.

Small servers (esp. ~512MB memory), or servers with a high max_connections setting, can run out of memory even if the other settings are fairly conservative.

If this is the case, you might see some messages from the OOM Killer in your kernel log - via the dmesg function or in /var/log/kern.log, indicating that it's killed the MySQL process because it wanted too much memory.

If you don't want to use a swap file, you may be able to prevent this behaviour by reducing max_connections or disabling the performance schema with performance_schema=off in your my.cnf file.

Swap space is generally a useful thing

Percona's Peter Zaitsev has discussed the merits of swap files on MySQL servers before on his MySQL performance blog.

It's a good read, but to summarise, his own preference was that "I tend to keep swap enabled on MySQL server but of course ensure there is not swapping happening (si/so columns in VMSTAT are zero or close to zero)."

The upside of having swap space is that the server won't kill off processes if it runs out of memory. The downside is that the server might spend time paging instead of running entirely from memory. It's really up to you, but I'm inclined to agree with the author.