[mythtv-users] mythconverg database server migration

[Michael T. Dean]

On 03/29/2017 11:09 AM, Erik Karlin wrote:
> I'm attempting to use the mythconverg_backup.pl / mythconverg_restore.pl
> scripts to move my database from one very old slow server to another
> old, but not as slow server. After a backup, my restore has been running
> for about 6 days now, with the following output from show processlist:
>
> | Id | User   | Host                    | db          | Command | Time | State  | Info
> +----+--------+-------------------------+-------------+---------+--------+--------+------------------------------------------------------------------------------------------------------+
> | 17 | mythtv |            myhost:37979 | mythconverg | Query   | 270851 | update | INSERT INTO `recordedseek` VALUES (1017,'2016-01-09 06:56:00',420,8972010,9),(1017,'2016-01-09 06:56 |
>
>
> Now, checking my mythweb statistics, I see:
> Total Running Time: 13 years 16 days 22 hrs 26 mins
>
> so I know there is an awful lot of data to restore, but is there
> anything I can do. I did a clean install of mysql 5.6 on an updated
> debian jessie...no tuning or config changes other than allowing LAN
> connections.
>
> This was an empty database. I ran:
> mythconverg_restore.pl --hostname dbhost --create_database --verbose
>
>
> A few questions...
> 1. will this ever end?
> 2. can I kill this

Yes

> and restart,

Yes

>   potentially exclude the big tables?

No, you shouldn't, nor should you ever need to.

> 3. is there any tuning to be done?

The reason it's so slow is almost definitely due to your system 
configuration--and I'd guess it's your file system configuration 
(something having to do with your MySQL temp directory location and/or 
file system barriers or ...).

> Again, this was a fresh install of mysql 5.6. From a quick search, there
> were some suggestions to turn off
> autocommit/unique_checks/foreign_key_checks and something about
> tuning key_buffer_size.
>
> I assume if I can kill the restore, I can drop and recreate the database
> without any problems,

mythconverg_restore.pl --drop_database --create_database --filename mythconverg-1214-20080626150513.sql.gz


>   but is there a faster way? What if I don't load
> the recordedseek table at all?

While the restore is slow, the only reason it seems that's the problem 
is because it's a big table, so a reasonable percentage of the restore 
time--therefore, it's highly likely that when you look, it will be 
restoring something into that table.  However, there's nothing different 
or special about that table, so even if you skip restoring it, the rest 
will still take too long, and the resulting database will still be 
unusable because your system configuration is making it too 
slow/unperformant (is that a word?).

>   I'd certainly prefer to not have to
> interfere in a database load, but I need this to finish in some
> definition of reasonable amount of time.
>
> Any thoughts? The restore is still running if that matters.
>
> Thanks in advance for any input

It should have completed long ago.  Something is wrong with your system 
and/or MySQL configuration.  Even if you let it run to completion, 
you'll probably have terrible performance once it's loaded because 
whatever is slowing the restore will slow the DBMS, itself.  So you 
really need to identify and fix the configuration issue that's slowing 
your MySQL.

The unfortunate thing is, though, that identifying the configuration 
issue is going to be a challenge (read, "will take lots of systematic 
trial and error--or a lucky guess by you or someone on list").

Mike