Use procedure to batch init data

create table t2 ( id int(11) not null, a int(11) default null, b int(11) default null, PRIMARY key (id), key a (a)) engine=INNODB;

-- drop procedure idata;
delimiter ;;
create procedure idata()
	declare i int;
	set i=1;
	while (i<=1000) DO
		insert into t2 values (i,i,i);
		set i=i+1;
	end while;

delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id <=100);

when to use join

2. 使用join语句的话,需要让小表做驱动表

判断要不要使用join语句时,就要看explain结果里面,Extra字段里面有没有出现“Block Nested Loop”字样

我们可以通过设置 join_buffer_size 来控制buffer的大小

如果使用Block Nested-Loop Join 算没法,扫描行数就会过多。尤其是大表上的join操作,这样可能要扫描被驱动表很多次,会占用大量的系统资源,这时候,join尽量不要用


General recommendation:

You can get a brief recommendation about MySQL by using the Perl script of mysql-tuner

How to use processlist?

select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;

Why there are so many sleep processes?

By default, mysqld will timeout database connections based on two server options:

Both are 28,800 seconds(8 hours) by default.

You must set these as follows in my.cnf (take effect after mysqld is restarted)


If you don’t wakt to restart mysql, then run these tow commands

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

You can check the MySQL server’s timezone by the following command

SELECT @@global.time_zone; # check the global time zone 
SELECT @@session.time_zone; # check the current session timezone 
SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); # check the current time zone compared to UTC
SELECT UNIX_TIMESTAMP(); # check the current system timestamp

SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
SET @@session.time_zone = "+00:00"; 



  1. Should MySQL have its timezone set to UTC?

Version: MySQL 5.7

Graphic describing InnoDB Group Replication, MySQL Router and MySQL Shell

  • create 3 sandbox instance:

after login to mysqljs:


\connect root@localhost:3310

var cluster = dba.createCluster('DevCluster');




sudo mysqlrouter --bootstrap localhost:3310

mysqlsh --uri root@localhost:6446 --sql

select @@port;


explain SELECT * FROM `click_tripadvisor` where refid='W@OYAAoQK3oAAv-wD0AAAADF';
explain SELECT * FROM `click_tripadvisor` where provider_hotel_code='HOMRST';

set global slow_query_log=1; # open the slow_query_log option, to log the slow query into given file

show global variables like '%slow%';

select @@global.long_query_time; # default long query time

set global long_query_time = 0; # set 0 second to show the queery time

set global slow_query_log=0; # close the slow_query_log option

find / -name 'ip-10-200-107-82-slow.log'
watch ls -lh /usr/local/mariadb-10.0.36-linux-x86_64/data/ip-10-200-107-82-slow.log

# download pt-query-digest
chmod u+x pt-query-digest

# analyze the slow log
./pt-query-digest /usr/local/mariadb-10.0.36-linux-x86_64/data/ip-10-200-107-82-slow.log > slow-query.log.out
yum install -y perl-libwww-perl perl-Time-HiRes

# analyze the slow log use the tcpdump
tcpdump -s 65535 -x -nn -q -tttt -i any -c 9999999 port 3306 | gzip -c > /tmp/tcpdump.txt.gz 
gunzip -c /tmp/tcpdump.txt.gz | ./pt-query-digest --type tcpdump > /tmp/digest_output.txt

# dump mysql data
mongodump --host localhost --port 27017 --db dacms --out mongo_bak/2018-11-16 -u mongodb -p pass --authenticationDatabase admin
mysqldump -uroot -p --routines --events --all-databases --force > all-database.sql

# PT 2.1
 pt-query-digest --processlist h=localhost --print --no-report --interval=0.01 > slow.log
# PT 2.2
 pt-query-digest --processlist h=localhost --interval=0.01 --output=slowlog > slow.log 

