Geek Answers Handbook

Big mysql database (innodb) - slow query performance, disappearing tables and long time to restore backups

I have a database with 3 tables having rows over 20 million each. I used the GUID as primary keys (unfortunately). Now our database is about 20 GB and growing at 5 GB per month.

A full backup of the database takes about 2 hours, and 30 hours to recover on a box with 4 GB of RAM.

As soon as all tables from the database disappeared. other mysql databases on the same server were fine, except for one - for which only data disappeared, leaving empty tables.

Selecting a query (among many slow queries) - getting the maximum value of a date column in one of the 20 m tables takes about 5 minutes to return the result. This request was used quite often.

I am looking for answers

  • recommended db design changes
  • ways to improve the performance of the selected query - maximum date column on 20 mm reports
  • other query performance
  • how to move towards future db growth

Thank you all for your attention.

+4
source share
4 answers

I saw larger installations (with InnoDB as the storage engine and GUID as the primary key), and there were no such problems.

As soon as all tables from the database disappeared. other mysql databases on the same server were fine, except for one - for which only data disappeared, leaving empty tables.

Tables may appear empty if the LSN below each LSN page. This can happen if the InnoDB log files are damaged. InnoDB , however, will give a warning in this case.

Selecting a query (among many slow queries) - getting the maximum value of a date column in one of the 20 m tables takes about 5 minutes to return the result. This request was used quite often.

Create an index in this column, the query will be instant.

Please post an exact request and I will tell you how to create a better index.

I do not see a problem in DB design as such, most likely it is something with your server.

Is it possible to reproduce this behavior on another server with a clean install of MySQL vanilla?

You can also try to split the data between tables. Set innodb_file_per_table and restore the backup.

+1
source

A free alternative to the hot backup innodb Percona XtraBackup Tool .

+1
source

For backup you can use the innodb hot backup tool. Not only does this allow you to perform consistent backups while your database is running, but recovery is much faster than the one you are doing (I assume mysqldump?). It costs money.

0
source

You can also try Mydumper: http://www.mydumper.org/

It is a great tool and is free and open source.

0
source

More articles:


All Articles