mysql數(shù)據(jù)庫環(huán)境優(yōu)化-創(chuàng)新互聯(lián)

一、環(huán)境優(yōu)化

1.1內(nèi)存優(yōu)化

由于數(shù)據(jù)庫主機一般內(nèi)存較大，因此采用huge page。而且盡量使用80%的內(nèi)存，以空間換時間。

成都創(chuàng)新互聯(lián)是一家專注于網(wǎng)站建設(shè)、網(wǎng)站設(shè)計與策劃設(shè)計,水富網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)10余年,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:水富等地區(qū)。水富做網(wǎng)站價格咨詢:13518219792

/etc/sysctl.conf配置參數(shù)：

vm.hugetlb_shm_group=3306

kernel.shmmax=243805679616

kernel.shmall=59522871

1.2虛擬內(nèi)存優(yōu)化

由于數(shù)據(jù)庫對存取速度反應(yīng)較大，因此關(guān)閉swap。

1.3網(wǎng)絡(luò)優(yōu)化

由于公司需要直接訪問IDC機房中數(shù)據(jù)庫，因此網(wǎng)絡(luò)需要優(yōu)化。

/etc/sysctl.conf配置參數(shù)：

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優(yōu)化

盡量減少對IO的限制。因此在保障硬盤讀寫正常的情況下，需要增加如下/etc/sysctl.conf參數(shù)：

fs.file-max = 1000000

/etc/security/limits.conf配置：

*               soft nofile                 65535

*               hard   nofile          65535

mysql  hard  memlock unlimited

mysql  soft   memlock unlimited

二、數(shù)據(jù)庫優(yōu)化

2.1源碼編譯優(yōu)化

采用較優(yōu)的編譯參數(shù)：

-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內(nèi)存優(yōu)化

盡量采用內(nèi)存的80%，作為數(shù)據(jù)庫內(nèi)存空間。

配置參數(shù)：

innodb_buffer_pool_size=180G

join_buffer_size=32M

key_buffer_size=256M

read_buffer_size=8388608

read_rnd_buffer_size=4M

2.3連接池優(yōu)化

采用連接池，以負載高并發(fā)的訪問。

配置參數(shù)：

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優(yōu)化

配置參數(shù)：

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網(wǎng)絡(luò)優(yōu)化

配置參數(shù)：

max_connections=1000

net_buffer_length=1M

2.6結(jié)構(gòu)優(yōu)化

對一些大表進行分區(qū)處理，如crm_consume、crm_consume_detail、crm_customer等，以優(yōu)化查詢。

2.7綜合優(yōu)化

/etc/my.cnf配置參數(shù)：

[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基準(zhǔn)測試

利用sysbench工具進行數(shù)據(jù)庫oltp測試，得出測試結(jié)論如下：

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優(yōu)化后測試

最好的測試結(jié)論如下：

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語句進行測試，性能也有很大的提升。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機、免備案服務(wù)器”等云主機租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

網(wǎng)頁題目：mysql數(shù)據(jù)庫環(huán)境優(yōu)化-創(chuàng)新互聯(lián)
當(dāng)前路徑：http://muchs.cn/article48/dhgihp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App設(shè)計、自適應(yīng)網(wǎng)站、微信公眾號、面包屑導(dǎo)航、小程序開發(fā)、全網(wǎng)營銷推廣

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)