[mythtv-users] Mysql max-connections and bind-address

[Simon Hobson]

Gary Buhrmaster wrote:
>Each specific configuration will vary, but starting about v0.25(ish)
>there were some situations where 100 (which is the default
>for at least some versions of mysql/mariadb, specified or not),
>was just a little to little.  Each release changes things somewhat,
>but I would bump the value to 250 just to be safe (it is possible
>the change the value on the fly; read the mysql/mariadb docs
>if you want to do that).

Wow, that's some change ! I'm still on 0.24, and according tomysqltuner.pl (http://mysqltuner.com/)in the last 2 days I've not exceeded 12 of an available 20 connections.

There is a memory requirement per available connection, which according tomysqltuner is currently 4.6M for my setup. That means 100 connections would eat up getting on for half a gig of ram on top of your global memory useage. The latter is almost a gig for me as I've uppedsysvar_tmp_table_size andmax_heap_table_size to 1/2G to try and avoid lots of disk I/O during rescheduling. It appears that MySQL doesn't grab all that memory when it's not used, but you need to be aware of how much it could take at peak times.

There's a lot of tuning you can do with MySQL, and it can make a huge difference to performance. The defaults are waaay top low for anything but a really trivial Myth system. With the caveat that I'm no expert on MySQL, I'd suggest looking at the following settings :
key_buffer
key_buffer_size
max_allowed_packet
thread_stack
thread_cache_size
max_connections
table_cache
join_buffer_size
tmp_table_size
max_heap_table_size

mysqltuner will advise on most of these - or at least show you the current settings and how they relate to your usage. Personally I've really struggled to work out what uses what, and what the totals would be for various settings - so I've tended to rely on mysqltuner to (at the very least) tell me what the max usage is.

As an example,key_buffer_size sets the size of the memory reserved for holding indexes - and according to the docs defaults to just 8M. According to mysqltuner, my indexes total 284M at the moment, so I've upped the value to something larger than that with some growth room. In theory, this means the indexes will live permanently in memory - which means any indexed search or join should never need to hit the disk for the index data.
I suspect there's a similar setting that (if your tables are small enough) would allow you to keep the data itself in memory - though I haven't figured out what it might be yet.

It's best to tune MySQL to keep it's own caches. While the OS will cache database reads - this cached data **will** get flushed fairly quickly on most systems due to the size of the video files being handled.