Please help me improve MySQL settings

Amin Sabet

Well-known member
I know very little about MySQL and would greatly appreciate some help adjusting the settings in my.cnf.

Everything's on one server. I don't have a separate server for MySQL. The server is "bare metal", not virtualized, and has the following specs:

240 GB on-board SSD
2TB "Nearline" SAS
Intel Xeon E5-2640 v2 Processor
RAM: 32 GB 1866 MHz DDR3

Current my.cnf:

Code:
[mysqld]
ft_min_word_len = 1
ft_stopword_file = "/dev/null"
tmpdir = "/tmp"
skip-networking
local-infile = 0
back_log = 100
symbolic-links=0
table_open_cache=8192
thread_cache_size=50
max_connections=200
innodb_buffer_pool_size=8192M
query_cache_type=1
query_cache_size=128M
query_cache_limit=8M
join_buffer_size=1M
table_definition_cache=6384
tmp_table_size=512M
max_heap_table_size=512M
innodb_thread_concurrency=0
key_buffer_size=1024M
max_allowed_packet=32M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=8M
myisam_sort_buffer_size=64M


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Output from Tuning-primer:

Code:
        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.5.42-cll x86_64

Uptime = 1 days 9 hrs 18 min 3 sec
Avg. qps = 55
Total Questions = 6683272
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 6683293 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 50
Current threads_cached = 22
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 200
Current threads_connected = 2
Historic max_used_connections = 24
The number of used connections is 12% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 673 M
Current InnoDB data space = 2.37 G
Current InnoDB buffer pool free = 75 %
Current innodb_buffer_pool_size = 8.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 9.45 G
Configured Max Per-thread Buffers : 2.58 G
Configured Max Global Buffers : 9.14 G
Configured Max Memory Limit : 11.72 G
Physical Memory : 31.32 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 1.16 G
Current key_buffer_size = 1.00 G
Key cache miss rate is 1 : 5021
Key buffer free ratio = 69 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 95 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 74.68 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 8 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly

OPEN FILES LIMIT
Current open_files_limit = 16594 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 8192 tables
Current table_definition_cache = 6384 tables
You have a total of 2015 tables
You have 2710 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 512 M
Current tmp_table_size = 512 M
Of 100697 temp tables, 20% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 96 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 6503
Your table locking seems to be fine


Output from mysqlmymonlite is attached (too long to post)
 

Attachments

@hellreturn - thanks, I just ran that and got the output below. I am not sure how to follow the recommendations though.