# filter the slow log by the given conditions
pt-query-digest --since '2015-04-10 00:00:00' --util '2015-04-10-23:59:59' /usr/local/mariadb-10.0.36-linux-x86_64/data/ip-10-200-107-82-slow.log > slowquery.log
pt-query-digest --filter '$event->{db} && $event->{db} =~ /`linkcenter-data`/' /usr/local/mariadb-10.0.36-linux-x86_64/data/ip-10-200-107-82-slow.log > employee-db-slowquery.log
pt-query-digest --filter '$event->{db} && $event->{db} =~ /market/' /usr/local/mariadb-10.0.36-linux-x86_64/data/ip-10-200-107-82-slow.log > market-db-slowquery.log
pt-query-digest --filter '$event->{user} && $event->{user} =~ /root/' /usr/local/mariadb-10.0.36-linux-x86_64/data/ip-10-200-107-82-slow.log > root-user-slowquery.log

# min_examined_row_limit: how many records query needs to analyze before it gets written to the query log
# log_queries_not_using_indexes: log ALL queries that didn't use an index
# log_output: FILE or TABLE. keep it set to FILE
# show_query_log_use_global_variable: it controls which variables will use global rather than session variables
# log_all_rate_limit: e.g. log_slow_rate_limit=10 means only log 1/10th of sessions regradless of long_query_time
# log_slow_rate_type: can be set to query in which case log_slow_rate_limit will limit by query, not by session
# slow_query_log_always_write_time: will log queries that take this long regardless of the rate limiter setting
# log_slow_slave_statements: replication statements are not logged by default
# log_slow_sp_statements: useful with stored routines

# show the actual data size and index size
select engine, count(*) as TABLES, 
  concat(round(sum(TABLE_ROWS)/1000000,2), 'M') rows, 
  concat(round(sum(data_length)/(1024*1024*1024),2), 'G') DATA,
  concat(round(sum(index_length)/(1024*1024*1024),2), 'G') idx,
  concat(round(sum(data_length+index_length)/(1024*1024*1024),2), 'G') total_size,
  round(sum(index_length)/sum(data_length), 2) idxfrac
from information_schema.TABLES
where table_schema not in ('mysql', 'performance_schema', 'information_schema')
group by ENGINE
ORDER BY SUM(data_length + index_length) DESC LIMIT 10;

show variables like '%innodb_buffer_pool_size%';
show variables like '%packet%';

# catch teh actual innodb buffer read stream
mysqladmin -uroot -pderbysoft ext -ri1 | grep Innodb_buffer_pool_reads

# use mysqltunner to tune mysql configuration
wget -O
perl --user root --pass derbysoft

# temporarily enable the query log. You can view the details of execution
set long_query_time=0;
perl xxxx-slow.log
tail -fn100 xxx-slow.log

# dump the batch insert sql
mysqldump -t -u Username -pPassword Database Table --where="id=SqlCondition" --complete-insert

check the original post at

The innodb_buffer_pool_size the most important variable for entire MySQL server. Last few years, you have seen the drastic improvements in the InnoDB storage engine features. After Oracle acquiring the MySQL, the importance of InnoDB storage engine grows multiple times over MyISAM. Even though MySQL supports multiple pluggable storage engines, one of the main reasons is its ACID compliance properties. Recent improvements in the MySQL 5.7 such as online innodb_buffer_pool_size configuration made it as a highly flexible and allow us to get the best performance without long downtime.

Initially, when I start to configure the innodb_buffer_pool_size variable on my test environment, like most of us, I googled as,

What is MySQL innodb_buffer_pool_size?
Do I need to change MySQL innodb_buffer_pool_size?
What are the recommendations for innodb_buffer_pool_size in MySQL?
What are the best practices for configuring innodb_buffer_pool_size in MySQL?
How to MySQL innodb_buffer_pool_size?
What is the best value for MySQL innodb_buffer_pool_size?
How to choose MySQL innodb_buffer_pool_size?
What is the optimal value for MySQL innodb_buffer_pool_size?
How large should be the MySQL innodb_buffer_pool_size?

