Heavy I/O and Swap usage on 512

Try –concurrency=2 instead of --concurrency=8 and see if that improves the situation. Also, mysqltuner.pl is good for adjusting MySQL settings for your situation; the ones in the troubleshooting guide are a little dated (and only adjust MyISAM, the old default storage engine).

Thanks! I have changed the concurrency setting, and it's now gone through two of the cron jobs, and there's nothing in swap so far, so at the very least, it has improved. I'll let it go through more of the cron jobs, and I'll also run the mysql tuner when I haven't rebooted so recently. Thanks for the help!

maybe you should also have a lock to stop two celery (whatever that is) jobs running at the same time.

make sure you set up monitoring, an email will do, say if the job normally takes 1 minute to complete, check with find if the lock becomes older than 15 minutes, or something, run it from cron every 5 minutes, then have it email you so you can determine why the job didn't finish or exit properly.

Since I last posted and reduced my concurrency setting, I no longer get high io emails from linode. However, I still get swap usage building up over time. I'm not sure how dangerous that is. For instance, I rebooted 3 days ago and swap usage is currently at 44% (memory is at 45%). I don't know the highest level it has reached, and I've been rebooting every few days.

Also, to clarify one mistake in my original post: I don't think the celery tasks are "hanging." I think they are just there waiting to be called upon, so that's not a problem. Reducing the concurrency seems to have helped their impact. I guess I can even reduce it to 1, if neccessary.

I have run mysqltuner.pl and changed settings twice based on its suggestions. The original settings I had were the default mysql, then modified according to linode's suggestions (which are listed in my original post). Also, when I tried to run OPTIMIZE TABLE on all my tables, I was told it could not do that ("Table does not support optimize, doing recreate + analyze instead" in response to running mysqlcheck -o). Here are the settings I have modified since my original post:

table_cache             = 16
query_cache_limit       = 4M
query_cache_size        = 32M
innodb_buffer_pool_size = 266M
tmp_table_size = 32M
max_heap_table_size = 32M

It still wants me to modify querycachelimit, tmptablesize, maxheaptablesize, tablecache. But it also states: "MySQL's maximum memory usage is dangerously high" and "Add RAM before increasing MySQL buffer variables."

Here's the relevant part of the mysqltuner output:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 0h 3m 21s (5M q [21.698 qps], 37K conn, TX: 4B, RX: 2B)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 362.0M global + 704.0K per thread (151 max threads)
[!!] Maximum possible memory usage: 465.8M (95% of installed RAM)
[OK] Slow queries: 0% (0/5M)
[OK] Highest usage of available connections: 2% (4/151)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[!!] Query cache efficiency: 14.8% (632K cached / 4M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (148 temp sorts / 29K sorts)
[!!] Temporary tables created on disk: 36% (1K on disk / 3K total)
[OK] Thread cache hit rate: 99% (4 created / 37K connections)
[!!] Table cache hit rate: 2% (16 open / 654 opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (4M immediate / 4M locks)
[OK] InnoDB data size / buffer pool: 144.6M/266.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    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 ***
    query_cache_limit (> 4M, or use smaller result sets)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_cache (> 16)

So my questions are:

1. Is this kind of swap usage something I need to worry about?

2. Any recommendations on what to do with the mysql settings?

3. Or should I really pinpoint what causes the swap usage to grow first? (and how can I do this?)

4. Is there any other info I need to provide?

Thanks.

````
[OK] Highest usage of available connections: 2% (4/151)

mysql by default allows 151 clients to connect to it, it's like apache's maxclients setting.

in 3 days, the highest concurrent connections was 4, so you could safely turn

max connections down to 20\. Then you can increase the amount of memory mysql is allowed to

use (max mysql memory usage = n times max connections), and hopefully decrease webpage load

times and therefore decrease overall memory usage, or increase the number of concurrent hits

you can take.

ps aux will tell you which processes are using the most memory,

running top, then pressing shift-m will sort processes by highest memory usage.

table_cache you can set to something like 512 and then ignore mysqltuner.pl messages about it

mysqltuner also always says to optimise tables, I do that via cron every hour.

[!!] Cannot calculate MyISAM index size - re-run script as root user
````
you might want to do that, too.

chesty,

Thanks for the help!

I have reduced max_connections to 20, and made the other adjustments suggested by mysqltuner.

I also re-ran mysqltuner with sudo, and the missing line was:

[OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K

So, I guess that one is fine. I will continue to monitor and tweak the mysql settings. If I continue to run into problems, I'll come back.

Thanks again!

mysqltuner keeps recommending that I increase my querycachelimit, which now stands at 192M. Considering I'm on a 512 linode, this already seems way too high.

I'm also starting to have doubts that this helps at all in my case. The main usage of mysql occurs every 30 minutes when I pull new data and load it into (mostly) one table. I have a lot of reads, because I need to check whether I've previously loaded each piece of data previously or not. For most of each pull, I've already loaded the data previously. But the reads change over time, and the reads that are driven by the website itself will rarely produce the same retrievals twice (and the retrievals it does repeat are relatively small).

The other recommendations from mysqltuner are to expand tmptablesize and maxheaptable_size, which each stand at 168M. This also seems high to me. I still accumulate swap usage (every day, but less than I used to).

I've taken my max_connections all the way down to 6, yet my max possible memory usage is at the limit for my linode after I have made recommended adjustments from mysqltuner over time.

My questions are:

1. Should I disable caching in mysql, given my use cases?

2. Should I continue to increase tmptablesize and maxheaptable_size?

3. Or should I try to pinpoint exactly what is causing the swap usage? Most of the every-30-minute events seem to use very little to no swap, with an occasional spike that does most of the damage. How would I capture what is causing the spikes if they occur when I am not at the terminal?

I don't think tmptablesize and maxheaptable_size increase memory usage, they increase io and disk usage because they limit how much space mysql can use for temporary disk tables for sorts, etc. I'm guessing here, that if they aren't high enough, a large query fails. You can google that to know for sure.

I have no concrete suggestions, if you're running the same queries every 30 minutes, you could cache the results in memcache, and turn off or turn down mysql query cache. I guess that depends on how much data you're collecting. Another option is to use unique keys and just insert the data every time, if the data is already in the db, the insert will fail, if not, it will succeed. I have no idea whether this will help you, but it has helped me in the past. For a particular app and db I was using, it was quicker to use unique keys and try: insert; except: pass everything than it was to do select/insert

Also get familiar with mysql explain to see how a query is being handled, and whether an index might help.

for gathering data, collectd is really good. it's written in C and is quick, it polls every 10 seconds by default. I haven't looked at the collectd package in a while, but last time I used it, it was fairly minimal out of the box, you have no know (or research) what things to monitor.

Hopefully someone else here can chip in, but you're more than capable of tuning your app yourself, it just takes research and testing.

OK, I've pinpointed what is causing my daily spike in swap io: it's the unattended-upgrades, which automatically installs security updates each day right when the spike occurs. Last night, I tried changing a few settings to help reduce this. I uncommented these lines:

Unattended-Upgrade::MinimalSteps "true";

Acquire::http::Dl-Limit "70";

This seems to have helped a little bit (or maybe there just weren't many updates). I suppose I can continue lowering the download speed, but I don't see any other settings that would obviously lower the swap io. Are there any ways to eliminate the swap io caused by this? Or is disabling it the only way?