mysql queries

mysql queries

  1. mysqldump -u root-p $sw0rdf1sh –all-databases > db_backup.sql
  2. mysqldump -u root-p $sw0rdf1sh –databases sampledatabase mysql | gzip > db_backup.sql.gz

 

  1. max connection can be calculated by the formula

 

max.connection=(available RAM-global buffers)/thread buffers

 

  1. To determine the sizes of all of your databases, at the mysql> prompt type the following command: All database

 

SELECT table_schema AS “Database”,

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Size (MB)”

FROM information_schema.TABLES

GROUP BY table_schema;

  1. To determine the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check: Specific database

 

SELECT table_name AS “Table”,

ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”

FROM information_schema.TABLES

WHERE table_schema = “database_name”

ORDER BY (data_length + index_length) DESC;

After the command finishes, it displays a list of all of the tables and th

 

  1. Try out the following example to create a database −

 

<html>

<head>

<title>Connecting MySQLi Server</title>

</head>

 

<body>

<?php

$dbhost = ‘localhost:3306’;

$dbuser = ‘root’;

$dbpass = ‘<password here>’;

$conn = mysqli_connect($dbhost, $dbuser, $dbpass);

 

if(! $conn ){

echo ‘Connected failure<br>’;

}

echo ‘Connected successfully<br>’;

$sql = “CREATE DATABASE TUTORIALS”;

 

if (mysqli_query($conn, $sql)) {

echo “Database created successfully”;

} else {

echo “Error creating database: ” . mysqli_error($conn);

}

mysqli_close($conn);

?>

</body>

</html>

 

Be careful while deleting any database because you will lose your all the data available in your database.

 

Here is an example to delete a database created in previous chapter −

 

[root@host]# mysqladmin -u root -p drop TUTORIALS

Enter password:******

This will give you a warning and it will confirm if you really want to delete this database or not.

 

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

 

Do you really want to drop the ‘TUTORIALS’ database [y/N] y

Database “TUTORIALS” dropped

 

  1. Finding long-running queries in MySQL

query_finder.sql

 

