mySql is Swaping

If it's not a problem, it's probably not a problem. It looks a little weird, though.

I normally deal with MySQL instances on low-RAM systems, but sure enough, I see the same thing on my PC:

rtucker@witte:~$ free -m
             total       used       free     shared    buffers     cached
Mem:         11662      11335        327          0        837       3918
-/+ buffers/cache:       6578       5084
Swap:        12287        997      11290
rtucker@witte:~$ uname -a
Linux witte 2.6.32-35-generic #78-Ubuntu SMP Tue Oct 11 16:11:24 UTC 2011 x86_64 GNU/Linux
rtucker@witte:~$ ps auxwww | grep mysql
mysql     1735  0.0  0.7 1246196 87212 ?       Ssl  Oct23  47:11 /usr/sbin/mysqld
rtucker  24878  0.0  0.0   7628   920 pts/13   S+   17:09   0:00 grep mysql

rtucker@witte:~$ sudo -i service mysql restart
mysql start/running, process 24995

(waits for it to stabilize)

rtucker@witte:~$ ps auxwww | grep mysql
mysql    24995 11.8  1.8 1112192 224984 ?      Ssl  17:09   0:03 /usr/sbin/mysqld
rtucker  26029  0.0  0.0   7624   908 pts/13   S+   17:10   0:00 grep mysql
rtucker@witte:~$ free -m
             total       used       free     shared    buffers     cached
Mem:         11662      11332        330          0        838       3967
-/+ buffers/cache:       6526       5136
Swap:        12287        763      11524

I'd recommend running mysqltuner.pl and posting the output… you might not want to change anything (it will ALWAYS find something wrong with your config!), but the output is great for summarizing your settings at-a-glance:

rtucker@witte:~$ mysqltuner.pl 

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.10-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 43M (Tables: 158)
[--] Data in InnoDB tables: 764M (Tables: 748)
[!!] Total fragmented tables: 26

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2m 10s (580 q [4.462 qps], 201 conn, TX: 57K, RX: 37K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 869.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.2G (10% of installed RAM)
[OK] Slow queries: 0% (3/580)
[OK] Highest usage of available connections: 1% (2/151)
[!!] Key buffer size / total MyISAM indexes: 16.0M/23.5M
[!!] Query cache efficiency: 0.0% (0 cached / 383 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 23% (48 on disk / 201 total)
[OK] Thread cache hit rate: 99% (2 created / 201 connections)
[!!] Table cache hit rate: 1% (96 open / 4K opened)
[OK] Open file limit used: 19% (195/1K)
[OK] Table locks acquired immediately: 100% (200 immediate / 200 locks)
[OK] InnoDB data size / buffer pool: 764.2M/800.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    key_buffer_size (> 23.5M)
    query_cache_limit (> 4M, or use smaller result sets)
    table_cache (> 96)</major@mhtx.net> 

(I probably should have run this before restarting mysqld, eh?)

The two big things to look at:

1) Maximum possible memory usage: this better be well below 2019 MB. I bet it's about 828 MB for you, though, if the pattern holds :-)

2) InnoDB data size / buffer pool: this ratio should be below 1, but the denominator must be less than the amount of RAM you have. MySQL eventually reserves the right to use this much memory. The ratio should also be pretty close to 1, depending on how much your data is expected to grow. The InnoDB buffer pool size is a setting you control.

