Hi,

I am running a dedicated mysql server on centOS 5.6 and Kernel Linux 2.6.18-238.el5, swappiness is set to 60 which is default.

thing is I have around 3000 databases, most of which are inactive, the server has 20G of RAM and 8 CPU Threads. MySQL is not using a lot of CPU time nor is it using a lot of Resident memory (Exactly 15G), virtual space (VIRT) in use by mysql is 20.2G and swap shown by top for MySQL is 4.5G BUT free -m shows me that linux has caches about 3G of ram used 9G of swap and I do not understand anymore how this happens.

Should I reduce swappiness to 40 ?
what can I change in my.cnf


my.cnf below:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld_safe]
socket = /var/lib/mysql/mysql.sock
nice = 0

[mysqld]

user = mysql
socket = /var/lib/mysql/mysql.sock
port = 3306

skip-external-locking
max_connections=100

myisam-recover = BACKUP
max_connections = 200
table_cache = 128
thread_concurrency = 16

query_cache_limit = 4M
query_cache_size = 1024M

sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

wait_timeout = 28800
tmp_table_size = 128M
max_heap_table_size = 128M

general_log_file = /var/log/mysql/mysql.log

innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size = 7G
innodb_additional_mem_pool_size = 24M
innodb_thread_concurrency = 16


[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[isamchk]
key_buffer = 16M

I posted my question to mysql forums also but can't get any help from there.

Since you haven't listed any performance numbers I assume that you don't care about the effect on performance, just on memory use? Are you under the misperception that all swapping is bad? If the process size approaches physical memory size, then some memory will be swapped so that there is always some available memory when needed at the expense of memory which is initialized but seldom or never used. Andrew Morton sets swapiness on his own machine to 100.

Without knowing what you are trying to achieve it's difficult to suggest changes to config.

HI,
Thanks for your replay, well, the thing is that I am not sure that the system is stable like this. I have noticed that if there is such a large amount of swap and at least half of it is owned by mysql, it seems that mysql is not able to shut down properly for maintenance. And I am concerned that if I run it like this for longer periods I might encounter corruption or something else unpleasant.

What I would like to achieve is that my db server is stable, even if it comes with performance penalties. Right now I don't think it is stable. But hey, I am not a DBA.

Well I have been stuck with this problem for over a month now, I have read the book "high performance mysql", I have read dozens of articles about mysql configuration but I fail to get the config right, since I have no real experience with big mysql servers.

I am quite desperate.

What does "not stable" mean? Is it crashing?

What does "not able to shut down properly" mean? Does it take a long time? A 20 GB application will not shut down in 10 seconds.

Yes, my mysql server has crashed twice in the past (well before I configured innoDB variables as they are in the my.cnf that I posted, since then it has not crashed but I have also rebooted mysql server on a frequent maintenance schedule just in case)
Not able to shut down properly means that it does not shut down. I need to kill -15 it, it takes a long time to try shutting down and then it fails and it keeps running but cant log in.