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        2016425日备份版本1

               my.cnf.20160425_02     2016425日备份版本2

 

posted on 2016-04-25 17:22  shininguang  阅读(941)  评论(0编辑  收藏  举报