Dotaz: vysoká záťaž mysql servra

[dusan456]

Zdravim,

na server server mi prisiel rusky vyhladavac, ktory urobi naraz niekolko dotazov za sekundu a mysql server to nejak nezvlada. Ak chcem zobrazit stranku, trva to velmi dlho, pripadne sa aj nepripoji na DB. Zatial som s tym nemal problemy. Inac mam ext3.

Ako by som mohol optimalizovat mysql-server na zvysenie vykonu? Som dost zufaly z toho, za kazdu radu budem povdacny.

toto je vypis z mtop:
load average: 66.25, 51.25, 40.51 mysqld 4.0.24_Debian-10sarge2-log up 0 day(s),  0:26 hrs
84 threads: 78 running, 0 cached. Queries/slow: 275.8K/1.7K Cache Hit: 94.37%
Opened tables: 47.9K  RRN: 326.5M  TLW: 79  SFJ: 69  SMP: 3  QPS: 156

mysql> show variables like '%size%';
+---------------------------------+------------+
| Variable_name                   | Value      |
+---------------------------------+------------+
| bdb_cache_size                  | 8388600    |
| bdb_log_buffer_size             | 0          |
| binlog_cache_size               | 32768      |
| bulk_insert_buffer_size         | 8388608    |
| delayed_queue_size              | 1000       |
| innodb_additional_mem_pool_size | 1048576    |
| innodb_buffer_pool_size         | 8388608    |
| innodb_log_buffer_size          | 1048576    |
| innodb_log_file_size            | 5242880    |
| join_buffer_size                | 131072     |
| key_buffer_size                 | 16777216   |
| max_binlog_cache_size           | 4294967295 |
| max_binlog_size                 | 104857600  |
| max_heap_table_size             | 16777216   |
| max_join_size                   | 4294967295 |
| max_relay_log_size              | 0          |
| myisam_max_extra_sort_file_size | 268435456  |
| myisam_max_sort_file_size       | 2147483647 |
| myisam_sort_buffer_size         | 8388608    |
| query_alloc_block_size          | 8192       |
| query_cache_size                | 16777216   |
| query_prealloc_size             | 8192       |
| range_alloc_block_size          | 2048       |
| read_buffer_size                | 131072     |
| read_rnd_buffer_size            | 262144     |
| sort_buffer_size                | 2097144    |
| thread_cache_size               | 0          |
| tmp_table_size                  | 33554432   |
| transaction_alloc_block_size    | 8192       |
| transaction_prealloc_size       | 4096       |
+---------------------------------+------------+
30 rows in set (0.00 sec)

Odpovědi

[stativ]

A nebylo by nejjednodušší odstranit přímo příčinu toho problému – zablokovat ten vyhledávač?

[dusan456]

To by som velmi nerad, lebo indexuje ruske stranky a ide o doveryhodny vyhladavac.

Verim, ze nastaveniami by sa to dalo zlepsit.

Inac velmi zatazeny je apache, bezi to na rovnakom stroji. Este prikladam vypis beziacich procesov.

Any help?

