MEMORY tables are very slow due to swapping

MySQL & MariaDB Other MySQL® Questions
1

Hi.

I have such a problem: one large MEMORY table and mysqld with memlock enabled (“show status” shows “locked_in_memory ON”). When I start mysqld everything seems like OK: “select …” from the table is very fast and mysqld process SWAP size is 9-10 mb. And at this moment I have about 200-300 mb of free memory (I use top to find it out). But in some time mysqld SWAP size is increasing up to 300-500 mb, and queries to the MEMORY table get very slow. Stopping other services like httpd on this server can help sometimes, but not very much, anyway, mysqld SWAP size is increasing and the performance is awful.
The OS on the server is RHEL 4. Mysql version is non rpm 5.1.15 (I also tried 5.1.14 and 5.0.27).

2

Well. You surely should not have MySQL swapping if it is MEMORY tables or buffer pool it does not matter.

Check if memlock actually worked - ulimit, SELinux or using non root account may be blocking it.

3

Thanks for answer.

ulimit -l

unlimited

I also wrote in /etc/security/limits.conf

  • hard memlock unlimited
  • soft memlock unlimited

I run mysqld as root.

4

Since memory locking is root-only thing, mysqld must have it not working at all on Linux.
And trying to do it is a very bad idea. Even if it worked, you would be screwed cause OS would swap out something active or drop some critical caches or dentries/inodes from slab.
Why don’t you convert your table to MyISAM? If indexes are properly buffered, you’ll get great select performance anyway, and OS will cache as much data pages as your memory allows.

5
[B]mcdavies wrote on Thu, 22 February 2007 01:30[/B]
And at this moment I have about 200-300 mb of free memory (I use top to find it out). But in some time mysqld SWAP size is increasing up to 300-500 mb, and queries to the MEMORY table get very slow. Stopping other services like httpd on this server can help sometimes, but not very much, anyway, mysqld SWAP size is increasing and the performance is awful. The OS on the server is RHEL 4.
Hm... do you have 300-500mb swap while also having 200-300mb of free memory? Some vendor-supplied RHEL4 had buggy vm management on i386 (I think 2.6.9-34-something and 2.6.9-42-something were affected). If this is the case, you'll have to compile custom kernel.
6

Well,

MySQL is normally started under root and memlock should be done before it changes user to mysql.

I do not think memory locking is bad idea if it is limited to proper size and if MySQL is configured not to hog all the memory

Way to often VM will try to be smarter than you are by swapping out things in case of active IO which can be hard to control.