Hi everyone,

I have install Zabbix monitoring server on a 512 linode, the linode have other things on and the memory is a bit short. The IO Rate in linode graph show spike after 10 or 15 minutes. Before upgrading memory, I just want to find what is the problem. I'm not in linux expert.

Without Zabbix running

             total       used       free     shared    buffers     cached
Mem:           512        475         37          0          6        238
-/+ buffers/cache:        230        282
Swap:          255          0        255

iostat 1 5
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.25    0.25    0.09    0.06    0.03   99.32

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              4.90        31.15       130.30    2442898   10219192
xvdb              0.00         0.00         0.00        280          8

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              3.00         0.00        32.00          0         32
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              0.00         0.00         0.00          0          0
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.25   99.75

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              0.00         0.00         0.00          0          0
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              0.00         0.00         0.00          0          0
xvdb              0.00         0.00         0.00          0          0

And the graph show IO Rate between 50 and 150

With zabbix at start point
iostat 1 5
             total       used       free     shared    buffers     cached
Mem:           512        501         10          0          7        259
-/+ buffers/cache:        235        276
Swap:          255          0        255

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.25    0.25    0.09    0.06    0.03   99.32

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              5.03        31.42       137.30    2470482   10795648
xvdb              0.00         0.00         0.00        280          8

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              0.00         0.00         0.00          0          0
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              0.00         0.00         0.00          0          0
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.25    0.00    0.00    0.00    0.00   99.75

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda             88.00         0.00      3200.00          0       3200
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              4.00         0.00       120.00          0        120
xvdb              0.00         0.00         0.00          0          0



Zabbix and linode graph with IORate of 1000

iostat 1 5

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.26    0.25    0.09    0.06    0.03   99.32

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              5.06        31.33       138.24    2481194   10947232
xvdb              0.00         0.00         0.00        280          8

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda             47.00         0.00      1848.00          0       1848
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              0.00         0.00         0.00          0          0
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.00    0.00    0.00    0.00    0.00  100.00

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              0.00         0.00         0.00          0          0
xvdb              0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.26    0.00    0.00    0.00    0.26   99.49

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
xvda              4.00         0.00       120.00          0        120
xvdb              0.00         0.00         0.00          0          0

For informations Zabbix use MySQL, the linode have apache2, MySQL, java socket server and zabbix-server. It's only a validation site for testing some functionality, no heavy traffic on.

I need to pass to a larger plan? It's possible to detect why is growing so much.

OS : Debian Squeeze

Thankful for any advice or ressource.

It looks like Zabbix increased your memory usage from 230 MB to 235 MB, leaving you 276 MB free. I wouldn't exactly consider memory usage below 50% to be "a bit short" but that's me.

The I/O usage doesn't look remarkably high, either. It looks like it's mostly writes, though, so it's probably something writing accumulated data to disk. It might be a good idea to watch "iotop" for awhile to get a better idea of what is going on, or check and see if something has a case of datarrhea.

_________________

/* TODO: need to add signature to posts */

I install iotop and is what I got. It's mysql write to disk. I uninstall XMail and now, IO Rate on the graph show 460 IO Rate.

  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1525 be/4 mysql       0.00 B/s  533.07 K/s  0.00 %  0.10 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1007 be/4 root        0.00 B/s    7.84 K/s  0.00 %  0.06 % [kjournald]
 2114 be/4 mysql       0.00 B/s   11.76 K/s  0.00 %  0.02 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1518 be/4 mysql       0.00 B/s    3.92 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1520 be/4 mysql       0.00 B/s  533.07 K/s  0.00 %  0.00 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]



  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1007 be/4 root        0.00 B/s   35.11 K/s  0.00 %  0.02 % [kjournald]
 2114 be/4 mysql       0.00 B/s    7.80 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 2230 be/4 root       15.60 K/s    7.80 K/s  4.12 %  0.00 % java -server -Dfile.encoding=UTF-8 -Djav~otoandplay.smartfoxserver.SmartFoxServer
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]
    5 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/u:0]

  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