The google results for the above keywords given me insights about MySQL innodb_buffer_pool_size. In my recent task, I created a new MySQL replication slave server on my production environment. During this process, I just implemented, whatever I learned from my testing experience.

The result was unbelievable, I was running MySQL 5.5 with 8 core CPU and 32 GB memory on a Linux platform. My backup file was around 320GB. The backup file took around 28 hours to restore on the MySQL 5.7 with default configuration on the similar server machine. Most of the tables are in InnoDB storage engine. After applying those InnoDB configurations, the restoration took just 6 hours to complete.

The dramatic change in the restoration time makes me dive deeper into the MySQL performance tuning. There are around 450 settings in MySQL 5.7, but I feel the most important variable setting is innodb_buffer_pool_size. This post is all about only MySQL innodb_buffer_pool_size. On my environment, most of the tables are in InnoDB, so this makes sense. It may be different in your case, still better understanding of this variable makes the life easier in most cases.

  • Why InnoDB buffer pool?

From the early stages of MySQL, the most widely used storage engine and default storage engine until MySQL 5.5 was MyISAM. MyISAM is using Operating System file cache to cache data that queries are reading over and over again. InnoDB handles caching itself, within the InnoDB buffer pool. So it is highly required to have enough InnoDB buffer pool space.

  • What is there inside MySQL InnoDB buffer pool?

MySQL InnoDB buffer pool contains the following things inside:

Data caching – InnoDB data pages.
Indices caching – index data.
Buffering data – Dirty pages – data which are modified in memory but not yet flushed (written) to a data disk.
Internal structures – InnoDB buffer pool additionally stores the internal structures such as Adaptive Hash Index, row level locks, etc.

The unit of the above-mentioned objects are calculated as – InnoDB pages. Each InnoDB pages is 16k in size.

  • How to choose the optimal InnoDB buffer pool size?

InnoDB buffer pool size is configured by the variable – innodb_buffer_pool_size. Before MySQL 5.7, this variable can be configured only at the offline. From MySQL 5.7 this can be configured online, does not require a server restart.

In an existing server, you can see the default value of innodb_buffer_pool_size by using the following command:

SHOW VARIABLES LIKE ‘%innodb_buffer_pool_size%’;

It is recommended that the buffer pool should be capable of holding entire database. But when we consider the practical situation it is not always possible.

Let me tell you an example, my server machine has around 32GB of RAM. My database size is around 300 GB. In this scenario, it is not possible to allocate 300 GB of RAM to innodb_buffer_poool_size variable.

  • How to deal with this situation?

When we cannot keep an entire database in memory, we should try to keep at least working data set in memory. In most cases, we are not going to process the data of an entire table, so the required data set should be in memory. So it is always better to allocate around 75% – 80% of the total available memory of server machine to innodb_buffer_pool_size.

  • How to set innodb_buffer_pool_size value?

This variable can be configured online from MySQL 5.7 using the following command:

SET GLOBAL innodb_buffer_pool_size = (value in bytes);

You should be very cautious to set the value for this variable at online. Because, when you restart the server, you will lose this configuration value. It is recommended to keep track of this changes and when you restart the server, you must set this value on the configuration file my.cnf.

Before MySQL 5.7 you have to set this configuration offline. You can set this value by editing my.cnf configuration file. Open my.cnf file and under the [mysqld] section, edit the following line.

innodb_buffer_pool_size = X G;

Now restart the MySQL server, and check the new configuration value by running

SHOW VARIABLES LIKE ‘%innodb_buffer_pool_size%’;
Online method:
SET GLOBAL innodb_buffer_pool_size = 26843545600;

Offline method:
innodb_buffer_pool = 26G

Note: Server restart required here.

  • Is innodb_buffer_pool_size is the reason for slow performance?

There is no doubt that innodb_buffer_pool_size is one of the most important variables for MySQL server. In the right scenario having a larger innodb_buffer_pool_size, will increase your database server performance dramatically. But is this always true?. No. We should have enough buffer pool, but it is not the only cause for slow performance. You can verify this by analyzing the performance of the InnoDB buffer pool.

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.

  • When to decrease the innodb_buffer_pool_size?

