List:General Discussion« Previous MessageNext Message »
From:Tyfanie Wineriter Date:June 13 2014 12:02am
Subject:RE: mysqlcheck downtime > 30 minutes for Java web application
View as plain text  
I would love to know the answer to this as well.

I know there are a number of functions of mysqlcheck... 
-c to check for corruption.. and ensure nothing needs to be repaired
-o to Optimize, which is kind of like defragmenting, removing deleted rows.. and this may be the one that is causing the 100% duplication & downtime..
-r for repair what -c found, or if there was some unanticipated downtime.

I'm trying to determine what the best practices is with MySQL databases.  As an Oracle DBA, I run stats fairly often for the CBO, but that doesn't look like the same thing that -o does.
I'm guessing that for a medium-usage database, I'd use -o during a specified downtime, perhaps accompanying the upgrade, whether it is monthly or whichever, but run -c more often to check for corruption.

Does anyone have any documentation or knowledge of best practices, especially for version 5.6?

~ Tyfanie Wineriter ~

Database Administrator
University of Oregon
1212 University of Oregon
Eugene, OR 97402-1212
(541) 346-1366


-----Original Message-----
From: Lukas Lehner [mailto:weblehner@stripped] 
Sent: Wednesday, June 11, 2014 6:38 AM
To: mysql
Subject: mysqlcheck downtime > 30 minutes for Java web application

Hi

I read in "MySQL, Fifth Edition" by Paul DuBois that we should do a regular mysqlcheck.

We have all InnoDB tables. As far as I unterstand the tables get read and write locked during mysqlcheck.

The database will grow 100x in size, at the moment only initial load.

It means big downtime.

$ time mysqlcheck --all-databases
[...]

real    29m41.449s
user    0m0.019s
sys     0m0.015s

mysql> SELECT table_schema "Data Base Name", SUM( data_length +
index_length) / 1024 / 1024
    -> "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| imzweffefr         |       38420.97170544 |
| information_schema |           0.00781250 |
| mysql              |           0.63046169 |
+--------------------+----------------------+
3 rows in set (5.86 sec)

You all do mysqlcheck? Is it really mandatory? What's the risk if I don't do it regularly?
Thread
mysqlcheck downtime > 30 minutes for Java web applicationLukas Lehner11 Jun 2014
  • RE: mysqlcheck downtime > 30 minutes for Java web applicationTyfanie Wineriter13 Jun 2014
    • RE: mysqlcheck downtime > 30 minutes for Java web applicationMartin Gainty13 Jun 2014
    • RE: mysqlcheck downtime > 30 minutes for Java web applicationMartin Gainty13 Jun 2014