servidor de sobrecarga do MySQL / mariadb [fechado]

1

Eu tenho VPS openVZ com 1gb de ram, 1gb de swap e 3 cpu.

Ele está rodando o Ubuntu com o Nginx, PHP, MySQL (que substituí com o mariadb).

Este é o TOP:

     3934 www       20   0 30908  10m 4152 S    2  1.0   0:27.26 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/local/php/etc/php-fpm.conf            
 3946 www       20   0 30664   9m 4176 S    1  1.0   0:25.86 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/local/php/etc/php-fpm.conf            
 3948 www       20   0 30856  10m 4228 S    1  1.0   0:23.16 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/local/php/etc/php-fpm.conf            
 3947 www       20   0 28548 7064 3392 S    0  0.7   1:01.48 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/local/php/etc/php-fpm.conf            
    1 root      20   0  3360 1744 1276 S    0  0.2   0:00.84 init                                                                                     
    2 root      20   0     0    0    0 S    0  0.0   0:00.00 [kthreadd/7615]                                                                          
    3 root      20   0     0    0    0 S    0  0.0   0:00.00 [khelper/7615]                                                                           
   95 root      20   0  2788  700  532 S    0  0.1   0:00.00 upstart-udev-bridge --daemon                                                             
  114 root      20   0  2796 1016  736 S    0  0.1   0:00.06 /sbin/udevd --daemon                                                                     
  143 root      20   0  2792  652  364 S    0  0.1   0:00.01 /sbin/udevd --daemon                                                                     
  146 root      20   0  2792  652  364 S    0  0.1   0:00.00 /sbin/udevd --daemon                                                                     
  185 root      20   0  2800  516  372 S    0  0.0   0:00.00 upstart-socket-bridge --daemon                                                           
  229 root      20   0  9144 6456 1968 S    0  0.6   0:02.22 /usr/sbin/munin-node                                                                     
  308 root      20   0  6636 2328 1896 S    0  0.2   0:00.11 /usr/sbin/sshd -D                                                                        
  533 root      20   0  2516  988  832 S    0  0.1   0:00.00 /usr/sbin/xinetd -dontfork -pidfile /var/run/xinetd.pid -stayalive -inetd_compat -inetd_i
  546 root      20   0  2572  920  732 S    0  0.1   0:00.16 cron                                                                                     
  607 syslog    20   0  2356  724  584 S    0  0.1   0:00.13 /sbin/syslogd -u syslog                                                                  
  649 root      20   0  3412 1572 1260 S    0  0.1   0:00.28 /bin/bash /usr/bin/mysqld_safe                                                           
 1581 mysql     20   0  994m 234m 8720 S    0 22.9  56:13.67 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plug
 1582 root      20   0  2104  572  500 S    0  0.1   0:00.28 logger -t mysqld -p daemon.error                                                         
 1793 root      20   0 12944  892  376 S    0  0.1   0:00.00 /usr/sbin/saslauthd -a pam -c -m /var/run/saslauthd -n 2                                 
 1795 root      20   0 12944  572   56 S    0  0.1   0:00.00 /usr/sbin/saslauthd -a pam -c -m /var/run/saslauthd -n 2                                 
 1799 root      20   0 13728 7364 1324 S    0  0.7   0:09.54 /usr/bin/python /usr/bin/supervisord                                                     
 1860 root      20   0 15120 1864  580 S    0  0.2   0:01.42 sendmail: MTA: accepting connections                                                     
 2392 root      20   0  9596 3068 2444 S    0  0.3   0:00.21 sshd: root@pts/0                                                                         
 2408 root      20   0  3440 1788 1432 S    0  0.2   0:00.00 -bash                                                                                    
 3170 root      20   0  2672 1180  964 T    0  0.1   0:00.30 top                                                                                      
 3926 root      20   0  5772  776  280 S    0  0.1   0:00.00 nginx: master process /usr/local/nginx/sbin/nginx -c /usr/local/nginx/conf/nginx.conf    
 3927 www       20   0 16232  11m  868 S    0  1.1   0:01.65 nginx: worker process                                                                    
 3932 root      20   0 28356 4904 1520 S    0  0.5   0:15.85 /usr/local/php/bin/php-cgi --fpm --fpm-config /usr/

Ele está funcionando bem quando eu reinicio o servidor, mas depois de 30min eu não consigo logar no phpmyadmin por exemplo. Está apenas carregando e nunca carrega.

Aqui está my.cnf:

    # Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 20M
max_allowed_packet = 1M
table_open_cache = 64
max_connections = 30
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

open_files_limit=10192
query_cache_size=150M
join_buffer_size=128K
thread_cache_size=14
table_cache=64
tmp_table_size=16M
max_heap_table_size=16M
innodb_buffer_pool_size=0

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/var
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/var
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

nginx.conf:

   user  www www;

worker_processes 1;

error_log  /home/wwwlogs/nginx_error.log  crit;

pid        /usr/local/nginx/logs/nginx.pid;

#Specifies the value for maximum file descriptors that can be opened by this process.
worker_rlimit_nofile 51200;

events
    {
        use epoll;
        worker_connections 51200;
    }