3) Data in MyISAM tables: should be very close to 0 MB. This is how much data you should plan to lose if someone sneezes near your database. (Sorry, couldn't resist.) But the InnoDB buffer pool only applies to InnoDB, and MyISAM memory management is a much more complex affair.

What I think is happening is that MySQL (and other things) have requested all of that RAM, but the kernel is betting they won't actually need it right away. This is somewhat analogous to how banks operate: a particular percentage of assets must be liquid (per the reserve requirement), and if this falls short at any time, various Things happen Very Quickly to solve the problem before all hell breaks loose. Up to a point, your kernel can "loan out" more memory than it has available. This can involve swapping stuff out.

Another cause of swap usage is disk caching. It seems counter-intuitive, but if your kernel thinks swapping stuff out will yield better overall performance, it'll swap stuff out. I don't think this is the immediate cause for you (I'd expect to see more *s on your Mem line in htop), but it is an example of the kernel's mindset. And yes, the kernel does predict how likely a given chunk of memory or disk is to be read when making these sorts of decisions.

tl;dr: It's probably normal. The kernel will do weird stuff for performance.

I agree with hoopycat. It's probably not a problem at all.

Sometimes, the kernel moves stuff to swap even when there's plenty of RAM left. That way, you'll have even more RAM left, which the kernel can use to boost I/O performance. Linux is very clever.

What you should really look out for is the amount of swap I/O, not raw swap usage. You can easily check this by looking at the I/O graph in your dashboard.

Thanks hybinet and hoopycat

My Swap IO is

MAX - 152.15

Avg - 6.03

Here is the mysqltuner.sh

–------ Performance Metrics -------------------------------------------------

[–] Up for: 1d 11h 0m 37s (2M q [22.936 qps], 37K conn, TX: 2B, RX: 425M)

[–] Reads / Writes: 89% / 11%

[–] Total buffers: 746.0M global + 24.3M per thread (151 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 4.3G (218% of installed RAM)

[OK] Slow queries: 0% (0/2M)

[OK] Highest usage of available connections: 23% (35/151)

[OK] Key buffer size / total MyISAM indexes: 600.0M/37.3M

[OK] Key buffer hit rate: 99.9% (64M cached / 92K reads)

[OK] Query cache efficiency: 90.5% (2M cached / 2M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (6 temp sorts / 34K sorts)

[OK] Temporary tables created on disk: 12% (12K on disk / 93K total)

[OK] Thread cache hit rate: 99% (97 created / 37K connections)

[!!] Table cache hit rate: 0% (64 open / 12K opened)

–------ Recommendations -----------------------------------------------------

General recommendations:

Add skip-innodb to MySQL configuration to disable InnoDB

Run OPTIMIZE TABLE to defragment tables for better performance

Enable the slow query log to troubleshoot bad queries

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

*** MySQL's maximum memory usage is dangerously high ***

*** Add RAM before increasing MySQL buffer variables ***

table_cache (> 64)

the LAMP Stack script set all these variables so I'm not sure about tweaking them.. but the warning "*** MySQL's maximum memory usage is dangerously high ***" doesn't sound to good! lol

How do I throttle this down?

Also I went to adjust table_cache but the setting is commented our in my.cnf - I'll uncomment and make 64

Also, I'm on a wordpress install with static file caching setup for most sites - should I skip-innodb ?? mysqltuner recommends this but I can't find much on google with a solid recommendation.

> [–] Total buffers: 746.0M global + 24.3M per thread (151 max threads)
> [OK] Highest usage of available connections: 23% (35/151)

The main problem with high RAM of mysqltuner are the number of connections of MySQL, change that to 50.

Maybe it's not the problem but should lower that max memory usage of MySQL?

If not enough try lowering the read/join/rnd buffers of mysql too.

Try lowering the max numbers of apache/php childrens too until it stops swapping….

But it's weird that swapping….

I concur re: lowering max connections. Each connection gets its own set of buffers (24.3 MB worth, in your case), so dropping that to 50 would mean a maximum possible consumption of 24.3 MB x 50 + 746.0 MB = 1961 MB.

Decreasing key_buffer from 600 MB to something closer to 37.3 MB could make a huge difference, though. Maybe 64 MB, to pick a good-looking power of two. This page explains what the MyISAM engine does with the key buffer; in short, it lets it store index blocks in RAM while relying on the OS to cache data blocks.

As for InnoDB… well, if you aren't using it, you can skip-innodb and save some RAM. I'd recommend migrating your existing MyISAM tables to InnoDB sooner or later, though. (Ever had to repair or optimize a table after a crash to get back online? Ever lost data because someone tripped over a cable? If so, MyISAM was probably involved…)

Note that InnoDB uses a buffer pool for caching, which stores both index and data blocks and therefore doesn't implicitly rely on the OS's caching facilities. This means a buffer pool size of 600 MB would be completely reasonable (if you have ~600 MB of data), but it also means your performance is more predictable. This would change the memory management dynamics quite a bit.

Don't be afraid to adjust the defaults. They're just starting points. key_buffer was set to 75% of 40% of the RAM (see lines 187 through 222) – that's it. You've got data based on your actual usage of MySQL, which is much better than blind guessing!

As for the swapping rate… eh, not too bad. Peaks are peaks, averages are averages, and the two numbers seem far enough apart and low enough that I'd say it's a symptom of "normal" swapping, rather than swap-thrashing. Your time-series graph likely has a rather low rumble all the time, with maybe one or two spikes up towards 150 blocks/sec.

Thanks guys.. you gave me a bunch to read and try out! I'm going to make some small small changes and see if it lowers the swap.. that 100 is high compared to what I'm used to seeing for my applications.

so @hoopycat - where's the third pig?