I have a database with 3 from the tables getting rows more than 20 million each. I have used GUIDs as primary secrets (regrettably). Now our database is all about 20GB and growing 5GB monthly.

It takes approximately 2 several hours to consider full backup from the database, and 30hrs to revive on the box with 4GB RAM.

We once have the tables from database disappeared. other mysql databases in same server were okay except one - that only data was disappeared departing empty tables.

A choose query (among many slow queries) - which gets max of the date column in a single of 20m table takes about 5 mins to come back result. This question used pretty frequently.

What I am searching solutions for

  1. suggested db design changes
  2. methods to enhanced choose query performance - max date column on 20m records
  3. other queries' performance
  4. how to pull off handling future db growth

Thanks all for the attention.

I have seen configurations of bigger size (with InnoDB as storage engine along with a GUID like a primary key), and there have been no such problems.

We once have the tables from database disappeared. other mysql databases in same server were okay except one - that only data was disappeared departing empty tables.

The tables may appear empty when the system LSN went underneath the each page's LSN. This might happen when the InnoDB logfiles are corrupt. InnoDB, however, will problem an alert within this situation.

A choose query (among many slow queries) - which gets max of the date column in a single of 20m table takes about 5 mins to come back result. This question used pretty frequently.

Create a catalog about this column, the query is going to be instant.

Please publish the precise query and I'll let you know how you can produce the best index.

I see no issue in the DB design as a result, most most likely it is something together with your server.

Can you really reproduce this behavior on another server having a clean vanilla MySQL installation?

You may even would like to try to separate data between your tables. Set innodb_file_per_table and restore in the backup.

A totally free alternaive to innodb hot backup is Percona XtraBackup Tool.

For backup, you could utilize the innodb hot backup tool. This not just allows you need to do consistent backup copies while your database expires, however the restore is a lot faster compared to one you are doing (I am presuming mysqldump?). It will have a price.

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

It's a useful gizmo and it is free and free