Linux mysql 并发优化_linux服务端并发优化

Mysql优化方面：

1.sql语句优化

创建索引： CREATE INDEX idx_user_openid_ptid ON tb_pt_user (openid,ptid);

2.服务器配置优化，主要优化缓存

配置文件位置 ：  /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

innodb_buffer_pool_size = 1G

innodb_additional_mem_pool_size = 64M

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 128M

innodb_log_file_size = 512M

innodb_log_files_in_group = 2

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 60

# SAFETY #

max-allowed-packet             = 16M

max-connect-errors             = 1000000

skip-name-resolve

sysdate-is-now                 = 1

# CACHES AND LIMITS #

tmp-table-size                 = 32M

max-heap-table-size            = 32M

query-cache-type               = 0

query-cache-size               = 0

max-connections                = 15000

thread-cache-size              = 50

open-files-limit               = 65535

table-definition-cache         = 1024

table-open-cache               = 2048

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

port = 3306

default-character-set=utf8

Nginx优化

配置文件位置 ：/etc/nginx/nginx.conf

# For more information on configuration, see:

#   * Official English Documentation: http://nginx.org/en/docs/

#   * Official Russian Documentation: http://nginx.org/ru/docs/

user              nginx;

worker_processes  8;   #1; the server 172.16.30.201 has 8 CPUs, modified by Shao Xiaoling

worker_cpu_affinity 00000001 00000010 00000100 00001000 00010000 00100000 01000000 10000000; # the server 172.16.30.201 has 8 CPUs, modified by Shao Xiaoling

worker_rlimit_nofile 150000;

error_log  /var/log/nginx/error.log;

#error_log  /var/log/nginx/error.log  notice;

#error_log  /var/log/nginx/error.log  info;

pid        /var/run/nginx.pid;

events {

worker_connections 150000;

multi_accept on;

use   epoll;             #epoll是多路复用IO(I/O Multiplexing)中的一种方式,但是仅用于linux2.6以上内核,可以大大提高nginx的性能; modified by Shao Xiaoling

}

http {

include       /etc/nginx/mime.types;

default_type  application/octet-stream;

log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '

'$status $body_bytes_sent "$http_referer" '

'"$http_user_agent" "$http_x_forwarded_for"';

access_log  /var/log/nginx/access.log  main;

sendfile        on;

tcp_nopush     on;

tcp_nodelay    on;

#keepalive_timeout  0;

keepalive_timeout  30;

keepalive_requests 100000;

reset_timedout_connection on;

client_body_timeout 10;

send_timeout 2;

#gzip  on;

gzip  on;  #modified by Shao Xiaoling

#the following gzip parameters added by shao Xiaoling

gzip_min_length 1k;

gzip_buffers    4 16k;

gzip_comp_level 6;

gzip_types text/html text/plain text/css text/javascript application/json application/javascript application/x-javascript application/xml;

gzip_vary on;

# Load config files from the /etc/nginx/conf.d directory

# The default server is in conf.d/default.conf

include /etc/nginx/conf.d/*.conf;

}

Tcp协议栈优化

1.安装服务：

yum  -y  install  netperf  iperf

2.修改配置文件

文件位置： /etc/sysctl.conf

kernel.sysrq = 0

kernel.panic = 20

kernel.softlockup_panic=1

kernel.core_uses_pid = 1

kernel.msgmnb = 65536

kernel.msgmax = 65536

kernel.shmmax = 25769803776

kernel.shmall = 4294967296

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

kernel.hung_task_timeout_secs = 0

kernel.core_pattern = core

fs.file-max = 655350

net.ipv4.ip_forward = 0

net.ipv4.conf.default.rp_filter = 1

net.ipv4.conf.default.accept_source_route = 0

net.ipv4.tcp_syncookies = 1

net.ipv4.tcp_fin_timeout = 3

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

net.ipv4.tcp_timestamps = 0

net.ipv4.ip_local_port_range = 2000 65000

net.ipv4.tcp_max_syn_backlog = 3240000

net.ipv4.tcp_max_tw_buckets = 1440000

net.ipv4.route.gc_timeout = 100

net.ipv4.tcp_syn_retries = 2

net.ipv4.tcp_synack_retries = 2

net.ipv4.tcp_sack = 1

net.nf_conntrack_max = 2048576

net.netfilter.nf_conntrack_max = 2048576

net.netfilter.nf_conntrack_tcp_timeout_established = 60

net.netfilter.nf_conntrack_tcp_timeout_time_wait = 30

net.netfilter.nf_conntrack_tcp_timeout_close_wait = 30

net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 30

net.ipv4.tcp_window_scaling = 1

net.ipv4.tcp_mem = 3097431 4129911 6194862

net.ipv4.tcp_rmem = 4096 87380 16777216

net.ipv4.tcp_wmem = 4096 65536 16777216

net.ipv4.tcp_congestion_control = cubic

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_keepalive_time = 30

net.ipv4.tcp_keepalive_probes = 2

net.ipv4.tcp_keepalive_intvl = 15

net.ipv6.conf.all.disable_ipv6 = 1

net.ipv6.conf.default.disable_ipv6 = 1

net.ipv6.conf.lo.disable_ipv6 = 1

net.bridge.bridge-nf-call-ip6tables = 0

net.bridge.bridge-nf-call-iptables = 0

net.bridge.bridge-nf-call-arptables = 0

net.core.wmem_default = 8388608

net.core.rmem_default = 8388608

net.core.rmem_max = 16777216

net.core.wmem_max = 16777216

net.core.netdev_max_backlog = 262144

net.core.somaxconn = 3240000

vm.swappiness = 5

vm.zone_reclaim_mode = 0

vm.overcommit_memory = 1

vm.panic_on_oom = 0

vm.drop_caches = 1

vm.dirty_ratio = 30

vm.dirty_background_ratio = 30

vm.dirty_writeback_centisecs = 50000

vm.vfs_cache_pressure = 200

vm.min_free_kbytes = 256000

3.使配置文件生效

sysctl  -p  /etc/sysctl.conf

在mysql，nginx修改配置文件时先stop服务再重新启动服务

查看mysql服务状态/etc/init.d/mysqld   status

查看nginx服务状态/etc/init.d/nginx     status

启动mysql服务状态/etc/init.d/mysqld   start

启动nginx服务状态/etc/init.d/nginx     start

Mysql命令

{start|stop|status|restart|condrestart|try-restart|reload|force-reload}

Nginx命令

{start|stop|reload|configtest|status|force-reload|upgrade|restart|reopen_logs}

配置文件备份是按照加上日期。

列如：my.cnf    my.cnf.20160425        2016年4月25日备份版本1

my.cnf.20160425_02     2016年4月25日备份版本2