Shulou(Shulou.com)06/01 Report--

Yesterday, when debugging MySQL transaction critical state with gdb, I found that some scenarios may be more complicated than I thought, so I also quickly swept through the end of yesterday's test, but indicated the meaning. At this point, I will compare Oracle's critical transaction state later, which is quite interesting.

Today simply wrote a few scripts to continue the sysbench stress testing of MySQL in a test environment.

First break through the bottleneck of 1000 connection resource settings

On the basis of the previous one, we ensured that we could meet the impact of 1000 connections in a short time, and made adjustments from various aspects, one of which gradually fell to IO throughput, and the redo log size setting suddenly became the focus and the most important.

Of course, in this test, my idea is to maintain continuous growth in performance, while adjusting and optimizing.

First of all, we can break through the 1000 connection mark. Let's run a preliminary test with the following script. The test lasts 10 seconds and see if we can initialize 1500 connections.

sysbench /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua --mysql-user=root --mysql-port=3306 --mysql-socket=/home/mysql/s1/s1.sock --mysql-host=localhost --mysql-db=sysbenchtest --tables=10 --table-size= 500000--threads=1500 --report-interval=5 --time= 10run Unexpectedly, the following error was thrown. Note that the error here doesn't seem to be database level anymore.

FATAL: unable to connect to MySQL server on socket '/home/mysql/s1/s1.sock', aborting...

FATAL: error 2001: Can't create UNIX socket (24)

PANIC: unprotected error in call to Lua API (cannot open /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua: Too many open files)

PANIC: Unprotected error in call to Lua API (cannot open /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua: Too many open files) is not the limit on the number of sockets supported, we have adjusted the value of process. The above command can be written in a different posture, that is, from socket connection to common TCP/IP connection.

sysbench /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua --mysql-user=perf_test --mysql-port=3306 --mysql-host=10.127.128.78 --mysql-password=perf_test --mysql-db=sysbenchtest --tables=10 --table-size= 500000--threads=1500 --report-interval=5 --time=10 run You can see that the error is displayed differently, But I can already see the meaning.

FATAL: unable to connect to MySQL server on host '10.127.128.78', port 3306, aborting...

FATAL: error 2004: Can't create TCP/IP socket (24)

PANIC: unprotected error in call to Lua API (cannot open /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua: Too many open files)

PANIC: unprotected error in call to Lua API (cannot open /home/sysbench/sysbench-1.0.3/src/lua/oltp_read_write.lua: Too many open files) This should be clear, that is, the kernel resource setting nofile adjustment.

Modify the/etc/security/limits.d/90-nproc.conf file and add the following sections, which will take effect after logging in again.

* soft nproc 65535

* soft nofile 65535

* hard nofile 65535

After restarting MySQL, you can see that the settings take effect.# cat /proc/`pidof mysqld`/limits | egrep "(processes|files)"

Max processes 65535 256589 processes

Max open files 65535 65535 files

Adjust prepare

We continue to switch on the pressure mode, and immediately the error changes. It's a mistake we're familiar with, and we hit it right from the start.

FATAL: `thread_init' function failed: /usr/local/share/sysbench/oltp_common.lua:273: SQL API error

(last message repeated 1 times)

FATAL: mysql_stmt_prepare() failed

FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 100000)"

FATAL: `thread_init' function failed: /usr/local/share/sysbench/oltp_common.lua:273: SQL API error

Here are a few related parameters.

Number of times Com_stmt_close prepare statements are closed

Number of times Com_stmt_execute prepare statement is executed

The number of times Com_stmt_prepare statements are created This type of scenario may not be universal, because in some scenarios, continuous connections are not mass connections in a short period of time, and this parameter max_prepared_stmt_count does not necessarily need to be set very large.

For example, I have an environment with nearly 500 connections, but max_prepared_stmt_count is still the default value of 16382, and it has been running stably for a long time.# mysqladmin pro|wc -l

424

# mysqladmin var|grep max_prepared_stmt_count

| max_prepared_stmt_count | 16382

In our stress scenario, a large number of connections will be created in a short time, and for performance and safety reasons, we will use the prepare method. We will test the power of sysbench connections in 10 seconds to see how the number of prepare statements changes.

Use show global status like '%stmt%' to get a basic data change.

mysql> show global status like '%stmt%';

+----------------------------+--------+

| Variable_name | Value |

+----------------------------+--------+

| Com_stmt_execute | 477403 |

| Com_stmt_close | 91000 |

| Com_stmt_fetch | 0 |

| Com_stmt_prepare | 298844 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 0 |

| Com_stmt_reprepare | 0 |

| Prepared_stmt_count | 0 |

+------------------------+----------------------------

mysql> show global status like '%stmt%';

+----------------------------+--------+

| Variable_name | Value |

+----------------------------+--------+

| Binlog_stmt_cache_disk_use | 0 |

| Binlog_stmt_cache_use | 0 |

| Com_stmt_execute | 477403 |

| Com_stmt_close | 91000 |

| Com_stmt_fetch | 0 |

| Com_stmt_prepare | 398045 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 0 |

| Com_stmt_reprepare | 0 |

| Prepared_stmt_count | 98091 |

+----------------------------+--------+

According to the current basic situation, we need to set it to 91*1500=136500, leaving a certain margin, so we can set it to 150000.

Then continue to test, you will see this parameter gradually soaring.

mysql> show global status like '%stmt%';

+----------------------------+--------+

| Variable_name | Value |

+----------------------------+--------+

| Binlog_stmt_cache_disk_use | 0 |

| Binlog_stmt_cache_use | 0 |

| Com_stmt_execute | 624184 |

| Com_stmt_close | 91000 |

| Com_stmt_fetch | 0 |

| Com_stmt_prepare | 537982 |

| Com_stmt_reset | 0 |

| Com_stmt_send_long_data | 0 |

| Com_stmt_reprepare | 0 |

| Prepared_stmt_count | 136500 |

+----------------------------

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

13417 mysql 20 0 34.8g 11g 12m S 1324.2 35.2 19:18.71 /usr/local/mysql/bin/mysqld --defaults-file=/home

23108 root 20 0 8924m 1.6g 2148 S 212.3 5.0 1:32.73 sysbench /home/sysbench/sysbench-1.0.3/src/lua/olt

The following diagram is the TPS diagram I got using redo of 100M,200M,500,1G.

Through this figure, we can also see a basic load situation. At 1G, TPS is relatively stable, but redo switching will still have some jitter. Of course redo is not the bigger the better,

5.5 The setting in is less than 4G, after 5.6 is less than 512G

We continue to optimize.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.