mysql数据库环境优化

zhangxinba

一、环境优化
1.1内存优化
　　由于数据库主机一般内存较大，因此采用huge page。而且尽量使用80%的内存，以空间换时间。
　　/etc/sysctl.conf配置参数：
　　vm.hugetlb_shm_group=3306
　　kernel.shmmax=243805679616
　　kernel.shmall=59522871
1.2虚拟内存优化
　　由于数据库对存取速度反应较大，因此关闭swap。
1.3网络优化
　　由于公司需要直接访问IDC机房中数据库，因此网络需要优化。
　　/etc/sysctl.conf配置参数：
　　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优化
　　尽量减少对IO的限制。因此在保障硬盘读写正常的情况下，需要增加如下/etc/sysctl.conf参数：
　　fs.file-max = 1000000
　　/etc/security/limits.conf配置：
　　*                soft  nofile                  65535
　　*                hard    nofile           65535
　　mysql   hard   memlock  unlimited
　　mysql   soft    memlock  unlimited
二、数据库优化
2.1源码编译优化
　　采用较优的编译参数：
　　-m64 -Ofast -flto -march=native -funroll-loops -mfpmath=sse -static -g
　　编译命令：
　　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内存优化
　　尽量采用内存的80%，作为数据库内存空间。
　　配置参数：
　　innodb_buffer_pool_size=180G
　　join_buffer_size=32M
　　key_buffer_size=256M
　　read_buffer_size=8388608
　　read_rnd_buffer_size=4M
2.3连接池优化
　　采用连接池，以负载高并发的访问。
　　配置参数：
　　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优化
　　配置参数：
　　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网络优化
　　配置参数：
　　max_connections=1000
　　net_buffer_length=1M
2.6结构优化
　　对一些大表进行分区处理，如crm_consume、crm_consume_detail、crm_customer等，以优化查询。
2.7综合优化
　　/etc/my.cnf配置参数：
　　[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'
三、性能测试
3.1基准测试
　　利用sysbench工具进行数据库oltp测试，得出测试结论如下：
　　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/9.58
　　execution time (avg/stddev):   60.0448/0.02
　　可知：
　　tps：572.21 per sec
　　qps：10299.77 per sec.
3.2优化后测试
　　最好的测试结论如下：
　　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/1090.72
　　execution time (avg/stddev):   599.2945/0.45
　　可知：
　　tps：864.91 per sec
　　qps：15568.43 per sec
3.3SQL测试
　　采用单个SQL语句进行测试，性能也有很大的提升。