2237 be/4 root        0.00 B/s    7.82 K/s  0.00 %  0.00 % java -server -Dfile.encoding=UTF-8 -Djav~otoandplay.smartfoxserver.SmartFoxServer
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]
    5 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/u:0]
    6 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/0]
    7 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/1]
    8 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/1:0]
    9 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/1]
   10 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/2]


  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1525 be/4 mysql       0.00 B/s   11.65 K/s  0.00 %  0.06 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1007 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.04 % [kjournald]
 2114 be/4 mysql       0.00 B/s    3.88 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 9479 be/4 www-data    0.00 B/s    0.00 B/s  0.00 %  0.00 % apache2 -k start
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]

  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1007 be/4 root        0.00 B/s   38.97 K/s  0.00 %  0.12 % [kjournald]
 1525 be/4 mysql       0.00 B/s    7.79 K/s  0.00 %  0.03 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 2114 be/4 mysql       0.00 B/s    3.90 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 9479 be/4 www-data    0.00 B/s    0.00 B/s  0.00 %  0.00 % apache2 -k start
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]


  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1525 be/4 mysql       0.00 B/s  500.27 K/s  0.00 %  0.11 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1007 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.03 % [kjournald]
 1518 be/4 mysql       0.00 B/s    3.91 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1520 be/4 mysql       0.00 B/s  500.27 K/s  0.00 %  0.00 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]



  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1525 be/4 mysql       0.00 B/s  690.48 K/s  0.00 %  0.17 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 2114 be/4 mysql       3.90 K/s   11.70 K/s  0.00 %  0.06 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1007 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.04 % [kjournald]
 1518 be/4 mysql       0.00 B/s    3.90 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1520 be/4 mysql       0.00 B/s  686.58 K/s  0.00 %  0.00 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]
    5 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/u:0]
    6 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/0]


  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1525 be/4 mysql       0.00 B/s  642.34 K/s  0.00 %  0.16 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1007 be/4 root        0.00 B/s    7.83 K/s  0.00 %  0.07 % [kjournald]
 2114 be/4 mysql       3.92 K/s   11.75 K/s  0.00 %  0.06 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 2087 be/4 mysql       0.00 B/s    3.92 K/s  0.00 %  0.02 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 1518 be/4 mysql       0.00 B/s    3.92 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 2082 be/5 zabbix      0.00 B/s    3.92 K/s  0.00 %  0.00 % zabbix_server
 1520 be/4 mysql       0.00 B/s  642.34 K/s  0.00 %  0.00 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]
    5 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/u:0]
    6 rt/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [migration/0]


  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
 1007 be/4 root        0.00 B/s    7.80 K/s  0.00 %  0.02 % [kjournald]
 1525 be/4 mysql       0.00 B/s    3.90 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
 2114 be/4 mysql       0.00 B/s    7.80 K/s  0.00 %  0.01 % mysqld --basedir=/usr --datadir=/var/lib~=/var/run/mysqld/mysqld.sock --port=3306
    1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init [2]
    2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kthreadd]
    3 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [ksoftirqd/0]
    4 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/0:0]

I'm guessing something is doing a lot of writing to MySQL. Is there anything involved that might be doing a lot of write queries?

You might want to give mysqltuner.pl a shot, to see if there's anything obviously amiss with your MySQL config, but... well, it's going to have to write stuff to disk sooner or later, and sooner is better than later. The problem is probably upstream of MySQL.

_________________

/* TODO: need to add signature to posts */

It's preferable to run all the tables in MyISAM or InnoDB? I run OPTIMIZE table, but don't give a good result.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 157M (Tables: 68)
[--] Data in InnoDB tables: 122M (Tables: 262)
[!!] Total fragmented tables: 267

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 36m 20s (10K q [4.823 qps], 488 conn, TX: 6M, RX: 1M)
[--] Reads / Writes: 59% / 41%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (79% of installed RAM)
[OK] Slow queries: 0% (0/10K)
[OK] Highest usage of available connections: 9% (14/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/941.0K
[!!] Key buffer hit rate: 92.8% (12K cached / 910 reads)
[OK] Query cache efficiency: 42.5% (2K cached / 5K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 182 sorts)
[!!] Joins performed without indexes: 17
[OK] Temporary tables created on disk: 17% (451 on disk / 2K total)
[OK] Thread cache hit rate: 97% (14 created / 488 connections)
[!!] Table cache hit rate: 0% (64 open / 8K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (6K immediate / 6K locks)
[!!] InnoDB data size / buffer pool: 122.1M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 64)
innodb_buffer_pool_size (>= 122M)

Unless you have a very good reason to use MyISAM (e.g. FULLTEXT indexes or memory tables), you should not use it. It does not support transactions or referential integrity, and its crash recovery tends to lose data. It's probably a good idea to move your MyISAM tables to InnoDB at some point (ALTER TABLE blah ENGINE=INNODB). As another plus, MyISAM and InnoDB both have their own cache structures and tuning parameters, so choosing one or the other simplifies things.

Run mysqltuner again in about ~24 hours and see what comes out... the performance metrics are the interesting part, but it takes awhile for it to settle out. It's looking like 41% of queries are writes, and at 4.823 queries/second, that's about ~2/second.

_________________

/* TODO: need to add signature to posts */

After 24h.

>> 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.49-3
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 160M (Tables: 68)
[--] Data in InnoDB tables: 146M (Tables: 262)
[!!] Total fragmented tables: 267

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 3h 28m 38s (3M q [19.616 qps], 11K conn, TX: 703M, RX: 368M)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (79% of installed RAM)
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 17% (26/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/968.0K
[OK] Key buffer hit rate: 99.7% (1M cached / 2K reads)
[!!] Query cache efficiency: 4.2% (109K cached / 2M selects)
[!!] Query cache prunes per day: 745583
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 11K sorts)
[!!] Temporary tables created on disk: 29% (40K on disk / 139K total)
[OK] Thread cache hit rate: 99% (30 created / 11K connections)
[!!] Table cache hit rate: 0% (64 open / 12K opened)
[OK] Open file limit used: 0% (2/1K)
[OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)
[!!] InnoDB data size / buffer pool: 146.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
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:
query_cache_limit (> 1M, or use smaller result sets)
query_cache_size (> 16M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 64)
innodb_buffer_pool_size (>= 146M)