Mysql optimizing

Will

Active member
Just looking for some tips on improving my mysql settings.

my.cnf
Code:
[mysqld]
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=800M
innodb_file_per_table=1
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
myisam_sort_buffer_size=16M
expire_logs_days=7
query_cache_size=32M
thread_cache_size=16K
max_allowed_packet=16M
local-infile=0
table_open_cache=2K
table_definition_cache=3K
open_files_limit=2K
max_connections=100
wait_timeout=30
interactive_timeout=60
connect_timeout=10
read_buffer_size=2M
slow_query_log=1
slow_query_log_file="/var/log/slow_queries.log"
sort_buffer_size=2M
join_buffer_size=4M
key_buffer_size=128M
query_cache_limit=4M
query_cache_type=1
long_query_time=5
tmp_table_size=128M
max_heap_table_size=128M
 
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
 
[mysqldump]
max_allowed_packet=16M
quick
 
[mysql]
no-auto-rehash
 
[mysqlhotcopy]
interactive-timeout

mysqltuner
Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.24-1~dotdeb.1-log
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 37M (Tables: 93)
[--] Data in InnoDB tables: 300M (Tables: 321)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 5M (Tables: 8)
[!!] Total fragmented tables: 183
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 3h 16m 24s (59M q [75.936 qps], 4M conn, TX: 578B, RX: 8B)
[--] Reads / Writes: 84% / 16%
[--] Total buffers: 1.1G global + 8.5M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.9G (76% of installed RAM)
[OK] Slow queries: 0% (142/59M)
[OK] Highest usage of available connections: 10% (10/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/22.4M
[OK] Key buffer hit rate: 100.0% (43M cached / 12K reads)
[OK] Query cache efficiency: 54.6% (19M cached / 34M selects)
[!!] Query cache prunes per day: 53192
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 430K sorts)
[!!] Joins performed without indexes: 19730
[!!] Temporary tables created on disk: 49% (309K on disk / 622K total)
[OK] Thread cache hit rate: 99% (10 created / 4M connections)
[!!] Table cache hit rate: 14% (762 open / 5K opened)
[OK] Open file limit used: 6% (275/4K)
[OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
[!!] Connections aborted: 8%
[OK] InnoDB data size / buffer pool: 300.7M/800.0M
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    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
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 4.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    table_cache (> 2048)

Running a VPS with 2.5GB RAM. Any suggestions would be appreciated. :)
 
Here are some of my suggestions based on the data:

- Lower max_connections to around 25
- Lower key_buffer_size to 32M
- Increase query_cache_size to around 64M
- Increase table_cache to 4096 or 8192

tmp_table_size is probably fine where it is. It's hard to say because most of those temp tables are likely due to fulltext searching, and those will never use in-memory temp tables, meaning that increasing the value would not help those. Your database seems relatively small, so 128 MB should be plenty. Indeed, if this is all that is running on your VPS, 2.5 GB of RAM is probably unneeded. You won't be able to use all of it. More RAM is never a bad thing in terms of performance, though you might be able to cut costs if you downsize (depending on current VPS plan, provider, costs, etc)
 
Here are some of my suggestions based on the data:

- Lower max_connections to around 25
- Lower key_buffer_size to 32M
- Increase query_cache_size to around 64M
- Increase table_cache to 4096 or 8192

tmp_table_size is probably fine where it is. It's hard to say because most of those temp tables are likely due to fulltext searching, and those will never use in-memory temp tables, meaning that increasing the value would not help those. Your database seems relatively small, so 128 MB should be plenty. Indeed, if this is all that is running on your VPS, 2.5 GB of RAM is probably unneeded. You won't be able to use all of it. More RAM is never a bad thing in terms of performance, though you might be able to cut costs if you downsize (depending on current VPS plan, provider, costs, etc)

Thanks, I will try your suggestions. The only thing is the max_connections. The number should be somewhere around the amount of users are online, correct?

To be clear about the server, it is actually 1GB dedicated and 2.5 burst. It's an unmanaged VPS so it's only like $18 a month so not a huge deal. :)
 
> The number should be somewhere around the amount of users are online, correct?

Not really. This is the number of concurrent MySQL connections being made to the MySQL server. Since most of the time spent online is not actually time spent doing MySQL queries, this is likely going to be much lower than the number of users online.
User online != active MySQL connection

Here is the pertinent line:

[OK] Highest usage of available connections: 10% (10/100)

In 9 days of being up, the server has only hit 10 connections in use at once. That's why I suggested 25. Still plenty of room to grow.
 
Thanks, I will try your suggestions. The only thing is the max_connections. The number should be somewhere around the amount of users are online, correct?... :)
Even at 500 online users, you probably don't need more than 50 max_connections.
 
> The number should be somewhere around the amount of users are online, correct?