Having larger innodb_buffer_pool_size is not always mandatory, but still, it depends on the database size and working data set.If it is a dedicated MySQL server machine, having excess memory will not be a problem, but when you are using shared server machine, having free memory will be useful for other applications and operating system.

You can use this command to check the memory status:


The sample output will be like as follows:


Total large memory allocated 26386366464
Dictionary memory allocated 23826297
Buffer pool size 1572672
Free buffers 8192
Database pages 1553364
Old database pages 573246
Modified db pages 36
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 881819, not young 18198964
0.02 youngs/s, 0.05 non-youngs/s
Pages read 681064, created 2749237, written 3988300
0.02 reads/s, 0.12 creates/s, 11.50 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1553364, unzip_LRU len: 0
I/O sum[5152]:cur[0], unzip sum[0]:cur[0]

Free buffers:

Having higher value for Free buffers is an indicator of you are having free InnoDB buffer pool. But make sure that, you are checking this value many times before coming to the conclusion. If you are seeing this higher value for a longer period of time, you can consider to decreasing the InnoDB buffer pool size.

InnoDB buffer pool hit ratio:

Buffer pool hit ratio = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100

Innodb_buffer_pool_hit_ration below – 99.9% indicates that – innodb_buffer_pool_could be increased.

  • What are the InnoDB buffer pool status variables?

You can view the InnoDB buffer pool status variables by running this command:

show global status like ‘%innodb_buffer_pool_pages%’;

The sample output will be:

mysql> show global status like ‘%innodb_buffer_pool_pages%’;
| Variable_name | Value |
| Innodb_buffer_pool_pages_data | 1553363 |
| Innodb_buffer_pool_pages_dirty | 14 |
| Innodb_buffer_pool_pages_flushed | 3994981 |
| Innodb_buffer_pool_pages_free | 8193 |
| Innodb_buffer_pool_pages_misc | 11116 |
| Innodb_buffer_pool_pages_total | 1572672 |
6 rows in set (0.03 sec)


innodb_buffer_pool_pages_data shows the number of dirty and clean data and index pages.

innodb_buffer_pool_pages_misc shows the number of pages that are busy because they have been allocated for administrative overhead such as row locks or the adaptive hash index.

innodb_buffer_pool_pages_free shows the free pages in InnoDB buffer pool – a large number of free pages over a longer period is a strong indicator that InnoDB buffer pool is too big and can easily be decreased.

innodb_buffer_pool_pages_dirty shows the number of InnoDB buffer pool data pages that are modified in memory but not yet written to the data files (dirty page flushing).

innodb_buffer_pool_pages_flushed: indicates the number of requests to flush the dirty pages from the InnoDB buffer pool.

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.

innodb_buffer_pool_wait_free: it indicates the number of times, queries has to wait for dirty pages to be flushed to disk. It is a counter, it counts how many times this flush has happened. If innodb_buffer_pool_wait_free > 0 is a strong indicator that the InnoDB buffer pool is too small.

innodb_buffer_pool_write_request – indicates the number of writes done to the buffer pool.The ratio of write requests to pages flushed indicates the number of rows changed in a block before it flushed to disk

Rows changes per flush = innodb_buffer_pool_write_request / innodb_buffer_pool_pages_flushed
Sample output:
mysql> show global status like ‘%innodb_buffer_pool_pages_data%’;
| Variable_name | Value |
| Innodb_buffer_pool_pages_data | 1527965 |
1 row in set (0.59 sec)

mysql> show global status like ‘%innodb_buffer_pool_pages_misc%’;
| Variable_name | Value |
| Innodb_buffer_pool_pages_misc | 44863 |
1 row in set (0.55 sec)

