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?

Ryan Griffith
  • 91
  • 10
  • Try running analyze on all tables instead. Much faster than optimize and it should be enough. – jkavalik Sep 19 '21 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 '21 at 00: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.

     – Ryan Griffith Sep 20 '21 at 01: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 '21 at 05:20

2 Answers2

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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 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? – Ryan Griffith Sep 19 '21 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. – RolandoMySQLDBA Sep 19 '21 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. – Ryan Griffith Sep 20 '21 at 01: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. – RolandoMySQLDBA Sep 20 '21 at 02: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? – Ryan Griffith Sep 20 '21 at 02:16
  • 1
    What you want is persistent index statistics. There is a way to save them inside the table. See https://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html on how to set up tables and query optimizer. You could also read everything on ANALYZE TABLE with histograms in MySQL 8.0. Notwithstanding, all of this info is a moot point when you reload a table. The mysqldump drops the table, then creates it. All stats go out the window. – RolandoMySQLDBA Sep 20 '21 at 12:41
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.

John K. N.
  • 17,649
  • 12
  • 51
  • 110