Tune MySQL

From MythTV Official Wiki
Jump to: navigation, search

Problem

Poor performance when using a default MySQL 5.5 installation with MythTV.

Some Details

On some pre-packaged installations like Mythbuntu the default MySQL settings do not take into account Innodb being a default database engine. When the databases are created, the old MyISAM settings do nothing for the new database in Innodb format. Because of this, ALL of the defaults for the Innodb engine get used, and are really horrible. Below is a basic beginning for drastically increasing performance for MySQL if the Innodb engine is being used. Keep in mind that there are many ways of tuning MySQL, and this is just one. These are "fair to good" tuning parameters specific to Innodb. You can get much more speed out of MySQL, but it will take some time to get the settings to mesh well with your particular hardware configuration.

Expected Gains

When I added these settings into my installation, the biggest gain I noticed was in the Videos section. The UI was much faster, as well as the Search For New Videos. I also use an external player (mplayer2) for MKV videos, and had always noticed a lag from when the video exited to when the UI was redrawn. The lag has almost disappeared. I have not done a new mythfilldatabase yet, but I suspect that will also have a performance gain as well. The logging as also been enabled so you can find slow queries, as well as understand how Myth talks to the MySQL instance. This may be helpful in back-tracking bugs or problems in your installation.

Solution

Warning.png
MySQL v8 (Ubuntu 20.04+) see *** below Deprecations

Find MySQL config files that MythTV is using. Usually in a default installation look for something like /etc/mysql/conf.d/mythtv.cnf

These settings are loaded after the initial my.cnf file and will override settings in there, so we are only going to do a small amount of engine tuning. This will increase some buffers from what is in the initial file for MySQL in general, and get some decent basics in place for Innodb.

mv /etc/mysql/conf.d/mythtv.cnf /etc/mysql/conf.d/mythtv.cnf.old

nano /etc/mysql/conf.d/mythtv.cnf

Paste in what is below, and restart MySQL:

[mysqld]
# Customized config for MythTV baseline

bind-address=0.0.0.0
skip-external-locking
key_buffer_size		= 64M
tmp_table_size		= 32M	
table_cache            	= 256
query_cache_limit	= 3M ***
query_cache_size        = 32M ***


# Set Base Innodb Specific settings here		   
innodb_flush_method		= O_DIRECT		   
innodb_file_per_table		= 1			   
innodb_file_format		= barracuda		   
innodb_max_dirty_pages_pct 	= 90			   
innodb_lock_wait_timeout 	= 20			      
innodb_flush_log_at_trx_commit 	= 2			      
innodb_additional_mem_pool_size = 16M			      
innodb_buffer_pool_size 	= 128M			      
innodb_thread_concurrency 	= 8	

# Logging Options
log-queries-not-using-indexes
log_error
long_query_time 	= 5
log_slow_queries	= /var/log/mysql/mysql-slow.log
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1			
log_error               = /var/log/mysql/error.log
binlog_do_db		= include_database_name