Not really. This is the number of concurrent MySQL connections being made to the MySQL server. Since most of the time spent online is not actually time spent doing MySQL queries, this is likely going to be much lower than the number of users online.
User online != active MySQL connection

Here is the pertinent line:

[OK] Highest usage of available connections: 10% (10/100)

In 9 days of being up, the server has only hit 10 connections in use at once. That's why I suggested 25. Still plenty of room to grow.

Thanks for the clarification.
 
I would suggest trying these lower values too:

PHP:
read_buffer_size=1M
sort_buffer_size=1M
join_buffer_size=1M
 
I would suggest trying these lower values too:

PHP:
read_buffer_size=1M
sort_buffer_size=1M
join_buffer_size=1M

I made these suggested changes.

new my.cnf
Code:
[mysqld]
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=800M
innodb_file_per_table=1
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
myisam_sort_buffer_size=16M
expire_logs_days=7
query_cache_size=64M
thread_cache_size=16K
max_allowed_packet=16M
local-infile=0
table_open_cache=8K
table_definition_cache=3K
open_files_limit=2K
max_connections=25
wait_timeout=30
interactive_timeout=60
connect_timeout=10
read_buffer_size=1M
slow_query_log=1
slow_query_log_file="/var/log/slow_queries.log"
sort_buffer_size=1M
join_buffer_size=1M
key_buffer_size=32M
query_cache_limit=4M
query_cache_type=1
long_query_time=5
tmp_table_size=128M
max_heap_table_size=128M
 
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
 
[mysqldump]
max_allowed_packet=16M
quick
 
[mysql]
no-auto-rehash
 
[mysqlhotcopy]
interactive-timeout

mysqltuner output
Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.24-1~dotdeb.1-log
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 38M (Tables: 93)
[--] Data in InnoDB tables: 306M (Tables: 322)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 2M (Tables: 8)
[!!] Total fragmented tables: 186
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 22h 0m 36s (9M q [59.762 qps], 731K conn, TX: 93B, RX: 1B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 1.0G global + 3.5M per thread (25 max threads)
[OK] Maximum possible memory usage: 1.1G (44% of installed RAM)
[OK] Slow queries: 0% (36/9M)
[OK] Highest usage of available connections: 40% (10/25)
[OK] Key buffer size / total MyISAM indexes: 32.0M/22.6M
[OK] Key buffer hit rate: 99.9% (5M cached / 3K reads)
[OK] Query cache efficiency: 63.4% (3M cached / 6M selects)
[!!] Query cache prunes per day: 20882
[OK] Sorts requiring temporary tables: 0% (4 temp sorts / 64K sorts)
[!!] Joins performed without indexes: 3519
[!!] Temporary tables created on disk: 49% (48K on disk / 97K total)
[OK] Thread cache hit rate: 99% (10 created / 731K connections)
[OK] Table cache hit rate: 21% (535 open / 2K opened)
[OK] Open file limit used: 1% (246/16K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] Connections aborted: 11%
[OK] InnoDB data size / buffer pool: 306.6M/800.0M
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_size (> 64M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)

Are there any major issues that stand out here?
 
It looks like you could increase the query cache some more. Perhaps to 128M. Other than that, most things seem pretty good.
 
It looks like you could increase the query cache some more. Perhaps to 128M. Other than that, most things seem pretty good.

Just made that change. I was looking at one thing in the recommendations.

Your applications are not closing MySQL connections properly

Should I worry about this? If so, how could I fix it?
 
Your applications are not closing MySQL connections properly

Should I worry about this? If so, how could I fix it?

Never saw this with Xenforo. Do you have addons installed?
Is your site very slow so it times out a lot?
 
Never saw this with Xenforo. Do you have addons installed?
Is your site very slow so it times out a lot?

Didn't see it on the server I was previously on. Yes, I have addons installed.
To me the site doesn't seem slow but I can't be online 24/7 so I can't be entirely sure. I did have one member just last night say that they receive this error a lot now. "The following error occurred: The server did not respond in time. Please try again."
 
If you've enabled error logging and slow query logging check the logs, they might give you a clue as to what is causing the disconnects. ;)
 
Just a thought, looking at your [wait_timeout=30] - try increasing it to 45 (seconds) and see if you still get disconnects?

You might find that search queries from your XF site search (which can be fairly intensive at times) are exceeding the MySQL wait timeout and causing the disconnects. I'm not saying that is the case, it could be an add-on, a query from the ACP or something else, but extending the timeout might eradicate the problem for you by giving the query extra time to complete.

Cheers,
Shaun :D
 
I have noticed the last few days that the load has been over 1 consistently when it usually doesn't really go over .8 at all. Top command says that mysqld is using over 80% cpu. My my.cnf hasn't changed much except for the suggestions made here. Any ideas?
 
I would guess it's caused by the leap second that we had recently. I had one machine where MySQL did that. I rebooted the server and it was fine.
 
Top Bottom