How To Optimize MySQL Tables

How To Optimize MySQL Tables

mysql> show table status like "user" \G

*************************** 1. row ***************************

           Name: user

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 6

 Avg_row_length: 115

    Data_length: 692

Max_data_length: 281474976710655

   Index_length: 2048

      Data_free: 0

 Auto_increment: NULL

    Create_time: 2022-04-16 04:36:27

    Update_time: 2022-04-16 04:36:27

     Check_time: 2022-04-16 07:13:50

      Collation: utf8_bin

       Checksum: NULL

 Create_options:

        Comment: Users and global privileges

1 row in set (0.00 sec)

The output shows some general information about the table. The following two numbers are important:

Data_length represents the amount of space the database takes up in total.

Data_free shows the allocated unused bytes within the database table. This information helps identify which tables need optimization and how much space will be released afterward.

Show Unused Space for all tables.

select table_name, data_length, data_free from information_schema.tables where table_schema='user' order by data_free desc;

Display Data in Megabytes.

select table_name, round(data_length/1024/1024), round(data_free/1024/1024) from information_schema.tables where table_schema='user' order by data_free desc;

Optimize a Table Using MySQL

optimize table user;

Optimize Multiple Tables at Once

optimize table user, db, proc;

Optimize Tables Using the Terminal

Syntax: 

mysqlcheck -o <schema> <table> -u <username> -p <password>

mysqlcheck -o <schema> <table> -u <username> -p <password>

 

How to findout of MySQL my.cnf file?

How to findout of MySQL my.cnf file?

mysql --help | grep "Default options" -A 1

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

What is MySQL Query Caching

What is MySQL Query Caching?

MySQL Query Caching provides database caching functionality. The SELECT statement text and the retrieved result are stored in the cache. When you make a similar query to the one already in the cache, MySQL will respond and give a query already in the cache. In this way, fewer resources are used, and your query runs faster.

To check the status

show variables like 'have_query_cache';

show variables like 'query_cache_%';

To adjust in MySQL configuration file:

query_cache_type=1

query_cache_size = 10M

query_cache_limit=256k

Disable the MySQL query cache without restarting MySQL.

set global query_cache_size = 0;

MySQL Troubleshooting

MySQL Troubleshooting

1. Not able to start the database.

-MySQL configuration file /etc/my.cnf is present and have all valid configuration.

-System has enough free resources to cater MySQL.

-Filesystems are not in READ ONLY mode.

-Check and fix the errors reported in /var/log/mysqld.log

2. MySQL is running, But users are unable to connect remotely.

-Verify you are connected to correct port of the database.

-Check firewall status on MySQL connectivity.

-Verify you are using correct user/password.

3. Users are unable to create new connections after a certain limit.

-Verify you are not hitting "max_connections limits" if yes, you need to increase it as per requirement.

show status like '%onn%';

show variables like "max_connections";

set global max_connections = 200; 

MySQL Statements For Table Maintenance

MySQL Statements For Table Maintenance

-CHECK TABLE    -------> For integrity checking

-REPAIR TABLE   -------> For repairs

-ANALYZE TABLE  -------> For analysis

-OPTIMIZE TABLE -------> For optimization

1.CHECK TABLE

The check table statement performs an integrity check on table structure and contents, and if the output from CHECK TABLE indicates that a table has problems, the table structure should be repaired.

check table <table_name>

check table <table_name>,<table_name>,<table_name>

2.REPAIR TABLE 

The repair table statement corrects the problem in a table that has become corrupted.

repair table <table_name>

repair table <table_name>,<table_name>,<table_name>

3.ANALYZE TABLE

The analyze table statement updates a table with information about the distribution of key values in the table, and this information is used the optimizer to make better choices about query execution plans.

analyzer table <table_name>

analyzer table <table_name>,<table_name>,<table_name>

4.OPTIMIZE TABLE

The optimize table statement cleans up a MyISAM table by defragmenting it, It involves reclaiming unused space resulting from deletes & updates, coalescing split records and stored non-contiguously and it also sorts the index pages if they are out of order and updates the index statistics.

optimize table <table_name>

optimize table <table_name>,<table_name>,<table_name>

To optimize all tables in all MySQL database using mysqlcheck 

mysqlcheck -o --all-databases -u root -p

To repair multiple MySQL databases using mysqlcheck

mysqlcheck -r --databases mysql qtest

To analyze databases using mysqlcheck

mysqlcheck -u root -p --analyze mysql