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

I. Environmental optimization 1.1 memory optimization

Because the database host generally has a large memory, it uses huge page. And try to use 80% of the memory to trade space for time.

/ etc/sysctl.conf configuration parameters:

Vm.hugetlb_shm_group=3306

Kernel.shmmax=243805679616

Kernel.shmall=59522871

1.2 Virtual memory optimization

Because the database responds greatly to the access speed, swap is closed.

1.3 Network optimization

Because the company needs direct access to the database in the IDC computer room, the network needs to be optimized.

/ etc/sysctl.conf configuration parameters:

Net.core.somaxconn = 40000

Net.ipv4.tcp_max_syn_backlog = 40000

Net.ipv4.ip_local_port_range = 1024 65535

Net.ipv4.tcp_tw_reuse = 1

Net.ipv4.tcp_tw_recycle = 1

Net.core.wmem_max=12582912

Net.core.rmem_max=12582912

Net.ipv4.tcp_rmem= 10240 87380 12582912

Net.ipv4.tcp_wmem= 10240 87380 12582912

Net.ipv4.tcp_window_scaling = 1

Net.ipv4.tcp_timestamps = 1

Net.ipv4.tcp_sack = 1

Net.ipv4.tcp_no_metrics_save = 1

Net.core.netdev_max_backlog = 5000

1.4IO optimization

Minimize restrictions on IO. Therefore, to ensure that the disk reads and writes normally, you need to add the following / etc/sysctl.conf parameters:

Fs.file-max = 1000000

/ etc/security/limits.conf configuration:

* soft nofile 65535

* hard nofile 65535

Mysql hard memlock unlimited

Mysql soft memlock unlimited

II. Database Optimization 2.1 Source Code compilation Optimization

Use better compilation parameters:

-M64-Ofast-flto-march=native-funroll-loops-mfpmath=sse-static-g

Compile command:

Cmake.. -DCMAKE_CXX_FLAGS_DEBUG== "- DUNIV_DEBUG-DUNIV_SYNC_DEBUG"\

-DCMAKE_BUILD_TYPE=RelWithDebInfo\

-DCMAKE_C_FLAGS_RELWITHDEBINFO= "- M64-Ofast-flto-march=native-funroll-loops-mfpmath=sse-static-g"\

-DCMAKE_CXX_FLAGS_RELWITHDEBINFO= "- M64-Ofast-flto-march=native-funroll-loops-mfpmath=sse-static-g"\

-DBUILD_CONFIG=mysql_release\

-DFEATURE_SET=community\

-DWITH_EMBEDDED_SERVER=ON\

-DCMAKE_INSTALL_PREFIX=/usr/local/percona\

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock\

-DMYSQL_DATADIR=/data/mysql/data\

-DSYSCONFDIR=/etc\

-DMYSQL_TCP_PORT=3306\

-DWITH_ARCHIVE_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_PERFSCHEMA_STORAGE_ENGINE=1\

-DWITH_READLINE=system\

-DENABLED_LOCAL_INFILE=1\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci\

-DWITH_EXTRA_CHARSETS=all\

-DWITH_FAST_MUTEXES=ON\

-DWITH_ATOMIC_LOCKS=rwlocks

2.2 memory optimization

Try to use 80% of the memory as the database memory space.

Configuration parameters:

Innodb_buffer_pool_size=180G

Join_buffer_size=32M

Key_buffer_size=256M

Read_buffer_size=8388608

Read_rnd_buffer_size=4M

2.3 connection Pool Optimization

Connection pooling is used to load highly concurrent access.

Configuration parameters:

Thread_handling=pool-of-threads

Thread_pool_high_prio_mode=statements

Thread_pool_max_threads=100000

Thread_pool_size=48

Thread_pool_oversubscribe=10

Thread_pool_stall_limit=300

2.4IO optimization

Configuration parameters:

Sync_binlog=1000

Innodb_read_io_threads=48

Innodb_thread_concurrency=0

Innodb_use_native_aio=1

Innodb_write_io_threads=48

Innodb_flush_method=ALL_O_DIRECT

2.5 Network optimization

Configuration parameters:

Max_connections=1000

Net_buffer_length=1M

2.6 structural optimization

Partition some large tables, such as crm_consume, crm_consume_detail, crm_customer, etc., to optimize the query.

2.7 Comprehensive optimization

/ etc/my.cnf configuration parameters:

[mysqld]

Audit_log_rotations=5

Audit_log_rotate_on_size=20480000

Slow_query_log=1

Slow_query_log_file=/data/mysql/data/db4-slow.log

Long_query_time=5

Back_log=1024

Big_tables=1

Bind_address=0.0.0.0

Binlog_cache_size=8M

Binlog_format=row

Basedir=/usr/local/percona

Binlog_stmt_cache_size=8M

Datadir=/data/mysql/data/

Delayed_queue_size=10000

Enforce-gtid-consistency=ON

Default_time_zone=+8:00

Event_scheduler=1

Expire_logs_days=7