Code:
>>  MySQLTuner 1.4.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
[OK] Currently running supported MySQL version 5.5.42-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 1G (Tables: 672)
[--] Data in InnoDB tables: 2G (Tables: 1268)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 35M (Tables: 34)
[!!] Total fragmented tables: 1173

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 12h 58m 21s (7M q [56.003 qps], 697K conn, TX: 123B, RX: 2B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 9.6G global + 13.2M per thread (200 max threads)
[OK] Maximum possible memory usage: 12.2G (39% of installed RAM)
[OK] Slow queries: 0% (0/7M)
[OK] Highest usage of available connections: 12% (24/200)
[OK] Key buffer size / total MyISAM indexes: 1.0G/1.2G
[OK] Key buffer hit rate: 100.0% (710M cached / 133K reads)
[OK] Query cache efficiency: 66.7% (2M cached / 4M selects)
[!!] Query cache prunes per day: 117950
[OK] Sorts requiring temporary tables: 0% (881 temp sorts / 282K sorts)
[!!] Temporary tables created on disk: 26% (29K on disk / 110K total)
[OK] Thread cache hit rate: 99% (24 created / 697K connections)
[OK] Table cache hit rate: 65% (2K open / 4K opened)
[OK] Open file limit used: 8% (1K/16K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[OK] InnoDB buffer pool / data size: 8.0G/2.4G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 128M)

"Temporary table size is already large - reduce result set size"

How?

"Reduce your SELECT DISTINCT queries without LIMIT clauses"

How?

"Variables to adjust: query_cache_size (> 128M)"

Change to 256M?
 
I would convert all myisam tables to innodb and optimize the server for innodb only.

You can't do much about the temporary tables, thats mostly a query issue (how the queries are written). XenForo is not perfect for that. But it also does not harm that much.

Your query cache is small but also not very efficient. Try to raise it to 512 M or even a gig and look at Query cache efficiency again in a few days.

Also your query_cache_limit is much to small for caching frequent XenForo results.
 
So, based on the recommendations so far, I should make the changes in red below? I altered a couple suggestions to make the changes smaller in magnitude.

Rich (BB code):
[mysqld]
ft_min_word_len = 1
ft_stopword_file = "/dev/null"
tmpdir = "/tmp"
skip-networking
local-infile = 0
back_log = 100
symbolic-links=0
table_open_cache=8192
thread_cache_size=50
max_connections=200
innodb_buffer_pool_size=8192M
query_cache_type=1
query_cache_size=256M
query_cache_limit=16M
join_buffer_size=1M
table_definition_cache=6384
tmp_table_size=256M
max_heap_table_size=256M
innodb_thread_concurrency=0
key_buffer_size=1536M
max_allowed_packet=32M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=8M
myisam_sort_buffer_size=64M


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
Is there something I can do with my.cnf settings that will make a noticeable difference? I did notice that repairing tables the other day made a huge difference.

The biggest problem is your myisam support. This is why it won't be possible to create a perfectly configured MySQL server. If you only run XenForo at your database server (with Enhanced Search), you can convert all tables to innodb without a problem. Without Enhanced Serach you have to be sure you run MySQL 5.6.4 or higher.

If I were you and had to support both table types, I would just optimize the query cache (up to at least 1G and the limit depends on your board size, just test few values) and leave the rest as it is. I would prop. also raise innodb_buffer_pool_size to 16G (you have 32G RAM, if you ran innodb only, I would raise it to 24G and minimize all myisam memory settings).
 
The biggest problem is your myisam support. This is why it won't be possible to create a perfectly configured MySQL server. If you only run XenForo at your database server (with Enhanced Search), you can convert all tables to innodb without a problem. Without Enhanced Serach you have to be sure you run MySQL 5.6.4 or higher.

If I were you and had to support both table types, I would just optimize the query cache (up to at least 1G and the limit depends on your board size, just test few values) and leave the rest as it is. I would prop. also raise innodb_buffer_pool_size to 16G (you have 32G RAM, if you ran innodb only, I would raise it to 24G and minimize all myisam memory settings).
Disagree with this. My experience with the query cache with tables where you get lots of updates, or on servers with lots of cores, is that it hinders performance. For any table that receives an update everything in the query cache for that table is flushed. It also does not scale with a single mutex across multiple cores. Finally, the overhead of managing a large query cache (in the hundreds of MB) negates any possible performance improvement.

TL;DR

The query cache does not improve performance by default, it can incur a performance penalty depending on your MySQL workload. Perform all tests with and without the query cache.

http://dev.mysql.com/doc/refman/5.6/en/query-cache.html
http://www.percona.com/blog/2015/01...works-and-workload-impacts-both-good-and-bad/
 
Correct. I also would not necessarily activate a query cache in an optimized MySQL server. Depends on the data you store and query. However it may make sense with XF where most work is read.

To optimize a server you have to try and check many settings, before you may be satisfied for some time. ;)
 
Last edited:
I keep query cache disabled on my servers since 50-80% of query results are different most of the time. Query cache can be helpful if you run wordpress or sites which would keep displaying same results to visitors.

In forums data changes and hence I keep query cache disabled. No look up penalty.
 
The biggest problem is your myisam support. This is why it won't be possible to create a perfectly configured MySQL server. If you only run XenForo at your database server (with Enhanced Search), you can convert all tables to innodb without a problem. Without Enhanced Serach you have to be sure you run MySQL 5.6.4 or higher.

Update:

I deleted my non-Xenforo related databases (not needed anymore as all my active sites are now Xenforo).

@MattW has installed Enhanced Search on all my sites and is converting my databases to MariaDB

Everything about the move to XenForo has been one good thing after another!

Thanks for the help!
 
Back
Top Bottom