# ps ax
  PID TTY      STAT   TIME COMMAND
    1 ?        S      0:00 init [2]
    2 ?        RN     0:00 [ksoftirqd/0]
    3 ?        S<     0:03 [events/0]
    4 ?        S<     0:00 [khelper]
    5 ?        S<     0:00 [kacpid]
   47 ?        S<     0:01 [kblockd/0]
   57 ?        S      0:00 [pdflush]
   58 ?        S      0:00 [pdflush]
   60 ?        S<     0:00 [aio/0]
   59 ?        S      0:07 [kswapd0]
  202 ?        S      0:00 [kseriod]
  222 ?        S<     0:00 [ata/0]
  223 ?        S      0:00 [scsi_eh_0]
  224 ?        S      0:00 [scsi_eh_1]
  235 ?        S      0:00 [khubd]
  314 ?        S      0:00 [pciehpd_event]
  316 ?        S      0:00 [shpchpd_event]
  347 ?        S      0:00 [kjournald]
  592 ?        S      0:00 [kjournald]
 2117 ?        Ss     0:00 /sbin/syslogd
 2120 ?        Ss     0:00 /sbin/klogd
 2128 ?        Ss     0:00 /usr/sbin/named -u bind
 2182 ?        Ss     0:00 amavisd (master)
 2190 ?        S      0:00 amavisd (child)
 2191 ?        S      0:00 amavisd (child)
 2193 ?        Ss     0:02 /usr/sbin/clamd
 2234 ?        Ss     0:00 /usr/bin/freshclam -d --quiet -p /var/run/clamav/freshclam.pid
 2239 ?        S      0:00 /usr/sbin/courierlogger -pid=/var/run/courier/authdaemon/pid -start /usr/lib/courier/authlib/auth
 2240 ?        S      0:00 /usr/lib/courier/authlib/authdaemond.mysql
 2246 ?        S      0:00 /usr/sbin/couriertcpd -address=0 -stderrlogger=/usr/sbin/courierlogger -maxprocs=40 -maxperip=20
 2249 ?        S      0:00 /usr/sbin/courierlogger imaplogin
 2250 ?        S      0:00 /usr/lib/courier/authlib/authdaemond.mysql
 2251 ?        S      0:00 /usr/lib/courier/authlib/authdaemond.mysql
 2252 ?        S      0:00 /usr/lib/courier/authlib/authdaemond.mysql
 2253 ?        S      0:00 /usr/lib/courier/authlib/authdaemond.mysql
 2254 ?        S      0:00 /usr/lib/courier/authlib/authdaemond.mysql
 2259 ?        S      0:00 /usr/sbin/couriertcpd -pid=/var/run/courier/pop3d.pid -stderrlogger=/usr/sbin/courierlogger -maxp
 2261 ?        S      0:00 /usr/sbin/courierlogger courierpop3login
 2272 ?        S      0:00 /usr/sbin/couriertcpd -pid=/var/run/courier/pop3d-ssl.pid -stderrlogger=/usr/sbin/courierlogger -
 2274 ?        S      0:00 /usr/sbin/courierlogger pop3d-ssl
 2281 ?        Ss     0:00 /usr/sbin/inetd
 2477 ?        Ss     0:00 /usr/lib/postfix/master
 2481 ?        S      0:00 qmgr -l -t fifo -u -c
 2566 ?        Rs     1:01 /usr/sbin/snort -m 027 -D -d -l /var/log/snort -u snort -g snort -c /etc/snort/snort.conf -S HOME
 2572 ?        Ss     0:00 /usr/sbin/sshd
 2576 ?        Ss     0:00 /usr/sbin/vsftpd
 2579 ?        Ss     0:00 /usr/sbin/atd
 2582 ?        Ss     0:00 /usr/sbin/cron
 2592 ?        Ss     0:00 sshd: root@pts/0
 2595 ?        S      0:01 /usr/sbin/apache
 2603 ?        S      0:00 /usr/sbin/apache-ssl
 2609 tty1     Ss+    0:00 /sbin/getty 38400 tty1
 2610 tty2     Ss+    0:00 /sbin/getty 38400 tty2
 2611 tty3     Ss+    0:00 /sbin/getty 38400 tty3
 2612 tty4     Ss+    0:00 /sbin/getty 38400 tty4
 2613 tty5     Ss+    0:00 /sbin/getty 38400 tty5
 2614 tty6     Ss+    0:00 /sbin/getty 38400 tty6
 2659 ?        S      0:00 /usr/lib/apache-ssl/gcache 33 /var/run/gcache_port
 2660 ?        S      0:05 /usr/sbin/apache-ssl
 2661 ?        S      0:01 /usr/sbin/apache-ssl
 2662 ?        S      0:02 /usr/sbin/apache-ssl
 2669 ?        S      0:04 /usr/sbin/apache-ssl
 2670 ?        S      0:01 /usr/sbin/apache-ssl
 2671 pts/0    Ss     0:00 -bash
 2986 ?        S      0:01 /usr/sbin/apache-ssl
 3480 ?        S      0:00 /usr/bin/imapd Maildir
 6372 pts/0    R+     0:00 mc
 6376 pts/1    Rs     0:00 bash -rcfile .bashrc
 9020 ?        S      0:02 /usr/sbin/apache-ssl
 9157 ?        S      0:03 /usr/sbin/apache-ssl
 9490 ?        S      0:02 /usr/sbin/apache-ssl
