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

[Mark Perkins]

> On 26 Sep 2013, at 7:40 pm, "Simon Hobson" <linux at thehobsons.co.uk> wrote:
> 
> 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.
> 
[Mark] I might post the full mysqltuner output shortly (because to be honest it is probably a bit over my head) but on first read it had highest usage of available connections at 101/250 which seems to confirm that I did indeed hit the connection limit.

> 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.

[Mark] If I read my output correctly I have 48.7M per thread so multiply your figure by 10 would be somewhere north of 5G. If I recall correctly I have 2G RAM and 2G swap partition so I guess I have a problem there already. Although the database is on a SSD so that probably explains why I don't see any performance impacts (up until when the SSD fails from excessive reads / writes maybe?)

> 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.
> 
[Mark] The main recommendation seems to be to add more RAM but I am on a 32bit install so not sure adding more is going to be effective.

> 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.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users