http
    {
        include       mime.types;
        default_type  application/octet-stream;

        server_names_hash_bucket_size 128;
        client_header_buffer_size 32k;
        large_client_header_buffers 4 32k;
        client_max_body_size 50m;

        sendfile on;
        tcp_nopush     on;

        keepalive_timeout 600;

        tcp_nodelay on;

        fastcgi_connect_timeout 9000;
        fastcgi_send_timeout 9000;
        fastcgi_read_timeout 9000;
        fastcgi_buffer_size 64k;
        fastcgi_buffers 4 64k;
        fastcgi_busy_buffers_size 128k;
        fastcgi_temp_file_write_size 256k;

        gzip on;
        gzip_min_length  1k;
        gzip_buffers     4 16k;
        gzip_http_version 1.0;
        gzip_comp_level 2;
        gzip_types       text/plain application/x-javascript text/css application/xml;
        gzip_vary on;

        #limit_zone  crawler  $binary_remote_addr  10m;

        #log format
        log_format  access  '$remote_addr - $remote_user [$time_local] "$request" '
             '$status $body_bytes_sent "$http_referer" '
             '"$http_user_agent" $http_x_forwarded_for';

server
    {
        listen       80;
        server_name my-server.com;
        index index.html index.htm index.php;
        root  /home/wwwroot;

        location ~ .*\.(php|php5)?$
            {
                try_files $uri =404;
                fastcgi_pass  unix:/tmp/php-cgi.sock;
                fastcgi_index index.php;
                include fcgi.conf;
                    fastcgi_connect_timeout 9000;
                    fastcgi_send_timeout 9000;
                    fastcgi_read_timeout 9000;
                    fastcgi_buffer_size 64k;
                    fastcgi_buffers 4 64k;
                    fastcgi_busy_buffers_size 128k;
                    fastcgi_temp_file_write_size 256k;
            }

        location /status {
            stub_status on;
            access_log   off;
        }

        location ~ .*\.(gif|jpg|jpeg|png|bmp|swf)$
            {
                expires      30d;
            }

        location ~ .*\.(js|css)?$
            {
                expires      12h;
            }

            location /munin/ {
                alias /var/cache/munin/www/;
                index index.html index.htm index.php;
            }   

        access_log  /home/wwwlogs/access.log  access;
    }
include vhost/*.conf;
}

mysqltuner mostra isso:

    ------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 10.0.8-MariaDB-1~precise-log is EOL software!  Upgrade soon!
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 44M (Tables: 27)
[--] Data in InnoDB tables: 48K (Tables: 3)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 1
Use of uninitialized value in addition (+) at /usr/bin/mysqltuner line 515, <>
    line 2 (#1)
    (W uninitialized) An undefined value was used as if it were already
    defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
    To suppress this warning assign a defined value to your variables.

    To help you figure out what was undefined, perl will try to tell you the
    name of the variable (if any) that was undefined. In some cases it cannot
    do this, so it also tells you what operation you used the undefined value
    in.  Note, however, that perl optimizes your program and the operation
    displayed in the warning may not necessarily appear literally in your
    program.  For example, "that $foo" is usually optimized into "that "
    . $foo, and the warning will refer to the concatenation (.) operator,
    even though there is no . in your program.

Use of uninitialized value $myvar{"have_innodb"} in string eq at
    /usr/bin/mysqltuner line 633, <> line 2 (#1)

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 1m 21s (10K q [1.402 qps], 738 conn, TX: 1M, RX: 491K)
[--] Reads / Writes: 8% / 92%
[--] Total buffers: 496.0M global + 416.0K per thread (100 max threads)
[OK] Maximum possible memory usage: 536.6M (52% of installed RAM)
[OK] Slow queries: 1% (178/10K)
[OK] Highest usage of available connections: 7% (7/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/11.3M
[!!] Key buffer hit rate: 79.5% (71K cached / 14K reads)
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 337 sorts)
[!!] Temporary tables created on disk: 39% (496 on disk / 1K total)
[OK] Thread cache hit rate: 99% (7 created / 738 connections)
[OK] Table cache hit rate: 72% (89 open / 122 opened)
[OK] Open file limit used: 0% (69/10K)
[!!] Table locks acquired immediately: 88%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Upgrade MySQL to version 4+ to utilize query caching
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

Alguém tem idéia do que está errado na configuração do servidor. Estou executando alguns scripts muito simples que normalmente executo em hospedagem gerenciada compartilhada sem qualquer problema.

    
por user211223 28.02.2014 / 17:36

1 resposta

1

Seu buffer de classificação é muito pequeno, aumentando o tmp_table_size e fazer mais trabalho na memória pode ajudar. Mas o grande problema aqui é nas consultas que você está executando no banco de dados / no seu esquema de banco de dados - consulte "Tabelas temporárias criadas no disco". O MySQL está tendo que processar uma grande quantidade de dados para obter os dados que estão sendo solicitados.

Você precisa começar a ajustá-los. Diminua seu limite de consulta lenta e comece a corrigir o material que é lento e executado com frequência.

    
por 28.02.2014 / 18:06