MySQL Performance: MySQL => MEMORY tables are very slow due to swapping

Members

Help

Home

Home » Performance » MySQL » MEMORY tables are very slow due to swapping

Show: Today's Messages :: Show Polls :: Message Navigator

MEMORY tables are very slow due to swapping [message #766]

Thu, 22 February 2007 01:30

mcdavies
Messages: 4
Registered: February 2007

Junior Member

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).

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #767 is a reply to message #766 ]

Thu, 22 February 2007 07:30

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #768 is a reply to message #767 ]

Thu, 22 February 2007 07:44

mcdavies
Messages: 4
Registered: February 2007

Junior Member

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.

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #769 is a reply to message #768 ]

Thu, 22 February 2007 07:50

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Try also disabling SELinux.

Generally it is clearly some form of bug - if memlock would work MySQL would not be swapping but you have to find if it is MySQL not calling memlock properly or some kernel issue.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #774 is a reply to message #769 ]

Thu, 22 February 2007 23:10

mcdavies
Messages: 4
Registered: February 2007

Junior Member

getenforce says "Disabled". I also wrote in /etc/limits file
* M unlimited

Here http://bugs.mysql.com/bug.php?id=22860 I read:
"Support says that memlock doesn't work on OSes other than Solaris."

Don't you know is it really so?

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #775 is a reply to message #766 ]

Fri, 23 February 2007 06:59

Alexey
Messages: 13
Registered: February 2007
Location: Moscow, Russian Federatio...

Junior Member

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.

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #776 is a reply to message #766 ]

Fri, 23 February 2007 07:05

Alexey
Messages: 13
Registered: February 2007
Location: Moscow, Russian Federatio...

Junior Member

mcdavies wrote on Thu, 22 February 2007 01:30

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.

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #786 is a reply to message #776 ]

Fri, 23 February 2007 19:35

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #787 is a reply to message #786 ]

Fri, 23 February 2007 19:37

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Well,

Sinisa tells memlock is unreliable on Linux... but it is not the question in this particular case.

At least on Some Linux versions in some configuration it works reasonably well. For some other is may not as people reply problems with it.

It is always hard with general statements.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: MEMORY tables are very slow due to swapping [message #788 is a reply to message #787 ]

Sat, 24 February 2007 01:46

mcdavies
Messages: 4
Registered: February 2007

Junior Member

Thanks everyone for answer. I'll try other OS with mysqld locked in memory.

Report message to a moderator

Previous Topic:	Assertion failure
Next Topic:	sometimes very simple queries are extreemly slow

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 7 11:44:44 EST 2009

Total time taken to generate the page: 0.00960 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.