Advise with mysql optimization

[johnburk]

With one of the servers I have a strange problem with the optimization of mysql.

Specially with

query_cache_size and table_cache

According to mysqltuner:

>>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.91-community-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 253M (Tables: 1771)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!!] Total fragmented tables: 2

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 4h 24m 22s (17M q [169.407 qps], 140K conn, TX: 2B, RX: 56M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 843.0M global + 5.2M per thread (300 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.3G (41% of installed RAM)
[OK] Slow queries: 0% (3/17M)
[OK] Highest usage of available connections: 28% (87/300)
[OK] Key buffer size / total MyISAM indexes: 128.0M/98.3M
[OK] Key buffer hit rate: 100.0% (706M cached / 344K reads)
[OK] Query cache efficiency: 88.2% (13M cached / 15M selects)
[!!] Query cache prunes per day: 40200
[OK] Sorts requiring temporary tables: 0% (12 temp sorts / 172K sorts)
[OK] Temporary tables created on disk: 10% (29K on disk / 288K total)
[OK] Thread cache hit rate: 99% (87 created / 140K connections)
[!!] Table cache hit rate: 0% (106 open / 20K opened)
[OK] Open file limit used: 0% (170/20K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 208.0K/1.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 512M) [see warning above]
    table_cache (> 4096)

The advise is to increase query_cache_size and table_cache. Yet it advises the same with a query_cache_size of 1024M and a table_cache of 128K!

Here is my.cnf

[mysqld]
delayed_insert_timeout=600
flush_time=14400
innodb_buffer_pool_size=1M
join_buffer_size=2M
key_buffer_size=128M
local-infile=0
log_slow_queries=/var/log/mysql-slow.log
max_allowed_packet=60M
max_heap_table_size=200M
myisam_sort_buffer_size=6M
open_files_limit=20K
query_cache_limit=32M
query_cache_min_res_unit=2048
query_cache_size=512M
read_buffer_size=1M 
read_rnd_buffer_size=1M 
safe-show-database
set-variable = max_connections=300
sort_buffer_size=1M
table_cache=4K
thread_cache_size=128
thread_concurrency=4
tmp_table_size=200M
wait_timeout=1200
interactive_timeout=2400
query_cache_type=1

[isamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size = 2M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size = 2M

The server has 6GB of ram and although in the last 24h there have been a max of 87 connections. On a monthly basis the max connection reaches 200 to 260.

I am doing something wrong, because the mysqltuner advise seems strange to me. Perhaps it is the flush, but I cannot figure it out.

What do you guys think?

[GaryT]

Change it to:

[mysqld]
delayed_insert_timeout=600
flush_time=14400
innodb_buffer_pool_size=1M
join_buffer_size=2M
key_buffer_size=128M
local-infile=0
log_slow_queries=/var/log/mysql-slow.log
max_allowed_packet=60M
max_heap_table_size=200M
myisam_sort_buffer_size=6M
open_files_limit=20K
query_cache_limit=32M
query_cache_min_res_unit=2048
query_cache_size=32M
read_buffer_size=1M
read_rnd_buffer_size=1M
safe-show-database
set-variable = max_connections=300
sort_buffer_size=1M
table_cache=4096
thread_cache_size=128
thread_concurrency=4
tmp_table_size=200M
wait_timeout=1200
interactive_timeout=2400
query_cache_type=1

[isamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size = 2M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size = 2M

Table_Cache does not need a value after the end, If anything use M and not K, 4K is tiny, Probably an avater or something...

query_cache_size
This is extremely high, I reduced this in the above.. so just make the changes and restart MySql server then run the tuner again, Roughly 24 hours later.

[johnburk]

Thank you Gary,

I see you changed

query_cache_size=32M
table_cache=4096

Reducing the query_cache size create a lot of prunes per day. I have already tried running for 48h with 32M, 64M, 128M, 256M, 512M and 1024M and yet mysqltuner advises more.


Most tutorials and optimization websites I read do not recommend a table_cache higher than 8096. Yet mysqltuner advises more. I have even tried 102400, yet it asks for more.

[GaryT]

It does not tell you to increase, It says what to adjust !

Look at what it says:

Increasing the query_cache size over 128M may reduce performance

And you tried 1024MB, I would say thats a little extreme.

It's like a circle, you increase 1 element then the next one needs increasing and such, Your best of starting fom minimal and working its way up, My my.cnf took over a week to get fully tuned, Its one thing that can take a long time to complete but by putting in high values will cause problems with others.

[johnburk]

Actually the advise of mysqltuner is

query_cache_size (> 512M)

So increase > it to more than 512M

With 32M within 1h and 22m I have

Query cache prunes per day: 832337

Mysql needs more cache to reduce number of prunes.

[GaryT]

Its telling you it's at large so why increase ? lol

If your going against the app why use it as you cleary do not want to do what it suggests.. So Ill say no more now.

[johnburk]

I though it advised to increase the cache, but warns that >128M reduces performance.


Do you another way to reduce query cache prunes per day without increasing the query_cache to >128mb?

[GaryT]

The warning.

Increasing the query_cache size over 128M may reduce performance

your setting:

query_cache_size=512M

Thats what I would call, EXTREMELY HIGH , I would not go higher than 120M

Also remove:

thread_concurrency=4

This setting does not exist in Linux.

Comment out:

#read_buffer_size=1M
#read_rnd_buffer_size=1M

For memory consumption

re-run your mysqltuner.pl and post the stats.

[johnburk]

I changed query_cache_size to 120M and removed the rest as adviced.

It has not been 24h yet, but here are my stats

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 253M (Tables: 1741)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!!] Total fragmented tables: 4

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 51m 38s (9M q [140.104 qps], 61K conn, TX: 2B, RX: 3B)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 507.0M global + 4.6M per thread (300 max threads)
[OK] Maximum possible memory usage: 1.8G (32% of installed RAM)
[OK] Slow queries: 0% (6/6M)
[OK] Highest usage of available connections: 26% (80/300)
[OK] Key buffer size / total MyISAM indexes: 128.0M/98.7M
[OK] Key buffer hit rate: 100.0% (1B cached / 144K reads)
[OK] Query cache efficiency: 84.4% (5M cached / 6M selects)
[!!] Query cache prunes per day: 353858
[OK] Sorts requiring temporary tables: 0% (23 temp sorts / 115K sorts)
[OK] Temporary tables created on disk: 17% (43K on disk / 245K total)
[OK] Thread cache hit rate: 99% (80 created / 61K connections)
[!!] Table cache hit rate: 5% (114 open / 2K opened)
[OK] Open file limit used: 0% (174/20K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 208.0K/1.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:
    query_cache_size (> 120M)
    table_cache (> 8196)

Andy advise on how I can reduce the Query cache prunes per day?

[GaryT]

MySQL started within last 24 hours - recommendations may be inaccurate

And this is your current my.cnf ?

[mysqld]
delayed_insert_timeout=600
flush_time=14400
innodb_buffer_pool_size=1M
join_buffer_size=2M
key_buffer_size=128M
local-infile=0
log_slow_queries=/var/log/mysql-slow.log
max_allowed_packet=60M
max_heap_table_size=200M
myisam_sort_buffer_size=6M
open_files_limit=20K
query_cache_limit=32M
query_cache_min_res_unit=2048
query_cache_size=32M
read_buffer_size=1M
read_rnd_buffer_size=1M
safe-show-database
set-variable = max_connections=300
sort_buffer_size=1M
table_cache=4096
thread_cache_size=128
thread_concurrency=4
tmp_table_size=200M
wait_timeout=1200
interactive_timeout=2400
query_cache_type=1

[isamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size = 2M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 2M
write_buffer_size = 2M

Update to:

[mysqld]
delayed_insert_timeout=600
flush_time=14400
innodb_buffer_pool_size=1M
join_buffer_size=2M
key_buffer_size=128M
local-infile=0
log_slow_queries=/var/log/mysql-slow.log
max_allowed_packet=64M
max_heap_table_size=128M
myisam_sort_buffer_size=64M
open_files_limit=8192
query_cache_limit=2M
query_cache_min_res_unit=2048
query_cache_size=64M
read_buffer_size=1M
read_rnd_buffer_size=1M
safe-show-database
set-variable = max_connections=300
sort_buffer_size=2M
table_cache=4096
thread_cache_size=128
thread_concurrency=4
tmp_table_size=200M
wait_timeout=100
interactive_timeout=400
query_cache_type=1

[isamchk]
key_buffer_size = 96M
sort_buffer_size = 96M
read_buffer_size = 16M
write_buffer_size = 16M

[myisamchk]
key_buffer_size = 96M
sort_buffer_size = 96M
read_buffer_size = 16M
write_buffer_size = 16M

[johnburk]

I used your my.cnf and it has been 28h and there are still a lot of

[!!] Query cache prunes per day: 232646

Tuning-primer says

However, 235154 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size

Most advice to limit query_cache to 128M are based on servers with 1GB ram. With 6GB of ram 256M should not be an issue. However I think my problem with this server is probably due to bad coding. So I'm going to look in to that.

Anyone else have another point of view regarding tips to reduce query cache prunes per day?

[GaryT]

I don't get why you use flush, And if you need to why have:

flush_time=14400

Thats, "Over The Top" is the word to decribe it ?

Same goes with:

delayed_insert_timeout=600

Thiscan cause issues alone.

I would honestly consider removing those.

thread_concurrency=4

does not exist on linux ! Correct me if I'm wrong.

Try the following:

[mysqld]
innodb_buffer_pool_size=1M
join_buffer_size=2M
key_buffer_size=128M
local-infile=0
log_slow_queries=/var/log/mysql-slow.log
max_allowed_packet=64M
max_heap_table_size=128M
myisam_sort_buffer_size=64M
open_files_limit=8192
query_cache_limit=2M
query_cache_min_res_unit=2048
query_cache_size=64M
read_buffer_size=1M
read_rnd_buffer_size=1M
safe-show-database
set-variable = max_connections=300
sort_buffer_size=2M
table_cache=4096
thread_cache_size=128
tmp_table_size=200M
wait_timeout=100
interactive_timeout=400
query_cache_type=1

[isamchk]
key_buffer_size = 96M
sort_buffer_size = 96M
read_buffer_size = 16M
write_buffer_size = 16M

[myisamchk]
key_buffer_size = 96M
sort_buffer_size = 96M
read_buffer_size = 16M
write_buffer_size = 16M