SELECT id,state,command,time,left(replace(info,’\n’,'<lf>’),120)

FROM information_schema.processlist

WHERE command <> ‘Sleep’

AND info NOT LIKE ‘%PROCESSLIST%’

ORDER BY time DESC LIMIT 50;

 

 

Script without using Performance_schema:

SELECT

trx.trx_id

,trx.trx_started

,trx.trx_mysql_thread_id

 

  1. FROM INFORMATION_SCHEMA.INNODB_TRX AS trx

INNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl

ON trx.trx_mysql_thread_id = pl.id

WHERE trx.trx_started < CURRENT_TIMESTAMP – INTERVAL 59 SECOND

AND pl.user <> ‘system_user’;

 

Script using Performance_schema:

 

SELECT

pl.id ‘PROCESS ID’

,trx.trx_started

,esh.event_name ‘EVENT NAME’

,esh.sql_text ‘SQL’

FROM information_schema.innodb_trx AS trx

INNER JOIN information_schema.processlist pl

ON trx.trx_mysql_thread_id = pl.id

INNER JOIN performance_schema.threads th

ON th.processlist_id = trx.trx_mysql_thread_id

INNER JOIN performance_schema.events_statements_history esh

ON esh.thread_id = th.thread_id

WHERE trx.trx_started < CURRENT_TIME – INTERVAL 59 SECOND

AND pl.user <> ‘system_user’

ORDER BY esh.EVENT_ID;

 

  1. As a Database Administrator, this is our responsibility to find all locked and blocked transactions of MySQL Server.

 

Below is a script (work for MySQL 5.5 and above versions) :

SELECT

pl.id

,pl.user

,pl.state

,it.trx_id

,it.trx_mysql_thread_id

,it.trx_query AS query

,it.trx_id AS blocking_trx_id

,it.trx_mysql_thread_id AS blocking_thread

,it.trx_query AS blocking_query

FROM information_schema.processlist AS pl

INNER JOIN information_schema.innodb_trx AS it

ON pl.id = it.trx_mysql_thread_id

INNER JOIN information_schema.innodb_lock_waits AS ilw

ON it.trx_id = ilw.requesting_trx_id

AND it.trx_id = ilw.blocking_trx_id

 

  1. When DBA is monitoring the database, it requires to find and kill some unwanted processes of MySQL Server.

 

How to find running processes or sessions?

 

Below are three different ways:

 

SELECT *

FROM INFORMATION_SCHEMA.PROCESSLIST

WHERE COMMAND != ‘Sleep’;

 

show  processlist;

 

/*Execute using MySQL Console*/

mysqladmin -u root -p -i 1 processlist;

How to kill running processes or sessions?

 

KILL CONNECTION <processid>

 

  1. This script is very useful for DBA for checking the size of tables and databases.

 

If you find freer space, DBA has to recover free space.

 

Please read interesting about MVCC:

 

Below are scripts:

 

/* Script for Database */

SELECT

table_schema AS DataBase_Name

,ROUND(sum( data_length + index_length ) / 1024 /1024,1) AS OccupiedSize_inMB

,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMB

FROM information_schema.TABLES

GROUP BY table_schema ;

 

/* Script for Tables */

SELECT table_Name  AS Table_Name,

ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS TableSize_InMB

,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMB

FROM   information_schema.tables

WHERE Table_Schema = ‘db_name’

 

 

Innodb buffer pool size script

  1. I have a rough figure like, if you have 3 GB of InnoDB data and indexes, you require at least 1 GB size of innodb_buffer_pool_size.

 

Below is a script to calculate the size for InnoDB buffer pool.

 

SELECT

CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) AS RIBPS

FROM

(

SELECT SUM(data_length+index_length) Total_InnoDB_Bytes

FROM information_schema.tables WHERE engine=’InnoDB’

) AS T;

After executing above statement, You will get one RIBPS value. e.g. result in 2 RIBPS, so you require 2 GB size of the buffer pool.

You can change this parameter in the config file and restart your MySQL Service.

 

InnoDB reserves additional memory for buffers and control the structures, so that the total allocated space is approximately 10% greater than the specified size.

 

 

 

 

 

  1. First check the current status of the different Log Parameters:

 

SHOW VARIABLES LIKE ‘%log%’;

Using above statement, you can check the current value of log parameter.

 

Now enable logging for general query and save the output into “mysql.general_log” table:

 

SET GLOBAL log_output = ‘TABLE’;

SET GLOBAL general_log = ‘ON’;

SELECT *FROM mysql.general_log;

Now enable logging for long running queries:

 

SET GLOBAL slow_query_log = 1;

SET GLOBAL long_query_time= 1;

SET GLOBAL log_queries_not_using_indexes= ON;

Sometimes, it is required to execute the FLUSH command of the log to get immediate updates into the Log Table.

 

FLUSH LOGS;

 

Best mysql sites

  1. https://www.a2hosting.in/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql
  2. https://www.tutorialspoint.com/mysqli/mysqli_where_clause.htm
  3. https://www.dbrnd.com/2015/11/mysql-how-to-log-general-and-long-running-queries-into-log-table/
  4. https://www.exoscale.com/syslog/keeping-mysql-in-check/

 

 

 

  1. How to create a daily dump in MySQL?

Ask Question

up vote

0

down vote

favorite

I want to make a daily dump of all the databases in MySQL using

 

Event Scheduler

 

, by now I have this query to create the event:

 

DELIMITER $$

CREATE EVENT `DailyBackup`

ON SCHEDULE EVERY 1 DAY STARTS ‘2015-11-09 00:00:01’

ON COMPLETION NOT PRESERVE ENABLE

DO

BEGIN

mysqldump -user=MYUSER -password=MYPASS all-databases > CONCAT(‘C:\Users\User\Documents\dumps\Dump’,DATE_FORMAT(NOW(),%Y %m %d)).sql

END $$

DELIMITER ;

The problem is that MySQL seems to not recognize the command ‘mysqldump’ and shows me an error like this: Syntax error: missing ‘colon’. I am not an expert in SQL and I’ve tried to find the solution, but I couldn’t, hope someone can help me with this.

 

Edit:

 

Help to make this statement a cron task

 

 

 

The InnoDB buffer pool performance can be calculated by using the following formula:

 

Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100

 

innodb_buffer_pool_reads: It indicates the number of requests that cannot be satisfied with InnoDB buffer pool. Need to read from the disk.

 

innodb_buffer_pool_read_requests: It indicates the number of requests of logical reads from memory.

 

For example on my server, let me check the InnoDB buffer pool performance:

 

innodb_buffer_pool_reads                = 91661

innodb_buffer_pool_read_requests   = 4029033624

 

Performance = 91661 / 4029033624 * 100

 

InnoDB Performance = 0.0022750120389663. This means the InnoDB can satisfy most of the requests from Buffer pool itself. Very minimal percentage of reads are done from the disk. So there is no need to increase the innodb_buffer_pool_size value.
So the logic is if your InnoDB buffer pool is able to satisfy all your query requests, then you have enough innodb_buffer_pool_size. If it reads from disk (the working data set is not available in buffer pool memory) then you should increase the innodb_buffer_pool_size.

When the performance of the InnoDB buffer pool maximum, it should not be the reason for the slow performance of the database server, in this case, you can check for other configurations.

 

 

  1. Stop the MySQL server process with the command sudo service mysql stop
  2. Start the MySQL server with the command sudo mysqld_safe —skip-grant-tables —skip-networking &
  3. Connect to the MySQL server as the root user with the command mysql -u root

At this point, you need to issue the following MySQL commands to reset the root password:

mysql> use mysql;

​mysql> update user set authentication_string=password(‘NEWPASSWORD’) where user=’root’;

​mysql> flush privileges;

​mysql> quit

Where NEWPASSWORD is the new password to be used.

Restart the MySQL daemon with the command sudo service mysql restart. You should now be able to log into MySQL with the new password.

And that’s it. You can now set, reset, and recover your MySQL password.

 

 

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD (‘Newpassword’);

 

Database size check command.

 

SELECT table_schema AS “Database”, SUM(data_length + index_length) / 1024 / 1024 / 1024 AS “Size (GB)” FROM information_schema.TABLES GROUP BY table_schema

 

 

Outlook password – db@dmin@123

 

Create table – command;

CREATE TABLE IF NOT EXISTS tasks (

task_id INT(11) NOT NULL AUTO_INCREMENT,

subject VARCHAR(45) DEFAULT NULL,

start_date DATE DEFAULT NULL,

end_date DATE DEFAULT NULL,

description VARCHAR(200) DEFAULT NULL,

PRIMARY KEY (task_id)

) ENGINE=InnoDB

 

 

 

 

 

 

 

Mysql important queries

Mysql important queries

if you use formula Cache hit rate = ((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100) and get 70% I think it is fair enough. However, if you have enough free memory you can increase query_cache_size=128 and obersve some days if hit ratio increases .

 

2. show global variables like ‘max_connections’;

 

3. mysqldump -u user -p[user_password] [database_name] | gzip > dumpfilename.sql.gz mysqldump -u user -p[user_password] [database_name] | gzip > dumpfilename.sql.gz binlog_format = ‘MIXED’  expire_logs_days=10

  1. Now you can restore the files when needed (remember to do this as user postgres after recreating the empty database):

# gunzip -c postgresql_db_2015-09-02.Wednesday.sql.gz | psql postgresql_db

 

 

 

 

 

 

 

Performance Tuning

  1. Enable InnoDB file-per-table

Solution

The innodb_file_per_table is enabled by default in MySQL 5.6 and above. You can see that in /etc/my.cnf file. The directive looks like this:

innodb_file_per_table=1

 

2.     Store MySQL Database Data on Separate Partition

 

 

To complete, this you will need to attach the new drive to your computer/server. For the purpose of this article, I will assume that the drive will be under /dev/sdb.

The next step is to prepare the new drive:

# fdisk /dev/sdb# mkfs.ext4 /dev/sdb1# mount /dev/sdb1  /ssd//dev/sdb1 /ssd ext3 defaults 0 0# service mysqld stop# service httpd stop# service nginx stop# cp /var/lib/mysql /ssd/ -Rp# mv /var/lib/mysql /var/lib/mysql-backup# ln -s /ssd/mysql /var/lib/mysql

Now you are ready to start your MySQL and web service:

# service mysqld start# service httpd start# service nginx start

 

3. Optimizing InnoDB buffer pool Usage

Here is what you need to consider:

  1. How much memory you need for other processes. This includes your system processes, page tables, socket buffers.
  2. Is your server dedicated for MySQL or you will be running other memory hungry services.

On a dedicated box, you would probably want to give about 60-70% of the memory to the innodb_buffer_pool_size. If you plan on running more services on a single box, you should re-consider the amount of memory you dedicate for your innodb_buffer_pool_size.

The value that you should edit in my.cnf is:

innodb_buffer_pool_size

4.Avoid Swappiness in MySQL

By default the option is enabled:

# sysctl vm.swappiness vm.swappiness = 60

To disable swappiness, run the following command:

# sysctl -w vm.swappiness=0

 

5. Set MySQL Max Connections

Keep in mind that too many connections can cause high RAM usage and lock up your MySQL server. Usually small websites will require between 100-200 connections while larger may require 500-800 or even more. The value you apply here strongly depends on your particular MySQL/MariaDB usage.

You can dynamically change the value of max_connections, without having to restart the MySQL service by running:

# mysql -u root -p

mysql> set global max_connections := 300;

 

6. Configure MySQL thread_cache_size

 

improve your performance you can set the thread_cache_size to a relatively high number. To find the thread cache hit rate, you can use the following technique:

mysql> show status like ‘Threads_created’;

mysql> show status like ‘Connections’;

Now use the following formula to calculate the thread cache hit rate percentage:

100 – ((Threads_created / Connections) * 100)

 

 

The good thing here is that the thread_cache_size can be dynamically changed without having to restart the MySQL service. You can achieve this by running:

mysql> set global thread_cache_size = 16;

7. Disable MySQL Reverse DNS Lookups

This unfortunately may cause delays in case of badly configured DNS or problems with DNS server. This is why you can disable the reverse DNS lookup by adding the following in your configuration file:

 

[mysqld]# Skip reverse DNS lookup of clientsskip-name-resolve

You will have to restart the MySQL service after applying these changes.

 

 

 

8. Configure MySQL query_cache_size

 

 

The reason behind that is the fact that threads need to lock the cache during updates. Usually value of 200-300 MB should be more than enough. If your website is relatively small, you can try giving the value of 64M and increase in time.

You will have to add the following settings in the MySQL configuration file:

query_cache_type = 1

query_cache_limit = 256K

query_cache_min_res_unit = 2k

query_cache_size = 80M

  9. Configure tmp_table_size and max_heap_table_size

 

This will affect the database performance. Administrators usually recommend giving 64M for both values for every GB of RAM on the server.

[mysqld]

tmp_table_size= 64M

max_heap_table_size= 64M

 

10. Enable MySQL Slow query Logs

Logging slow queries can help you determine issues with your database and help you debug them. This can be easily enabled by adding the following values in your MySQL configuration file:

slow-query-log = 1

slow-query-log-file = /var/lib/mysql/mysql-slow.log

long_query_time = 1

The first directive enables the logging of slow queries, while the second one tells MySQL where to store the actual log file. Use long_query_time to define the amount of time that is considered long for MySQL query to be completed.

 

 

 

11. Check for MySQL idle Connections

# mysqladmin processlist -u root -p | grep “Sleep”

The first thing you would do here is to check the code and fix it. If you don’t have access to the code that is being ran, you can change the wait_timeout directive. The default value is 28800 seconds, while you can safely decrease it to something like 60:

wait_timeout=60

 

 

12. Choosing Right MySQL Filesystem

 

As per MariaDB’s recommendations, the best file systems are XFS, Ext4 and Btrfs. All of them are enterprise journaling filesystems that can be used with very large files and large storage volumes.

Below you can find some useful information about the three filesystems:

Filesystems XFS Ext4 Btrfs
Maximum filesystem size 8EB 1EB 16EB
Maximum file size 8EB 16TB 16EB

 

 

13. Set MySQL max_allowed_packet

MySQL splits data into packets. Usually a single packet is considered a row that is sent to a client. The max_allowed_packet directive defines the maximum size of packet that can be sent.

Setting this value too low can cause a query to stall and you will receive an error in your MySQL error log. It is recommended to set the value to the size of your largest packet.

 

14. Check MySQL Performance Tuning

There are plenty of tools available for benchmarking, but I would like to suggest you one that is simple and easy to use. The tool is called mysqltuner.

To download and run it, use the following set of commands:

# wget https://github.com/major/MySQLTuner-perl/tarball/master

# tar xf master

# cd major-MySQLTuner-perl-993bc18/

# ./mysqltuner.pl

 

15. Optimize and Repair MySQL Databases

 

Surprisingly, there is a free open source tool called ‘mysqlcheck‘, which automatically check, repair and optimize databases of all tables in Linux.

# mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

# mysqlcheck -u root -p –auto-repair –check –optimize databasename

That’s it! I hope you have found the above article useful and help you tune up your MySQL server. As always if you have any further questions or comments, please submit them in the comment section below.

 

 

4 Useful Commandline Tools to Monitor MySQL Performance in Linux

 

1. Mytop

2. Mtop

3. Innotop

4. mysqladmin

[root@localhost ~]# mysqladmin -u root -p version

Enter password:

 

 

 

 

 

Performance Tuning in MySQL

Performance Tuning in MySQL

 

Why we changing mysql default configuration?

  • Do not even attempt to use the default MySQL configuration. It won’t be enough; the default config file is really weak and small.

 

1.   Enable InnoDB file-per-table SolutionStore
2.   MySQL Database Data on Separate Partition
3.   Optimizing InnoDB buffer pool Usage
4.   Set MySQL Max Connections
5.   Avoid Swappiness in MySQL

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Daily Activity Checklist

 

  1. Database size check command.

SELECT table_schema AS “Database”, SUM(data_length + index_length) / 1024 / 1024 / 1024 AS “Size (GB)” FROM information_schema.TABLES GROUP BY table_schema

 

  1. Slow query log need to set in all server for checking long Queries.

 

  1. Finding query block if block remains for more than one hour. We want to consider the query with the respective user and kill the query.

 

 

  1. Monitoring tool for mysql server  if nagios is there. We need separate page for monitoring mysql servers. ( Monitoring memory, cpu, ram, network and services ).

 

5.       Enable MySQL Slow query Logs

Logging slow queries can help you determine issues with your database and help you debug them. This can be easily enabled by adding the following values in your MySQL configuration file:

slow-query-log = 1

slow-query-log-file = /var/lib/mysql/mysql-slow.log

long_query_time = 1

The first directive enables the logging of slow queries, while the second one tells MySQL where to store the actual log file. Use long_query_time to define the amount of time that is considered long for MySQL query to be completed.

 

 

 

How to enable the slow query log in MySQL & All Queries

https://www.a2hosting.in/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql

mysql queriesmysql queries1. mysqldump -u root-p $sw0rdf1sh –all-databases > db_backup.sql2. mysqldump -u root-p $sw0rdf1sh –databases sampledatabase mysql | gzip > db_backup.sql.gz
3. max connection can be calculated by the formula
max.connection=(available RAM-global buffers)/thread buffers
5. To determine the sizes of all of your databases, at the mysql> prompt type the following command: All database
SELECT table_schema AS “Database”, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Size (MB)” FROM information_schema.TABLES GROUP BY table_schema;6. To determine the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check: Specific database
SELECT table_name AS “Table”,ROUND(((data_length + index_length) / 1024 / 1024), 2) AS “Size (MB)”FROM information_schema.TABLESWHERE table_schema = “database_name”ORDER BY (data_length + index_length) DESC;After the command finishes, it displays a list of all of the tables and th
7.  Try out the following example to create a database −
<html>   <head>      <title>Connecting MySQLi Server</title>   </head>      <body>      <?php         $dbhost = ‘localhost:3306’;         $dbuser = ‘root’;         $dbpass = ‘<password here>’;         $conn = mysqli_connect($dbhost, $dbuser, $dbpass);            if(! $conn ){            echo ‘Connected failure<br>’;         }         echo ‘Connected successfully<br>’;         $sql = “CREATE DATABASE TUTORIALS”;                  if (mysqli_query($conn, $sql)) {            echo “Database created successfully”;         } else {            echo “Error creating database: ” . mysqli_error($conn);         }         mysqli_close($conn);      ?>   </body></html>
Be careful while deleting any database because you will lose your all the data available in your database.
Here is an example to delete a database created in previous chapter −
[root@host]# mysqladmin -u root -p drop TUTORIALSEnter password:******This will give you a warning and it will confirm if you really want to delete this database or not.
Dropping the database is potentially a very bad thing to do.Any data stored in the database will be destroyed.
Do you really want to drop the ‘TUTORIALS’ database [y/N] yDatabase “TUTORIALS” dropped
8. Finding long-running queries in MySQL query_finder.sql
SELECT id,state,command,time,left(replace(info,’\n’,'<lf>’),120)FROM information_schema.processlistWHERE command <> ‘Sleep’ AND info NOT LIKE ‘%PROCESSLIST%’ORDER BY time DESC LIMIT 50;

Script without using Performance_schema:SELECT  trx.trx_id ,trx.trx_started ,trx.trx_mysql_thread_id
10.   FROM INFORMATION_SCHEMA.INNODB_TRX AS trxINNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl  ON trx.trx_mysql_thread_id = pl.idWHERE trx.trx_started < CURRENT_TIMESTAMP – INTERVAL 59 SECOND  AND pl.user <> ‘system_user’;
Script using Performance_schema:
SELECT  pl.id ‘PROCESS ID’ ,trx.trx_started ,esh.event_name ‘EVENT NAME’ ,esh.sql_text ‘SQL’FROM information_schema.innodb_trx AS trxINNER JOIN information_schema.processlist pl  ON trx.trx_mysql_thread_id = pl.idINNER JOIN performance_schema.threads th  ON th.processlist_id = trx.trx_mysql_thread_idINNER JOIN performance_schema.events_statements_history esh  ON esh.thread_id = th.thread_idWHERE trx.trx_started < CURRENT_TIME – INTERVAL 59 SECOND  AND pl.user <> ‘system_user’ORDER BY esh.EVENT_ID;
11. As a Database Administrator, this is our responsibility to find all locked and blocked transactions of MySQL Server.
Below is a script (work for MySQL 5.5 and above versions) :SELECT     pl.id    ,pl.user    ,pl.state    ,it.trx_id     ,it.trx_mysql_thread_id     ,it.trx_query AS query    ,it.trx_id AS blocking_trx_id    ,it.trx_mysql_thread_id AS blocking_thread    ,it.trx_query AS blocking_queryFROM information_schema.processlist AS pl INNER JOIN information_schema.innodb_trx AS it ON pl.id = it.trx_mysql_thread_idINNER JOIN information_schema.innodb_lock_waits AS ilw ON it.trx_id = ilw.requesting_trx_id         AND it.trx_id = ilw.blocking_trx_id
11. When DBA is monitoring the database, it requires to find and kill some unwanted processes of MySQL Server.
How to find running processes or sessions?
Below are three different ways:
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != ‘Sleep’;
show  processlist;
/*Execute using MySQL Console*/mysqladmin -u root -p -i 1 processlist;How to kill running processes or sessions?
KILL CONNECTION <processid>
12. This script is very useful for DBA for checking the size of tables and databases.
If you find freer space, DBA has to recover free space.
Please read interesting about MVCC:
Below are scripts:
/* Script for Database */SELECT  table_schema AS DataBase_Name ,ROUND(sum( data_length + index_length ) / 1024 /1024,1) AS OccupiedSize_inMB    ,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMB FROM information_schema.TABLES GROUP BY table_schema ;
/* Script for Tables */SELECT table_Name  AS Table_Name,   ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS TableSize_InMB   ,ROUND(sum( data_free )/ 1024 / 1024,1) AS FreeSpace_inMBFROM   information_schema.tables WHERE Table_Schema = ‘db_name’

Innodb buffer pool size script 14. I have a rough figure like, if you have 3 GB of InnoDB data and indexes, you require at least 1 GB size of innodb_buffer_pool_size.
Below is a script to calculate the size for InnoDB buffer pool.
SELECT  CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) AS RIBPS FROM( SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine=’InnoDB’) AS T;After executing above statement, You will get one RIBPS value. e.g. result in 2 RIBPS, so you require 2 GB size of the buffer pool.You can change this parameter in the config file and restart your MySQL Service.
InnoDB reserves additional memory for buffers and control the structures, so that the total allocated space is approximately 10% greater than the specified size.

 

 

15. First check the current status of the different Log Parameters:
SHOW VARIABLES LIKE ‘%log%’;Using above statement, you can check the current value of log parameter.
Now enable logging for general query and save the output into “mysql.general_log” table:
SET GLOBAL log_output = ‘TABLE’;SET GLOBAL general_log = ‘ON’;SELECT *FROM mysql.general_log;Now enable logging for long running queries:
SET GLOBAL slow_query_log = 1;SET GLOBAL long_query_time= 1;SET GLOBAL log_queries_not_using_indexes= ON;Sometimes, it is required to execute the FLUSH command of the log to get immediate updates into the Log Table.
FLUSH LOGS;