13000 ?        Ss     0:00 sshd: root@pts/2
13048 pts/2    Ss     0:00 -bash
17208 pts/2    S+     0:00 mc
17210 pts/3    Ss     0:00 bash -rcfile .bashrc
 9782 ?        R      0:00 pickup -l -t fifo -u -c
10406 ?        R      0:02 /usr/sbin/apache
10491 ?        S      0:02 /usr/sbin/apache
10493 ?        S      0:02 /usr/sbin/apache
10600 ?        R      0:02 /usr/sbin/apache
10827 ?        D      0:02 /usr/sbin/apache
10901 ?        S      0:02 /usr/sbin/apache
10970 ?        D      0:02 /usr/sbin/apache
11040 ?        S      0:02 /usr/sbin/apache
11041 ?        S      0:02 /usr/sbin/apache
11195 ?        D      0:02 /usr/sbin/apache
11262 ?        D      0:02 /usr/sbin/apache
11330 ?        S      0:02 /usr/sbin/apache
11426 ?        S      0:02 /usr/sbin/apache
11505 ?        R      0:02 /usr/sbin/apache
11519 ?        S      0:02 /usr/sbin/apache
11763 ?        S      0:02 /usr/sbin/apache
11765 ?        S      0:02 /usr/sbin/apache
11839 ?        R      0:01 /usr/sbin/apache
11850 ?        S      0:02 /usr/sbin/apache
11959 ?        D      0:02 /usr/sbin/apache
11979 ?        R      0:01 /usr/sbin/apache
12052 ?        S      0:01 /usr/sbin/apache
12124 ?        R      0:02 /usr/sbin/apache
12125 ?        S      0:01 /usr/sbin/apache
12127 ?        R      0:02 /usr/sbin/apache
12181 ?        S      0:02 /usr/sbin/apache
12330 ?        R      0:01 /usr/sbin/apache
12394 ?        D      0:01 /usr/sbin/apache
12514 ?        S      0:02 /usr/sbin/apache
12554 ?        S      0:02 /usr/sbin/apache
12566 ?        D      0:02 /usr/sbin/apache
12576 ?        R      0:01 /usr/sbin/apache
12750 ?        S      0:01 /usr/sbin/apache
12757 ?        R      0:02 /usr/sbin/apache
12764 ?        D      0:02 /usr/sbin/apache
12855 ?        S      0:02 /usr/sbin/apache
12862 ?        S      0:01 /usr/sbin/apache
12863 ?        S      0:01 /usr/sbin/apache
12864 ?        S      0:01 /usr/sbin/apache
12865 ?        S      0:01 /usr/sbin/apache
12866 ?        D      0:01 /usr/sbin/apache
12925 ?        D      0:01 /usr/sbin/apache
12926 ?        S      0:01 /usr/sbin/apache
12949 ?        S      0:01 /usr/sbin/apache
13039 ?        R      0:01 /usr/sbin/apache
13094 ?        S      0:01 /usr/sbin/apache
13095 ?        D      0:02 /usr/sbin/apache
13096 ?        S      0:01 /usr/sbin/apache
13126 ?        S      0:01 /usr/sbin/apache
13127 ?        S      0:01 /usr/sbin/apache
13254 ?        S      0:02 /usr/sbin/apache
13330 ?        S      0:01 /usr/sbin/apache
13399 ?        D      0:01 /usr/sbin/apache
13438 ?        S      0:01 /usr/sbin/apache
13486 ?        S      0:01 /usr/sbin/apache
13637 ?        S      0:01 /usr/sbin/apache
13667 ?        S      0:01 /usr/sbin/apache
13684 ?        S      0:01 /usr/sbin/apache
13739 ?        R      0:01 /usr/sbin/apache
13747 ?        S      0:01 /usr/sbin/apache
13969 ?        S      0:01 /usr/sbin/apache
13983 ?        R      0:01 /usr/sbin/apache
14026 ?        R      0:01 /usr/sbin/apache
14109 ?        R      0:01 /usr/sbin/apache
14137 ?        D      0:01 /usr/sbin/apache
14247 ?        S      0:01 /usr/sbin/apache
14258 ?        R      0:01 /usr/sbin/apache
14272 ?        S      0:01 /usr/sbin/apache
14488 ?        S      0:01 /usr/sbin/apache
14496 ?        R      0:01 /usr/sbin/apache
14497 ?        S      0:01 /usr/sbin/apache
14503 ?        S      0:01 /usr/sbin/apache
14526 ?        R      0:01 /usr/sbin/apache
14535 ?        D      0:01 /usr/sbin/apache
14574 ?        R      0:01 /usr/sbin/apache
14627 ?        S      0:01 /usr/sbin/apache
14637 ?        S      0:01 /usr/sbin/apache
14647 ?        D      0:01 /usr/sbin/apache
14665 ?        S      0:01 /usr/sbin/apache
14730 ?        S      0:01 /usr/sbin/apache
14775 ?        S      0:01 /usr/sbin/apache
14790 ?        S      0:01 /usr/sbin/apache
14810 ?        S      0:01 /usr/sbin/apache
14852 ?        R      0:01 /usr/sbin/apache
14909 ?        S      0:01 /usr/sbin/apache
15022 ?        S      0:01 /usr/sbin/apache
15038 ?        S      0:01 /usr/sbin/apache
15109 ?        R      0:01 /usr/sbin/apache
15132 ?        R      0:01 /usr/sbin/apache
15157 ?        D      0:01 /usr/sbin/apache
15289 ?        S      0:01 /usr/sbin/apache
15420 ?        S      0:01 /usr/sbin/apache
15425 ?        S      0:01 /usr/sbin/apache
15501 ?        S      0:01 /usr/sbin/apache
15515 ?        S      0:01 /usr/sbin/apache
15569 ?        D      0:01 /usr/sbin/apache
15740 ?        S      0:01 /usr/sbin/apache
15777 ?        D      0:01 /usr/sbin/apache
15804 ?        R      0:01 /usr/sbin/apache
15827 ?        S      0:01 /usr/sbin/apache
15959 ?        S      0:01 /usr/sbin/apache
15989 ?        S      0:01 /usr/sbin/apache
16083 ?        R      0:01 /usr/sbin/apache
16102 ?        S      0:01 /usr/sbin/apache
16103 ?        D      0:01 /usr/sbin/apache
16124 ?        S      0:01 /usr/sbin/apache
16173 ?        D      0:01 /usr/sbin/apache
16254 ?        R      0:01 /usr/sbin/apache
16271 ?        S      0:01 /usr/sbin/apache
16319 ?        S      0:01 /usr/sbin/apache
16320 ?        D      0:01 /usr/sbin/apache
16366 ?        S      0:01 /usr/sbin/apache
16510 ?        S      0:01 /usr/sbin/apache
16606 ?        S      0:01 /usr/sbin/apache
16760 ?        S      0:01 /usr/sbin/apache
16876 ?        S      0:01 /usr/sbin/apache
16903 ?        R      0:01 /usr/sbin/apache
16936 ?        S      0:01 /usr/sbin/apache
16963 ?        S      0:01 /usr/sbin/apache
17093 ?        S      0:01 /usr/sbin/apache
17109 ?        S      0:01 /usr/sbin/apache
17159 ?        S      0:01 /usr/sbin/apache
17290 ?        S      0:01 /usr/sbin/apache
17382 ?        S      0:01 /usr/sbin/apache
17563 ?        S      0:00 /usr/sbin/apache
17650 ?        S      0:01 /usr/sbin/apache
17679 ?        S      0:01 /usr/sbin/apache
17734 ?        D      0:00 /usr/sbin/apache
17758 ?        S      0:01 /usr/sbin/apache
17770 ?        R      0:01 /usr/sbin/apache
17792 ?        S      0:01 /usr/sbin/apache
17815 ?        S      0:00 /usr/sbin/apache
17849 ?        D      0:01 /usr/sbin/apache
17883 ?        S      0:00 /usr/sbin/apache
17967 ?        R      0:01 /usr/sbin/apache
18400 ?        S      0:00 /usr/sbin/apache
18491 ?        D      0:00 /usr/sbin/apache
18813 ?        R      0:00 /usr/sbin/apache
18976 ?        S      0:00 /usr/sbin/apache
19336 ?        D      0:00 /usr/sbin/apache
19834 ?        S      0:00 /usr/sbin/apache
19995 ?        S      0:00 /usr/sbin/apache
20639 ?        S      0:00 /usr/sbin/apache
20787 ?        D      0:00 /usr/sbin/apache
21251 pts/1    S<     0:00 /bin/sh /usr/bin/mysqld_safe
21288 pts/1    S<     0:00 /bin/sh /usr/bin/mysqld_safe
21289 pts/1    S<     0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/
21290 pts/1    S<     0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
21513 pts/3    S+     0:00 /usr/bin/perl /usr/bin/mtop
22378 ?        R      0:00 /usr/sbin/apache
22536 ?        D      0:00 /usr/sbin/apache
23301 ?        R      0:00 /usr/sbin/apache
23436 ?        S      0:00 /usr/sbin/apache
23555 ?        R      0:00 /usr/sbin/apache
23674 ?        R      0:00 /USR/SBIN/CRON
23929 ?        R      0:00 /usr/sbin/apache
23991 ?        R      0:00 /usr/sbin/couriertcpd -pid=/var/run/courier/pop3d.pid -stderrlogger=/usr/sbin/courierlogger -maxp
24063 pts/1    R+     0:00 ps ax

