[mythtv-users] Problem with latest run of optimize_mythdb.pl

Stephen Worthington stephen_agent at jsw.gen.nz
Thu Dec 31 10:09:08 UTC 2015


On Wed, 30 Dec 2015 14:03:57 -0700, you wrote:

>On Wednesday, December 30, 2015 12:53:57 PM Jerome Yuzyk wrote:
>> On Thursday, December 31, 2015 03:34:46 AM Stephen Worthington wrote:
>> >
>> > time.  So I am now looking at only doing the defragmentation
>> > automatically when I know I have an hour or more before the next
>> > recording, if I can work out how to do that.
>> 
>> https://www.mythtv.org/wiki/Upcoming_Parse.pl was the starting point for a script I'm working on to determine if it's safe to run a defrag, though I haven't implemented it or the defrag optimizing yet.
>
>But then I got curious about how fragmented my tables were, and after looking at a couple scripts one post mentioned that mysqltuner.pl reports the number of fragmented tables. I've been running it every Monday morning but hadn't noticed it reporting fragmentation. My last report returned
>
>    Data in InnoDB tables: 192K (Tables: 5)
>    Data in MyISAM tables: 195M (Tables: 107)
>    Total fragmented tables: 0
>
>so I don't need to worry. 
>
>Maybe it can show whether you need to be concerned at all.

Thank you, excellent references.  I had forgotten about mysqltuner.pl.
I took a look at its code, and this is the query I need to find any
tables needing to be defragmented:

mysql> SELECT CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME) FROM
information_schema.TABLES WHERE TABLE_SCHEMA NOT IN
('information_schema','performance_schema', 'mysql') AND Data_free > 0
AND NOT ENGINE='MEMORY';
+-----------------------------------------------+
| CONCAT(CONCAT(TABLE_SCHEMA, '.'), TABLE_NAME) |
+-----------------------------------------------+
| mythconverg.inuseprograms                     |
| mythconverg.jobqueue                          |
| mythconverg.record                            |
| mythconverg.record_tmp                        |
+-----------------------------------------------+
4 rows in set (0.01 sec)

The upcoming_parse.pl script is a good base to work from, but it does
not take into account recordings currently in progress.  And as I do
not do Perl, I think I need to write my script in Python, where I do
have a little skill.


More information about the mythtv-users mailing list