I would recommend start Here to analyze your tables: http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html
Once you have evidence of FTS or unbalanced index entries then optimise http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html Martin Gainty ______________________________________________ Member _____ _ _____ _ _____ ___ _ _____ _ _ _ |_ _| |_ ___ | _ |___ ___ ___| |_ ___ | __|___| _| |_ _ _ _ ___ ___ ___ | __|___ _ _ ___ _| |___| |_|_|___ ___ | | | | -_| | | . | .'| _| | -_| |__ | . | _| _| | | | .'| _| -_| | __| . | | | | . | .'| _| | . | | |_| |_|_|___| |__|__| _|__,|___|_|_|___| |_____|___|_| |_| |_____|__,|_| |___| |__| |___|___|_|_|___|__,|_| |_|___|_|_| |_| > From: tyfan...@uoregon.edu > To: webleh...@gmail.com; mysql@lists.mysql.com > Subject: RE: mysqlcheck downtime > 30 minutes for Java web application > Date: Fri, 13 Jun 2014 00:02:48 +0000 > > 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:webleh...@gmail.com] > 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? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql >
