0 like 0 dislike
250 views
asked in MySQL Database Forum by (13.4k points)  
edited by

how to optimize and repair MySQL databases and monitoring of your MySQL server including CPU/RAM 

commented by (13.4k points)  
You can refer  : 15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips

https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/

Please log in or register to answer this question.

6 Answers

1 like 0 dislike
answered by (13.4k points)  

Mail alert notification MariaDB maintainence task activity

EMAIL="nijamutheen0@gmail.com"

mysqlcheck -c -A
mysqlcheck -o -A
mysqlcheck -a -A
mysqlcheck --auto-repair -A

##SEND AN EMAIL###
    MESSAGE="$(tail -15 /var/lib/publishman.com.err)"
    SUBJECT=" Maintenance Done on $(date) "
    echo -e "   LOGS :  \n$MESSAGE "  | mail -s "$SUBJECT" "$EMAIL"
0 like 0 dislike
answered by (13.4k points)  
selected by
 
Best answer

From time to time, MySQL 5.1 databases need a little house keeping.  We found our production DB had a hard time running a simple join query between two tables with about 400k rows.  It was taking between 30 and 100 seconds to run.  On QA however, it was taking 58 milliseconds. The columns involved were already indexed.  Thankfully it wasn’t impacting our users, but it still bugged me. The solution was simple, just run some cleanup commands. After the cleanup, on the live server the same query took just 4.8 milliseconds – that’s more like it!

Summary of solution:

  1. Backup database
  2. Check
  3. Optimize
  4. Analyze
$ mysqldump -u root -p --create-options --routines --triggers dbname > ./db.dmp
# note these cause LOCKS, so be careful on your production server!
$ mysqlcheck -u root -p --check --databases dbname
$ mysqlcheck -u root -p --optimize --databases dbname
$ mysqlcheck -u root -p --analyze --databases dbname

 

Complete details about each step:

1) First make database backup with mysqldump:
Don’t forget the argument –routines if you have stored procedures or functions and –triggers if using triggers:

$ mysqldump -u root -p --create-options --routines --triggers dbname > ./db.dmp
# copy to another server
$ scp ./db.dmp user@somehost:~/

For bonus points, actually restore the database on another system to make sure you have a valid backup.

This step may be impractical if the database is huge. In that case you are probably already using replication and have a backup system worked out.

2) Check:
Checks table for integrity errors.
http://dev.mysql.com/doc/refman/5.1/en/check-table.html

To check a single table:

mysql> CHECK TABLE {table name};

To check all tables in a database, from command line:

$ mysqlcheck -u root -p --check --databases dbname

This seems like a really smart thing to do on a regular basis.

3) Optimize:

Like a defrag operation, the optimize tables command reclaims unused space. At least, that is what it does for MyISAM. With InnoDB it basically runs an ALTER TABLE statement that changes nothing but tells MySQL to rebuild the table and its indexes.
http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

To optimize a single table:

mysql> OPTIMIZE TABLE {table name};

To optimize all tables in a database, from command line:

$ mysqlcheck -u root -p --optimize --databases dbname

If you get “Table does not support optimize, doing recreate + analyze instead”, that is normal for InnoDB.

4) Analyze:

Analyze rebuilds and optimizes the performance of indexes, specially it rebuilds the key distribution. If you have a slow running query but indexes are in place, it might be time to run this. A read lock goes into effect while this is running. If you have only InnoDB tables, this is already taken care of by Optimize.

http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

To analyze a single table:

mysql> ANALYZE TABLE {table name};

To analyze all tables in a database, from command line:

$ mysqlcheck -u root -p --analyze --databases dbname

With InnoDB and ANALYZE TABLE, there are some oddities. In particular, the number of samples the analyzer takes can vary (configuration option is innodb_stats_sample_pages). The default is low, and this means running analyze tables repeatedly will produce slightly different results.

0 like 0 dislike
answered by (13.4k points)  

Perform Routine Table Checks, Optimization, and Repairs

Tables should be actively maintained and checked regularly as a proactive measure of problem prevention.  When problems do arise, this step will help minimize the impact of the problem on the server and data.  The mysqlcheck binary program can be used to perform most of these checks.  The mysqlcheck program can be used to perform database table consistency checks, table optimization, table analysis, or table repair by passing different arguments to the CLI.

TIP: you can create renamed binary copies if the mysqlcheck OR use symbolic links to make calls to the CLI more intuitive.  This is because there is a special feature in the code that permits the default behavior of the CLI to change when the file or link is named in a certain fashion. 

You can achieve this behavior by:

cp mysqlcheck mysqlrepair
cp mysqlcheck mysqloptimize
cp mysqlcheck mysqlanalyze

OR

ln -s mysqlcheck mysqlrepair
ln -s mysqlcheck mysqloptimize
ln -s mysqlcheck mysqlanalyze

It is reccommended that this CLI be added to the crontab of the UNIX user account that mysql runs under.  

Create a ~/.my.cnf file with login credentials so that the MySQL CLIs can run non-interactively in cron and be able to authenticate.

[client]
user=myuser
password=myuserpassword

Protect the file by tightening the UNIX file permissions so that only the UNIX user and/or root may view the file, since it has a password in clear text.

chmod 600 ~/.my.cnf
ls -lta .my.cnf
-rw------- 1 mysql mysql 37 2008-12-14 12:01 .my.cnf

Add the following to the crontab of the UNIX user (in most cases mysql) that MySQL runs under:

0 0 * * 0 /usr/local/mysql/bin/mysqlcheck --all-databases --check-only-changed --silent

When a table is being checked by mysqlcheck, it cannot be updated.  So, performing mysqlcheck on large tables should be used at your discretion.  It may not make sense to perform mysqlcheck on large tables if you cannot afford the table to be unavailable for updates.

Alternatively, you could create the .my.cnf in the /root directory on Linux systems, and place the job into the /etc/cron.daily directory.  On Fedora 10, I placed the .my.cnf file in the /root directory.  An executable shell script with the mysqlcheck command was placed in the /etc/cron.daily directory. The /etc/crontab script on linux calls a run-parts script which runs all scripts it finds in the /etc/cron.daily, /etc/cron.hourly, /etc/cron.weekly, and /etc/cron.monthly directories.

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
asked Mar 31, 2019 in MySQL Database Forum by nijamutheen j (13.4k points)  
0 like 0 dislike
1 answer
Welcome to PostgreSQL Database Discussion Forum where you can ask questions and receive answers from other members of the community. Can discuss here Oracle, Postgresql, mariadb , mySQL , AWS , Linux , MSSQL , MongoDB , Greenplum databases related queries ...etc.
...