[mythtv-users] Tuning MySQL for 0.25

[Andre]

On 28 Jun 2012, at 18:45, Dan Wilga wrote:

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

No, only MythTV, I have one frontend, occasionally use the local frontend on the backend machine a laptop & a couple of iPads but they use iOS torc so I don't know how much database activity that causes, oh and lots of mythweb use. The worst connections I've seen has been 58 and just now I get this: 53!

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 6m 38s (1M q [27.492 qps], 1K conn, TX: 4B, RX: 560M)
[--] Reads / Writes: 20% / 80%
[--] Total buffers: 74.0M global + 66.6M per thread (60 max threads)
[OK] Maximum possible memory usage: 4.0G (81% of installed RAM)
[OK] Slow queries: 0% (6K/1M)
[!!] Highest connection usage: 88%  (53/60)
[OK] Key buffer size / total MyISAM indexes: 32.0M/263.0M
[OK] Key buffer hit rate: 99.7% (99M cached / 313K reads)
[OK] Query cache efficiency: 94.2% (1M cached / 1M selects)
[!!] Query cache prunes per day: 509
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[!!] Joins performed without indexes: 471
[OK] Temporary tables created on disk: 11% (586 on disk / 4K total)
[OK] Thread cache hit rate: 91% (107 created / 1K connections)
[!!] Table cache hit rate: 1% (152 open / 12K opened)
[OK] Open file limit used: 27% (279/1K)
[OK] Table locks acquired immediately: 99% (619K immediate / 619K locks)
[OK] InnoDB data size / buffer pool: 48.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 60)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 16M)
    join_buffer_size (> 64.0M, or always use indexes with joins)
    table_cache (> 256)


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

That was what I had hoped but mysqltuner.pl seems well regarded and it's certainly improved DB performance for me when I first tried it. It's other recommendations seem sound so the dire warnings about memory footprint were alarming.

Thanks

Andre