2

I frequently have to restore our website's production DB to our test server.

When trying to access the restored copy of production, the performance is AWFUL. For example, one particularly heavy web page's worth of queries typically takes 8 seconds to return the results. After restoring from production, this easily takes upwards for 5 minutes.

This can all be rectified by running mysqlcheck -u myuser -p -o my_db_name

The results look something like this:

my_db_name.table1
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
my_db_name.table2
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
etc...

Why is this slow performance happening in the first place? I haven't seen much in terms of this issue when doing searches online.

I have never experienced this before with either MySQL or MSSQL.

I have a solution (to run mysqlcheck, or as others have pointed out, just the analyze portion of that), but it doesn't seem like the "right" way to solve it - I'd like to understand the underlying issue.

Thoughts?

4
  • Try running analyze on all tables instead. Much faster than optimize and it should be enough.
    – jkavalik
    Sep 19 at 17:29
  • 1
    I think you are looking in the wrong place. Let's see the queries on that 8-second webpage. The tables are probably missing some indexes and/or the queries need reformulating.
    – Rick James
    Sep 20 at 0:36
  • @RickJames, that's an inherited ORM rabbit hole, but your point is well received. Unfortunately it is not reasonable to rewrite at the present moment. The page executes many queries, a few of which are heavy hitters or N+1 queries, thus are also heavy hitters when viewed from that perspective. Regardless, even if I rewrote the base queries, the question still remains why the performance takes a massive hit immediately after importing data from a .sql file. Sep 20 at 1:30
  • @RyanGriffith - Probably caching. The first query after a reboot will be slow because nothing is cached in RAM yet. Things like 'restore' or 'check' may or may not mess up the cache ("buffer pool").
    – Rick James
    Sep 20 at 5:20
2

I have very good news for you : You need not worry about that message at all, That is the exact output MySQL prints when you execute OPTIMIZE TABLE against an InnoDB table.

In my old post from Sep 27, 2015 : (How can I defragment tables in MySQL?), I explain the message. Turns out that InnoDB implements OPTIMIZE TABLE by doing a copy of the data into a temp table, executes ANALYZE TABLE on the temp table, switches the old table and new temp table, and discards the old table.

I also addressed this message in earlier posts

Since you did a restore, OPTIMIZE TABLE is overkill. Run ANALYZE TABLE as already pointed out in the comment posted on your question.

6
  • I think I was not clear: I'm not concerned with the output of the mysqlcheck command, I'm curious why the restore of the DB performs awfully in the first place. What causes a restore to perform poorly until an analyze / optimize is executed? Is this typical? Sep 19 at 23:25
  • 1
    Both the information_schema and the Index Statistics are never up-to-date. After loading a table from a mysqldump, you need to do ANALYZE TABLE. Sep 19 at 23:45
  • What keeps the index stats up to date normally? Why are they not updated during the import? This begs the question of whether or not analyze table should be ran regularly or what would maintain and update this on a regular basis. Sep 20 at 1:23
  • 1
    Depends on whether innodb_stats_on_metadata is ON or OFF. If it is OFF, ANALYZE TABLE is needed but index stats can change over time still. If it is ON, index stats can be updated with a simple SHOW TABLE STATUS command. Sep 20 at 2:08
  • That's great info, but I suppose my question is: What maintains index stats long term? If I setup a database and it's used through normal OLTP, what maintains the indexes so they don't go stale and become ever increasingly more poor performing from the moment they're created? Sep 20 at 2:16
1

Question from comment on RolandoMySQLDBA's answer:

I'm curious why the restore of the DB performs awfully in the first place.

When you restore a database from a backup, then all the data resides on the disk. Until you read the data for the first time, the query is going to have to rely on Disk I/O throughput for performance.

Once the data has been read once from disk it normally resides in memory for a certain amount of time. Memory I/O performance is way better than Disk I/O performance.

I'm not sure of the specifics of ANALYZE TABLE... or mysqlcheck, but it will have a positive impact on the way the data is available on disk after restoring from a backup. The data will be ordered in some form (clustered index) as to achieve faster searches on the data.

When you restore a database the data can be unordered. What happens is the following:

  1. You restore (an unordered) database.
  2. You analyze data / mysqlcheck data --> data becomes ordered, statistics are updated
  3. You read the data from disk --> data is moved to RAM
  4. You read the data again from RAM --> performance is at its peak

If you omit step 2. then you still might observe better performance after step 3. when performing step 4. which is the same SELECT statement again.

Why? Well because the data has been moved to RAM.

Most RDBMS will behave similar, be it PostgreSQL, MySQL, Oracle Database or Microsoft SQL Server.

Initial Question

Why is this slow performance happening in the first place? I haven't seen much in terms of this issue when doing searches online.

I have never experienced this before with either MySQL or MSSQL.

Lucky you. If the data is unordered or statistics (Oracle DB/ MS SQL Server) are outdated in the source database, then restoring the database from a backup might not automatically optimize indexes or statistics. Performing these steps after a restore will help mitigate issues which could possibly arise from fragmented indexes and/or outdated statistics.

SELECTing data a second time should normally result in better performance after a database restore.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.