Optimizing mySQL : What's In Your my.ini?

[Tim Tyler]

My mySQL config (on Windows) is a hodgepodge of settings that work, but I know it is far from optimized.

I running two IPB's on an Azure VM (small) with 1.75GB ram. Total db size when dumped is about 1GB. I average about 300 users in a 15 minute period according to IPB stats.

Here's my config:

[mysqld]

character-set-server=utf8
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=NONE
general-log=0
general_log_file="WEB-01.log"
slow-query-log=0
slow_query_log_file="slow.log"
long_query_time=10

max_connections=100
query_cache_size=16M
max_allowed_packet = 1M
table_open_cache = 256
tmp_table_size=10M
thread_cache_size = 8

myisam_max_sort_file_size=100G
myisam_sort_buffer_size = 64M

key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
sort_buffer_size = 1M

innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=40M
innodb_log_file_size=21M
innodb_thread_concurrency=8

Any suggestions are welcome!

[Makoto]

This is my primary servers configuration right now. (Dedicated server with 16GB of memory)

# MariaDB database server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
 
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
 
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
 
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
bind-address = 127.0.0.1
 
## Fine Tuning
max_connections = 512
connect_timeout = 30
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size       = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 512M
max_heap_table_size = 512M
thread_handling = pool-of-threads
userstat = 1
 
## MyISAM
myisam_recover          = BACKUP
key_buffer_size = 64M
#open-files-limit = 2000
table_open_cache = 4096
table_cache = 4096
table_definition_cache = 4096
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
join_buffer_size = 4M
 
## Query Cache Configuration
query_cache_limit = 128K
query_cache_size = 0M
query_cache_type = OFF
 
## Logging and Replication
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
log_warnings = 2
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit = 1000
log_slow_verbosity = query_plan
#log-queries-not-using-indexes
#log_slow_admin_statements
#server-id = 1
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
#log_bin = /var/log/mysql/mariadb-bin
#log_bin_index = /var/log/mysql/mariadb-bin.index
#sync_binlog = 1
#expire_logs_days = 10
#max_binlog_size         = 100M
#relay_log = /var/log/mysql/relay-bin
#relay_log_index = /var/log/mysql/relay-bin.index
#relay_log_info_file = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
 
## InnoDB
default_storage_engine = InnoDB
innodb_log_file_size = 1G
innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 64M
innodb_log_buffer_size = 32M
innodb_file_per_table = 1
innodb_open_files = 800
innodb_io_capacity = 800
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method = O_DIRECT
 
## Security Features
# chroot = /var/lib/mysql/
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
 
 
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
 
[mysql]
#no-auto-rehash
 
[isamchk]
key_buffer = 16M
 
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[AlexJ]

Isn't this bit small?

max_allowed_packet = 16M

And isn't this quite huge?

tmp_table_size = 512M

[Grumpy]

Any suggestions are welcome!

At a glance, I don't spot any glaring issues. Check out the sticky thread and try mysqltuner if you want to. Hard to say much more from that much info.

Isn't this bit small?

max_allowed_packet = 16M

And isn't this quite huge?

tmp_table_size = 512M

well... if that max allowed packet is working for him, I guess we don't have anything to argue. I have it larger, but then again, I have like 10,000 forums... so some administrative queries tend to get gigantic.

And he has 16GB of ram, he probably can afford 512MB for tmp tables. It's probably not full. But! I find the types of queries usually run by IPBs that gets stuck in there very poor for storing in tmp tables and just builds up lot of inefficient queries in there. So, I actually have it lower telling it to re-generate. Turns out it's better that way. It's likely that I need to investigate into join buffer and sort buffer instead... but too lazy. DB is far from the bottleneck anyway (for me, cuz ssd).

[Makoto]

And he has 16GB of ram, he probably can afford 512MB for tmp tables. It's probably not full. But! I find the types of queries usually run by IPBs that gets stuck in there very poor for storing in tmp tables and just builds up lot of inefficient queries in there. So, I actually have it lower telling it to re-generate. Turns out it's better that way. It's likely that I need to investigate into join buffer and sort buffer instead... but too lazy. DB is far from the bottleneck anyway (for me, cuz ssd).

Yeah, you're right. The tmp_table_size variable is unnecessarily high on my server, I've just never tuned it back down. I've found pretty much the same thing you have, the queries that get stuck there are poor for storing in tmp tables, and it does just seem to build up. Keeping it lower and forcing it to re-generate would be better.