mysql> show global status like ‘%innodb_buffer_pool_pages_free%’;
| Variable_name | Value |
| Innodb_buffer_pool_pages_free | 92 |
1 row in set (0.58 sec)

mysql> show global status like ‘%innodb_buffer_pool_pages_dirty%’;
| Variable_name | Value |
| Innodb_buffer_pool_pages_dirty | 757 |
1 row in set (0.49 sec)

mysql> show global status like ‘%innodb_buffer_pool_pages_flushed%’;
| Variable_name | Value |
| Innodb_buffer_pool_pages_flushed | 839273 |
1 row in set (0.59 sec)

mysql> show global status like ‘%innodb_buffer_pool_reads%’;
| Variable_name | Value |
| Innodb_buffer_pool_reads | 1684867 |
1 row in set (0.58 sec)

mysql> show global status like ‘%innodb_buffer_pool_read_requests%’;
| Variable_name | Value |
| Innodb_buffer_pool_read_requests | 26439817181 |
1 row in set (0.61 sec)

mysql> show global status like ‘%innodb_buffer_pool_wait_free%’;
| Variable_name | Value |
| Innodb_buffer_pool_wait_free | 0 |
1 row in set (0.51 sec)

mysql> show global status like ‘%innodb_buffer_pool_write_request%’;
| Variable_name | Value |
| Innodb_buffer_pool_write_requests | 49063623 |
1 row in set (0.52 sec)

Some useful queries:
How much memory should allocate to InnoDB buffer pool?

Recommended InnoDB buffer pool size based on the all InnoDB data and indices with additional 50% memory:

set @idbdataindx = (select sum(data_length+index_length) from information_schema.tables where engine = ‘innodb’);

set @ibpsG = @idbdataindx * 1.5 / (102410241024);

select @ibpsG;

Sample output:

mysql> set @idbdataindx = (select sum(data_length+index_length) from information_schema.tables where engine = ‘innodb’);
Query OK, 0 rows affected, 1 warning (42.17 sec)

mysql> select @idbdataindx;
| @idbdataindx |
| 251380645888 |
1 row in set (0.50 sec)

mysql> set @ibpsG = @idbdataindx * 1.5 / (102410241024);
Query OK, 0 rows affected (0.50 sec)

mysql> select @ibpsG;
| @ibpsG |
| 351.174705505 |
1 row in set (0.61 sec)

  • How much actual GB of memory is in use by InnoDB buffer pool at this moment?

By multiplying data available in the buffer pool and InnoDB page (InnoDB buffer pool unit) size we can find the actual memory is in use by InnoDB buffer pool at this moment.

set @ibpdata = (select variable_value from information_schema.global_status where variable_name = ‘innodb_buffer_pool_pages_data’);

set @idbpgsize = (select variable_value from information_schema.global_status where variable_name = ‘innodb_page_size’);

set @ibpsize = @ibpdata * @idbpgsize / (102410241024);

select @ibpsize;

Sample output:

mysql> set @ibpdata = (select variable_value from information_schema.global_status where variable_name = ‘innodb_buffer_pool_pages_data’);

Query OK, 0 rows affected (0.50 sec)

mysql> set @idbpgsize = (select variable_value from information_schema.global_status where variable_name = ‘innodb_page_size’);

Query OK, 0 rows affected (0.51 sec)

mysql> set @ibpsize = @ibpdata * @idbpgsize / (102410241024);

Query OK, 0 rows affected (0.51 sec)

mysql> select @ibpsize;
| @ibpsize |
| 23.310394287109375 |
1 row in set (0.50 sec)

Note: In MySQL 5.7 the global status table moved to the performance_schema database. By default, it is disabled on the information_schema database.

Finally, there is no single solution for everyone, you have to understand your environment and decide wisely. Searching on the internet will give you some insights but it may not directly suitable to your environment. One thing I can say is, you have start somewhere, so learn it and master the task. I hope this post will give you some idea about InnoDB buffer pool. If you feel, I missed something here / any doubt or clarification on this post, please mention on the comment section.