[mythtv-users] Tuning MySQL for 0.25

[Dan Wilga]

On 6/28/12 10:04 AM, Andre wrote:
> I've googled for articles reducing mysql memory footprint but everything I find is about fitting in 256MB and rather out of date. I've tried starting out with the example my-small.cnf and my-media.cnf files but as soon as max_connections=100 is added memory footprint goes through the roof. Currently I'm running with max_connections=60 which seemed the maximum that I could get away with in 5GB and is a few more connections than I have seen through mysqltuner.pl so far.
>
> So what are people running with for this?
> Can I ignore the max footprint?
> Does MythTV really need 100 connections?
>
I have to wonder why mysqltuner is reporting anything even close to 50 
max connections. Using this command:

   watch 'echo show full processlist | mysql --user=mythtv 
--password=mythtv'

I can see that my backend, while idle, has 5 connections. With one 
frontend running (and idle), this goes up to 8. I'd imagine this going 
up for every transcode or commflag process, as well. On my server:

   SHOW GLOBAL STATUS WHERE variable_name LIKE 'max_used_connections'

returns 38--and this includes a period of time where I was accidentally 
running two instances of the backend. So I still don't see how you ever 
got to 60 connections. Are you using this DB for things other than MythTV?

As far as the necessity of having the theoretical maximum of memory 
available, to be honest, I run a high-traffic production web site that 
uses about 18 Gb of RAM and has a theoretical max of several hundred Gb. 
The max. memory statistic is really just the worst possible case; your 
chances of ever hitting it are pretty small.

-- 
Dan Wilga                                                        "Ook."