[edois]

omg, tech apachu tam jede nejak moc.. hodte ho na jiny port (treba 79), nainstalujte si lighttpd a nastavte ji jako reverzni proxy.. usetrite _hodne_ pameti (lighttpd zvlada v jednom workeru stovky requestu, ja tim na jednom serveru usetril pres giga a pul)

[Bilbo]

Mozna zkusit nastavit v robots.txt crawl-delay a doufat ze to robot od toho vyhledavace bude respektovat (sice je to nestandardni rozsireni robots.txt, ale spousta robotu to respektuje)

User-Agent: * Crawl-Delay: 10

a robot by mel pak celat 10 sekund mezi requesty. Sice to neresi pricinu (pomale mysql), ale lze to pouzit jako alespon docasne reseni do odstraneni problemu

[Bilbo]

Tedy, v tech robots.txt by to melo vypadat napr. takto:

User-Agent: *
Crawl-Delay: 10

[zelial]

vypadá to, že tam máš nějaké slow queries. podívej se do logů, které to jsou a jestli by nešly optimalizovat.

[stativ]

Nevím jak ty stránky fungují, ale možná by ti mohla pomoct reverse proxy anebo tohle.

[dusan456]

Problem bol vyrieseny. Dakujem vsetkym za vysokohodnotne rady.

Prve co som urobil bolo, ze som doplnil do robots.txt Crawl-Delay: 5 a pomohlo to, hned sa trocha ukludnil.

Z tych procesov bolo jasne, ze apache je velmi zatazeny, preto som sa rozhodol urobit reverzny proxy server nginx pred apache a z vysledku som velmi spokojny.

[Honza Lunek]

Zkuz taky v my.cnf nastavit key_buffer_size na nejakou vyssi hodnotu znez tech 16M co tam mas .. cislem 64M nebo 128M nic nezkazis... tak se podivej na Počet skutečných čtení bloku zklíče z disku. Pokud je hodnota příliš velká, pravděpodobně máte malou vyrovnávací paměť (key_buffer_size). Úspěšnost vyrovnávací paměti můžete spočítat jako Key_reads/Key_read_requests.

ja se ten pomer snazim mit trvale pod 1:500 .. ale vetsinou mezi 1:800-1500 ..