Posted on 1 Comment

Swapping on a database server

It’s just such a bad idea…
When tuning your database server, make sure you leave enough memory free for the operating system’s filesystem cache (particularly if you’re using MyISAM tables!)
If it’s not a dedicated server (like sharing a system with Apache), be even more careful. Apache 1.3 loves memory.

Some MySQL server settings are global, some are local (per connection thread). If you make the local ones too big in the default server config, then you’re eating way too much memory. It’s much wiser to keep those particular buffers (such as read_buffer_size) fairly small by default, and using SET LOCAL varname = # inside the connection when doing a query that has higher requirements.

If you have a dedicated db server, just make sure it doesn’t swap at all. Well I do believe linux always swaps a bit, but you don’t want to see any activity there once the system is up and running.

Posted on 1 Comment

1 thought on “Swapping on a database server

  1. One of the tuning things I do for both MySQL and memcached systems is check to ensure that there is no or almost no swapping going on. Swapping is a great way to kill performance in an otherwise carefully tuned system. Vmstat and top are your friends on a unix system.

    Getting programmers to make software changes is unfortunately harder than making database server settings changes. But necessary to do the DBA job properly, since that’s where the biggest improvements will be.

    For now I’m still using big per-connection default buffers of various sorts to compensate for design and coding issues. It’ll be a month or more before the first big batch of changes to dramatically improve things goes live. Still at least three more generations to go before things are fairly efficient overall. You’d find lots of things to grimace at.:)

    My next challenge: persuading programmers who are used to normalised data that yes, you do want multiple copies of the data for report generation.

Comments are closed.