Mysql: less than half of memory used. High load

Question

I have an Ubuntu 8.04 LTS, with 8Gb memory, with dual [email protected], software raid on sata II. For a couple of monts now I try to tune the server and it seems that I'm still far from being done. Server is used only for the mysql database (mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2). The server has an average of 200queries/s with a max of 800queries/s when the load goes up to 15 (average being about 6).

For now I would like at least to take advantage of the whole memory, hoping for a better performance, now being used only half of it. Any hints are welcome.

Below are part of the settings in my.cnf (if you want more values, i can paste them):

key_buffer = 600M
max_allowed_packet = 1M
table_cache = 8000
sort_buffer_size = 8M
max_connections = 170
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
wait_timeout=15
connect_timeout=10
interactive_timeout=40
join_buffer_size=6M
query_cache_limit=2M
old_passwords=1
max_heap_table_size = 128M
tmp_table_size = 128M
open_files_limit = 8192

vmstat output for 5 seconds:

procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
 1  0   8648 3448312 387860 1273572    0    0     0   178 2161 2526 42 24 34  0
 2  0   8648 3448552 387860 1273572    0    0     0    85 1962 2219 48 40 12  0
 7  0   8648 3448116 387860 1273572    0    0     0   106 1575 1809 52 46  1  0
 6  0   8648 3448132 387860 1273572    0    0     0   194 1423 1481 57 44  0  0
12  0   8648 3423296 387860 1273572    0    0     0    67 2184 2525 61 40  0  0
 7  0   8648 3371656 387860 1273576    0    0     0   118 1648 1859 60 40  0  0
 2  0   8648 3423604 387860 1273576    0    0     0    69 1700 1882 59 41  0  0
 8  0   8648 3423052 387860 1273576    0    0     0    95 2047 2240 62 38  0  0
 3  0   8648 3423184 387860 1273576    0    0     0    54 1588 1839 57 43  0  0
 8  0   8648 3423468 387860 1273576    0    0     0   107 1592 1875 58 42  1  0
10  0   8648 3389928 387860 1273528    0    0     0    59 1810 2054 59 41  0  0
13  0   8648 3422836 387860 1273576    0    0     0    93 1691 1835 60 40  0  0
10  0   8648 3367176 387860 1273576    0    0     0    79 1809 2122 65 35  0  0
 6  0   8648 3358868 387860 1273576    0    0     0    95 1638 1847 56 34 10  0
 7  0   8648 3421612 387860 1273576    0    0     0    75 2127 2441 61 39  0  0
 9  0   8648 3391568 387860 1273576    0    0     0    96 1942 2182 63 37  0  0
19  0   8648 3249100 387860 1273576    0    0     0    82 1627 1853 63 37  0  0
 8  0   8648 3405004 387860 1273580    0    0     0   143 2075 2360 64 36  0  0
11  0   8648 3410264 387860 1273580    0    0     0    60 2025 2341 62 38  0  0
 6  0   8648 3389752 387860 1273580    0    0     0    48 1690 1917 58 42  0  0
 4  0   8648 3388100 387860 1273580    0    0     0    81 2191 2598 64 35  1  0
14  0   8648 3389944 387860 1273576    0    0     0   102 1825 2054 55 45  0  0
19  0   8648 3296832 387860 1273576    0    0     0    99 1799 2092 59 41  0  0
15  0   8648 3267500 387860 1273576    0    0     0    85 1466 1705 58 42  0  0
11  0   8648 3414908 387860 1273576    0    0     0    85 1627 1837 52 48  0  0
15  0   8648 3374624 387860 1273576    0    0     0    69 1947 2230 62 39  0  0
 8  0   8648 3405948 387860 1273576    0    0     0    98 1558 1845 60 40  0  0
 9  0   8648 3380652 387860 1273576    0    0     0   122 1845 2175 65 35  0  0
 9  0   8648 3370580 387860 1273576    0    0     0    50 2075 2436 62 37  1  0

---

Thanks for sharing your thoughts. I have 20h since i moved the large tables from myisam to innodb. The performance did not increase, but the default latency (while server is idle) did for sure. So I'm thinking on switching back to myisam (as most of the queries are readings anyway). Regarding the increasing of the myisam index to 4Gb, i don't know if it solves the situation, as it's not used entirely anyway. I paste below some analyze made with MySQLTuner script.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.51a-3ubuntu5.8
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 389M (Tables: 821)
[--] Data in InnoDB tables: 112M (Tables: 38)
[!!] Total fragmented tables: 35

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 47m 22s (44M q [689.243 qps], 1M conn, TX: 18B, RX: 3B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 4.8G global + 12.2M per thread (150 max threads)
[OK] Maximum possible memory usage: 6.6G (84% of installed RAM)
[OK] Slow queries: 0% (1K/44M)
[!!] Highest connection usage: 100%  (151/150)
[OK] Key buffer size / total MyISAM indexes: 600.0M/216.9M
[OK] Key buffer hit rate: 100.0% (11B cached / 148K reads)
[OK] Query cache efficiency: 39.7% (7M cached / 19M selects)
[!!] Query cache prunes per day: 5593858
[OK] Sorts requiring temporary tables: 2% (148K temp sorts / 6M sorts)
[!!] Joins performed without indexes: 25416
[OK] Temporary tables created on disk: 9% (296K on disk / 3M total)
[OK] Thread cache hit rate: 96% (35K created / 1M connections)
[OK] Table cache hit rate: 78% (2K open / 2K opened)
[OK] Open file limit used: 17% (2K/16K)
[OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
[OK] InnoDB data size / buffer pool: 112.9M/4.0G

So I'm going to switch back to the initial conf, that worked slightly better. If you see any setting that would improve performance by increasing memory consumption, it would be great. The projects could be improved on structure/coding a lot (loosing a lot on jois), but for now I would have to find a solution as damage control.

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking
skip-name-resolv
key_buffer = 600M
max_allowed_packet = 1M
table_cache = 8000
sort_buffer_size = 1M
max_connections = 150
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 128M
read_buffer_size = 2M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
wait_timeout=15
connect_timeout=10
interactive_timeout=40
join_buffer_size=6M
query_cache_limit=2M
old_passwords=1
max_heap_table_size = 128M
tmp_table_size = 128M
open_files_limit = 8192
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=/home/mysqlbin/mysql-bin
#log-bin=mysql-bin
expire_logs_days=10
max_binlog_size=100M

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 300M
sort_buffer_size = 120M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 300M
sort_buffer_size = 120M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Answer 1

I am going to assume that you're using myisam engine for your db(s), if this is the case, you'll want to set your myisam index to 4GB as that is the max index size myisam supports.

You should also try to consider what engine is best for your db(s). Myisam is better for reading, but isn't as efficient as writing as innodb. Also, myisam locks the entire table whereas innodb only locks the row. You can run hybrid engine environments but 8GB isn't enough to fully accomplish this.

Could you please elaborate on what engine you're are using?

Answer 2

Set innodb_buffer_pool_size to about 80% of your total memory.

From http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size :

innodb_buffer_pool_size: The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The default value is 8MB. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system. Also, the time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time may be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 7.6.2, “The InnoDB Buffer Pool”