Increasing join_buffer_size seemed to yield the most benefit for me, so I think it's probably worth looking into if you ever decide to tweak in the future.

[AlexJ]

Kirito will you be willing to share your mysqltuner output? Curious to see your table cache hit rate. Thanks!

[Makoto]

Kirito will you be willing to share your mysqltuner output? Curious to see your table cache hit rate. Thanks!

Sure,

 >>  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
[OK] Logged in using credentials from debian maintenance account.
 
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-MariaDB-1~wheezy
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 7M (Tables: 88)
[--] Data in InnoDB tables: 2G (Tables: 400)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 3M (Tables: 2)
[!!] Total fragmented tables: 104
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 15h 50m 12s (244M q [243.126 qps], 14M conn, TX: 1040B, RX: 53B)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 4.7G global + 11.3M per thread (512 max threads)
[OK] Maximum possible memory usage: 10.3G (65% of installed RAM)
[OK] Slow queries: 0% (93/244M)
[OK] Highest usage of available connections: 12% (62/512)
[OK] Key buffer size / total MyISAM indexes: 64.0M/691.0K
[OK] Key buffer hit rate: 100.0% (3M cached / 208 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (724 temp sorts / 6M sorts)
[OK] Temporary tables created on disk: 11% (451K on disk / 3M total)
[OK] Thread cache hit rate: 99% (3K created / 14M connections)
[OK] Table cache hit rate: 33% (1K open / 3K opened)
[OK] Open file limit used: 2% (238/8K)
[OK] Table locks acquired immediately: 99% (294M immediate / 294M locks)
[OK] InnoDB data size / buffer pool: 2.7G/4.0G

[Dmacleo]

Turns out it's better that way. It's likely that I need to investigate into join buffer and sort buffer instead... but too lazy. DB is far from the bottleneck anyway (for me, cuz ssd).

something I need to look at too, would appreciate you posting if you do get a chance to look. I am starting from a fairly fresh and stock mariadb install and have not yet had chance to run tuner to see how ts going.

[Grumpy]

something I need to look at too, would appreciate you posting if you do get a chance to look. I am starting from a fairly fresh and stock mariadb install and have not yet had chance to run tuner to see how ts going.

Well, one of the problem is that mysqltuner will keep on telling you to increase tmp tables despite that such action will make it consistently worse. I guess it's a bit like: why look through a big list of temporary tables we generated only to find nothing good?

[Makoto]

Well, one of the problem is that mysqltuner will keep on telling you to increase tmp tables despite that such action will make it consistently worse. I guess it's a bit like: why look through a big list of temporary tables we generated only to find nothing good?

Yep, that's exactly what happened with me. I kept trying to increase it bit by bit until I eventually figured that out.

The top answer here is worth reading over:

http://stackoverflow.com/questions/13259275/mysql-tmp-table-size-max-heap-table-size

[AlexJ]

How do u figure out what is good value for table cache hit rate?

Mine is:

[OK] Table cache hit rate: 22% (1K open / 7K opened)

Issue is, over the period of 10 days it drops to 10% with like 13K opened tables. Table cache hit rate is one of the areas where I was never able to optimize it right.

[Dmacleo]

Well, one of the problem is that mysqltuner will keep on telling you to increase tmp tables despite that such action will make it consistently worse. I guess it's a bit like: why look through a big list of temporary tables we generated only to find nothing good?

thats what I was seeing also and had been wondering about.

[Grumpy]

How do u figure out what is good value for table cache hit rate?

Mine is:

[OK] Table cache hit rate: 22% (1K open / 7K opened)

Issue is, over the period of 10 days it drops to 10% with like 13K opened tables. Table cache hit rate is one of the areas where I was never able to optimize it right.

Well, ideally, it a good value would be 100%. lol But I don't think we can ever achieve that with IPB.

Your sweet spot may be around 1.5k to 3k max openable tables. In megabytes, I don't know that is, but it's going to be very small like <5MB. I calculated this by looking at your hit rate at 7k and at 13k. 22% of 7k is 1.5k and 1.3k for 10% of 13k and simply doubled it as max. My theory is that there is a few set of caches that are hit often and are useful. And then there are everything else that's completely inefficient and just fills it up. Mysql and mariadb uses tiered LRU to sort these tmp tables. So the useful ones that are hit often are in the front and keeps on getting hit. Whereas the useless ones will keep getting pushed out back of the cache as there no room keeping the list short. But this isn't about increasing hit rates. It's about increasing performance. Your hit rates will be even worse with this method.

Note. The more you fine tune your setup, the more frequently you need to tune it to adapt to changes.

--------------------------

Mysqltuner doesn't realize that IPB is building useless queries in there. So, when it sees that it's full, it just tells you that you need more of it and will keep on encouraging higher hit rates, which in general is better.

[Makoto]

Tuning table_cache, like many things with MySQL, can be interestingly difficult. (4 year old article, take it with a grain of salt, things may have dramatically improved since then)

There are still some known issues where setting table_open_cache too high can have a very negative performance impact, which is the opposite of what you might expect.

What's the best value to use considering this? Who knows. If you really care, you'll have to test and see what works best for you.

mysqltuner can be a great tool, but don't follow the advice it may give you blindly.

For example,

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
    query_cache_size (>= 8M)

You can see I have query_cache intentionally disabled in my configuration, but mysqltuner is telling me I should enable it (or it's just complaining that the variable is too low even though it's disabled, I'm not entirely sure to be honest. This is probably worth mentioning either way.) Query cache can be beneficial for simple websites, but for complex applications such as IP.Board, it can be detrimental to performance.

I believe this is the case with IP.Board, which is why I don't use it.

It also recommends I run OPTIMIZE TABLE to defragment tables for better performance. Ignoring the fact that my database server runs on a SSD and therefor fragmentation doesn't really matter, running OPTIMIZE TABLE on InnoDB tables is generally a bad idea. (IP.Board also has a scheduler task that runs OPTIMIZE queries once a day, but the task automatically disables itself when using InnoDB.)

[AlexJ]

Which tables you are using for InnoDB? Also are you using any sphinx or anything? Sorry for hijacking topic but searching for 98.64.54 is pain in da butt on my forums because it has "dots" with numbers...

8K opened tables and sometimes it goes to 15K over the time of 10 days since i also run opencart on same machine. RAM wise i have 32GB RAM but I don't have SSD yet on same machine.

Your QPS are much higher then mine :P Once i hit 100QPS i am thinking to add SSD but till then regular HD in RAID 1 just works fine for me.

[OK] Table cache hit rate: 30% (2K open / 8K opened)
[OK] Open file limit used: 13% (3K/24K)

Anyone got any suggestions for my current values?

open_files_limit = 9000
table_definition_cache = 8000
#increase file descriptor limit
table_open_cache = 12000

I am trying to get more then 50% table cache hit rate :)

[RevengeFNF]

I got a VPS with 3 cores and 4Gb of Ram.

Here is my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=160
max_user_connections=70
wait_timeout=30
interactive_timeout=50
long_query_time=5
log-queries-not-using-indexes

query_cache_size=128M
tmp_table_size=140M
max_heap_table_size=140M
thread_cache_size = 8
join_buffer_size =8M
read_buffer_size=128K
table_open_cache=460
innodb_buffer_pool_size =700M
query_cache_limit =6M
key_buffer_size =960M

#innodb_use_native_aio = 0
innodb_file_per_table

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

mysqltuner output

 >>  MySQLTuner 1.3.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.37
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 952M (Tables: 9)
[--] Data in InnoDB tables: 607M (Tables: 191)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 13M (Tables: 1)
[!!] Total fragmented tables: 38

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 15h 55m 6s (1M q [11.414 qps], 51K conn, TX: 33B, RX: 485M)
[--] Reads / Writes: 52% / 48%
[--] Total buffers: 1.9G global + 10.6M per thread (160 max threads)
[!!] Maximum possible memory usage: 3.6G (88% of installed RAM)
[OK] Slow queries: 2% (48K/1M)
[OK] Highest usage of available connections: 6% (11/160)
[OK] Key buffer size / total MyISAM indexes: 960.0M/548.2M
[OK] Key buffer hit rate: 99.9% (48M cached / 42K reads)
[OK] Query cache efficiency: 38.9% (368K cached / 947K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 42K sorts)
[OK] Temporary tables created on disk: 8% (2K on disk / 34K total)
[OK] Thread cache hit rate: 99% (11 created / 51K connections)
[OK] Table cache hit rate: 87% (452 open / 514 opened)
[OK] Open file limit used: 7% (79/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 700.0M/608.0M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries

[RevengeFNF]

In June i posted my my.cnf in the post above.

Since then i've been reading a lot about mysql performance, specially  in the Percona blog.

My board was already fast, but i want it even faster and i think i did it. One of the things i noticed, i was having 2% of Slow Queries and now i have 0%. Before:  Slow queries: 2% (48K/1M) - Now: Slow queries: 0% (8/2M)

First thing i did, was to convert all MyISAM tables to InnoDB. You can see from the post above that i was using only 9 tables with myisam, but one of them was the post table. If you convert this table and you are on mysql 5.5 or below, be sure to use Sphinx for fulltext searchs, because innodb does not support it on that mysql versions.

After that i needed to tune my.cnf to innodb.

First Thing, the most importing value to tune is the InnoDB Buffer Pool Size. This value, the bigger the better. Its safe to use something like 70/80% of your total memory to this. My InnoDB Tables occupies 2.1Gb and i set this value to 2.7Gb.

I  changed the InnoDB Flush Method to O_DIRECT. This prevents double buffering from the Pool Buffer and the OS, saving I/O.

I changed the Innodb Pool Instances to 2. This means it will divide the Pool Buffer in 2. In my case, instead of having 1 pool of 2.7Gb, it will have  2 pool's of 1.35Gb each. This is faster, but you need to make sure each pool have at least 1Gb. 

Another thing we need to change is the Innodb Log File Size. The default in Mysql 5.5 is only 5Mb which is really very low and is bad for I/O. The common sense is to have at least one hour for logs. 5Mb was giving me only 5 minutes of logs. I changed this value to 256Mb. Note that on Mysql version below 5.5, a big log file size will make the recovery crashes very slow. On 5.5 and above this is not a problem.

I also changed the read io theads and write io threads from the default 4 to 8(is the default in percona).

Before i was using 2Mb for the Sort Buffer. In the Percona Blog i've seen some benchmarks and a value above 256k can do more harm than good. Even if mysqltuner for example says to raise this value, don't do it. I changed it to 256K. I also changed the Join Buffer Size from 8Mb to 2Mb, for the same reason. A bigger value than you need can cause more harm.

Last but also important, is the Query Cache. The common sense is that if the Query Cache Efficiency is below 50%, its better to disable it. If its above, its better to keep it. I was having 39%, so i should disabled, but i decided to tune it. So i changed the Query Cache Min Res Unit to 1k. This solved 2 problems i was having with Query Cache. The efficiency raised to 61/68% and stopped the fragmentation i was having in it.

Im still reading blogs regarding mysql performance, so i will probably change more things in the future.

This is my current my.cnf 

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=120
max_user_connections=70
wait_timeout=30
interactive_timeout=50

#Slow Queries
slow_query_log = 1
slow_query_log_file=/var/log/mysq-slow.log
long_query_time=5

#InnoDB
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size =2800M
innodb_log_file_size=256M
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_buffer_pool_instances=2

#MyISAM
key_buffer_size =40M

#Thread Cache
thread_cache_size = 8

#Table cache
table_open_cache=1000
open_files_limit =2000

#Buffers
join_buffer_size =2M
read_buffer_size=128K
sort_buffer_size=256K

#Query Cache
query_cache_size=128M
query_cache_limit =6M
query_cache_min_res_unit=1k

[Machsterdaemon]

I have a very simple web server use for my own sites and always use the innodb engine.

I'm using MySQL 5.6.21 on my current server but I'm planning to setup a new server next month with MariaDB 10 instead when I move from a VPS to a dedi.

The last two entries on the bottom are inserted automatically by the system but not me.

[mysqld]
ft_min_word_len=3
optimizer_search_depth=0

# INNODB
innodb_log_file_size=128M
innodb_buffer_pool_size=768M
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
innodb_file_format=Barracuda
innodb_file_format_check=1
innodb_large_prefix=1

# CHARSET
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake

# Connection variables
max_connections=50
wait_timeout=120

# Query Cache
query_cache_size=0
query_cache_type=0

# Slow Query Log
long_query_time=3
slow-query-log=1

# Binary log/replication
log-bin="/var/lib/mysql/binlog"
expire_logs_days=10
sync_binlog=1
sync_relay_log=1
relay_log_info_repository=TABLE
master_info_repository=TABLE
binlog_format=ROW
transaction-isolation=READ-COMMITTED
innodb_autoinc_lock_mode=2

# Security variables
local-infile=0
max_allowed_packet=268435456
open_files_limit=10000

 

[RevengeFNF]

Just to tell that i was using MySQL 5.5 and changed to MariaDB 10, and wow... What a huge diference...

[ASTRAPI]

MariaDB 10 is a lot better