<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On 31 August 2013 19:08, Simon Hobson <span dir="ltr"><<a href="mailto:linux@thehobsons.co.uk" target="_blank">linux@thehobsons.co.uk</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I've been trying to tune my database to reduce times on activities like rescheduling. However I seem to have hit something of a wall.<br>
<br>
The backend is an HP Microserver with 2G of RAM - running Debian Wheezy and MythBackend 0.24.1*. The database in it's entirety is 200 to 250M. I've been <a href="http://usingmysqltuner.pl" target="_blank">usingmysqltuner.pl</a>, but some of the recommendations it comes up with either don't have an effect or are out of my control. Key bits from mysqltuner :<br>
<br>
[OK] Currently running supported MySQL version 5.1.49-3<br>
[OK] Operating on 64-bit architecture<br>
<br>
[--] Data in MyISAM tables: 231M (Tables: 96)<br>
[--] Data in InnoDB tables: 48K (Tables: 3)<br>
[--] Total buffers: 434.0M global + 3.6M per thread (30 max threads)<br>
[OK] Maximum possible memory usage: 540.9M (27% of installed RAM)<br>
[OK] Highest usage of available connections: 46% (14/30)<br>
[OK] Key buffer size / total MyISAM indexes: 384.0M/278.1M<br>
[OK] Key buffer hit rate: 99.9% (49M cached / 28K reads)<br>
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 8K sorts)<br>
[!!] Joins performed without indexes: 2512<br>
[OK] Temporary tables created on disk: 16% (449 on disk / 2K total)<br>
[!!] Table cache hit rate: 1% (145 open / 7K opened)<br>
[OK] Open file limit used: 1% (261/16K)<br>
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)<br>
<br>
-------- Recommendations -----------------------------------------------------<br>
General recommendations:<br>
Run OPTIMIZE TABLE to defragment tables for better performance<br>
Enable the slow query log to troubleshoot bad queries<br>
Adjust your join queries to always utilize indexes<br>
Increase table_cache gradually to avoid file descriptor limits<br>
Variables to adjust:<br>
join_buffer_size (> 1.0M, or always use indexes with joins)<br>
table_cache (> 8192)<br>
<br>
So as far as I can tell, I've not got buffers large enough to hold indexes in memory, the data in memory, and should have "fast" response times.<br>
But if (for example) I bring up the upcoming recordings screen in MythWeb, and click on "Don't Record" for a program - it takes perhaps 10s to refresh the page. During that time, top shows that :<br>
Wait I/O goes to 40% or more - I've seen 90+%. And cached memory jumps from around 50M (I recently did a "echo 3 > /proc/sys/vm/drop_caches" to empty the caches) to 250M or more and then drops back to 50M or so. This is much the same after multiple tests - so any data really ought to have been read into cache after the first one or two goes.<br>
<br>
I'm thinking that MySQL is creating some large temporary tables and for some reason this is hitting disk i/o - either for the temp tables, or by flushing out data that should be held in MySQL's caches.<br>
<br>
I stopped increasingtable_cache as a found a note that having this value constantly increasing is normal if the system is creating temporary tables. See comments to<br>
<a href="http://dev.mysql.com/doc/refman/5.1/en/table-cache.html" target="_blank">http://dev.mysql.com/doc/refman/5.1/en/table-cache.html</a><br>
<br>
At the moment I;ve got these settings in /etc/mysql/my.cnf :<br>
key_buffer = 16M<br>
key_buffer_size = 384M<br>
max_allowed_packet = 16M<br>
thread_stack = 192K<br>
thread_cache_size = 8<br>
max_connections = 30<br>
table_cache = 8192<br>
#thread_concurrency = 10<br>
join_buffer_size = 1M<br>
query_cache_limit = 1M<br>
query_cache_size = 24M<br>
<br>
Could anyone suggest what else I might be needing to adjust ? I've tried turningjoin_buffer_size up to 16M, but other than considerably increasing MySQL's max memory usage it's not made any difference.<br>
<br>
* Yes, I know it's a few versions behind, but if it aint broke ...<br>
_______________________________________________<br>
mythtv-users mailing list<br>
<a href="mailto:mythtv-users@mythtv.org">mythtv-users@mythtv.org</a><br>
<a href="http://www.mythtv.org/mailman/listinfo/mythtv-users" target="_blank">http://www.mythtv.org/mailman/listinfo/mythtv-users</a></blockquote><div><br></div><div>You've probably thought about this already, but have you tried rationalising your recording rules.?</div>
<div><br></div><div>When I looked at mine I found I had multiple rules for many programmes (including 20 different recording rules for the recording the Simpsons), accumulated over the years, plus dozens and dozens of rules for things I would never want to record again. Removing the unwanted stuff made a significant improvement to the rescheduling time.</div>
<div><br></div><div>I also removed a lot of stuff from 'Previously Recorded' that I knew I wouldn't want to record again. It meant losing my recording history but that wasn't a problem for me. </div><div>
<br></div><div>HTH</div></div><br></div></div>