Tune MySQL
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
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