Federated

Gtid_mode=ON

Innodb_additional_mem_pool_size=64M

Innodb_buffer_pool_instances=16

Metadata_locks_hash_instances=16

Table_open_cache_instances=16

Innodb_buffer_pool_size=180G

Innodb_data_file_path=ibdata1:1024M:autoextend

Innodb_data_home_dir=/data/mysql/data/

Innodb_file_per_table=1

Innodb_flush_log_at_trx_commit=2

Innodb_autoinc_lock_mode=2

Innodb_flush_method=ALL_O_DIRECT

Innodb_flush_neighbors=0

Innodb_io_capacity=1000

Innodb_lock_wait_timeout=50

Innodb_log_buffer_size=512M

Innodb_log_file_size=4096M

Innodb_log_files_in_group=3

Innodb_log_group_home_dir=/data/mysql/data/

Innodb_max_dirty_pages_pct=75

Innodb_old_blocks_pct=30

Innodb_old_blocks_time=1000

Innodb_open_files=4096

Innodb_purge_threads=1

Innodb_random_read_ahead=1

Innodb_read_io_threads=48

Innodb_thread_concurrency=0

Innodb_use_native_aio=1

Innodb_write_io_threads=48

Join_buffer_size=32M

Key_buffer_size=256M

Log-bin=mysql-bin

Log-error=/var/log/mysql-error.log

Log_output=FILE

Log_slave_updates=1

Max_allowed_packet=128M

Max_connect_errors=10000000

Max_connections=1000

Max_heap_table_size=64M

Max_tmp_tables=1024

Myisam_recover=FORCE,BACKUP

Myisam_sort_buffer_size=128M

Net_buffer_length=1M

Open_files_limit=65535

Pid-file=/data/mysql/data/mysql.pid

Port=3306

Query_cache_size=0

Query_cache_type=0

Report_host=192.168.201.11

Report_port=3306

Read_buffer_size=8388608

Read_rnd_buffer_size=4M

Relay-log=mysql-relay-bin

Log_warnings=9

Sync_binlog=1000

Rpl_semi_sync_master_enabled=1

Rpl_semi_sync_master_timeout=1000

Server-id=11

Skip-external-locking

Skip-name-resolve

Socket=/tmp/mysql.sock

Sort_buffer_size=16M

Table_definition_cache=4096

Transaction_isolation=read-committed

Table_open_cache=16384

Thread_cache_size=2048

Thread_stack=1048576

Tmp_table_size=64M

Slave_net_timeout=30

Master-info-repository=TABLE

Relay-log-info-repository=TABLE

Sync-master-info=1

Slave-parallel-workers=4

Binlog-checksum=CRC32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Thread_handling=pool-of-threads

Thread_pool_high_prio_mode=statements

Thread_pool_max_threads=100000

Thread_pool_size=48

Thread_pool_oversubscribe=10

Thread_pool_stall_limit=300

Innodb_monitor_enable ='%'

Performance_schema = ON

Performance_schema_instrument ='% = on'

III. Performance testing 3.1 benchmark testing

The database oltp test is carried out by using sysbench tool, and the test conclusions are as follows:

OLTP test statistics:

Queries performed:

Read: 481348

Write: 137528

Other: 68764

Total: 687640

Transactions: 34382 (572.21 per sec.)

Read/write requests: 618876 (10299.77 per sec.)

Other operations: 68764 (1144.42 per sec.)

Ignored errors: 0 (0.00 per sec.)

Reconnects: 0 (0.00 per sec.)

General statistics:

Total time: 60.0864s

Total number of events: 34382

Total time taken by event execution: 3842.8699s

Response time:

Min: 23.10ms

Avg: 111.77ms

Max: 323.79ms

Approx. 95 percentile: 166.95ms

Threads fairness:

Events (avg/stddev): 537.2188 take 9.58

Execution time (avg/stddev): 60.0448Universe 0.02

It is known that:

Tps:572.21 per sec

Qps:10299.77 per sec.

3.2 Test after optimization

The best test conclusions are as follows:

OLTP test statistics:

Queries performed:

Read: 7265944

Write: 2075984

Other: 1037992

Total: 10379920

Transactions: 518996 (864.91 per sec.)

Read/write requests: 9341928 (15568.43 per sec.)

Other operations: 1037992 (1729.83 per sec.)

Ignored errors: 0 (0.00 per sec.)

Reconnects: 0 (0.00 per sec.)

General statistics:

Total time: 600.0560s

Total number of events: 518996

Total time taken by event execution: 38354.8485s

Response time:

Min: 6.49ms

Avg: 73.90ms

Max: 252.93ms

Approx. 95 percentile: 118.04ms

Threads fairness:

Events (avg/stddev): 8109.3125 Universe 1090.72

Execution time (avg/stddev): 599.2945 + 0.45

It is known that:

Tps:864.91 per sec

Qps:15568.43 per sec

3.3SQL test

Using a single SQL statement for testing, the performance is also greatly improved.

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.