Skip to main content

MySQL optimization request

ougogo
Hi all, I think I have some problem with my MySQL configuration. If someone could give me his opinion I am taking all the criticism ! My my.cnf (shame on me...) [mysqld] innodb_file_per_table=1 open_files_limit=50000
./mysqltuner.pl [18:44:41] root@* [~]# ./mysqltuner.pl >> MySQLTuner 1.2.1 mod - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Modified by George Liu (eva2000) at http://vbtechsupport.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.35-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 6G (Tables: 36616) [--] Data in InnoDB tables: 2G (Tables: 17990) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 1M (Tables: 141) [!!] Total fragmented tables: 1136 -------- Security Recommendations ------------------------------------------- [!!] User 'spa**_preau@**.**.**.**' has no password set. [!!] User 'spa**_preau@**.**.**.**' has no password set. [!!] User 'spa**_preau@***.fr' has no password set. [!!] User 'spa**_preau@***.fr' has no password set. [!!] User 'spa**_preau@localhost' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 18h 35m 57s (66M q [277.076 qps], 1M conn, TX: 637B, RX: 9B) [--] Reads / Writes: 92% / 8% [--] Total buffers: 168.0M global + 2.8M per thread (151 max threads) [OK] Maximum possible memory usage: 583.2M (1% of installed RAM) [OK] Slow queries: 0% (936/66M) [OK] Highest usage of available connections: 53% (81/151) [OK] Key buffer size / total MyISAM indexes: 8.0M/2.0G [OK] Key buffer hit rate: 99.8% (4B cached / 9M reads) [!!] Query cache is disabled. In certain usage cases you may want to leave Query cache disabled i.e. InnoDB table usage [OK] Sorts requiring temporary tables: 0% (8K temp sorts / 9M sorts) [!!] Joins performed without indexes: 36654 [OK] Temporary tables created on disk: 22% (912K on disk / 4M total) [!!] Thread cache is disabled [!!] Table cache hit rate: 0% (400 open / 90M opened) [OK] Open file limit used: 1% (576/50K) [OK] Table locks acquired immediately: 99% (71M immediate / 71M locks) [!!] Connections aborted: 14% [!!] InnoDB data size / buffer pool: 2.2G/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries. Do not forget to disable slow query logging after troubleshooting - For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html - For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html - For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html - For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html Adjust your join queries to always utilize indexes Set thread_cache_size to 4 as a starting value Increase table_cache gradually to avoid file descriptor limits Your applications are not closing MySQL connections properly Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 128.0K, or always use indexes with joins) thread_cache_size (start at 4) table_cache (> 400, table_open_cache hit rate <20%) innodb_buffer_pool_size (>= 2G, innodb data size is larger than innodb_buffer_size, set innodb_buffer_pool_size appropriately)
./mysqlmymonlite.sh run [19:07:56] root@*** [~/tools]# ./mysqlmymonlite.sh run ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.0 WHM mysqlmymon.com compiled by George Liu (eva2000) vbtechsupport.com ------------------------------------------------------------- Report Generated: Sat Feb 1 19:07:59 CET 2014 ---------------------------- MySQL Uptime: ---------------------------- MySQL server was started 2014-01-30 00:08:55 Uptime: 2 days 18 hours 59 min 4 sec Uptime 241144 Uptime_since_flush_status 241144 Max Used Connections: 81/151 (53.64%) Key Buffer Used: 6.54M/8.00M (81.76%) Current Key Buffer Usage: 6.19M (77.37%) InnoDB Buffer Pool: 128.00M/128.00M (100.00%) Table Locks Waited: 26.27k Rate: 0.1/s (0.04%) -------------------- Query Cache Limit (bytes): 1048576 Query Cache Min Unit (bytes): 4096 Query Cache Size (bytes): 0 Query Cache Free Mem (bytes): 0 Query Cache Used Mem (bytes): 0 Queries in Query Cache: 1 Query Cache Avg Size (bytes): 0 ---------------------------- Hardware Info: ---------------------------- Processors physical = 1, cores = 4, virtual = 4, hyperthreading = no 4 3400.201 4 Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz 4 8192 KB ---------------------------- TOP stats ---------------------------- Linux 2.6.32-042stab083.2 ((yourserverhostname)) 02/01/14 _x86_64_(8 CPU) 19:07:59 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle 19:08:01 all 1.01 56.46 39.37 0.13 0.00 0.00 0.25 0.00 2.78 19:08:01 0 0.90 58.11 38.29 0.00 0.00 0.00 0.90 0.00 1.80 19:08:01 1 0.00 57.67 38.10 0.00 0.00 0.00 0.53 0.00 3.70 19:08:01 2 3.03 55.56 38.89 0.00 0.00 0.00 0.00 0.00 2.53 19:08:01 3 0.00 54.35 42.39 0.00 0.00 0.00 0.00 0.00 3.26 19:08:01 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 19:08:01 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 19:08:01 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 19:08:01 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 19:08:01 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle 19:08:03 all 0.00 59.26 36.55 0.00 0.00 0.00 0.13 0.00 4.06 19:08:03 0 0.00 63.10 32.62 0.00 0.00 0.00 0.00 0.00 4.28 19:08:03 1 0.00 59.72 37.44 0.00 0.00 0.00 0.00 0.00 2.84 19:08:03 2 0.00 63.04 30.98 0.00 0.00 0.00 0.00 0.00 5.98 19:08:03 3 0.00 52.74 44.28 0.00 0.00 0.00 0.00 0.00 2.99 19:08:03 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 19:08:03 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 19:08:03 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 19:08:03 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle Average: all 0.51 57.86 37.96 0.06 0.00 0.00 0.19 0.00 3.42 Average: 0 0.49 60.39 35.70 0.00 0.00 0.00 0.49 0.00 2.93 Average: 1 0.00 58.75 37.75 0.00 0.00 0.00 0.25 0.00 3.25 Average: 2 1.57 59.16 35.08 0.00 0.00 0.00 0.00 0.00 4.19 Average: 3 0.00 53.51 43.38 0.00 0.00 0.00 0.00 0.00 3.12 Average: 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 top - 19:08:03 up 74 days, 7:26, 1 user, load average: 2.31, 2.78, 3.07 Tasks: 133 total, 3 running, 123 sleeping, 1 stopped, 6 zombie Cpu(s): 0.0%us, 0.6%sy, 2.0%ni, 12.4%id, 85.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 31457280k total, 21736440k used, 9720840k free, 0k buffers Swap: 31457280k total, 404000k used, 31053280k free, 15573028k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 27115 user1 24 4 219m 32m 8172 S 23.8 0.1 0:00.76 php 27093 user1 24 4 0 0 0 Z 11.9 0.0 0:00.42 php 27185 user2 24 4 203m 15m 7884 R 7.9 0.0 0:00.04 php 13342 nobody 24 4 1331m 74m 11m S 4.0 0.2 0:05.96 httpd 22288 nobody 24 4 1331m 64m 2504 S 4.0 0.2 0:02.03 httpd 3183 mysql 26 6 2569m 400m 5924 S 2.0 1.3 1184:02 mysqld 22583 mailman 28 8 194m 10m 3584 S 2.0 0.0 0:01.80 python 26094 nobody 24 4 1331m 62m 2420 S 2.0 0.2 0:00.38 httpd 27180 user3 24 4 0 0 0 Z 2.0 0.0 0:00.01 php 27182 user4 24 4 200m 12m 7220 S 2.0 0.0 0:00.01 php 27183 user3 24 4 0 0 0 Z 2.0 0.0 0:00.01 php 27190 user5 24 4 201m 12m 7228 R 2.0 0.0 0:00.01 php 1 root 20 0 19232 492 356 S 0.0 0.0 0:23.04 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd/121 3 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper/121 481 user6 30 10 214m 7008 6964 S 0.0 0.0 0:53.32 php 592 user7 30 10 136m 1164 724 S 0.0 0.0 0:06.51 pure-ftpd 593 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 789 user8 30 10 202m 5936 5932 S 0.0 0.0 0:59.47 php 1177 user7 30 10 136m 1016 660 S 0.0 0.0 0:06.41 pure-ftpd 1178 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 1309 root 20 0 64308 200 196 S 0.0 0.0 0:00.00 saslauthd 1342 root 20 0 64308 24 20 S 0.0 0.0 0:00.00 saslauthd 1436 root 30 10 132m 152 92 S 0.0 0.0 1:08.34 pure-uploadscri 1565 nobody 24 4 171m 55m 868 S 0.0 0.2 0:00.05 httpd 1566 root 28 8 80200 10m 2952 S 0.0 0.0 0:00.05 leechprotect 1703 root 20 0 21456 216 192 S 0.0 0.0 0:00.05 atd 2266 nobody 24 4 1317m 74m 11m S 0.0 0.2 0:04.94 httpd 2808 user9 20 0 23388 2848 2184 S 0.0 0.0 0:00.01 imap 2813 user10 20 0 23292 2444 2036 S 0.0 0.0 0:00.00 imap 2924 root 16 -4 10644 188 184 S 0.0 0.0 0:00.00 udevd 3064 root 20 0 11308 1228 1224 S 0.0 0.0 0:00.00 mysqld_safe 5009 root 20 0 66608 500 408 S 0.0 0.0 0:00.03 sshd 5218 root 20 0 237m 5092 836 S 0.0 0.0 2:47.37 rsyslogd 5530 root 30 10 135m 716 624 S 0.0 0.0 0:03.77 pure-ftpd 5535 root 28 8 133m 988 944 S 0.0 0.0 0:03.08 pure-authd 5619 nobody 24 4 1333m 75m 11m S 0.0 0.2 0:06.18 httpd 5976 user8 30 10 202m 5940 5936 S 0.0 0.0 0:59.45 php 6700 user6 30 10 214m 7008 6964 S 0.0 0.0 0:52.83 php 6782 user8 30 10 202m 6888 5936 S 0.0 0.0 0:59.86 php 6862 nobody 24 4 1259m 72m 10m S 0.0 0.2 0:03.02 httpd 8306 user11 30 10 135m 864 636 S 0.0 0.0 0:00.00 pure-ftpd 8307 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 9351 user6 30 10 214m 7012 6968 S 0.0 0.0 0:53.00 php 9355 user8 30 10 202m 5936 5932 S 0.0 0.0 0:59.47 php 9619 user6 30 10 212m 7008 6964 S 0.0 0.0 0:39.34 php 10031 user12 30 10 136m 892 664 S 0.0 0.0 0:00.10 pure-ftpd 10032 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 10189 user13 20 0 23300 2648 2024 S 0.0 0.0 0:00.00 imap 11421 nobody 24 4 1331m 64m 2480 S 0.0 0.2 0:00.87 httpd 11584 user12 30 10 135m 932 696 S 0.0 0.0 0:00.11 pure-ftpd 11585 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 11724 cpanelro 20 0 100m 17m 2920 S 0.0 0.1 0:00.02 cpsrvd-ssl 13087 root 20 0 100m 20m 5804 S 0.0 0.1 1:06.60 cpsrvd-ssl 13219 nobody 24 4 1291m 73m 11m S 0.0 0.2 0:02.72 httpd 13543 root 20 0 15612 1372 824 T 0.0 0.0 0:02.66 find 14959 nobody 24 4 1179m 64m 3420 S 0.0 0.2 0:01.56 httpd 16480 named 20 0 477m 63m 1980 S 0.0 0.2 11:42.62 named 17600 nobody 24 4 1235m 71m 10m S 0.0 0.2 0:01.09 httpd 19078 root 20 0 98300 4096 3096 S 0.0 0.0 0:00.02 sshd 19149 root 20 0 57808 2324 1620 S 0.0 0.0 0:00.00 sftp-server 19198 root 28 8 141m 4080 1320 S 0.0 0.0 2:53.85 /usr/local/cpan 19374 nobody 24 4 1243m 63m 2492 S 0.0 0.2 0:01.38 httpd 19448 root 20 0 98300 4396 3420 S 0.0 0.0 0:00.01 sshd 19462 nobody 24 4 1283m 65m 2520 S 0.0 0.2 0:03.18 httpd 19555 root 20 0 19600 1152 836 S 0.0 0.0 0:00.66 dovecot 19590 dovenull 20 0 42464 3556 2692 S 0.0 0.0 0:00.05 pop3-login 19591 dovenull 20 0 42468 3672 2700 S 0.0 0.0 0:00.32 imap-login 19592 dovecot 20 0 13304 1128 936 S 0.0 0.0 0:00.21 anvil 19593 root 20 0 13436 1316 976 S 0.0 0.0 0:00.36 log 19595 dovenull 20 0 42464 3624 2692 S 0.0 0.0 0:00.21 pop3-login 19596 root 20 0 16392 2144 1128 S 0.0 0.0 0:00.64 config 19597 dovenull 20 0 42840 3960 2748 S 0.0 0.0 0:00.77 imap-login 19598 dovecot 20 0 29432 1992 1432 S 0.0 0.0 0:01.08 auth 19640 user6 30 10 214m 7008 6964 S 0.0 0.0 0:52.83 php 19645 user6 30 10 214m 7008 6964 S 0.0 0.0 0:53.17 php 19700 root 20 0 11444 1820 1416 S 0.0 0.0 0:00.00 bash 19740 root 28 8 188m 76m 3300 S 0.0 0.2 0:09.03 spamd child 20436 nobody 24 4 1267m 65m 2500 S 0.0 0.2 0:01.81 httpd 20492 user6 30 10 214m 7008 6964 S 0.0 0.0 0:53.36 php 20733 user8 30 10 200m 5936 5932 S 0.0 0.0 0:59.41 php 21451 nobody 24 4 1331m 73m 10m S 0.0 0.2 0:02.33 httpd 22382 user14 30 10 136m 964 696 S 0.0 0.0 0:00.04 pure-ftpd 22383 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 22410 root 20 0 36280 3532 1428 S 0.0 0.0 0:01.05 queueprocd - wa 22490 root 20 0 114m 3248 1136 S 0.0 0.0 0:02.96 dnsadmin - serv 22543 cpanelro 20 0 100m 17m 2920 S 0.0 0.1 0:00.01 cpsrvd-ssl 22569 mailman 28 8 199m 8692 860 S 0.0 0.0 0:00.00 mailmanctl 22571 root 20 0 81536 9.8m 1620 S 0.0 0.0 0:30.02 tailwatchd 22577 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:01.76 python 22578 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:01.94 python 22579 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:01.88 python 22580 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:01.75 python 22581 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:01.79 python 22582 mailman 28 8 195m 11m 3584 S 0.0 0.0 0:01.94 python 22584 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:00.05 python 22617 root 20 0 71716 3208 2848 S 0.0 0.0 0:08.20 cPhulkd - proce 22662 root 20 0 23528 2148 1428 S 0.0 0.0 0:26.84 cpdavd-dormant 22717 root 24 4 171m 66m 11m S 0.0 0.2 0:04.30 httpd 22743 root 38 18 27252 2520 1164 S 0.0 0.0 0:00.23 cpanellogd - sl 22996 root 20 0 11444 724 316 S 0.0 0.0 0:00.00 bash 22997 root 20 0 4076 560 472 S 0.0 0.0 0:00.00 tee 22998 root 20 0 4064 660 564 S 0.0 0.0 0:00.00 logger 23177 root 28 8 429m 237m 5688 S 0.0 0.8 2:05.58 clamd 23195 mailnull 28 8 71536 2976 704 S 0.0 0.0 0:00.11 exim 23238 root 28 8 161m 49m 3296 S 0.0 0.2 0:02.86 /usr/local/cpan 23535 user6 30 10 214m 7008 6964 S 0.0 0.0 0:53.19 php 24386 user6 30 10 214m 7004 6960 S 0.0 0.0 0:52.66 php 25211 root 20 0 19408 1528 1180 S 0.0 0.0 0:00.00 ssl-params 25741 user6 30 10 214m 7004 6960 S 0.0 0.0 0:53.41 php 25762 cpanelro 20 0 100m 17m 2920 S 0.0 0.1 0:00.01 cpsrvd-ssl 25821 user6 30 10 214m 7004 6960 S 0.0 0.0 0:52.50 php 25962 root 28 8 148m 6264 1424 S 0.0 0.0 1:55.08 lfd - sleeping 26690 user2 24 4 0 0 0 Z 0.0 0.0 0:00.13 php 26696 user2 24 4 0 0 0 Z 0.0 0.0 0:00.11 php 26698 user2 24 4 0 0 0 Z 0.0 0.0 0:00.14 php 26730 root 20 0 11444 728 316 S 0.0 0.0 0:00.00 bash 26732 root 20 0 4076 560 472 S 0.0 0.0 0:00.00 tee 26733 root 20 0 4064 656 564 S 0.0 0.0 0:00.00 logger 26775 root 20 0 9692 1896 1148 S 0.0 0.0 0:00.00 mysqlmymonlite. 27173 root 20 0 15028 1188 880 R 0.0 0.0 0:00.00 top 27355 nobody 24 4 1299m 65m 3120 S 0.0 0.2 0:04.58 httpd 28192 cpanelro 28 8 100m 18m 2920 S 0.0 0.1 0:00.02 cpsrvd-ssl 28298 user15 30 10 135m 888 664 S 0.0 0.0 0:00.01 pure-ftpd 28299 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 28337 cpanelro 28 8 100m 18m 2920 S 0.0 0.1 0:00.02 cpsrvd-ssl 28916 root 20 0 114m 1128 644 S 0.0 0.0 0:05.76 crond 30407 user8 30 10 202m 5940 5936 S 0.0 0.0 0:59.55 php 31393 root 20 0 98300 4112 3096 S 0.0 0.0 0:00.05 sshd 31436 root 20 0 57816 2360 1620 S 0.0 0.0 0:00.76 sftp-server 31529 user15 30 10 135m 936 700 S 0.0 0.0 0:00.01 pure-ftpd 31530 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 32525 user16 20 0 23304 2640 2036 S 0.0 0.0 0:00.00 imap total used free shared buffers cached Mem: 30720 21207 9512 0 0 15208 Low: 30720 21207 9512 High: 0 0 0 -/+ buffers/cache: 5999 24720 Swap: 30720 394 30325 MemTotal: 31457280 kB MemFree: 9740852 kB Cached: 15573044 kB Buffers: 0 kB Active: 10644780 kB Inactive: 6177008 kB Active(anon): 880144 kB Inactive(anon): 368600 kB Active(file): 9764636 kB Inactive(file): 5808408 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 31457280 kB SwapFree: 31053280 kB Dirty: 4700 kB Writeback: 0 kB AnonPages: 1248744 kB Shmem: 15960 kB Slab: 4894580 kB SReclaimable: 4848548 kB SUnreclaim: 46032 kB Private + Shared = RAM usedProgram 4.0 KiB + 17.5 KiB = 21.5 KiBudevd 52.0 KiB + 56.0 KiB = 108.0 KiBpure-authd 56.0 KiB + 64.5 KiB = 120.5 KiBatd 64.0 KiB + 60.5 KiB = 124.5 KiBpure-uploadscript 8.0 KiB + 177.5 KiB = 185.5 KiBmysqld_safe 0.0 KiB + 192.0 KiB = 192.0 KiBsaslauthd (2) 176.0 KiB + 47.0 KiB = 223.0 KiBtee (2) 188.0 KiB + 37.0 KiB = 225.0 KiBlogger (2) 208.0 KiB + 47.0 KiB = 255.0 KiBanvil 240.0 KiB + 99.0 KiB = 339.0 KiBinit 356.0 KiB + 50.0 KiB = 406.0 KiBlog 360.0 KiB + 60.5 KiB = 420.5 KiBssl-params 376.0 KiB + 55.5 KiB = 431.5 KiBdovecot 528.0 KiB + 33.5 KiB = 561.5 KiBcrond 664.0 KiB + 29.0 KiB = 693.0 KiBfind 360.0 KiB + 350.5 KiB = 710.5 KiBcPhulkd - proce 804.0 KiB + 77.0 KiB = 881.0 KiBauth 756.0 KiB + 172.5 KiB = 928.5 KiBmysqlmymonlite. 844.0 KiB + 145.0 KiB = 989.0 KiBcpdavd-dormant 1.1 MiB + 56.0 KiB = 1.1 MiBconfig 684.0 KiB + 498.5 KiB = 1.2 MiBbash (3) 1.3 MiB + 113.5 KiB = 1.4 MiBcpanellogd - sl 1.4 MiB + 295.0 KiB = 1.7 MiBsftp-server (2) 1.8 MiB + 359.0 KiB = 2.1 MiBpop3-login (2) 2.1 MiB + 186.0 KiB = 2.2 MiBdnsadmin - serv 2.1 MiB + 132.0 KiB = 2.3 MiBqueueprocd - wa 2.4 MiB + 69.5 KiB = 2.5 MiBexim 2.1 MiB + 376.0 KiB = 2.5 MiBimap-login (2) 2.4 MiB + 1.2 MiB = 3.6 MiBimap (4) 2.6 MiB + 1.0 MiB = 3.6 MiBpure-ftpd (17) 4.4 MiB + 43.0 KiB = 4.4 MiBrsyslogd 3.1 MiB + 1.4 MiB = 4.5 MiBsshd (4) 4.7 MiB + 148.0 KiB = 4.9 MiBlfd - sleeping 7.7 MiB + 138.5 KiB = 7.8 MiBmailmanctl 7.6 MiB + 385.0 KiB = 8.0 MiBleechprotect 8.2 MiB + 250.5 KiB = 8.5 MiBtailwatchd 16.4 MiB + 14.7 MiB = 31.1 MiBcpsrvd-ssl (6) 27.8 MiB + 11.5 MiB = 39.4 MiB/usr/local/cpan (2) 33.1 MiB + 7.4 MiB = 40.4 MiBphp (18) 59.6 MiB + 2.4 MiB = 62.0 MiBpython (8) 62.2 MiB + 103.0 KiB = 62.3 MiBnamed 52.0 MiB + 11.3 MiB = 63.3 MiBspamd child 134.1 MiB + 66.1 MiB = 200.2 MiBhttpd (17) 236.8 MiB + 53.0 KiB = 236.9 MiBclamd 399.2 MiB + 191.5 KiB = 399.4 MiBmysqld --------------------------------- 1.2 GiB ================================= Private + Shared = RAM usedProgram ---------------------------- Server Disk Usage ---------------------------- Filesystem Type Size Used Avail Use% Mounted on /dev/simfs simfs 1.5T 975G 526G 65% / none devtmpfs 15G 4.0K 15G 1% /dev none tmpfs 15G 0 15G 0% /dev/shm /dev/simfs simfs 1.5T 975G 526G 65% /home ---------------------------- MySQL datadir disk usage ---------------------------- /var/lib/mysql/ uses 18040220 kilobytes of disk space ---------------------------- ulimit -aH ---------------------------- core file size (blocks, -c) 1000000 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 255461 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 4096 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 14335 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited ---------------------------------------------------------- tail -15 /etc/security/limits.conf ---------------------------------------------------------- # - nice - max nice priority allowed to raise to values: [-20, 19] # - rtprio - max realtime priority # # # #* soft core 0 #* hard rss 10000 #@student hard nproc 20 #@faculty soft nproc 20 #@faculty hard nproc 50 #ftp hard nproc 0 #@student - maxlogins 4 # End of file ---------------------------------------------------------- mysqld process limits: cat /proc/3183/limits ---------------------------------------------------------- Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 8388608 bytes Max core file size 0 0 bytes Max resident set unlimited unlimited bytes Max processes 14335 14335 processes Max open files 50000 50000 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 255461 255461 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us ---------------------------------- CPANEL WEB SERVER & PHP Info: ---------------------------------- WHM / Cpanel version: 11.42.0.1 WHM's PHP Handler config: /usr/local/cpanel/bin/rebuild_phpconf --current Available handlers: suphp cgi none DEFAULT PHP: 5 PHP4 SAPI: none PHP5 SAPI: suphp SUEXEC: enabled RUID2: not installed PHP 5.4.23 (cli) (built: Dec 27 2013 14:01:04) Copyright (c) 1997-2013 The PHP Group Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies with the ionCube PHP Loader v4.4.1, Copyright (c) 2002-2013, by ionCube Ltd., and with Zend Guard Loader v3.3, Copyright (c) 1998-2013, by Zend Technologies System => Linux yourserverhostname 2.6.32-042stab083.2 #1 SMP Fri Nov 8 18:08:40 MSK 2013 x86_64 Build Date => Dec 27 2013 13:59:35 Configure Command => './configure' '--enable-bcmath' '--enable-calendar' '--enable-exif' '--enable-ftp' '--enable-gd-native-ttf' '--enable-libxml' '--enable-mbstring' '--enable-pdo=shared' '--enable-soap' '--enable-sockets' '--enable-zip' '--prefix=/usr/local' '--with-curl=/opt/curlssl/' '--with-freetype-dir=/usr' '--with-gd' '--with-gettext' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/usr' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libdir=lib64' '--with-libexpat-dir=/usr' '--with-libxml-dir=/opt/xml2' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-mysqli=/usr/bin/mysql_config' '--with-openssl=/usr' '--with-openssl-dir=/usr' '--with-pcre-regex=/opt/pcre' '--with-pdo-mysql=shared' '--with-pdo-sqlite=shared' '--with-pic' '--with-png-dir=/usr' '--with-tidy=/opt/tidy/' '--with-xmlrpc' '--with-xpm-dir=/usr' '--with-xsl=/opt/xslt/' '--with-zlib' '--with-zlib-dir=/usr' Server API | Command Line Interface Configuration File (php.ini) Path | /usr/local/lib Loaded Configuration File | /usr/local/lib/php.ini PHP API | 20100412 Zend Extension Build | API220100525,NTS PHP Extension Build | API20100525,NTS DOM/XML API Version | 20031129 Client API version | 5.5.35 Client API library version | 5.5.35 Client API header version | 5.5.34 API Extensions | pdo_mysql Client API version | mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $ Phar API version | 1.1.1 display_errors | Off | Off error_log | error_log | error_log extension_dir | /usr/local/lib/php/extensions/no-debug-non-zts-20100525 | /usr/local/lib/php/extensions/no-debug-non-zts-20100525 file_uploads | On | On log_errors | On | On log_errors_max_len | 1024 | 1024 max_execution_time | 0 | 0 max_file_uploads | 20 | 20 memory_limit | 320M | 320M open_basedir | /home:/usr:/tmp:/var/netenberg | /home:/usr:/tmp:/var/netenberg post_max_size | 20M | 20M realpath_cache_size | 16K | 16K realpath_cache_ttl | 120 | 120 sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i sql.safe_mode | Off | Off upload_max_filesize | 20M | 20M upload_tmp_dir | no value | no value sqlite3.extension_dir | no value | no value cURL support | enabled cURL Information | 7.24.0 ZLib Version | 1.2.3 GD Support | enabled GD Version | bundled (2.1.0 compatible) FreeType Support | enabled FreeType Linkage | with freetype FreeType Version | 2.3.11 GIF Read Support | enabled GIF Create Support | enabled JPEG Support | enabled libJPEG Version | 6b PNG Support | enabled libPNG Version | 1.2.49 WBMP Support | enabled libXML support | active libXML Compiled Version | 2.9.0 libXML Loaded Version | 20900 libXML streams | enabled PCRE (Perl Compatible Regular Expressions) Support | enabled PCRE Library Version | 8.21 2011-12-12 Soap Client | enabled Soap Server | enabled ZLib Support | enabled Server version: Apache/2.2.26 (Unix) Server built: Dec 27 2013 13:55:28 Cpanel::Easy::Apache v3.22.25 rev9999 Server's Module Magic Number: 20051115:33 Server loaded: APR 1.4.8, APR-Util 1.5.2 Compiled using: APR 1.4.8, APR-Util 1.5.2 Architecture: 64-bit Server MPM: Worker threaded: yes (fixed thread count) forked: yes (variable process count) Server compiled with.... -D APACHE_MPM_DIR="server/mpm/worker" -D APR_HAS_SENDFILE -D APR_HAS_MMAP -D APR_HAVE_IPV6 (IPv4-mapped addresses disabled) -D APR_USE_SYSVSEM_SERIALIZE -D APR_USE_PTHREAD_SERIALIZE -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT -D APR_HAS_OTHER_CHILD -D AP_HAVE_RELIABLE_PIPED_LOGS -D DYNAMIC_MODULE_LIMIT=128 -D HTTPD_ROOT="/usr/local/apache" -D SUEXEC_BIN="/usr/local/apache/bin/suexec" -D DEFAULT_SCOREBOARD="logs/apache_runtime_status" -D DEFAULT_ERRORLOG="logs/error_log" -D AP_TYPES_CONFIG_FILE="conf/mime.types" -D SERVER_CONFIG_FILE="conf/httpd.conf" WARNING: MaxClients (500) is not an integer multiple of ThreadsPerChild (15), lowering MaxClients to 495 for a maximum of 33 child processes, Warning: DocumentRoot [/home/***/public_html/****/sub/interspire] does not exist Warning: DocumentRoot [/home/***/public_html/****/sub/solecart] does not exist Loaded Modules: core_module (static) authn_file_module (static) authn_default_module (static) authz_host_module (static) authz_groupfile_module (static) authz_user_module (static) authz_default_module (static) auth_basic_module (static) file_cache_module (static) cache_module (static) disk_cache_module (static) mem_cache_module (static) include_module (static) filter_module (static) deflate_module (static) log_config_module (static) logio_module (static) env_module (static) expires_module (static) headers_module (static) unique_id_module (static) setenvif_module (static) version_module (static) proxy_module (static) proxy_connect_module (static) proxy_ftp_module (static) proxy_http_module (static) proxy_scgi_module (static) proxy_ajp_module (static) proxy_balancer_module (static) ssl_module (static) mpm_worker_module (static) http_module (static) mime_module (static) dav_module (static) status_module (static) autoindex_module (static) asis_module (static) info_module (static) suexec_module (static) cgid_module (static) dav_fs_module (static) negotiation_module (static) dir_module (static) actions_module (static) speling_module (static) userdir_module (static) alias_module (static) rewrite_module (static) so_module (static) bwlimited_module (shared) suphp_module (shared) security2_module (shared) Syntax OK Compiled in modules: core.c mod_authn_file.c mod_authn_default.c mod_authz_host.c mod_authz_groupfile.c mod_authz_user.c mod_authz_default.c mod_auth_basic.c mod_file_cache.c mod_cache.c mod_disk_cache.c mod_mem_cache.c mod_include.c mod_filter.c mod_deflate.c mod_log_config.c mod_logio.c mod_env.c mod_expires.c mod_headers.c mod_unique_id.c mod_setenvif.c mod_version.c mod_proxy.c mod_proxy_connect.c mod_proxy_ftp.c mod_proxy_http.c mod_proxy_scgi.c mod_proxy_ajp.c mod_proxy_balancer.c mod_ssl.c worker.c http_core.c mod_mime.c mod_dav.c mod_status.c mod_autoindex.c mod_asis.c mod_info.c mod_suexec.c mod_cgid.c mod_dav_fs.c mod_negotiation.c mod_dir.c mod_actions.c mod_speling.c mod_userdir.c mod_alias.c mod_rewrite.c mod_so.c ------------------------------- From /usr/local/apache/conf/httpd.conf ------------------------------- Timeout 300 KeepAlive Off MaxKeepAliveRequests 100 KeepAliveTimeout 5 StartServers 5 MinSpareServers 5 MaxSpareServers 10 MinSpareServers 5 MaxSpareServers 10 ServerLimit 500 MaxClients 500 MaxRequestsPerChild 10000 ---------------------------- mysqlreport output ---------------------------- MySQL 5.5.35-cll uptime 2 18:59:11 Sat Feb 1 19:08:06 2014 __ Key _________________________________________________________________ Buffer used 6.54M of 8.00M %Used: 81.76 Current 5.82M %Usage: 72.80 Write hit 99.67% Read hit 99.77% __ Questions ___________________________________________________________ Total 66.95M 277.6/s DMS 59.70M 247.6/s %Total: 89.17 Com_ 7.66M 31.8/s 11.44 -Unknown 2.32M 9.6/s 3.47 COM_QUIT 1.92M 8.0/s 2.86 Slow 10 s 944 0.0/s 0.00 %DMS: 0.00 Log: OFF DMS 59.70M 247.6/s 89.17 SELECT 55.37M 229.6/s 82.69 92.74 UPDATE 1.96M 8.1/s 2.92 3.28 DELETE 1.04M 4.3/s 1.56 1.75 INSERT 1.04M 4.3/s 1.55 1.74 REPLACE 300.13k 1.2/s 0.45 0.50 Com_ 7.66M 31.8/s 11.44 set_option 2.76M 11.4/s 4.12 change_db 1.70M 7.1/s 2.54 stmt_prepar 536.29k 2.2/s 0.80 __ Rows ________________________________________________________________ Rows 53.07G 220.1k/s Using idx 23.48G 97.4k/s %Index: 44.25 Rows/question 792.61 __ SELECT and Sort _____________________________________________________ Scan 13.21M 54.8/s %SELECT: 23.85 Range 1.16M 4.8/s 2.09 Full join 35.72k 0.1/s 0.06 Range check 1.07k 0.0/s 0.00 Full rng join 355 0.0/s 0.00 Sort scan 7.20M 29.9/s Sort range 2.45M 10.2/s Sort mrg pass 8.61k 0.0/s __ Table Locks _________________________________________________________ Waited 26.27k 0.1/s %Total: 0.04 Immediate 71.58M 296.8/s __ Tables ______________________________________________________________ Open 400 of 400 %Cache: 100.00 Opened 91.13M 377.9/s __ Connections _________________________________________________________ Max used 81 of 151 %Max: 53.64 Total 1.92M 8.0/s __ Created Temp ________________________________________________________ Disk table 916.25k 3.8/s %Disk: 29.13 Table 3.14M 13.0/s Size: 16.0M File 17.80k 0.1/s __ Threads _____________________________________________________________ Running 2 of 5 Created 1.92M 8.0/s Slow 0 0/s Cached 0 of 0 %Hit: 0.02 __ Aborted _____________________________________________________________ Clients 848 0.0/s Connects 274.25k 1.1/s __ Bytes _______________________________________________________________ Sent 644.06G 2.7M/s Received 9.11G 37.8k/s __ InnoDB Buffer Pool __________________________________________________ Usage 128.00M of 128.00M %Usage: 100.00 Read hit 95.59% Pages Free 0 %Total: 0.00 Data 8.14k 99.43 %Drty: 0.95 Misc 47 0.57 Latched 0 0.00 Reads 1.89G 7.8k/s From disk 83.22M 345.1/s %Disk: 4.41 Ahead Rnd 0 0/s Writes 25.65M 106.3/s Wait Free 0 0/s %Wait: 0.00 Flushes 1.80M 7.5/s __ InnoDB Lock _________________________________________________________ Waits 33514 0.1/s Current 0 Time acquiring Total 1776787 ms Average 53 ms Max 15274 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 83.34M 345.6/s Writes 2.61M 10.8/s fsync 951.84k 3.9/s Pending Reads 0 Writes 0 fsync 1 Pages Created 116.10k 0.5/s Read 83.33M 345.5/s Written 1.80M 7.5/s Rows Deleted 79.56k 0.3/s Inserted 719.16k 3.0/s Read 2.01G 8.3k/s Updated 8.80M 36.5/s ---------------------------- Total Server MyISAM and InnoDB Size ---------------------------- *************************** 1. row *************************** innodb_tablespace_utilization_perc: 100 total_size_gb: 11.246537491679192 index_size_gb: 2.6045795269310474 data_size_gb: 8.641957964748144 innodb_total_size_gb: 2.8715667724609375 innodb_data_size_gb: 2.2258453369140625 innodb_index_size_gb: 0.645721435546875 myisam_total_size_gb: 8.372694794088602 myisam_data_size_gb: 6.415046516805887 myisam_index_size_gb: 1.9576482772827148 perc_index: 23.158945800 perc_data: 76.841054100 innodb_perc_index: 22.486728900 innodb_perc_data: 77.513271000 myisam_perc_index: 23.381340400 myisam_perc_data: 76.618659500 innodb_perc_total_index: 24.791772600 innodb_perc_total_data: 25.756262000 myisam_perc_total_index: 75.161777800 myisam_perc_total_data: 74.231401500 ---------------------------- MySQL settings ---------------------------- [mysqld] innodb_file_per_table=1 open_files_limit=50000 --- +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | NO_CHAIN | | concurrent_insert | AUTO | | connect_timeout | 10 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_storage_engine | InnoDB | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | engine_condition_pushdown | ON | | event_scheduler | OFF | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | general_log_file | /var/lib/mysql/***.log | | group_concat_max_len | 1024 | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_innodb | YES | | have_ndbcluster | NO | | have_openssl | DISABLED | | have_partitioning | YES | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | | hostname | yourserverhostname | | ignore_builtin_innodb | OFF | | init_connect | | | init_file | | | init_slave | | | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 8388608 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 134217728 | | innodb_change_buffering | all | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 500 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 0 | | innodb_open_files | 300 | | innodb_print_all_deadlocks | OFF | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 0 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_spin_wait_delay | 6 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 5.5.35 | | innodb_write_io_threads | 4 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | keep_files_on_create | OFF | | key_buffer_size | 8388608 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | lc_messages | en_US | | lc_messages_dir | /usr/share/mysql/ | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | lock_wait_timeout | 31536000 | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | /var/lib/mysql/yourserverhostname.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | long_query_time | 10.000000 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_connect_errors | 10 | | max_connections | 151 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_long_data_size | 1048576 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 18446744073709551615 | | metadata_locks_cache_size | 1024 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | OFF | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old | OFF | | old_alter_table | OFF | | old_passwords | OFF | | open_files_limit | 50000 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on | | performance_schema | OFF | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 1000 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 10000 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 1000000 | | performance_schema_max_rwlock_classes | 30 | | performance_schema_max_rwlock_instances | 1000000 | | performance_schema_max_table_handles | 100000 | | performance_schema_max_table_instances | 50000 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 1000 | | pid_file | /var/lib/mysql/yourserverhostname.pid | | plugin_dir | /usr/lib64/mysql/plugin/ | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 0 | | skip_external_locking | ON | | skip_name_resolve | OFF | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_max_allowed_packet | 1073741824 | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slave_type_conversions | | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/***-slow.log | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 2097152 | | sql_auto_is_null | OFF | | sql_big_selects | ON | | sql_big_tables | OFF | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_low_priority_updates | OFF | | sql_max_join_size | 18446744073709551615 | | sql_mode | | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | 0 | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | InnoDB | | stored_program_cache | 256 | | sync_binlog | 0 | | sync_frm | ON | | sync_master_info | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | | system_time_zone | CET | | table_definition_cache | 400 | | table_open_cache | 400 | | thread_cache_size | 0 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | | thread_statistics | OFF | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 16777216 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | unique_checks | ON | | updatable_views_with_limit | YES | | userstat | OFF | | version | 5.5.35-cll | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | | wait_timeout | 28800 | +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------+---------------+ | Variable_name | Value | +------------------------------------------+---------------+ | Aborted_clients | 848 | | Aborted_connects | 274284 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Bytes_received | 9112285334 | | Bytes_sent | 644073885974 | | Com_admin_commands | 57241 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 719 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_begin | 476 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 1700600 | | Com_change_master | 0 | | Com_check | 55245 | | Com_checksum | 0 | | Com_commit | 1267 | | Com_create_db | 13 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 272 | | Com_create_procedure | 1 | | Com_create_server | 0 | | Com_create_table | 8577 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 2 | | Com_create_view | 0 | | Com_dealloc_sql | 24 | | Com_delete | 1041821 | | Com_delete_multi | 110 | | Com_do | 0 | | Com_drop_db | 7 | | Com_drop_event | 0 | | Com_drop_function | 0 | | Com_drop_index | 111 | | Com_drop_procedure | 2 | | Com_drop_server | 0 | | Com_drop_table | 173 | | Com_drop_trigger | 0 | | Com_drop_user | 5 | | Com_drop_view | 0 | | Com_empty_query | 0 | | Com_execute_sql | 24 | | Com_flush | 31 | | Com_grant | 1713 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 1027314 | | Com_insert_select | 10465 | | Com_install_plugin | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_lock_tables | 9297 | | Com_optimize | 60 | | Com_preload_keys | 0 | | Com_prepare_sql | 24 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 520 | | Com_rename_table | 0 | | Com_rename_user | 0 | | Com_repair | 2 | | Com_replace | 300142 | | Com_replace_select | 5 | | Com_reset | 0 | | Com_resignal | 0 | | Com_revoke | 2 | | Com_revoke_all | 2 | | Com_rollback | 38 | | Com_rollback_to_savepoint | 36 | | Com_savepoint | 556 | | Com_select | 55370001 | | Com_set_option | 2756930 | | Com_signal | 0 | | Com_show_authors | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 168 | | Com_show_charsets | 125 | | Com_show_client_statistics | 0 | | Com_show_collations | 0 | | Com_show_contributors | 0 | | Com_show_create_db | 2706 | | Com_show_create_event | 0 | | Com_show_create_func | 0 | | Com_show_create_proc | 9 | | Com_show_create_table | 163549 | | Com_show_create_trigger | 39 | | Com_show_databases | 376 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 0 | | Com_show_events | 125 | | Com_show_errors | 0 | | Com_show_fields | 325445 | | Com_show_function_status | 3603 | | Com_show_grants | 20175 | | Com_show_index_statistics | 0 | | Com_show_keys | 630 | | Com_show_master_status | 60 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 3603 | | Com_show_processlist | 808 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_relaylog_events | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 60 | | Com_show_status | 7209 | | Com_show_storage_engines | 5 | | Com_show_table_statistics | 0 | | Com_show_table_status | 314450 | | Com_show_tables | 65950 | | Com_show_thread_statistics | 0 | | Com_show_triggers | 152773 | | Com_show_user_statistics | 0 | | Com_show_variables | 1281 | | Com_show_warnings | 38 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 511380 | | Com_stmt_execute | 536447 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 536447 | | Com_stmt_reprepare | 483 | | Com_stmt_reset | 405760 | | Com_stmt_send_long_data | 0 | | Com_truncate | 727 | | Com_uninstall_plugin | 0 | | Com_unlock_tables | 9849 | | Com_update | 1937356 | | Com_update_multi | 19028 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connections | 1917781 | | Created_tmp_disk_tables | 916284 | | Created_tmp_files | 17805 | | Created_tmp_tables | 3145027 | | Delayed_errors | 4 | | Delayed_insert_threads | 2 | | Delayed_writes | 12457 | | Flush_commands | 1 | | Handler_commit | 11447522 | | Handler_delete | 220433 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 5943012 | | Handler_read_key | 1555787590 | | Handler_read_last | 505473 | | Handler_read_next | 21877776588 | | Handler_read_prev | 45884273 | | Handler_read_rnd | 603138368 | | Handler_read_rnd_next | 28151090216 | | Handler_rollback | 472 | | Handler_savepoint | 533 | | Handler_savepoint_rollback | 36 | | Handler_update | 562447644 | | Handler_write | 1491898789 | | Innodb_buffer_pool_pages_data | 8190 | | Innodb_buffer_pool_bytes_data | 134184960 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_bytes_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 1801346 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 2 | | Innodb_buffer_pool_pages_total | 8192 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 100076 | | Innodb_buffer_pool_read_ahead_evicted | 5346 | | Innodb_buffer_pool_read_requests | 1889058976 | | Innodb_buffer_pool_reads | 83316715 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 25645521 | | Innodb_data_fsyncs | 951910 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 1366852030464 | | Innodb_data_reads | 83444349 | | Innodb_data_writes | 2609383 | | Innodb_data_written | 60924825088 | | Innodb_dblwr_pages_written | 1801346 | | Innodb_dblwr_writes | 39685 | | Innodb_have_atomic_builtins | ON | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 3820768 | | Innodb_log_writes | 676296 | | Innodb_os_log_fsyncs | 702461 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 1888249344 | | Innodb_page_size | 16384 | | Innodb_pages_created | 116102 | | Innodb_pages_read | 83425893 | | Innodb_pages_written | 1801346 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 1776787 | | Innodb_row_lock_time_avg | 53 | | Innodb_row_lock_time_max | 15274 | | Innodb_row_lock_waits | 33514 | | Innodb_rows_deleted | 79558 | | Innodb_rows_inserted | 719167 | | Innodb_rows_read | 2012559367 | | Innodb_rows_updated | 8802327 | | Innodb_truncated_status_writes | 0 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 5132 | | Key_blocks_used | 6698 | | Key_read_requests | 4253947575 | | Key_reads | 9580482 | | Key_write_requests | 402208587 | | Key_writes | 1308539 | | Last_query_cost | 0.000000 | | Max_used_connections | 81 | | Not_flushed_delayed_rows | 0 | | Open_files | 303 | | Open_streams | 0 | | Open_table_definitions | 400 | | Open_tables | 401 | | Opened_files | 156867873 | | Opened_table_definitions | 90390899 | | Opened_tables | 91183606 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 | | Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_table_handles_lost | 0 | | Performance_schema_table_instances_lost | 0 | | Performance_schema_thread_classes_lost | 0 | | Performance_schema_thread_instances_lost | 0 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 0 | | Qcache_free_memory | 0 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 0 | | Queries | 68410828 | | Questions | 66957772 | | Rpl_status | AUTH_MASTER | | Select_full_join | 35719 | | Select_full_range_join | 355 | | Select_range | 1157875 | | Select_range_check | 1072 | | Select_scan | 13207366 | | Slave_heartbeat_period | 0.000 | | Slave_open_temp_tables | 0 | | Slave_received_heartbeats | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 945 | | Sort_merge_passes | 8613 | | Sort_range | 2448229 | | Sort_rows | 838168307 | | Sort_scan | 7198628 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 71586936 | | Table_locks_waited | 26273 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 0 | | Threads_connected | 5 | | Threads_created | 1917329 | | Threads_running | 3 | | Uptime | 241164 | | Uptime_since_flush_status | 241164 | +------------------------------------------+---------------+ +---------+--------------+-----------+----------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+--------------+-----------+----------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+ | 11023 | eximstats | localhost | eximstats | Sleep | 24 | | | | 1786924 | DELAYED | localhost | eximstats | Delayed insert | 24 | Waiting for INSERT | | | 1877100 | leechprotect | localhost | leechprotect | Sleep | 3436 | | | | 1917450 | DELAYED | localhost | **** | Delayed insert | 30 | Waiting for INSERT | | | 1917777 | *** | localhost | **** | Query | 0 | Sorting result | SELECT artid, secid, title, autor, counter, publish FROM nuked_sections WHERE secid='15' AND publish | | 1917779 | *** | localhost | **** | Query | 0 | Sorting result | SELECT artid, secid, title, autor, counter, publish FROM nuked_sections WHERE secid='15' AND publish | | 1917780 | root | localhost | | Query | 0 | | show processlist | +---------+--------------+-----------+----------------+----------------+------+--------------------+------------------------------------------------------------------------------------------------------+ ------------------------------------ INNODB Buffer Pool Stats ------------------------------------ *************************** 1. row *************************** POOL_ID: 0 POOL_SIZE: 8192 FREE_BUFFERS: 0 DATABASE_PAGES: 8190 OLD_DATABASE_PAGES: 3003 MODIFIED_DATABASE_PAGES: 0 PENDING_DECOMPRESS: 0 PENDING_READS: 0 PENDING_FLUSH_LRU: 0 PENDING_FLUSH_LIST: 0 PAGES_MADE_YOUNG: 83718903 PAGES_NOT_MADE_YOUNG: 0 PAGES_MADE_YOUNG_RATE: 1802.5213835467225 PAGES_MADE_NOT_YOUNG_RATE: 0 NUMBER_PAGES_READ: 83425893 NUMBER_PAGES_CREATED: 116102 NUMBER_PAGES_WRITTEN: 1801346 PAGES_READ_RATE: 1798.3607435581507 PAGES_CREATE_RATE: 1.714255102587454 PAGES_WRITTEN_RATE: 13.071195157229337 NUMBER_PAGES_GET: 1889058976 HIT_RATE: 850 YOUNG_MAKE_PER_THOUSAND_GETS: 150 NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0 NUMBER_PAGES_READ_AHEAD: 100076 NUMBER_READ_AHEAD_EVICTED: 5346 READ_AHEAD_RATE: 0 READ_AHEAD_EVICTED_RATE: 0.30356600774986164 LRU_IO_TOTAL: 101405 LRU_IO_CURRENT: 1 UNCOMPRESS_TOTAL: 0 UNCOMPRESS_CURRENT: 0 ---------------------------- INNODB Status ---------------------------- *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 140201 19:08:19 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 56 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 198104 1_second, 195527 sleeps, 19711 10_second, 1072 background, 1070 flush srv_master_thread log flush and writes: 211882 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 159465, signal count 126131 Mutex spin waits 1195880, rounds 6382889, OS waits 72353 RW-shared spins 49196, rounds 1273446, OS waits 36953 RW-excl spins 32496, rounds 1514029, OS waits 47099 Spin rounds per wait: 5.34 mutex, 25.89 RW-shared, 46.59 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 43999565 Purge done for trx's n:o < 43999458 undo n:o < 0 History list length 4034 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 1917782, OS thread handle 0x7fd2b9fe0700, query id 68410835 localhost root show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0], aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 83444349 OS file reads, 2609383 OS file writes, 951910 OS fsyncs 1798.36 reads/s, 16384 avg bytes/read, 16.48 writes/s, 5.04 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 6152, seg size 6154, 9468 merges merged operations: insert 30089, delete mark 3211, delete 59 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276707, node heap has 2 buffer(s) 231.80 hash searches/s, 42.20 non-hash searches/s --- LOG --- Log sequence number 57133242733 Log flushed up to 57133242733 Last checkpoint at 57133228887 0 pending log writes, 0 pending chkp writes 696175 log i/o's done, 2.82 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 116628036 Buffer pool size 8192 Free buffers 0 Database pages 8190 Old database pages 3003 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 83718903, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 83425893, created 116102, written 1801346 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 8190, unzip_LRU len: 0 I/O sum[101405]:cur[1], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 3183, id 140543080584960, state: sleeping Number of rows inserted 719167, updated 8802327, deleted 79558, read 2012559367 0.29 inserts/s, 564.04 updates/s, 0.18 deletes/s, 8192.21 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ---------------------------- MySQL Error Log ---------------------------- 140201 17:46:55 [Warning] Found invalid password for user: 140201 17:46:55 [Warning] Found invalid password for user: 140201 17:46:55 [Warning] Found invalid password for user: 140201 17:46:55 [Warning] Found invalid password for user: 140201 17:46:55 [Warning] Found invalid password for user: 140201 17:46:55 [Warning] Found invalid password for user: Report Complete: Sat Feb 1 19:08:19 CET 2014 ----------------------------
Thanks in advance !

Comments

14 comments

Date Votes
  • thinkbot
    add there in the end myisam_use_mmap=1 max_connections = 200 max_user_connections = 75 join_buffer_size=2M sort_buffer_size=256K table_open_cache = 8000 table_definition_cache = 6000 max_allowed_packet = 32M thread_cache_size = 128 query_cache_type = 1 query_cache_size = 100M query_cache_limit = 1M max_heap_table_size = 50M tmp_table_size = 50M key_buffer_size = 2500M innodb_buffer_pool_size = 3G innodb_log_buffer_size = 32M innodb_old_blocks_time=1000 innodb_concurrency_tickets=5000 innodb_autoextend_increment=64M innodb_open_files=2000 innodb_stats_on_metadata=0 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 then restart and generate mysqltuner again after few hours or 24h
    0
  • ougogo
    Hi thinkbot, I will try it righ now ! But at first glance, CPU usage and memory does not change. .vB I'll wait a few hours. Thanks. NB : I may be worrying for nothing. I have a lot of sites under Wordpress, Prestashop, etc..
    0
  • thinkbot
    "Cpu(s): 0.0%us, 0.6%sy, 2.0%ni, 12.4%id, 85.0%wa, 0.0%hi, 0.0%si, 0.0%st" That's your problem, high I/O usage, becouse of small MyISAM and Innodb buffers so instead of reading from RAM, server does a lot of slow I/O from HDD, in your case now, CPU load should decrease a bit, I/O would decrease A LOT, and RAM usage will increase, since higher buffers but thanks to that your sites would scale better when more users comes with your current settings, the bottleneck was HDD you should notice site speed increase, especially when you have more users, hdd I/O doesn't scale so difference when there is more users at once will be huge
    0
  • ougogo
    So, here again my results. ./mysqltuner.pl [06:41:27] root@**** [~]# ./mysqltuner.pl >> MySQLTuner 1.2.1 mod - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Modified by George Liu (eva2000) at http://vbtechsupport.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.35-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 6G (Tables: 36322) [--] Data in InnoDB tables: 2G (Tables: 18079) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 868K (Tables: 141) [!!] Total fragmented tables: 1160 -------- Security Recommendations ------------------------------------------- [!!] User has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 13h 35m 51s (17M q [361.829 qps], 470K conn, TX: 159B, RX: 2B) [--] Reads / Writes: 82% / 18% [--] Total buffers: 5.6G global + 2.9M per thread (200 max threads) [OK] Maximum possible memory usage: 6.2G (20% of installed RAM) [OK] Slow queries: 1% (206K/17M) [OK] Highest usage of available connections: 43% (87/200) [OK] Key buffer size / total MyISAM indexes: 2.4G/2.0G [OK] Key buffer hit rate: 99.9% (900M cached / 855K reads) [OK] Query cache efficiency: 61.9% (9M cached / 14M selects) [!!] Query cache prunes per day: 1234617 [!!] Sorts requiring temporary tables: 35% (233K temp sorts / 663K sorts) [!!] Joins performed without indexes: 2503 [!!] Temporary tables created on disk: 34% (106K on disk / 308K total) [OK] Thread cache hit rate: 99% (87 created / 470K connections) [!!] Table cache hit rate: 0% (8K open / 17M opened) [OK] Open file limit used: 20% (10K/50K) [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks) [!!] Connections aborted: 11% [OK] InnoDB data size / buffer pool: 2.2G/3.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Your applications are not closing MySQL connections properly Variables to adjust: query_cache_size (> 100M) sort_buffer_size (> 256K) read_rnd_buffer_size (> 256K) join_buffer_size (> 2.0M, or always use indexes with joins) tmp_table_size (> 50M, increase tmp_table_size) max_heap_table_size (> 50M, increase max_heap_table_size) table_cache (> 8000, table_open_cache hit rate <20%)
    ./mysqlmymonlite.sh run [06:43:42] root@**** [~/tools]# ./mysqlmymonlite.sh run ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.0 WHM mysqlmymon.com compiled by George Liu (eva2000) vbtechsupport.com ------------------------------------------------------------- Report Generated: Mon Feb 3 06:43:49 CET 2014 ---------------------------- MySQL Uptime: ---------------------------- MySQL server was started 2014-02-02 17:05:49 Uptime: 13 hours 37 min 59 sec Uptime 49079 Uptime_since_flush_status 49079 Max Used Connections: 87/200 (43.50%) Key Buffer Used: 11.52M/2.44G (0.46%) Current Key Buffer Usage: 483.04M (19.32%) InnoDB Buffer Pool: 1.98G/3.00G (66.17%) Query Cache: 77.71M/100.00M (77.71%) Table Locks Waited: 11.11k Rate: 0.2/s (0.15%) -------------------- Query Cache Limit (bytes): 1048576 Query Cache Min Unit (bytes): 4096 Query Cache Size (bytes): 104857600 Query Cache Free Mem (bytes): 23374720 Query Cache Used Mem (bytes): 81482880 Queries in Query Cache: 1 Query Cache Avg Size (bytes): 81482880 ---------------------------- Hardware Info: ---------------------------- Processors physical = 1, cores = 4, virtual = 4, hyperthreading = no 4 3400.201 4 Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz 4 8192 KB ---------------------------- TOP stats ---------------------------- Linux 2.6.32-042stab083.2 ((yourserverhostname)) 02/03/14 _x86_64_(8 CPU) 06:43:49 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle 06:43:51 all 4.44 5.96 2.53 0.13 0.00 0.00 0.00 0.00 86.95 06:43:51 0 0.51 11.17 1.52 0.00 0.00 0.00 0.00 0.00 86.80 06:43:51 1 15.42 4.21 5.61 0.47 0.00 0.00 0.00 0.00 74.30 06:43:51 2 0.53 4.26 1.06 0.00 0.00 0.00 0.00 0.00 94.15 06:43:51 3 0.52 4.71 1.57 0.00 0.00 0.00 0.00 0.00 93.19 06:43:51 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:43:51 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:43:51 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:43:51 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:43:51 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle 06:43:53 all 0.12 2.99 1.12 0.00 0.00 0.00 0.00 0.00 95.76 06:43:53 0 0.00 7.73 1.93 0.00 0.00 0.00 0.00 0.00 90.34 06:43:53 1 0.00 2.50 1.00 0.00 0.00 0.00 0.00 0.00 96.50 06:43:53 2 0.00 0.51 1.01 0.00 0.00 0.00 0.00 0.00 98.48 06:43:53 3 0.00 1.02 0.51 0.00 0.00 0.00 0.00 0.00 98.48 06:43:53 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:43:53 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:43:53 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 06:43:53 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle Average: all 2.26 4.46 1.82 0.06 0.00 0.00 0.00 0.00 91.39 Average: 0 0.25 9.41 1.73 0.00 0.00 0.00 0.00 0.00 88.61 Average: 1 7.97 3.38 3.38 0.24 0.00 0.00 0.00 0.00 85.02 Average: 2 0.26 2.33 1.04 0.00 0.00 0.00 0.00 0.00 96.37 Average: 3 0.26 2.84 1.03 0.00 0.00 0.00 0.00 0.00 95.88 Average: 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 top - 06:43:53 up 75 days, 19:02, 1 user, load average: 1.24, 1.75, 1.70 Tasks: 95 total, 1 running, 93 sleeping, 0 stopped, 1 zombie Cpu(s): 0.0%us, 0.5%sy, 1.9%ni, 11.8%id, 85.7%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 31457280k total, 23648412k used, 7808868k free, 0k buffers Swap: 31457280k total, 355940k used, 31101340k free, 12337948k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 28946 mysql 26 6 10.9g 5.5g 53m S 21.9 18.3 203:04.86 mysqld 29042 user1 24 4 0 0 0 Z 13.9 0.0 0:00.07 php 28780 nobody 24 4 1075m 59m 2416 S 2.0 0.2 0:00.03 httpd 29044 user2 24 4 200m 11m 7168 S 2.0 0.0 0:00.01 php 1 root 20 0 19232 492 356 S 0.0 0.0 0:23.50 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd/121 3 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper/121 481 user3 30 10 214m 7008 6964 S 0.0 0.0 0:55.46 php 789 user1 30 10 202m 5936 5932 S 0.0 0.0 0:59.47 php 1309 root 20 0 64308 200 196 S 0.0 0.0 0:00.00 saslauthd 1342 root 20 0 64308 24 20 S 0.0 0.0 0:00.00 saslauthd 1436 root 30 10 132m 152 92 S 0.0 0.0 1:11.99 pure-uploadscri 1703 root 20 0 21456 292 240 S 0.0 0.0 0:00.05 atd 2924 root 16 -4 10644 188 184 S 0.0 0.0 0:00.00 udevd 5009 root 20 0 66608 500 408 S 0.0 0.0 0:00.04 sshd 5218 root 20 0 238m 5476 824 S 0.0 0.0 2:54.74 rsyslogd 5530 root 30 10 135m 716 624 S 0.0 0.0 0:04.06 pure-ftpd 5535 root 28 8 133m 988 944 S 0.0 0.0 0:03.23 pure-authd 5976 user1 30 10 202m 5940 5936 S 0.0 0.0 0:59.45 php 6700 user3 30 10 214m 7008 6964 S 0.0 0.0 0:54.94 php 6782 user1 30 10 202m 6888 5936 S 0.0 0.0 0:59.86 php 8770 root 20 0 19600 1168 836 S 0.0 0.0 0:00.22 dovecot 8822 dovenull 20 0 42468 3664 2688 S 0.0 0.0 0:00.50 pop3-login 8823 dovenull 20 0 42736 3776 2700 S 0.0 0.0 0:00.25 imap-login 8824 dovecot 20 0 13304 1124 936 S 0.0 0.0 0:00.06 anvil 8825 root 20 0 13436 1304 976 S 0.0 0.0 0:00.10 log 8827 dovenull 20 0 42468 3552 2688 S 0.0 0.0 0:00.06 pop3-login 8828 dovenull 20 0 42584 3700 2700 S 0.0 0.0 0:00.10 imap-login 8829 root 20 0 16412 2156 1128 S 0.0 0.0 0:00.20 config 9351 user3 30 10 214m 7012 6968 S 0.0 0.0 0:55.11 php 9355 user1 30 10 202m 5936 5932 S 0.0 0.0 0:59.47 php 9619 user3 30 10 212m 7008 6964 S 0.0 0.0 0:41.46 php 13087 root 20 0 100m 20m 5808 S 0.0 0.1 1:12.18 cpsrvd-ssl 14070 mailman 28 8 199m 8696 860 S 0.0 0.0 0:00.00 mailmanctl 14075 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:02.17 python 14076 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:02.28 python 14077 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:02.10 python 14078 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:02.12 python 14079 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:02.07 python 14080 mailman 28 8 195m 11m 3584 S 0.0 0.0 0:02.29 python 14081 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:02.14 python 14082 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:00.06 python 14211 root 24 4 170m 65m 10m S 0.0 0.2 0:03.42 httpd 14216 nobody 24 4 162m 55m 904 S 0.0 0.2 0:01.67 httpd 14217 root 28 8 80200 10m 2948 S 0.0 0.0 0:00.05 leechprotect 14682 root 28 8 437m 237m 5704 S 0.0 0.8 1:46.31 clamd 14728 mailnull 28 8 71540 2980 704 S 0.0 0.0 0:00.13 exim 14792 root 28 8 161m 49m 3296 S 0.0 0.2 0:03.22 /usr/local/cpan 14860 root 28 8 171m 59m 3308 S 0.0 0.2 0:25.76 spamd child 15686 root 20 0 114m 1312 644 S 0.0 0.0 0:01.51 crond 16480 named 20 0 477m 64m 2036 S 0.0 0.2 12:59.80 named 17237 user4 20 0 23292 2444 2036 S 0.0 0.0 0:00.00 imap 17238 user5 20 0 23284 2440 2024 S 0.0 0.0 0:00.00 imap 17245 user4 20 0 23268 2408 1992 S 0.0 0.0 0:00.00 imap 17262 user5 20 0 23288 2420 2008 S 0.0 0.0 0:00.00 imap 18446 user6 20 0 23300 2640 2016 S 0.0 0.0 0:00.03 imap 19198 root 28 8 141m 3988 1320 S 0.0 0.0 2:58.50 /usr/local/cpan 19640 user3 30 10 214m 7008 6964 S 0.0 0.0 0:54.96 php 19645 user3 30 10 214m 7008 6964 S 0.0 0.0 0:55.28 php 20256 root 28 8 148m 12m 1424 S 0.0 0.0 0:39.66 lfd - sleeping 20492 user3 30 10 214m 7008 6964 S 0.0 0.0 0:55.50 php 20507 nobody 24 4 1333m 80m 11m S 0.0 0.3 0:07.64 httpd 20733 user1 30 10 200m 5936 5932 S 0.0 0.0 0:59.41 php 21616 user7 20 0 23272 2424 2012 S 0.0 0.0 0:00.00 imap 22274 nobody 24 4 1317m 78m 11m S 0.0 0.3 0:03.81 httpd 22410 root 20 0 36280 3548 1428 S 0.0 0.0 0:01.57 queueprocd - wa 22490 root 20 0 114m 3220 1136 S 0.0 0.0 0:04.14 dnsadmin - serv 22571 root 20 0 81620 9.8m 1644 S 0.0 0.0 0:42.03 tailwatchd 22617 root 20 0 71716 3208 2848 S 0.0 0.0 0:12.04 cPhulkd - proce 22662 root 28 8 23524 2184 1460 S 0.0 0.0 0:40.32 cpdavd-dormant 22743 root 38 18 27252 2512 1164 S 0.0 0.0 0:00.32 cpanellogd - sl 23379 nobody 24 4 1317m 77m 11m S 0.0 0.3 0:03.84 httpd 23535 user3 30 10 214m 7008 6964 S 0.0 0.0 0:55.32 php 24026 user8 20 0 23596 3016 2188 S 0.0 0.0 0:00.01 imap 24386 user3 30 10 214m 7004 6960 S 0.0 0.0 0:54.74 php 24817 nobody 24 4 1323m 61m 2428 S 0.0 0.2 0:00.13 httpd 25606 dovecot 20 0 29432 1968 1424 S 0.0 0.0 0:00.00 auth 25741 user3 30 10 214m 7004 6960 S 0.0 0.0 0:55.54 php 25821 user3 30 10 214m 7004 6960 S 0.0 0.0 0:54.62 php 26685 nobody 24 4 1333m 75m 11m S 0.0 0.2 0:01.84 httpd 26830 root 20 0 98300 4388 3420 S 0.0 0.0 0:00.01 sshd 26891 root 20 0 11444 1812 1408 S 0.0 0.0 0:00.00 bash 27358 user9 20 0 21184 4580 1812 S 0.0 0.0 0:00.05 pop3 28129 root 20 0 11308 416 412 S 0.0 0.0 0:00.00 bash 28130 root 20 0 4076 452 448 S 0.0 0.0 0:00.00 tee 28131 root 20 0 4064 452 448 S 0.0 0.0 0:00.00 logger 28231 nobody 24 4 1333m 65m 3668 S 0.0 0.2 0:00.62 httpd 28380 root 20 0 19408 1528 1180 S 0.0 0.0 0:00.00 ssl-params 28522 root 20 0 11308 1268 1224 S 0.0 0.0 0:00.00 mysqld_safe 28763 root 20 0 11444 728 316 S 0.0 0.0 0:00.00 bash 28764 root 20 0 4076 552 472 S 0.0 0.0 0:00.00 tee 28765 root 20 0 4064 656 564 S 0.0 0.0 0:00.00 logger 28798 root 20 0 9696 1892 1148 S 0.0 0.0 0:00.00 mysqlmymonlite. 29041 root 20 0 15052 1160 880 R 0.0 0.0 0:00.00 top 30407 user1 30 10 202m 5940 5936 S 0.0 0.0 0:59.55 php total used free shared buffers cached Mem: 30720 23093 7626 0 0 12048 Low: 30720 23093 7626 High: 0 0 0 -/+ buffers/cache: 11045 19674 Swap: 30720 347 30372 MemTotal: 31457280 kB MemFree: 7809088 kB Cached: 12337948 kB Buffers: 0 kB Active: 14585820 kB Inactive: 4217816 kB Active(anon): 5565204 kB Inactive(anon): 900484 kB Active(file): 9020616 kB Inactive(file): 3317332 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 31457280 kB SwapFree: 31101340 kB Dirty: 1648 kB Writeback: 4 kB AnonPages: 6465688 kB Shmem: 10840 kB Slab: 4844456 kB SReclaimable: 4792716 kB SUnreclaim: 51740 kB -------------------------- ps_mem.py output: http://www.pixelbeat.org/scripts/ps_mem.py -------------------------- Private + Shared = RAM usedProgram 4.0 KiB + 18.5 KiB = 22.5 KiBudevd 52.0 KiB + 57.0 KiB = 109.0 KiBpure-authd 64.0 KiB + 60.5 KiB = 124.5 KiBpure-uploadscript 104.0 KiB + 28.0 KiB = 132.0 KiBtee (2) 104.0 KiB + 31.0 KiB = 135.0 KiBlogger (2) 0.0 KiB + 192.0 KiB = 192.0 KiBsaslauthd (2) 132.0 KiB + 64.5 KiB = 196.5 KiBatd 164.0 KiB + 60.0 KiB = 224.0 KiBpure-ftpd 48.0 KiB + 177.5 KiB = 225.5 KiBmysqld_safe 204.0 KiB + 42.0 KiB = 246.0 KiBanvil 240.0 KiB + 99.0 KiB = 339.0 KiBinit 344.0 KiB + 45.0 KiB = 389.0 KiBlog 360.0 KiB + 58.5 KiB = 418.5 KiBssl-params 392.0 KiB + 53.5 KiB = 445.5 KiBdovecot 360.0 KiB + 355.5 KiB = 715.5 KiBcPhulkd - proce 712.0 KiB + 38.5 KiB = 750.5 KiBcrond 788.0 KiB + 71.0 KiB = 859.0 KiBauth 752.0 KiB + 170.5 KiB = 922.5 KiBmysqlmymonlite. 484.0 KiB + 542.5 KiB = 1.0 MiBbash (3) 1.1 MiB + 49.0 KiB = 1.1 MiBconfig 844.0 KiB + 378.0 KiB = 1.2 MiBcpdavd-dormant 1.3 MiB + 113.5 KiB = 1.4 MiBcpanellogd - sl 1.8 MiB + 388.0 KiB = 2.2 MiBpop3-login (2) 2.0 MiB + 187.0 KiB = 2.2 MiBdnsadmin - serv 2.2 MiB + 133.0 KiB = 2.3 MiBqueueprocd - wa 2.0 MiB + 344.0 KiB = 2.4 MiBsshd (2) 2.0 MiB + 400.0 KiB = 2.4 MiBimap-login (2) 2.4 MiB + 72.5 KiB = 2.5 MiBexim 2.8 MiB + 146.0 KiB = 2.9 MiBpop3 4.7 MiB + 45.0 KiB = 4.8 MiBrsyslogd 3.7 MiB + 1.2 MiB = 4.9 MiBimap (7) 7.7 MiB + 140.5 KiB = 7.8 MiBmailmanctl 7.6 MiB + 390.0 KiB = 8.0 MiBleechprotect 1.4 MiB + 6.7 MiB = 8.1 MiBphp (17) 8.2 MiB + 252.5 KiB = 8.5 MiBtailwatchd 11.6 MiB + 152.0 KiB = 11.7 MiBlfd - sleeping 17.7 MiB + 493.5 KiB = 18.2 MiBcpsrvd-ssl 27.8 MiB + 11.5 MiB = 39.3 MiB/usr/local/cpan (2) 35.3 MiB + 11.3 MiB = 46.6 MiBspamd child 59.6 MiB + 2.5 MiB = 62.1 MiBpython (8) 63.9 MiB + 108.0 KiB = 64.0 MiBnamed 83.7 MiB + 64.6 MiB = 148.3 MiBhttpd (9) 236.9 MiB + 59.0 KiB = 237.0 MiBclamd 5.5 GiB + 1.8 MiB = 5.5 GiBmysqld --------------------------------- 6.2 GiB ================================= Private + Shared = RAM usedProgram ---------------------------- Server Disk Usage ---------------------------- Filesystem Type Size Used Avail Use% Mounted on /dev/simfs simfs 1.5T 1000G 501G 67% / none devtmpfs 15G 4.0K 15G 1% /dev none tmpfs 15G 0 15G 0% /dev/shm /dev/simfs simfs 1.5T 1000G 501G 67% /home/ ---------------------------- MySQL datadir disk usage ---------------------------- /var/lib/mysql/ uses 18317164 kilobytes of disk space ---------------------------- ulimit -aH ---------------------------- core file size (blocks, -c) 1000000 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 255461 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 4096 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 14335 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited ---------------------------------------------------------- tail -15 /etc/security/limits.conf ---------------------------------------------------------- # - nice - max nice priority allowed to raise to values: [-20, 19] # - rtprio - max realtime priority # # # #* soft core 0 #* hard rss 10000 #@student hard nproc 20 #@faculty soft nproc 20 #@faculty hard nproc 50 #ftp hard nproc 0 #@student - maxlogins 4 # End of file ---------------------------------------------------------- mysqld process limits: cat /proc/28946/limits ---------------------------------------------------------- Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 8388608 bytes Max core file size 1024000000 1024000000 bytes Max resident set unlimited unlimited bytes Max processes 14335 14335 processes Max open files 50000 50000 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 255461 255461 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us ---------------------------------- CPANEL WEB SERVER & PHP Info: ---------------------------------- WHM / Cpanel version: 11.42.0.1 WHM's PHP Handler config: /usr/local/cpanel/bin/rebuild_phpconf --current Available handlers: suphp cgi none DEFAULT PHP: 5 PHP4 SAPI: none PHP5 SAPI: suphp SUEXEC: enabled RUID2: not installed PHP 5.4.23 (cli) (built: Dec 27 2013 14:01:04) Copyright (c) 1997-2013 The PHP Group Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies with the ionCube PHP Loader v4.4.1, Copyright (c) 2002-2013, by ionCube Ltd., and with Zend Guard Loader v3.3, Copyright (c) 1998-2013, by Zend Technologies System => Linux yourserverhostname 2.6.32-042stab083.2 #1 SMP Fri Nov 8 18:08:40 MSK 2013 x86_64 Build Date => Dec 27 2013 13:59:35 Configure Command => './configure' '--enable-bcmath' '--enable-calendar' '--enable-exif' '--enable-ftp' '--enable-gd-native-ttf' '--enable-libxml' '--enable-mbstring' '--enable-pdo=shared' '--enable-soap' '--enable-sockets' '--enable-zip' '--prefix=/usr/local' '--with-curl=/opt/curlssl/' '--with-freetype-dir=/usr' '--with-gd' '--with-gettext' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/usr' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libdir=lib64' '--with-libexpat-dir=/usr' '--with-libxml-dir=/opt/xml2' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-mysqli=/usr/bin/mysql_config' '--with-openssl=/usr' '--with-openssl-dir=/usr' '--with-pcre-regex=/opt/pcre' '--with-pdo-mysql=shared' '--with-pdo-sqlite=shared' '--with-pic' '--with-png-dir=/usr' '--with-tidy=/opt/tidy/' '--with-xmlrpc' '--with-xpm-dir=/usr' '--with-xsl=/opt/xslt/' '--with-zlib' '--with-zlib-dir=/usr' Server API | Command Line Interface Configuration File (php.ini) Path | /usr/local/lib Loaded Configuration File | /usr/local/lib/php.ini PHP API | 20100412 Zend Extension Build | API220100525,NTS PHP Extension Build | API20100525,NTS DOM/XML API Version | 20031129 Client API version | 5.5.35 Client API library version | 5.5.35 Client API header version | 5.5.34 API Extensions | pdo_mysql Client API version | mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $ Phar API version | 1.1.1 display_errors | Off | Off error_log | error_log | error_log extension_dir | /usr/local/lib/php/extensions/no-debug-non-zts-20100525 | /usr/local/lib/php/extensions/no-debug-non-zts-20100525 file_uploads | On | On log_errors | On | On log_errors_max_len | 1024 | 1024 max_execution_time | 0 | 0 max_file_uploads | 20 | 20 memory_limit | 320M | 320M open_basedir | /home:/usr:/tmp:/var/netenberg | /home:/usr:/tmp:/var/netenberg post_max_size | 20M | 20M realpath_cache_size | 16K | 16K realpath_cache_ttl | 120 | 120 sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i sql.safe_mode | Off | Off upload_max_filesize | 20M | 20M upload_tmp_dir | no value | no value sqlite3.extension_dir | no value | no value cURL support | enabled cURL Information | 7.24.0 ZLib Version | 1.2.3 GD Support | enabled GD Version | bundled (2.1.0 compatible) FreeType Support | enabled FreeType Linkage | with freetype FreeType Version | 2.3.11 GIF Read Support | enabled GIF Create Support | enabled JPEG Support | enabled libJPEG Version | 6b PNG Support | enabled libPNG Version | 1.2.49 WBMP Support | enabled libXML support | active libXML Compiled Version | 2.9.0 libXML Loaded Version | 20900 libXML streams | enabled PCRE (Perl Compatible Regular Expressions) Support | enabled PCRE Library Version | 8.21 2011-12-12 Soap Client | enabled Soap Server | enabled ZLib Support | enabled Server version: Apache/2.2.26 (Unix) Server built: Dec 27 2013 13:55:28 Cpanel::Easy::Apache v3.22.25 rev9999 Server's Module Magic Number: 20051115:33 Server loaded: APR 1.4.8, APR-Util 1.5.2 Compiled using: APR 1.4.8, APR-Util 1.5.2 Architecture: 64-bit Server MPM: Worker threaded: yes (fixed thread count) forked: yes (variable process count) Server compiled with.... -D APACHE_MPM_DIR="server/mpm/worker" -D APR_HAS_SENDFILE -D APR_HAS_MMAP -D APR_HAVE_IPV6 (IPv4-mapped addresses disabled) -D APR_USE_SYSVSEM_SERIALIZE -D APR_USE_PTHREAD_SERIALIZE -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT -D APR_HAS_OTHER_CHILD -D AP_HAVE_RELIABLE_PIPED_LOGS -D DYNAMIC_MODULE_LIMIT=128 -D HTTPD_ROOT="/usr/local/apache" -D SUEXEC_BIN="/usr/local/apache/bin/suexec" -D DEFAULT_SCOREBOARD="logs/apache_runtime_status" -D DEFAULT_ERRORLOG="logs/error_log" -D AP_TYPES_CONFIG_FILE="conf/mime.types" -D SERVER_CONFIG_FILE="conf/httpd.conf" WARNING: MaxClients (500) is not an integer multiple of ThreadsPerChild (15), lowering MaxClients to 495 for a maximum of 33 child processes, Warning: DocumentRoot [/home/****/public_html/*****/sub/interspire] does not exist Warning: DocumentRoot [/home/****/public_html/*****/sub/solecart] does not exist Loaded Modules: core_module (static) authn_file_module (static) authn_default_module (static) authz_host_module (static) authz_groupfile_module (static) authz_user_module (static) authz_default_module (static) auth_basic_module (static) file_cache_module (static) cache_module (static) disk_cache_module (static) mem_cache_module (static) include_module (static) filter_module (static) deflate_module (static) log_config_module (static) logio_module (static) env_module (static) expires_module (static) headers_module (static) unique_id_module (static) setenvif_module (static) version_module (static) proxy_module (static) proxy_connect_module (static) proxy_ftp_module (static) proxy_http_module (static) proxy_scgi_module (static) proxy_ajp_module (static) proxy_balancer_module (static) ssl_module (static) mpm_worker_module (static) http_module (static) mime_module (static) dav_module (static) status_module (static) autoindex_module (static) asis_module (static) info_module (static) suexec_module (static) cgid_module (static) dav_fs_module (static) negotiation_module (static) dir_module (static) actions_module (static) speling_module (static) userdir_module (static) alias_module (static) rewrite_module (static) so_module (static) bwlimited_module (shared) suphp_module (shared) security2_module (shared) Syntax OK Compiled in modules: core.c mod_authn_file.c mod_authn_default.c mod_authz_host.c mod_authz_groupfile.c mod_authz_user.c mod_authz_default.c mod_auth_basic.c mod_file_cache.c mod_cache.c mod_disk_cache.c mod_mem_cache.c mod_include.c mod_filter.c mod_deflate.c mod_log_config.c mod_logio.c mod_env.c mod_expires.c mod_headers.c mod_unique_id.c mod_setenvif.c mod_version.c mod_proxy.c mod_proxy_connect.c mod_proxy_ftp.c mod_proxy_http.c mod_proxy_scgi.c mod_proxy_ajp.c mod_proxy_balancer.c mod_ssl.c worker.c http_core.c mod_mime.c mod_dav.c mod_status.c mod_autoindex.c mod_asis.c mod_info.c mod_suexec.c mod_cgid.c mod_dav_fs.c mod_negotiation.c mod_dir.c mod_actions.c mod_speling.c mod_userdir.c mod_alias.c mod_rewrite.c mod_so.c ------------------------------- From /usr/local/apache/conf/httpd.conf ------------------------------- Timeout 300 KeepAlive Off MaxKeepAliveRequests 100 KeepAliveTimeout 5 StartServers 5 MinSpareServers 5 MaxSpareServers 10 MinSpareServers 5 MaxSpareServers 10 ServerLimit 500 MaxClients 500 MaxRequestsPerChild 10000 ---------------------------- mysqlreport output ---------------------------- MySQL 5.5.35-cll uptime 0 13:38:6 Mon Feb 3 06:43:55 2014 __ Key _________________________________________________________________ Buffer used 11.52M of 2.44G %Used: 0.46 Current 483.07M %Usage: 19.32 Write hit 99.69% Read hit 99.90% __ Questions ___________________________________________________________ Total 17.74M 361.5/s QC Hits 9.30M 189.5/s %Total: 52.43 DMS 6.96M 141.7/s 39.20 Com_ 2.58M 52.5/s 14.53 -Unknown 1.56M 31.9/s 8.82 COM_QUIT 471.68k 9.6/s 2.66 Slow 100 ms 206.34k 4.2/s 1.16 %DMS: 2.97 Log: ON DMS 6.96M 141.7/s 39.20 SELECT 5.72M 116.5/s 32.23 82.22 UPDATE 546.62k 11.1/s 3.08 7.86 INSERT 325.04k 6.6/s 1.83 4.67 DELETE 267.76k 5.5/s 1.51 3.85 REPLACE 97.21k 2.0/s 0.55 1.40 Com_ 2.58M 52.5/s 14.53 set_option 580.70k 11.8/s 3.27 stmt_prepar 439.19k 8.9/s 2.48 stmt_execut 439.19k 8.9/s 2.48 __ Rows ________________________________________________________________ Rows 8.27G 168.5k/s Using idx 3.77G 76.8k/s %Index: 45.60 Rows/question 466.05 __ SELECT and Sort _____________________________________________________ Scan 1.03M 20.9/s %SELECT: 17.94 Range 122.92k 2.5/s 2.15 Full join 2.25k 0.0/s 0.04 Range check 264 0.0/s 0.00 Full rng join 27 0.0/s 0.00 Sort scan 377.46k 7.7/s Sort range 287.30k 5.9/s Sort mrg pass 234.40k 4.8/s __ Query Cache _________________________________________________________ Memory usage 77.78M of 100.00M %Usage: 77.78 Block Fragmnt 10.90% Hits 9.30M 189.5/s Inserts 5.31M 108.1/s Insrt:Prune 7.58:1 93.9/s Hit:Insert 1.75:1 __ Table Locks _________________________________________________________ Waited 11.11k 0.2/s %Total: 0.15 Immediate 7.36M 149.9/s __ Tables ______________________________________________________________ Open 8000 of 8000 %Cache: 100.00 Opened 18.10M 368.7/s __ Connections _________________________________________________________ Max used 87 of 200 %Max: 43.50 Total 471.76k 9.6/s __ Created Temp ________________________________________________________ Disk table 106.34k 2.2/s %Disk: 52.41 Table 202.90k 4.1/s Size: 50.0M File 443.10k 9.0/s __ Threads _____________________________________________________________ Running 1 of 3 Created 87 0.0/s Slow 0 0/s Cached 84 of 128 %Hit: 99.98 __ Aborted _____________________________________________________________ Clients 166 0.0/s Connects 54.95k 1.1/s __ Bytes _______________________________________________________________ Sent 159.98G 3.3M/s Received 2.36G 48.1k/s __ InnoDB Buffer Pool __________________________________________________ Usage 1.98G of 3.00G %Usage: 66.17 Read hit 99.96% Pages Free 66.52k %Total: 33.83 Data 129.32k 65.77 %Drty: 0.04 Misc 770 0.39 Latched 0 0.00 Reads 280.38M 5.7k/s From disk 113.72k 2.3/s %Disk: 0.04 Ahead Rnd 0 0/s Writes 8.95M 182.3/s Wait Free 0 0/s %Wait: 0.00 Flushes 513.04k 10.5/s __ InnoDB Lock _________________________________________________________ Waits 15687 0.3/s Current 0 Time acquiring Total 4439666 ms Average 283 ms Max 17804 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 135.63k 2.8/s Writes 717.19k 14.6/s fsync 236.27k 4.8/s Pending Reads 0 Writes 0 fsync 0 Pages Created 16.61k 0.3/s Read 117.54k 2.4/s Written 513.04k 10.5/s Rows Deleted 52.17k 1.1/s Inserted 399.00k 8.1/s Read 463.42M 9.4k/s Updated 3.19M 65.0/s ---------------------------- Total Server MyISAM and InnoDB Size ---------------------------- *************************** 1. row *************************** innodb_tablespace_utilization_perc: 100 total_size_gb: 11.26354070007801 index_size_gb: 2.609251383692026 data_size_gb: 8.654289316385984 innodb_total_size_gb: 2.863800048828125 innodb_data_size_gb: 2.2171630859375 innodb_index_size_gb: 0.646636962890625 myisam_total_size_gb: 8.397421095520258 myisam_data_size_gb: 6.436297629028559 myisam_index_size_gb: 1.9611234664916992 perc_index: 23.165463200 perc_data: 76.834536700 innodb_perc_index: 22.579682600 innodb_perc_data: 77.420317300 myisam_perc_index: 23.353877800 myisam_perc_data: 76.646122100 innodb_perc_total_index: 24.782470800 innodb_perc_total_data: 25.619239200 myisam_perc_total_index: 75.160388100 myisam_perc_total_data: 74.371186200 ---------------------------- MySQL settings ---------------------------- [mysqld] innodb_file_per_table=1 open_files_limit=50000 myisam_use_mmap=1 max_connections = 200 max_user_connections = 75 join_buffer_size=2M sort_buffer_size=256K table_open_cache = 8000 table_definition_cache = 6000 max_allowed_packet = 32M thread_cache_size = 128 query_cache_type = 1 query_cache_size = 100M query_cache_limit = 1M max_heap_table_size = 50M tmp_table_size = 50M key_buffer_size = 2500M innodb_buffer_pool_size = 3G innodb_log_buffer_size = 32M innodb_old_blocks_time=1000 innodb_concurrency_tickets=5000 innodb_autoextend_increment=64M innodb_open_files=2000 innodb_stats_on_metadata=0 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 --- +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | NO_CHAIN | | concurrent_insert | AUTO | | connect_timeout | 10 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_storage_engine | InnoDB | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | engine_condition_pushdown | ON | | event_scheduler | OFF | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | general_log_file | /var/lib/mysql/***.log | | group_concat_max_len | 1024 | | have_compress | YES | | have_crypt | YES | | have_csv | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_innodb | YES | | have_ndbcluster | NO | | have_openssl | DISABLED | | have_partitioning | YES | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | | hostname | yourserverhostname | | ignore_builtin_innodb | OFF | | init_connect | | | init_file | | | init_slave | | | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 8388608 | | innodb_autoextend_increment | 1000 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 3221225472 | | innodb_change_buffering | all | | innodb_checksums | ON | | innodb_commit_concurrency | 0 | | innodb_concurrency_tickets | 5000 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 33554432 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 75 | | innodb_max_purge_lag | 0 | | innodb_mirrored_log_groups | 1 | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_open_files | 2000 | | innodb_print_all_deadlocks | OFF | | innodb_purge_batch_size | 20 | | innodb_purge_threads | 0 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_spin_wait_delay | 6 | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 5.5.35 | | innodb_write_io_threads | 4 | | interactive_timeout | 28800 | | join_buffer_size | 2097152 | | keep_files_on_create | OFF | | key_buffer_size | 2621440000 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | lc_messages | en_US | | lc_messages_dir | /usr/share/mysql/ | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | lock_wait_timeout | 31536000 | | locked_in_memory | OFF | | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | /var/lib/mysql/yourserverhostname.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | ON | | log_warnings | 1 | | long_query_time | 0.100000 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 33554432 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_connect_errors | 10 | | max_connections | 200 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 52428800 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_long_data_size | 33554432 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 75 | | max_write_lock_count | 18446744073709551615 | | metadata_locks_cache_size | 1024 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | ON | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old | OFF | | old_alter_table | OFF | | old_passwords | OFF | | open_files_limit | 50000 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on | | performance_schema | OFF | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 1000 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 10000 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 1000000 | | performance_schema_max_rwlock_classes | 30 | | performance_schema_max_rwlock_instances | 1000000 | | performance_schema_max_table_handles | 100000 | | performance_schema_max_table_instances | 50000 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 1000 | | pid_file | /var/lib/mysql/yourserverhostname.pid | | plugin_dir | /usr/lib64/mysql/plugin/ | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 104857600 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rpl_recovery_rank | 0 | | secure_auth | OFF | | secure_file_priv | | | server_id | 0 | | skip_external_locking | ON | | skip_name_resolve | OFF | | skip_networking | OFF | | skip_show_database | OFF | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_max_allowed_packet | 1073741824 | | slave_net_timeout | 3600 | | slave_skip_errors | OFF | | slave_transaction_retries | 10 | | slave_type_conversions | | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | mysql-slow.log | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 262144 | | sql_auto_is_null | OFF | | sql_big_selects | ON | | sql_big_tables | OFF | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_low_priority_updates | OFF | | sql_max_join_size | 18446744073709551615 | | sql_mode | | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | 0 | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | | storage_engine | InnoDB | | stored_program_cache | 256 | | sync_binlog | 0 | | sync_frm | ON | | sync_master_info | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | | system_time_zone | CET | | table_definition_cache | 6000 | | table_open_cache | 8000 | | thread_cache_size | 128 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | | thread_statistics | OFF | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 52428800 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | unique_checks | ON | | updatable_views_with_limit | YES | | userstat | OFF | | version | 5.5.35-cll | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | | wait_timeout | 28800 | +---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+ +------------------------------------------+--------------+ | Variable_name | Value | +------------------------------------------+--------------+ | Aborted_clients | 166 | | Aborted_connects | 54961 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Bytes_received | 2360623303 | | Bytes_sent | 160008718030 | | Com_admin_commands | 7305 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 1 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_begin | 25 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 376517 | | Com_change_master | 0 | | Com_check | 72 | | Com_checksum | 0 | | Com_commit | 948 | | Com_create_db | 0 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 848 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 0 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 267770 | | Com_delete_multi | 31 | | Com_do | 0 | | Com_drop_db | 5 | | Com_drop_event | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_procedure | 0 | | Com_drop_server | 0 | | Com_drop_table | 27 | | Com_drop_trigger | 0 | | Com_drop_user | 0 | | Com_drop_view | 0 | | Com_empty_query | 0 | | Com_execute_sql | 0 | | Com_flush | 3 | | Com_grant | 38 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 324166 | | Com_insert_select | 892 | | Com_install_plugin | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_lock_tables | 783 | | Com_optimize | 25 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 2226 | | Com_rename_table | 0 | | Com_rename_user | 0 | | Com_repair | 0 | | Com_replace | 97247 | | Com_replace_select | 2 | | Com_reset | 0 | | Com_resignal | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 2 | | Com_rollback_to_savepoint | 1 | | Com_savepoint | 2227 | | Com_select | 5718823 | | Com_set_option | 580770 | | Com_signal | 0 | | Com_show_authors | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 12 | | Com_show_charsets | 0 | | Com_show_client_statistics | 0 | | Com_show_collations | 0 | | Com_show_contributors | 0 | | Com_show_create_db | 225 | | Com_show_create_event | 0 | | Com_show_create_func | 0 | | Com_show_create_proc | 2 | | Com_show_create_table | 14854 | | Com_show_create_trigger | 0 | | Com_show_databases | 18 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 0 | | Com_show_events | 7 | | Com_show_errors | 0 | | Com_show_fields | 33381 | | Com_show_function_status | 306 | | Com_show_grants | 1695 | | Com_show_index_statistics | 0 | | Com_show_keys | 155 | | Com_show_master_status | 7 | | Com_show_open_tables | 0 | | Com_show_plugins | 0 | | Com_show_privileges | 0 | | Com_show_procedure_status | 306 | | Com_show_processlist | 164 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_relaylog_events | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 7 | | Com_show_status | 1470 | | Com_show_storage_engines | 4 | | Com_show_table_statistics | 0 | | Com_show_table_status | 28294 | | Com_show_tables | 8057 | | Com_show_thread_statistics | 0 | | Com_show_triggers | 13714 | | Com_show_user_statistics | 0 | | Com_show_variables | 289 | | Com_show_warnings | 48 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 402980 | | Com_stmt_execute | 439222 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 439222 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 220938 | | Com_stmt_send_long_data | 0 | | Com_truncate | 254 | | Com_uninstall_plugin | 0 | | Com_unlock_tables | 822 | | Com_update | 544026 | | Com_update_multi | 2667 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connections | 471823 | | Created_tmp_disk_tables | 106357 | | Created_tmp_files | 443202 | | Created_tmp_tables | 202967 | | Delayed_errors | 0 | | Delayed_insert_threads | 1 | | Delayed_writes | 1354 | | Flush_commands | 1 | | Handler_commit | 950587 | | Handler_delete | 105927 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 607394 | | Handler_read_key | 277356254 | | Handler_read_last | 81021 | | Handler_read_next | 3485956278 | | Handler_read_prev | 7408989 | | Handler_read_rnd | 132013036 | | Handler_read_rnd_next | 4223190818 | | Handler_rollback | 84 | | Handler_savepoint | 2184 | | Handler_savepoint_rollback | 1 | | Handler_update | 73619521 | | Handler_write | 273108330 | | Innodb_buffer_pool_pages_data | 129317 | | Innodb_buffer_pool_bytes_data | 2118729728 | | Innodb_buffer_pool_pages_dirty | 46 | | Innodb_buffer_pool_bytes_dirty | 753664 | | Innodb_buffer_pool_pages_flushed | 513104 | | Innodb_buffer_pool_pages_free | 66520 | | Innodb_buffer_pool_pages_misc | 770 | | Innodb_buffer_pool_pages_total | 196607 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 3600 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 280432528 | | Innodb_buffer_pool_reads | 113724 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 8950818 | | Innodb_data_fsyncs | 236304 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 1927925760 | | Innodb_data_reads | 135632 | | Innodb_data_writes | 717273 | | Innodb_data_written | 17585750016 | | Innodb_dblwr_pages_written | 513104 | | Innodb_dblwr_writes | 10983 | | Innodb_have_atomic_builtins | ON | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 1875453 | | Innodb_log_writes | 180424 | | Innodb_os_log_fsyncs | 185695 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 770456576 | | Innodb_page_size | 16384 | | Innodb_pages_created | 16613 | | Innodb_pages_read | 117537 | | Innodb_pages_written | 513104 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 4439666 | | Innodb_row_lock_time_avg | 283 | | Innodb_row_lock_time_max | 17804 | | Innodb_row_lock_waits | 15687 | | Innodb_rows_deleted | 52166 | | Innodb_rows_inserted | 398995 | | Innodb_rows_read | 463484299 | | Innodb_rows_updated | 3189605 | | Innodb_truncated_status_writes | 0 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 2066998 | | Key_blocks_used | 11792 | | Key_read_requests | 903021202 | | Key_reads | 860021 | | Key_write_requests | 99991517 | | Key_writes | 310978 | | Last_query_cost | 0.000000 | | Max_used_connections | 87 | | Not_flushed_delayed_rows | 0 | | Open_files | 9605 | | Open_streams | 0 | | Open_table_definitions | 8000 | | Open_tables | 8000 | | Opened_files | 30041179 | | Opened_table_definitions | 16851280 | | Opened_tables | 18151791 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 | | Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_table_handles_lost | 0 | | Performance_schema_table_instances_lost | 0 | | Performance_schema_thread_classes_lost | 0 | | Performance_schema_thread_instances_lost | 0 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 6849 | | Qcache_free_memory | 23346904 | | Qcache_hits | 9304068 | | Qcache_inserts | 5308284 | | Qcache_lowmem_prunes | 700480 | | Qcache_not_cached | 333008 | | Qcache_queries_in_cache | 26626 | | Qcache_total_blocks | 63588 | | Queries | 18807838 | | Questions | 17744698 | | Rpl_status | AUTH_MASTER | | Select_full_join | 2252 | | Select_full_range_join | 27 | | Select_range | 122937 | | Select_range_check | 264 | | Select_scan | 1025971 | | Slave_heartbeat_period | 0.000 | | Slave_open_temp_tables | 0 | | Slave_received_heartbeats | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 206350 | | Sort_merge_passes | 234453 | | Sort_range | 287369 | | Sort_rows | 143190822 | | Sort_scan | 377498 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 7360273 | | Table_locks_waited | 11111 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 83 | | Threads_connected | 4 | | Threads_created | 87 | | Threads_running | 2 | | Uptime | 49096 | | Uptime_since_flush_status | 49096 | +------------------------------------------+--------------+ +--------+--------------+-----------+--------------+----------------+-------+--------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+--------------+-----------+--------------+----------------+-------+--------------------+------------------------------------------------------------------------------------------------------+ | 672 | eximstats | localhost | eximstats | Sleep | 162 | | | | 277977 | leechprotect | localhost | leechprotect | Sleep | 25919 | | | | 452909 | DELAYED | localhost | eximstats | Delayed insert | 162 | Waiting for INSERT | | | 471821 | user1_MaTT | localhost | user1_mhsc | Query | 0 | Sorting result | SELECT artid, secid, title, autor, counter, publish FROM nuked_sections WHERE secid='15' AND publish | | 471822 | root | localhost | | Query | 0 | | show processlist | +--------+--------------+-----------+--------------+----------------+-------+--------------------+------------------------------------------------------------------------------------------------------+ ------------------------------------ INNODB Buffer Pool Stats ------------------------------------ *************************** 1. row *************************** POOL_ID: 0 POOL_SIZE: 196607 FREE_BUFFERS: 66520 DATABASE_PAGES: 129317 OLD_DATABASE_PAGES: 47754 MODIFIED_DATABASE_PAGES: 46 PENDING_DECOMPRESS: 0 PENDING_READS: 0 PENDING_FLUSH_LRU: 0 PENDING_FLUSH_LIST: 0 PAGES_MADE_YOUNG: 18897 PAGES_NOT_MADE_YOUNG: 910783 PAGES_MADE_YOUNG_RATE: 0 PAGES_MADE_NOT_YOUNG_RATE: 0 NUMBER_PAGES_READ: 117537 NUMBER_PAGES_CREATED: 16613 NUMBER_PAGES_WRITTEN: 513104 PAGES_READ_RATE: 0 PAGES_CREATE_RATE: 0 PAGES_WRITTEN_RATE: 5.583217016312161 NUMBER_PAGES_GET: 280432528 HIT_RATE: 1000 YOUNG_MAKE_PER_THOUSAND_GETS: 0 NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0 NUMBER_PAGES_READ_AHEAD: 3600 NUMBER_READ_AHEAD_EVICTED: 0 READ_AHEAD_RATE: 0 READ_AHEAD_EVICTED_RATE: 0 LRU_IO_TOTAL: 268 LRU_IO_CURRENT: 0 UNCOMPRESS_TOTAL: 0 UNCOMPRESS_CURRENT: 0 ---------------------------- INNODB Status ---------------------------- *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 140203 6:44:05 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 48 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 37396 1_second, 36746 sleeps, 3729 10_second, 100 background, 99 flush srv_master_thread log flush and writes: 40914 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 26868, signal count 18669 Mutex spin waits 238865, rounds 822270, OS waits 13403 RW-shared spins 8881, rounds 249880, OS waits 7953 RW-excl spins 385, rounds 156809, OS waits 5117 Spin rounds per wait: 3.44 mutex, 28.14 RW-shared, 407.30 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 440B0AE6 Purge done for trx's n:o < 440B0A88 undo n:o < 0 History list length 4251 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 471824, OS thread handle 0x7fe36d5b6700, query id 18807859 localhost root show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0], aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 135632 OS file reads, 717273 OS file writes, 236304 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 8.37 writes/s, 3.19 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 6152, seg size 6154, 226 merges merged operations: insert 346, delete mark 542, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 6375023, node heap has 770 buffer(s) 2.75 hash searches/s, 0.52 non-hash searches/s --- LOG --- Log sequence number 58507934554 Log flushed up to 58507934554 Last checkpoint at 58507926992 0 pending log writes, 0 pending chkp writes 184180 log i/o's done, 2.62 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 3296722944; in additional pool allocated 0 Dictionary memory allocated 116998902 Buffer pool size 196607 Free buffers 66520 Database pages 129317 Old database pages 47754 Modified db pages 46 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 18897, not young 910783 0.00 youngs/s, 0.00 non-youngs/s Pages read 117537, created 16613, written 513104 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 129317, unzip_LRU len: 0 I/O sum[268]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 28946, id 140616457889536, state: sleeping Number of rows inserted 398995, updated 3189605, deleted 52166, read 463484299 0.04 inserts/s, 2.50 updates/s, 0.00 deletes/s, 7807.21 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ---------------------------- MySQL Error Log ---------------------------- 140202 20:01:22 [Warning] Found invalid password for user: '***'; Ignoring user 140203 2:54:03 [Warning] IP address '**.**.**.**' could not be resolved: Name or service not known Report Complete: Mon Feb 3 06:44:05 CET 2014 ----------------------------
    @thinkbot I also make your test. ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt # 38.8s user time, 210ms system time, 107.28M rss, 218.68M vsz # Current date: Mon Feb 3 06:48:38 2014 # Hostname: fr1.heberg.ch # Files: /var/lib/mysql/mysql-slow.log # Overall: 207.00k total, 2.83k unique, 4.19 QPS, 1.94x concurrency ______ # Time range: 2014-02-02 17:05:29 to 2014-02-03 06:48:34 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 95620s 100ms 112s 462ms 1s 1s 241ms # Lock time 5251s 0 31s 25ms 3ms 397ms 38us # Rows sent 98.25M 0 884.23k 497.70 12.54 3.49k 0 # Rows examine 1.34G 0 2.05M 6.79k 34.83k 44.16k 0.99 # Query size 199.58M 6 2.14M 1011.03 381.65 32.41k 97.36 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ================ ===== ======= ===== =========== # 1 0x98E3784F79D0F8A0 12984.7932 13.6% 26754 0.4853 1.10 UPDATE membres # 2 0x2FA0029B8F8B29CA 12600.2711 13.2% 26306 0.4790 1.19 UPDATE membres # 3 0xC59968CB43F1F8EA 10976.6881 11.5% 26724 0.4107 0.79 UPDATE superparrain # 4 0x7E974146D0EDB776 7596.6852 7.9% 16764 0.4532 1.11 UPDATE membres # 5 0x2C79EB838F6B7F1F 4641.5211 4.9% 20403 0.2275 0.31 INSERT UPDATE oqms?w?y?_wfFileMods # 6 0x15E2435282435897 3279.7220 3.4% 7663 0.4280 0.55 UPDATE superparrain # 7 0xF228C91161D1EC7C 2542.8146 2.7% 164 15.5050 5.99 SELECT tables # 8 0x94CC484BABCF0C47 1994.0424 2.1% 11155 0.1788 0.15 UPDATE oqms?w?y?_wfFileMods # 9 0x9C8FD83C6AD28760 1992.7830 2.1% 3659 0.5446 1.30 SELECT nuked_sections # 10 0x998F14BA98910AE1 1787.6517 1.9% 396 4.5143 15.49 UPDATE smtp sends # 11 0x08A7B745DE1D86C0 1375.9367 1.4% 3362 0.4093 0.33 INSERT UPDATE wp?_wfFileMods # 12 0x99AA0165670CE848 1311.2632 1.4% 1020 1.2856 1.75 ADMIN PREPARE # 13 0xD8DC565BF6A4ECBB 926.6497 1.0% 2244 0.4129 0.61 UPDATE stats_jour # 14 0x115DB0A473D72743 868.9743 0.9% 2233 0.3892 0.45 UPDATE stats_jour # 15 0xA41EB2589A97142B 742.6675 0.8% 2088 0.3557 0.30 SELECT nuked_sections nuked_vote # 16 0x834BC03B7B25C18C 686.0906 0.7% 2168 0.3165 0.67 INSERT fofo_sessions # 17 0x16A0ABD65129D9DA 656.7569 0.7% 1597 0.4112 0.35 SELECT nuked_sections nuked_vote # 18 0x0DB356EC3C2DCA94 636.8639 0.7% 756 0.8424 1.63 SELECT membres # 19 0x6A25572142A18911 633.7830 0.7% 806 0.7863 0.94 SELECT barre_bans # 20 0x212C5333E5E5E7B0 591.4930 0.6% 1684 0.3512 0.45 SELECT nuked_sections nuked_vote # 22 0x09E1349C6C4F2D9C 585.8553 0.6% 673 0.8705 1.08 UPDATE xf_session # 23 0xEBC9BB79DCC29010 549.5733 0.6% 1085 0.5065 0.58 UPDATE chat # 28 0xFE92B056DD08C32C 432.6780 0.5% 636 0.6803 0.97 SELECT membres # 30 0x7924DBE89AA990B7 404.3276 0.4% 585 0.6912 1.03 REPLACE datastore # 31 0x813031B8BBC3B329 397.1997 0.4% 848 0.4684 0.71 COMMIT # 32 0x94350EA2AB8AAC34 366.5412 0.4% 784 0.4675 0.89 UPDATE wp_options # 33 0x92F3B1B361FB0E5B 341.0910 0.4% 147 2.3203 44.33 SELECT wp_options # 34 0x91CEDDF0BED8F5F2 326.6269 0.3% 294 1.1110 1.41 UPDATE nuked_stats # 36 0x5864D8B3ABF4452F 320.7474 0.3% 26 12.3364 1.72 UPDATE membres # 37 0x7764470AFEBE1131 319.3998 0.3% 350 0.9126 1.44 SELECT membres # 38 0x49F186DD58DC05CC 318.6609 0.3% 613 0.5198 0.89 UPDATE wp_postmeta # 39 0x6F89EB1689608207 314.3144 0.3% 732 0.4294 0.58 UPDATE stats_jour # 40 0x98AA2BDF64B0644C 309.8116 0.3% 257 1.2055 1.57 UPDATE barre_bans # 41 0x028D50B38C88D6B2 299.1247 0.3% 164 1.8239 0.20 SELECT tables # 42 0x841D532195546EEC 278.0452 0.3% 26 10.6940 2.62 UPDATE membres # 44 0x32DCD227E0552B32 267.5159 0.3% 25 10.7006 1.46 UPDATE membres # 45 0x67A347A2812914DF 256.3636 0.3% 562 0.4562 6.71 SELECT posts # 46 0x2620E50F6E66CA4F 219.9076 0.2% 26 8.4580 1.41 UPDATE membres # 47 0xB4E31171FCC331C4 217.1977 0.2% 26 8.3538 0.41 UPDATE membres # 48 0xEF74DC08DC55A38E 216.6803 0.2% 25 8.6672 2.77 UPDATE membres # 53 0xAE7397F91F8C1C57 176.9384 0.2% 26 6.8053 0.48 UPDATE membres # 54 0x06E4022038CBE3BE 176.7471 0.2% 364 0.4856 0.50 SELECT nuked_comment # 55 0xA2BE714C593DDB13 171.1832 0.2% 132 1.2968 1.39 REPLACE dark_taigachat_activity # 58 0x692BC3956420D1E3 160.9358 0.2% 25 6.4374 1.69 UPDATE membres # 60 0xE4C8367CEAC837A5 156.3203 0.2% 166 0.9417 1.30 INSERT rpg_connectes # 61 0xA766EE8F7AB39063 154.1350 0.2% 252 0.6116 1.11 SELECT wp_terms wp_term_taxonomy wp_term_relationships # 62 0x141DAADA8779A9F0 152.5500 0.2% 146 1.0449 1.10 UPDATE w_membres # 63 0x7D5D79FD04338B32 136.3367 0.1% 294 0.4637 0.54 INSERT jeu_grille_coupjoue # 64 0xB2E482A9D9236BF0 135.3125 0.1% 21 6.4435 79.54 SELECT images imagehits # 65 0x53D917CFFA3E3EDC 134.5143 0.1% 26 5.1736 1.13 UPDATE membres # 66 0x17CD1C02EAF6222D 131.7209 0.1% 304 0.4333 0.66 SELECT membres # 67 0x20373A9E62F5C1C9 131.1555 0.1% 50 2.6231 6.16 SELECT wp_posts # 68 0x11F8C8E8FCD8E300 130.4943 0.1% 248 0.5262 0.93 UPDATE session # 69 0x10A3AF65945546AD 124.9898 0.1% 271 0.4612 0.95 UPDATE stats_jour # 70 0x5A7AFFC2F35EBD24 124.6397 0.1% 26 4.7938 1.40 UPDATE membres # 71 0x1336B9727E6A3389 121.4717 0.1% 112 1.0846 0.88 UPDATE phpbb_wwh # 73 0x6DFE4DF720C9441E 117.2912 0.1% 101 1.1613 2.25 SELECT membres # 74 0x7AEDF19FDD3A33F1 116.4531 0.1% 162 0.7188 2.26 SELECT wp_options # 75 0x3206685F5B66C645 107.7461 0.1% 259 0.4160 0.58 INSERT wp_postmeta # 76 0x65C3EFAFE5E7D1CE 105.1838 0.1% 172 0.6115 0.80 UPDATE ps_configuration # 77 0x38C55A09DD89B49D 104.2195 0.1% 51 2.0435 1.64 SELECT nuked_sections_cat # 79 0x55563D0553F47299 100.4452 0.1% 170 0.5909 0.86 UPDATE session # 80 0x8180211D6D0004CA 99.7365 0.1% 217 0.4596 0.64 UPDATE coupe_membres # 81 0x1A970BAF4651067D 98.5468 0.1% 87 1.1327 3.55 UPDATE visite # 83 0x187A53304EFC404B 97.8692 0.1% 65 1.5057 2.00 UPDATE nuked_sections # 84 0x6A06B4EE10B3B16B 96.9680 0.1% 97 0.9997 2.27 SELECT template # 85 0x4AB834D3DC96227C 96.0891 0.1% 205 0.4687 0.71 DELETE xf_deferred # 87 0x255B366A164E2E45 91.4314 0.1% 129 0.7088 0.19 SELECT cpg?x_comments cpg?x_pictures cpg?x_albums # 89 0x18239C52BF8ECBF2 89.6424 0.1% 129 0.6949 0.45 SELECT cpg?x_comments cpg?x_pictures cpg?x_albums # 90 0xFE648E9166EAEC3F 89.1034 0.1% 24 3.7126 1.22 CREATE TABLE cp_kronolith_sharesng # 91 0xE2C723964C87AB4C 87.9819 0.1% 47 1.8720 1.36 SELECT nuked_config # 92 0xAA8DB00350539EB6 85.9001 0.1% 202 0.4252 0.82 DELETE wp_options # 93 0xF5D3A58C26F6D2BA 85.8654 0.1% 64 1.3416 2.02 UPDATE rpg_users # 95 0xDC2D3B06B798B6E1 83.4271 0.1% 44 1.8961 1.98 SELECT jeuxbingosite # 96 0xF25E1F4471121091 82.3711 0.1% 12 6.8643 14.23 SELECT ps_connections # 97 0x35E21F87A99FC6A6 79.2434 0.1% 147 0.5391 1.28 UPDATE e?tfya_options # 98 0x449CFC2C345F0E8F 78.7519 0.1% 115 0.6848 1.68 UPDATE membres # 99 0xE311ED4B24F25CB0 78.5118 0.1% 24 3.2713 1.37 CREATE TABLE cp_nag_sharesng # 100 0x4B9F6474150C03D7 78.2273 0.1% 197 0.3971 0.82 INSERT wp?_wfHoover # 101 0x643FDF72354F8656 75.6944 0.1% 44 1.7203 2.45 SELECT maj # 102 0x1CE942D405CDAFEA 74.7127 0.1% 124 0.6025 0.61 SELECT nuked_stats_visitor # 103 0xCCD8C7F6ECC07A9A 73.7805 0.1% 24 3.0742 1.16 CREATE TABLE cp_mnemo_sharesng # 104 0x5BAB3AD0459A8897 72.9432 0.1% 24 3.0393 0.69 CREATE TABLE cp_turba_sharesng # 105 0xB5C7DEAFBBE8ACD5 72.3977 0.1% 96 0.7541 2.32 UPDATE membres # 106 0xB95B9991BF228756 72.3224 0.1% 90 0.8036 1.12 DELETE rpg_connectes # 110 0xABB8F5006AA991D4 69.5017 0.1% 106 0.6557 2.30 UPDATE DELETE UPDATE DELETE UPDATE DELETE UPDATE DELETE UPDATE wp?_wfConfig # 111 0x68BAB85EC649ED57 68.3140 0.1% 96 0.7116 0.74 SELECT membres # 113 0xD238821E0336E8C6 67.1400 0.1% 59 1.1380 1.20 UPDATE connectes # 114 0x3B2E46AD50FA7DC3 67.0704 0.1% 81 0.8280 0.51 UPDATE nuked_sessions # 115 0x1705804C26E8FEC7 66.3355 0.1% 112 0.5923 2.38 INSERT sends # 117 0x15EC439D13A061F8 65.7757 0.1% 24 2.7407 1.58 CREATE TABLE cp_ingo_sharesng # 118 0x1425177AF56711BC 64.6493 0.1% 79 0.8183 1.68 SELECT wp_posts wp_term_relationships # 120 0x2F027A4AD1665D70 60.2369 0.1% 111 0.5427 0.90 INSERT grattchasso_liste_servi # 121 0xDF61CBF7A712230A 59.3720 0.1% 115 0.5163 0.48 DELETE grattchasso_liste # 122 0xFCFB45659F3FC52C 59.2127 0.1% 119 0.4976 0.54 SELECT cpg?x_comments cpg?x_pictures cpg?x_albums cpg?x_categories # 124 0x2B9E58B24C085F0A 57.5869 0.1% 104 0.5537 0.44 INSERT ticketgratte_grattage # 125 0x76A19B469A6B9BE1 56.8384 0.1% 15 3.7892 19.10 SELECT histo # 126 0x3E5FE0C77FF8CDBC 53.2193 0.1% 33 1.6127 1.34 SELECT cpg?x_config # 127 0x2055D6C58DE6C2E2 52.9426 0.1% 20 2.6471 12.93 DELETE smtp # 130 0x7E41925DCC695110 51.5279 0.1% 41 1.2568 1.27 SELECT attachment filedata attachmenttype # 131 0x93B82D0D8EB2D758 51.0838 0.1% 58 0.8808 1.05 INSERT magicrai_gallery.cpg?x_sessions # 134 0xA9CC496E396F4DF2 49.1211 0.1% 49 1.0025 0.94 SELECT rpg_connectes # 135 0xCD2DC053EAC9548E 48.0471 0.1% 24 2.0020 0.55 CREATE TABLE cp_turba_shares # 136 0x49595F8ABFCFA78A 46.2702 0.0% 51 0.9073 1.27 INSERT wp_statpress # 138 0xED0C883AD2DE9208 44.7176 0.0% 41 1.0907 1.47 INSERT stat_? # 139 0x6E08DD4AAEF40DBF 43.7883 0.0% 31 1.4125 1.48 SELECT datastore # 140 0xADA9EAFC7C633848 43.4412 0.0% 24 1.8101 0.92 CREATE TABLE cp_nag_shares # 141 0xF7D16C3EB1440589 43.3523 0.0% 24 1.8063 1.14 CREATE TABLE cp_ingo_shares # 143 0x423CB38797385AA9 42.6604 0.0% 38 1.1226 1.49 UPDATE user # 144 0x3A166B0BF8123A32 42.0459 0.0% 104 0.4043 0.38 INSERT grattchasso_liste # 145 0xA0DF42BFCF03F24A 42.0303 0.0% 24 1.7513 1.28 CREATE TABLE cp_rampage_tagged # 146 0x672235BF0B363E89 41.9316 0.0% 37 1.1333 2.13 SELECT cache # 147 0x84189EB6687170D0 41.4190 0.0% 73 0.5674 0.81 SELECT nuked_sections # 148 0xC2CCC45BF0EF0D07 41.2104 0.0% 24 1.7171 0.56 CREATE TABLE cp_turba_objects # 149 0xA17330522E4B026D 41.0864 0.0% 105 0.3913 0.65 SELECT wp_posts # 151 0x304D93FB1C7B1DE6 40.3673 0.0% 24 1.6820 0.71 CREATE TABLE cp_mnemo_shares # 156 0x691FF87C2BE461B9 36.7625 0.0% 24 1.5318 0.27 CREATE TABLE cp_kronolith_shares # 157 0x2535E11701415ED3 36.6323 0.0% 91 0.4026 1.68 UPDATE barre_stats # 158 0xB3E42047793D0D35 36.5106 0.0% 11 3.3191 22.03 INSERT sends # 159 0x7EE686B57D86C228 36.5008 0.0% 44 0.8296 1.59 UPDATE phpbb_sessions # 161 0x10889E188619C5EB 35.5647 0.0% 47 0.7567 1.05 SELECT membres # 162 0x7D0BDF5216CAD8DB 35.0387 0.0% 24 1.4599 1.97 CREATE TABLE cp_kronolith_events # 165 0xD30C6D3498ECB6CA 33.9729 0.0% 38 0.8940 2.80 INSERT UPDATE oa?yq_options # 166 0x8C8A41523EC03F30 33.7167 0.0% 38 0.8873 0.54 SELECT nuked_sections # 167 0x5646343CD90450E6 33.6999 0.0% 39 0.8641 0.43 SELECT cpg?x_comments cpg?x_pictures cpg?x_albums # 168 0x85808F8E82B508CD 33.6426 0.0% 41 0.8206 1.53 UPDATE cache # 169 0x8916753E602E11D5 33.5124 0.0% 65 0.5156 0.66 INSERT vip_code # 170 0xA8C019341B87A2E3 33.4468 0.0% 34 0.9837 2.15 UPDATE cpg?x_pictures # 171 0xBCABFF5FDBCC17A0 33.1573 0.0% 11 3.0143 2.13 SELECT dbtech_vbdonate_donations user avatar customavatar # 175 0x8E62DBAAF3251A67 31.9126 0.0% 47 0.6790 0.64 INSERT nuked_stats_visitor # 176 0x1EFAC260E72F51D6 31.3454 0.0% 38 0.8249 4.93 SELECT histo # 177 0x8F2A6A9816BBD7EA 30.8878 0.0% 24 1.2870 0.41 CREATE TABLE cp_rampage_tags # 181 0xE6B4B834EDA12E19 30.0739 0.0% 19 1.5828 1.10 SELECT session # 182 0x842E9547B902BDFA 29.8854 0.0% 24 1.2452 1.22 CREATE TABLE cp_rampage_objects # 183 0x07B99B3D486EFA55 28.4665 0.0% 29 0.9816 0.49 UPDATE magicrai_gallery.cpg?x_sessions # 187 0xA0792A8190EF0C09 27.5201 0.0% 45 0.6116 1.45 INSERT UPDATE xf_draft # 189 0x2E62135949C59D18 26.6779 0.0% 36 0.7411 4.59 SELECT ptp # 190 0xA3782151FA2361CB 26.3683 0.0% 20 1.3184 0.91 DELETE nuked_sessions # 191 0xA56CE620465A3AA9 26.3007 0.0% 17 1.5471 2.07 SELECT nuked_sessions # 192 0x704708EFF16F79BD 26.2228 0.0% 24 1.0926 0.29 CREATE TABLE cp_nag_tasks # 193 0xBAC3F006BB71D83A 25.6166 0.0% 51 0.5023 0.61 SELECT cpg?x_comments # 194 0x2F414A7FC5D440E8 25.5306 0.0% 45 0.5673 1.22 REPLACE cache # 195 0xDA19772BD6135F30 25.5290 0.0% 29 0.8803 0.95 UPDATE thread # 196 0x6D00CD4B50BB97BC 25.5010 0.0% 24 1.0625 0.85 CREATE TABLE cp_horde_dav_collections # 198 0x4A66DE2C8C8F1136 24.5892 0.0% 34 0.7232 1.20 REPLACE chat # 199 0x285DA821D6DF9186 24.3997 0.0% 69 0.3536 0.55 SELECT post # 200 0xCEE2F442985238AB 24.3798 0.0% 39 0.6251 0.55 INSERT histo # 201 0x8E44A9BE65673654 24.3608 0.0% 56 0.4350 0.52 INSERT wp?_wfHits # 202 0x0F51A88F6BCF98D8 24.2801 0.0% 46 0.5278 0.83 SELECT wp_posts # 203 0xD720AED6045DEEB9 24.1951 0.0% 47 0.5148 1.23 INSERT phpbb_sessions # 206 0x4BE64FBACF74FA87 24.0223 0.0% 21 1.1439 0.55 SELECT xf_session # 209 0xEFB51CA3E00E631D 23.4067 0.0% 24 0.9753 0.23 CREATE TABLE cp_rampage_users # 210 0x5347802785453361 23.3442 0.0% 31 0.7530 0.82 SELECT pre_common_syscache # 211 0x9D98157080A2FEF3 23.0941 0.0% 26 0.8882 0.17 INSERT maj # 213 0x9D919D61D8507093 22.6683 0.0% 58 0.3908 0.26 INSERT UPDATE wp?_wfLeechers # 214 0xCBE376606B66AA7D 22.5781 0.0% 12 1.8815 3.07 SELECT nuked_modules # 217 0x3A5CC08B48F015E9 22.3667 0.0% 54 0.4142 0.47 INSERT cpg?x_temp_messages # 220 0x45C153518B16102D 21.9276 0.0% 29 0.7561 0.32 SELECT cpg?x_comments cpg?x_pictures cpg?x_albums # 221 0xBE197B724CE7C796 21.8666 0.0% 29 0.7540 1.95 UPDATE connectes # 223 0x8C2ED5B00E903E5D 21.8036 0.0% 30 0.7268 0.75 SELECT superparrain # 224 0xA242801E36807045 21.6085 0.0% 19 1.1373 1.49 DELETE sends # 225 0xC94CC36C1E871894 21.4897 0.0% 14 1.5350 2.10 SELECT cpg?x_plugins # 226 0x93050946DE86E530 21.2781 0.0% 17 1.2517 3.07 DELETE chat # 227 0x79627BF09CEE3913 21.1880 0.0% 12 1.7657 2.48 SELECT cpg?x_languages # 228 0x203063739EC2F624 21.1146 0.0% 25 0.8446 1.14 SELECT superparrain # 229 0x2132921475420455 21.0503 0.0% 19 1.1079 2.13 SELECT xf_data_registry # 230 0x60CC6AF1FC8909DA 20.7282 0.0% 19 1.0910 0.46 TRUNCATE TABLE oqms?w?y?_wfNet?s oqms0w9y2_wfNet404s # 232 0xC89E060FE92799F6 20.3097 0.0% 24 0.8462 0.96 SELECT wp_terms wp_term_taxonomy # 233 0x19CA7A4ABBB6E5D9 19.7512 0.0% 49 0.4031 0.34 UPDATE membres # 234 0x1972540C5F3947F5 19.2543 0.0% 16 1.2034 1.47 SELECT phpbb_config # 235 0x34F2DCFBA107501A 19.1511 0.0% 32 0.5985 3.33 SELECT wp_usermeta # 238 0x1817DEE900202F28 18.5991 0.0% 19 0.9789 1.69 SELECT rpg_connectes # 239 0xA4B69C2D7569D6B9 18.4341 0.0% 14 1.3167 1.45 TRUNCATE TABLE wp?_wfNet?s wp1_wfNet404s # 240 0xB39DF2CFF98D1D7A 18.3738 0.0% 10 1.8374 2.68 INSERT verification_clics # 241 0x8702CC75D9608220 18.0153 0.0% 29 0.6212 1.02 SELECT membres # 248 0x8837CD23D1218BB7 17.4201 0.0% 24 0.7258 0.34 CREATE TABLE cp_alarms # 250 0x7EDD67F850C9495A 17.3445 0.0% 28 0.6194 0.90 SELECT wp_posts # 254 0xB721DB7E19420BB7 17.1206 0.0% 30 0.5707 0.45 UPDATE wp_wfHits # 255 0xBE797E1DD5E4222F 16.5209 0.0% 15 1.1014 1.14 SELECT wp_posts # 256 0xB6C8BFC1570D1E2D 16.4378 0.0% 24 0.6849 0.39 CREATE TABLE cp_uids # 257 0x5355BED47E8E66F8 16.3948 0.0% 18 0.9108 1.23 SELECT ps_category ps_category_lang ps_category_group # 260 0x5B5317A372BF0DB7 16.1627 0.0% 27 0.5986 1.17 SELECT cpg?x_comments cpg?x_pictures cpg?x_albums cpg?x_categories # 261 0xEB042071E0BA392E 15.9507 0.0% 12 1.3292 1.44 SELECT phpbb_styles phpbb_styles_template phpbb_styles_theme phpbb_styles_imageset # 263 0x38F8A73FE90308B9 15.8482 0.0% 19 0.8341 0.82 UPDATE membres # 266 0x9EF6D3CBC75B7899 15.3121 0.0% 35 0.4375 0.38 UPDATE jm_session # 269 0x17A919C1FE97951A 15.2479 0.0% 11 1.3862 2.86 SELECT xf_user xf_user_profile xf_user_option xf_user_privacy xf_permission_combination # 270 0x8E346A594CC20AE1 15.1835 0.0% 26 0.5840 0.93 SELECT wp_posts # 272 0x49E7A665CC89FAFE 15.0694 0.0% 35 0.4306 0.17 INSERT UPDATE wp_wfLeechers # 273 0x30F9374F88CF0C06 15.0616 0.0% 27 0.5578 0.59 UPDATE xf_user_alert # 275 0x91977BD20D0DB4B1 14.8609 0.0% 23 0.6461 1.54 UPDATE phpbb_config # 276 0x2D92A2ED36339E2C 14.7756 0.0% 19 0.7777 0.20 TRUNCATE TABLE oqms?w?y?_wfVulnScanners oqms0w9y2_wfVulnScanners # 277 0x9A76588656728EDB 14.7147 0.0% 31 0.4747 0.43 INSERT DELETE INSERT DELETE INSERT DELETE INSERT xf_template_phrase # 278 0x6CCEA69278F5208F 14.5737 0.0% 24 0.6072 0.60 SELECT ac_terms ac_term_taxonomy ac_term_relationships # 280 0x9B656F956B4D1787 14.4885 0.0% 17 0.8523 1.93 SELECT nuked_sections # 283 0x5BFB3D7E44522D71 14.1986 0.0% 40 0.3550 0.45 SELECT wp_posts # 284 0xF79BEB6C7D616A2E 14.1291 0.0% 16 0.8831 1.71 INSERT session # 285 0xF77682B5A03AB24B 14.1037 0.0% 26 0.5424 0.51 DELETE xf_mail_queue # 290 0xAFBF83F8C8A38975 13.7577 0.0% 34 0.4046 0.42 SELECT cpg?x_comments cpg?x_pictures cpg?x_albums # 291 0x83593753EC54D237 13.7141 0.0% 19 0.7218 0.88 SELECT cpg?x_comments cpg?x_pictures cpg?x_albums # 293 0x7D8570A30585A0E9 13.4880 0.0% 10 1.3488 0.47 TRUNCATE TABLE superparrain `superparrain` # 296 0xF0AE2620CDE6F4FD 13.2459 0.0% 17 0.7792 1.01 SELECT wp_posts wp_term_relationships wp_term_taxonomy # 302 0x30F46A8E4CF57CF8 13.0697 0.0% 23 0.5682 1.91 SELECT nuked_sections # 303 0x6DB76E1FC19F0BF0 13.0391 0.0% 16 0.8149 0.50 SELECT template # 304 0xD05E20847C111D8B 12.9197 0.0% 30 0.4307 0.44 SELECT post user userfield usertextfield icon avatar customavatar editlog postparsed sigparsed sigpic # 306 0x426D3847001F1BDF 12.8436 0.0% 10 1.2844 1.59 SELECT membres # 310 0x439407F4098D93D1 12.6735 0.0% 32 0.3960 1.84 SELECT contentread user ipdata # 314 0x704FB3C9780C51B2 12.1765 0.0% 13 0.9367 4.69 SELECT wp_posts # 316 0x28F51182CE726D9A 12.0200 0.0% 16 0.7513 0.66 DELETE cpg?x_temp_messages # 319 0xAE3BC91C54A75BFA 11.8993 0.0% 24 0.4958 0.61 INSERT UPDATE xf_deferred # 324 0xCEBE2E30D5E4057A 11.7256 0.0% 13 0.9020 0.35 SELECT stats_jour # 326 0x199231B7786AC92F 11.6459 0.0% 26 0.4479 0.46 SELECT nuked_page # 332 0x3EE612D23715FDC7 11.4356 0.0% 13 0.8797 0.89 INSERT stat_be_? # 334 0xDA6A5C8D33CD0751 11.2024 0.0% 20 0.5601 2.15 SELECT nuked_comment # 335 0xF002BE2A5E58DBD8 11.1593 0.0% 16 0.6975 0.54 SELECT stats_jour # 336 0x159B5679000330B1 11.0586 0.0% 10 1.1059 1.14 UPDATE CC_joueurs # 339 0x9174EDDB77190BB8 10.9262 0.0% 15 0.7284 0.41 DELETE failures # 340 0xF816DC562876DA38 10.8440 0.0% 16 0.6778 0.95 UPDATE membres # 343 0x15712B16909E999B 10.7407 0.0% 24 0.4475 0.44 INSERT CC_grille?_case # 347 0x9042FAC9B7EBC644 10.5022 0.0% 13 0.8079 0.89 UPDATE users # 349 0x50B012B84A236154 10.4198 0.0% 10 1.0420 0.78 UPDATE jos_session # 357 0x0E20F2889DCE7F73 10.0378 0.0% 13 0.7721 1.14 UPDATE cpg?x_mod_online # 359 0x984A885989A2A4EB 9.9857 0.0% 31 0.3221 0.43 SELECT thread post # 361 0x453B68C4DB36D420 9.9717 0.0% 16 0.6232 0.74 INSERT lvzvisitor_browsers # 362 0xA0445779C688142E 9.9699 0.0% 23 0.4335 0.68 UPDATE lol_prono # 367 0x61BF770AEA227A84 9.7678 0.0% 24 0.4070 0.40 SELECT cpg_pictures # 370 0x80E79ACD74F0D287 9.6362 0.0% 17 0.5668 0.53 SELECT nuked_forums_messages # 374 0xA1353A96C9FDE55E 9.4652 0.0% 25 0.3786 0.46 SELECT wp_users # 375 0x3E154E20CEC57FAE 9.4643 0.0% 13 0.7280 1.08 UPDATE membres # 376 0xF13DECC7A3297E32 9.2735 0.0% 18 0.5152 0.21 TRUNCATE TABLE verification_clics `verification_clics` # 377 0x020BD2612391B6A3 9.2592 0.0% 14 0.6614 0.26 UPDATE nuked_forums_threads # 378 0x88D357B4E17A2A48 9.1671 0.0% 17 0.5392 0.31 INSERT xf_mail_queue # 383 0x335E603F3FDDF654 8.8393 0.0% 13 0.6799 1.63 SELECT ac_posts # 384 0x948537CA114673D6 8.7554 0.0% 12 0.7296 0.63 UPDATE CC_joueurs # 388 0x3653BC8F311AFB16 8.6774 0.0% 11 0.7889 0.41 SELECT ps_product ps_category_product # 389 0x80CB7551BDDA101B 8.6669 0.0% 14 0.6191 0.27 TRUNCATE TABLE wp?_wfVulnScanners wp1_wfVulnScanners # 394 0x566479A5ADB1ABB7 8.6457 0.0% 10 0.8646 0.80 INSERT ps_connections_source # 396 0xE534496560C7046E 8.6002 0.0% 14 0.6143 0.41 CREATE TABLE aaggregate_temp_? aaggregate_temp_1391361000 # 404 0xC8AB1551D3DCB253 8.1353 0.0% 17 0.4785 0.39 UPDATE membres # 406 0x75747A14CC3BE151 8.0657 0.0% 12 0.6721 0.65 DELETE affiche_page # 419 0xE8896DE642F6C717 7.8240 0.0% 16 0.4890 0.23 UPDATE rpg_users # 422 0x5DD4307B869A2146 7.7452 0.0% 17 0.4556 0.64 SELECT nuked_forums_read # 423 0x399B4F37A9BA0448 7.7361 0.0% 15 0.5157 2.90 SELECT be_horeca_loisir # 429 0xD0701F9086AAB03A 7.6416 0.0% 10 0.7642 0.84 SELECT ft_terms ft_term_taxonomy ft_term_relationships # 431 0xB67A53708664F8CB 7.5836 0.0% 10 0.7584 1.04 SELECT wp_posts # 439 0x7DF4971FA4423FFA 7.3520 0.0% 13 0.5655 0.67 SELECT cpg?x_pictures # 459 0xB1CB32ACEE46A8E2 6.5568 0.0% 15 0.4371 1.03 SELECT cpg?x_temp_messages # 471 0x30ED02FA64B8904C 6.2388 0.0% 11 0.5672 1.03 SELECT cpg?x_pictures cpg?x_albums cpg?x_comments # 482 0xE70904DE3C0DDCA7 5.8749 0.0% 15 0.3917 0.34 INSERT SELECT cp_kronolith_sharesng kronolith_sharesng cp_uids # 503 0xEE2AFF45B72A8D4C 5.4193 0.0% 10 0.5419 0.56 INSERT modsec # 504 0xEFEF35AC652B65CC 5.4175 0.0% 13 0.4167 0.34 UPDATE site_kunena_sessions # MISC 0xMISC 11105.1443 11.6% 30427 0.3650 0.0 <2575 ITEMS>
    I don't joined all of the result for ethical reasons against my clients. Thank you again! !
    0
  • thinkbot
    you can post the first 5 slowest # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ================ ===== ======= ===== =========== # 1 0x98E3784F79D0F8A0 12984.7932 13.6% 26754 0.4853 1.10 UPDATE membres # 2 0x2FA0029B8F8B29CA 12600.2711 13.2% 26306 0.4790 1.19 UPDATE membres # 3 0xC59968CB43F1F8EA 10976.6881 11.5% 26724 0.4107 0.79 UPDATE superparrain # 4 0x7E974146D0EDB776 7596.6852 7.9% 16764 0.4532 1.11 UPDATE membres # 5 0x2C79EB838F6B7F1F 4641.5211 4.9% 20403 0.2275 0.31 INSERT UPDATE oqms?w?y?_wfFileMods queries needs to be optimized, since they use a lot of I/O, the ones that are cousing problems are queries doing full scans or/and using temporary tables on disk you can increase this query_cache_size = 100M to query_cache_size = 150M It would be great if you could upgrade your mysql to 5.6, or best to Percona 5.6 anyway the main problem are not optimized queries, so please post those 5 top queries if possible
    0
  • ougogo
    Ok, I would put MySQL update after checking the changelog. Here the 5 top queries # Query 1: 0.54 QPS, 0.26x concurrency, ID 0x98E3784F79D0F8A0 at byte 134778818 # This item is included in the report because it matches --limit. # Scores: V/M = 1.10 # Time range: 2014-02-02 17:05:31 to 2014-02-03 06:48:33 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 12 26754 # Exec time 13 12985s 100ms 19s 485ms 2s 730ms 266ms # Lock time 10 541s 18us 17s 20ms 3ms 399ms 23us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 2 27.45M 1.05k 1.05k 1.05k 1.05k 0 1.05k # Query size 1 3.55M 130 146 138.99 143.84 4.29 136.99 # String: # Databases user1_seconde # Hosts localhost # Users user1_roland # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ###### # 10s+ # # Tables # SHOW TABLE STATUS FROM `user1_seconde` LIKE 'membres'\G # SHOW CREATE TABLE `user1_seconde`.`membres`\G UPDATE membres SET barre_date = now(), argent = argent +2, tbpointbarreperiode = tbpointbarreperiode +2 WHERE email ='anonymous@free.fr'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select barre_date = now(), argent = argent +2, tbpointbarreperiode = tbpointbarreperiode +2 from membres where email ='anonymous@free.fr'\G # Query 2: 0.53 QPS, 0.26x concurrency, ID 0x2FA0029B8F8B29CA at byte 134811297 # This item is included in the report because it matches --limit. # Scores: V/M = 1.19 # Time range: 2014-02-02 17:05:36 to 2014-02-03 06:48:31 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 12 26306 # Exec time 13 12600s 100ms 19s 479ms 1s 754ms 293ms # Lock time 19 1018s 14us 18s 39ms 11ms 471ms 28us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 1 26.99M 1.05k 1.05k 1.05k 1.05k 0 1.05k # Query size 1 2.48M 99 99 99 99 0 99 # String: # Databases user1_seconde # Hosts localhost # Users user1_roland # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ##### # 10s+ # # Tables # SHOW TABLE STATUS FROM `user1_seconde` LIKE 'membres'\G # SHOW CREATE TABLE `user1_seconde`.`membres`\G UPDATE membres SET argent = argent+0.2, tbpmasterbarre = tbpmasterbarre+0.2 WHERE pseudo='user1'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select argent = argent+0.2, tbpmasterbarre = tbpmasterbarre+0.2 from membres where pseudo='user1'\G # Query 3: 0.54 QPS, 0.22x concurrency, ID 0xC59968CB43F1F8EA at byte 1374497 # This item is included in the report because it matches --limit. # Scores: V/M = 0.79 # Time range: 2014-02-02 17:05:37 to 2014-02-03 06:48:33 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 12 26724 # Exec time 11 10977s 100ms 12s 411ms 1s 570ms 253ms # Lock time 8 442s 11us 10s 17ms 159us 255ms 44us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 26.10k 1 1 1 1 0 1 # Query size 0 1.58M 62 62 62 62 0 62 # String: # Databases user1_seconde # Hosts localhost # Users user1_roland # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ### # 10s+ # # Tables # SHOW TABLE STATUS FROM `user1_seconde` LIKE 'superparrain'\G # SHOW CREATE TABLE `user1_seconde`.`superparrain`\G UPDATE superparrain SET win = win +0.2 WHERE pseudo='user1'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select win = win +0.2 from superparrain where pseudo='user1'\G # Query 4: 0.34 QPS, 0.15x concurrency, ID 0x7E974146D0EDB776 at byte 134810801 # This item is included in the report because it matches --limit. # Scores: V/M = 1.11 # Time range: 2014-02-02 17:05:38 to 2014-02-03 06:48:33 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 8 16764 # Exec time 7 7597s 100ms 18s 453ms 1s 711ms 279ms # Lock time 12 681s 12us 17s 41ms 16ms 463ms 49us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 1 17.20M 1.05k 1.05k 1.05k 1.05k 0 1.05k # Query size 0 1.02M 59 67 63.95 65.89 1.96 62.76 # String: # Databases user1_seconde # Hosts localhost # Users user1_roland # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s #### # 10s+ # # Tables # SHOW TABLE STATUS FROM `user1_seconde` LIKE 'membres'\G # SHOW CREATE TABLE `user1_seconde`.`membres`\G UPDATE membres SET argent = argent+0.2 WHERE pseudo ='xs28'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select argent = argent+0.2 from membres where pseudo ='xs28'\G # Query 5: 2.16 QPS, 0.49x concurrency, ID 0x2C79EB838F6B7F1F at byte 207172280 # This item is included in the report because it matches --limit. # Scores: V/M = 0.31 # Time range: 2014-02-03 01:30:59 to 04:08:29 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 9 20403 # Exec time 4 4642s 100ms 7s 227ms 455ms 265ms 163ms # Lock time 0 1s 27us 98ms 72us 80us 770us 63us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 3 7.48M 265 735 384.62 381.65 26.68 381.65 # String: # Databases user2_wp # Hosts localhost # Users user2_matt # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `user2_wp` LIKE 'oqms0w9y2_wfFileMods'\G # SHOW CREATE TABLE `user2_wp`.`oqms0w9y2_wfFileMods`\G insert into oqms0w9y2_wfFileMods (filename, filenameMD5, knownFile, oldMD5, newMD5) values ('wp-content/sedlex/backup-scheduler/BackupScheduler_20130424133933_r2lahr31b1.z10', unhex(md5('wp-content/sedlex/backup-scheduler/BackupScheduler_20130424133933_r2lahr31b1.z10')), 0, '', unhex('660A7CFEDD5771EC54FCF47DCC263628')) ON DUPLICATE KEY UPDATE newMD5=unhex('660A7CFEDD5771EC54FCF47DCC263628'), knownFile=0\G
    Thanks a lot !
    0
  • thinkbot
    1. user1_seconde select barre_date = now(), argent = argent +2, tbpointbarreperiode = tbpointbarreperiode +2 from membres where email ='anonymous@free.fr' please check if you have idnex for email in memres table 2, 4. same as above, but check if index for field pseudo exists 3. user1_second table superparrain, check if field 'pseudo' have index Please also post details of query 7 # 7 0xF228C91161D1EC7C 2542.8146 2.7% 164 15.5050 5.99 SELECT tables And do you have munin plugin for cpanel installed ? it would give you view of server performance on graphs, it will nicely show CPU, RAM, HDD usage
    0
  • ougogo
    Ok, I created index for the fields of the tables of users 1, 2, 3 and 4. Here the details of query 7 # Query 7: 0.00 QPS, 0.05x concurrency, ID 0xF228C91161D1EC7C at byte 648698 # This item is included in the report because it matches --limit. # Scores: V/M = 5.99 # Time range: 2014-02-02 17:11:31 to 2014-02-03 06:45:17 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 164 # Exec time 2 2543s 10s 84s 16s 29s 10s 13s # Lock time 0 210ms 39us 50ms 1ms 76us 7ms 52us # Rows sent 0 164 1 1 1 1 0 1 # Rows examine 0 57.66k 360 360 360 360 0 360 # Query size 0 9.61k 60 60 60 60 0 60 # String: # Databases information_schema # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s # 10s+ ################################################################ # Tables # SHOW TABLE STATUS FROM `information_schema` LIKE 'tables'\G # SHOW CREATE TABLE `information_schema`.`tables`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT data_free FROM tables WHERE ENGINE = 'InnoDB' LIMIT 1\G
    You an also find few stats from Munin on cPanel and from stats software of my Node server : click here Thank you !
    0
  • thinkbot
    OK, great that you have munin monitoring It would be great if you could update mysql to 5.6 or to Percona 5.6 Then we could increase table_open_cache and add table_open_cache_instances, couse in 5.5 it doesnt scale well; also can you regenerate slow.txt ? so we can see updated counters for queries and can you post updated munin graphs of cpanel vps, cpu, load after adding the indexes ? you should also make sure swap is disabled nano /etc/sysctl.conf and add in the bottom vm.swappiness = 0 then save and run sysctl -p also add noatime to your mounting mount -o remount,noatime / mount -o remount,noatime /home and add noatime to fstab nano /etc/fstab and there you will have something like UUID=8bf4813a-f5e0-4d94-8f16-4b47aa0d6679 / ext4 usrjquota=quota.user,jqfmt=vfsv0 1 1 there where is / and /home add in parameters 'noatime' so instead usrjquota=quota.user,jqfmt=vfsv0 would be usrjquota=quota.user,jqfmt=vfsv0,noatime
    0
  • cPanelMichael
    Hello :) I just wanted to note (based on the recommendations) that MySQL 5.6 natively supported in cPanel version 11.42. cPanel version 11.42 is currently only available in the "Current" and "Edge" build tiers, but it's expected to reach the "Release" tier later this month (current estimate is 02-17-14). Thank you.
    0
  • ougogo
    Hi, Sorry for late. Here you can find results after MySQL 5.6 upgrade. ./mysqltuner.pl [20:40:52] root@***** [~]# ./mysqltuner.pl >> MySQLTuner 1.2.1 mod - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Modified by George Liu (eva2000) at http://vbtechsupport.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.6.15-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster x [--] Data in MyISAM tables: 6G (Tables: 36570) [--] Data in InnoDB tables: 2G (Tables: 17921) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [--] Data in MEMORY tables: 869K (Tables: 137) [!!] Total fragmented tables: 1188 -------- Security Recommendations ------------------------------------------- [!!] User '***' has no password set. [!!] User '***' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 7d 6h 57m 42s (180M q [286.289 qps], 5M conn, TX: 1575B, RX: 25B) [--] Reads / Writes: 74% / 26% [--] Total buffers: 5.7G global + 2.9M per thread (200 max threads) [OK] Maximum possible memory usage: 6.2G (20% of installed RAM) [OK] Slow queries: 0% (1M/180M) [OK] Highest usage of available connections: 45% (91/200) [OK] Key buffer size / total MyISAM indexes: 2.4G/2.0G [OK] Key buffer hit rate: 100.0% (9B cached / 689K reads) [OK] Query cache efficiency: 77.2% (117M cached / 152M selects) [!!] Query cache prunes per day: 1146228 [OK] Sorts requiring temporary tables: 2% (196K temp sorts / 8M sorts) [!!] Joins performed without indexes: 368830 [!!] Temporary tables created on disk: 30% (1M on disk / 3M total) [OK] Thread cache hit rate: 99% (91 created / 5M connections) [!!] Table cache hit rate: 0% (8K open / 115M opened) [OK] Open file limit used: 20% (10K/50K) [OK] Table locks acquired immediately: 99% (50M immediate / 50M locks) [!!] Connections aborted: 19% [OK] InnoDB data size / buffer pool: 2.2G/3.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Your applications are not closing MySQL connections properly Variables to adjust: query_cache_size (> 150M) [see warning above] join_buffer_size (> 2.0M, or always use indexes with joins) tmp_table_size (> 50M, increase tmp_table_size) max_heap_table_size (> 50M, increase max_heap_table_size) table_cache (> 8000, table_open_cache hit rate <20%)
    ./mysqlmymonlite.sh run [20:58:13] root@***** [~/tools]# ./mysqlmymonlite.sh run Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. Warning: Using a password on the command line interface can be insecure. ------------------------------------------------------------- System MySQL monitoring stats mysqlmymonlite.sh - 0.5.0 WHM mysqlmymon.com compiled by George Liu (eva2000) vbtechsupport.com ------------------------------------------------------------- Report Generated: Fri Feb 14 20:58:20 CET 2014 ---------------------------- MySQL Uptime: ---------------------------- MySQL server was started 2014-02-07 13:47:28.000000 Uptime: 7 days 7 hours 10 min 52 sec Uptime 630652 Uptime_since_flush_status 630652 Max Used Connections: 91/200 (45.50%) Key Buffer Used: 116.78M/2.44G (4.67%) Current Key Buffer Usage: 492.54M (19.70%) Query Cache: 120.01M/150.00M (80.01%) Table Locks Waited: 112.80k Rate: 0.2/s (0.22%) -------------------- Warning: Using a password on the command line interface can be insecure. Query Cache Limit (bytes): 1048576 Query Cache Min Unit (bytes): 4096 Query Cache Size (bytes): 157286400 Query Cache Free Mem (bytes): 31442520 Query Cache Used Mem (bytes): 125843880 Queries in Query Cache: 1 Query Cache Avg Size (bytes): 125843880 ---------------------------- Hardware Info: ---------------------------- Processors physical = 1, cores = 4, virtual = 4, hyperthreading = no 4 3400.201 4 Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz 4 8192 KB ---------------------------- TOP stats ---------------------------- Linux 2.6.32-042stab083.2 ((yourserverhostname)) 02/14/14 _x86_64_(8 CPU) 20:58:20 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle 20:58:22 all 1.41 12.68 7.55 0.13 0.00 0.00 0.13 0.00 78.10 20:58:22 0 0.49 16.67 10.29 0.49 0.00 0.00 0.49 0.00 71.57 20:58:22 1 1.01 13.13 8.59 0.00 0.00 0.00 0.00 0.00 77.27 20:58:22 2 3.12 9.90 6.25 0.00 0.00 0.00 0.52 0.00 80.21 20:58:22 3 0.00 11.11 5.29 0.00 0.00 0.00 0.00 0.00 83.60 20:58:22 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 20:58:22 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 20:58:22 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 20:58:22 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 20:58:22 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle 20:58:24 all 0.13 13.81 6.08 0.00 0.00 0.00 0.13 0.00 79.85 20:58:24 0 0.48 17.87 10.63 0.00 0.00 0.00 0.00 0.00 71.01 20:58:24 1 0.53 7.45 5.32 0.00 0.00 0.00 0.00 0.00 86.70 20:58:24 2 0.50 18.41 3.48 0.00 0.00 0.00 0.00 0.00 77.61 20:58:24 3 0.00 10.42 3.65 0.00 0.00 0.00 0.52 0.00 85.42 20:58:24 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 20:58:24 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 20:58:24 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 20:58:24 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle Average: all 0.76 13.25 6.82 0.06 0.00 0.00 0.13 0.00 78.98 Average: 0 0.49 17.27 10.46 0.24 0.00 0.00 0.24 0.00 71.29 Average: 1 0.78 10.36 6.99 0.00 0.00 0.00 0.00 0.00 81.87 Average: 2 1.78 14.25 4.83 0.00 0.00 0.00 0.25 0.00 78.88 Average: 3 0.00 10.76 4.46 0.00 0.00 0.00 0.26 0.00 84.51 Average: 4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 6 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Average: 7 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 top - 20:58:25 up 87 days, 9:16, 1 user, load average: 2.70, 3.52, 3.58 Tasks: 102 total, 2 running, 95 sleeping, 0 stopped, 5 zombie Cpu(s): 0.1%us, 0.5%sy, 1.9%ni, 10.8%id, 86.7%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 31457280k total, 22133808k used, 9323472k free, 0k buffers Swap: 31457280k total, 785364k used, 30671916k free, 10599460k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 31722 user1 24 4 0 0 0 Z 15.9 0.0 0:00.08 php 31063 mysql 26 6 11.5g 6.3g 58m S 4.0 21.0 3351:41 mysqld 14641 nobody 24 4 1333m 76m 11m S 2.0 0.2 0:11.41 httpd 31719 user2 24 4 0 0 0 Z 2.0 0.0 0:00.01 php 31724 user3 24 4 0 0 0 Z 2.0 0.0 0:00.01 php 31726 user4 24 4 0 0 0 Z 2.0 0.0 0:00.01 php 31727 user5 24 4 204m 11m 7136 R 2.0 0.0 0:00.01 php 1 root 20 0 19232 492 356 S 0.0 0.0 0:26.70 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd/121 3 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper/121 481 user6 30 10 214m 2844 2800 S 0.0 0.0 1:12.77 php 789 user7 30 10 202m 2404 2400 S 0.0 0.0 0:59.47 php 1309 root 20 0 64308 200 196 S 0.0 0.0 0:00.00 saslauthd 1342 root 20 0 64308 24 20 S 0.0 0.0 0:00.00 saslauthd 1436 root 30 10 132m 148 92 S 0.0 0.0 1:27.04 pure-uploadscri 1703 root 20 0 21456 240 188 S 0.0 0.0 0:00.07 atd 2924 root 16 -4 10644 188 184 S 0.0 0.0 0:00.00 udevd 4755 root 20 0 19600 1164 836 S 0.0 0.0 0:00.24 dovecot 4847 dovenull 20 0 42464 3652 2688 S 0.0 0.0 0:00.22 pop3-login 4848 dovenull 20 0 42468 3616 2728 S 0.0 0.0 0:00.09 imap-login 4849 dovecot 20 0 13304 1128 936 S 0.0 0.0 0:00.07 anvil 4850 root 20 0 13436 1320 976 S 0.0 0.0 0:00.13 log 4852 dovenull 20 0 42340 3540 2688 S 0.0 0.0 0:00.07 pop3-login 4853 dovenull 20 0 42736 3924 2728 S 0.0 0.0 0:00.27 imap-login 4854 root 20 0 16416 2140 1128 S 0.0 0.0 0:00.24 config 4855 dovecot 20 0 29544 1996 1432 S 0.0 0.0 0:00.42 auth 5009 root 20 0 66608 500 408 S 0.0 0.0 0:00.04 sshd 5218 root 20 0 238m 5560 832 S 0.0 0.0 3:47.73 rsyslogd 5530 root 30 10 135m 696 620 S 0.0 0.0 0:05.86 pure-ftpd 5535 root 28 8 133m 988 944 S 0.0 0.0 0:04.67 pure-authd 5976 user7 30 10 202m 2408 2404 S 0.0 0.0 0:59.45 php 6700 user6 30 10 214m 2844 2800 S 0.0 0.0 1:12.16 php 6782 user7 30 10 202m 2408 2404 S 0.0 0.0 0:59.86 php 7427 user8 20 0 23280 2348 1936 S 0.0 0.0 0:00.03 imap 7438 nobody 24 4 1325m 76m 11m S 0.0 0.3 0:13.99 httpd 8716 root 28 8 181m 69m 3288 S 0.0 0.2 0:03.88 spamd child 8719 root 20 0 36280 6540 1428 S 0.0 0.0 0:00.74 queueprocd - wa 8788 root 20 0 114m 22m 1280 S 0.0 0.1 0:01.80 dnsadmin - serv 8828 root 20 0 81584 14m 1620 S 0.0 0.0 0:24.32 tailwatchd 8854 root 20 0 71716 8448 2848 S 0.0 0.0 0:05.89 cPhulkd - proce 8862 root 20 0 110m 19m 3932 S 0.0 0.1 0:11.27 cpdavd - accept 8867 root 38 18 27252 3520 1164 S 0.0 0.0 0:00.16 cpanellogd - sl 9351 user6 30 10 214m 2848 2804 S 0.0 0.0 1:12.43 php 9355 user7 30 10 202m 2404 2400 S 0.0 0.0 0:59.47 php 9619 user6 30 10 212m 2844 2800 S 0.0 0.0 0:58.79 php 10236 cpanelro 20 0 100m 17m 2900 S 0.0 0.1 0:00.02 cpsrvd-ssl 10314 user8 20 0 23264 2516 1904 S 0.0 0.0 0:00.08 imap 11306 root 20 0 19408 1524 1180 S 0.0 0.0 0:00.00 ssl-params 12083 mailman 28 8 199m 8672 840 S 0.0 0.0 0:00.00 mailmanctl 12084 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:00.74 python 12085 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:00.78 python 12086 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:00.77 python 12087 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:00.72 python 12088 mailman 28 8 195m 10m 3584 S 0.0 0.0 0:00.77 python 12089 mailman 28 8 195m 11m 3584 S 0.0 0.0 0:00.81 python 12090 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:00.76 python 12091 mailman 28 8 194m 10m 3584 S 0.0 0.0 0:00.05 python 12150 root 20 0 114m 1356 644 S 0.0 0.0 0:06.53 crond 12189 root 24 4 171m 66m 11m S 0.0 0.2 0:01.75 httpd 12964 root 28 8 438m 239m 5788 S 0.0 0.8 0:22.60 clamd 13004 mailnull 28 8 71540 2980 704 S 0.0 0.0 0:00.10 exim 13055 root 28 8 161m 49m 3296 S 0.0 0.2 0:01.75 /usr/local/cpan 13087 root 20 0 100m 20m 5800 S 0.0 0.1 1:52.88 cpsrvd-ssl 13348 root 20 0 98300 4396 3424 S 0.0 0.0 0:00.02 sshd 13772 root 20 0 11444 1812 1408 S 0.0 0.0 0:00.00 bash 13887 cpanelro 20 0 100m 17m 2912 S 0.0 0.1 0:00.01 cpsrvd-ssl 16480 named 20 0 477m 93m 1872 S 0.0 0.3 24:46.25 named 16565 root 20 0 22184 600 596 S 0.0 0.0 0:00.00 xinetd 16791 nobody 24 4 1331m 74m 11m S 0.0 0.2 0:09.58 httpd 19175 user9 20 0 23252 4628 1820 S 0.0 0.0 0:00.04 pop3 19198 root 28 8 141m 4020 1320 S 0.0 0.0 3:35.23 /usr/local/cpan 19640 user6 30 10 214m 2844 2800 S 0.0 0.0 1:12.11 php 19645 user6 30 10 214m 2844 2800 S 0.0 0.0 1:12.53 php 20492 user6 30 10 214m 2844 2800 S 0.0 0.0 1:12.81 php 20716 nobody 24 4 1331m 74m 11m S 0.0 0.2 0:06.62 httpd 20733 user7 30 10 200m 2404 2400 S 0.0 0.0 0:59.41 php 23535 user6 30 10 214m 2844 2800 S 0.0 0.0 1:12.59 php 24386 user6 30 10 214m 2840 2796 S 0.0 0.0 1:11.88 php 25741 user6 30 10 214m 2840 2796 S 0.0 0.0 1:12.83 php 25821 user6 30 10 214m 2840 2796 S 0.0 0.0 1:11.65 php 25829 nobody 24 4 171m 56m 860 S 0.0 0.2 0:00.05 httpd 25830 root 28 8 80192 10m 2948 S 0.0 0.0 0:00.05 leechprotect 25845 user10 20 0 23280 2616 2012 S 0.0 0.0 0:00.00 imap 25996 user11 20 0 23320 2664 2044 S 0.0 0.0 0:00.01 imap 26237 user3 30 10 136m 904 672 S 0.0 0.0 0:00.00 pure-ftpd 26238 root 30 10 135m 364 216 S 0.0 0.0 0:00.00 pure-ftpd 26264 user12 20 0 23064 2128 1748 S 0.0 0.0 0:00.00 imap 27320 root 28 8 151m 13m 1424 S 0.0 0.0 2:05.29 lfd - sleeping 27384 nobody 24 4 1331m 65m 3096 S 0.0 0.2 0:02.72 httpd 28363 root 20 0 11436 1244 1240 S 0.0 0.0 0:00.04 mysqld_safe 28434 user5 20 0 0 0 0 Z 0.0 0.0 0:00.01 cpsrvd-ssl 28459 user3 20 0 100m 18m 2984 S 0.0 0.1 0:00.02 cpsrvd-ssl 29499 user3 20 0 100m 18m 2996 S 0.0 0.1 0:00.01 cpsrvd-ssl 30352 nobody 24 4 1331m 62m 2460 S 0.0 0.2 0:00.69 httpd 30407 user7 30 10 202m 2408 2404 S 0.0 0.0 0:59.55 php 30429 user12 20 0 23292 2440 2032 S 0.0 0.0 0:00.00 imap 31052 root 20 0 11444 728 316 S 0.0 0.0 0:00.00 bash 31053 root 20 0 4076 556 472 S 0.0 0.0 0:00.00 tee 31054 root 20 0 4064 656 564 S 0.0 0.0 0:00.00 logger 31198 nobody 24 4 1331m 61m 2416 S 0.0 0.2 0:00.18 httpd 31247 root 20 0 9696 1896 1148 S 0.0 0.0 0:00.00 mysqlmymonlite. 31716 root 20 0 14896 1152 880 R 0.0 0.0 0:00.00 top total used free shared buffers cached Mem: 30720 21615 9104 0 0 10351 Low: 30720 21615 9104 High: 0 0 0 -/+ buffers/cache: 11264 19455 Swap: 30720 766 29953 MemTotal: 31457280 kB MemFree: 9323432 kB Cached: 10599460 kB Buffers: 0 kB Active: 13884940 kB Inactive: 4120584 kB Active(anon): 6218304 kB Inactive(anon): 1187760 kB Active(file): 7666636 kB Inactive(file): 2932824 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 31457280 kB SwapFree: 30671916 kB Dirty: 1208 kB Writeback: 0 kB AnonPages: 7406064 kB Shmem: 10756 kB Slab: 4128268 kB SReclaimable: 4071400 kB SUnreclaim: 56868 kB -------------------------- ps_mem.py output: http://www.pixelbeat.org/scripts/ps_mem.py -------------------------- Private + Shared = RAM usedProgram 4.0 KiB + 17.5 KiB = 21.5 KiBudevd 4.0 KiB + 41.0 KiB = 45.0 KiBxinetd 52.0 KiB + 57.0 KiB = 109.0 KiBpure-authd 100.0 KiB + 16.5 KiB = 116.5 KiBlogger 104.0 KiB + 14.5 KiB = 118.5 KiBtee 60.0 KiB + 60.5 KiB = 120.5 KiBpure-uploadscript 80.0 KiB + 64.5 KiB = 144.5 KiBatd 0.0 KiB + 192.0 KiB = 192.0 KiBsaslauthd (2) 8.0 KiB + 193.5 KiB = 201.5 KiBmysqld_safe 208.0 KiB + 43.0 KiB = 251.0 KiBanvil 240.0 KiB + 99.0 KiB = 339.0 KiBinit 360.0 KiB + 47.0 KiB = 407.0 KiBlog 356.0 KiB + 58.5 KiB = 414.5 KiBssl-params 400.0 KiB + 48.5 KiB = 448.5 KiBdovecot 448.0 KiB + 259.5 KiB = 707.5 KiBpure-ftpd (3) 756.0 KiB + 37.5 KiB = 793.5 KiBcrond 808.0 KiB + 72.0 KiB = 880.0 KiBauth 752.0 KiB + 186.5 KiB = 938.5 KiBmysqlmymonlite. 496.0 KiB + 491.0 KiB = 987.0 KiBbash (2) 1.1 MiB + 51.0 KiB = 1.1 MiBconfig 1.8 MiB + 364.0 KiB = 2.1 MiBpop3-login (2) 2.0 MiB + 344.0 KiB = 2.4 MiBsshd (2) 2.3 MiB + 105.5 KiB = 2.4 MiBcpanellogd - sl 2.0 MiB + 386.0 KiB = 2.4 MiBimap-login (2) 2.4 MiB + 72.5 KiB = 2.5 MiBexim 2.8 MiB + 161.5 KiB = 3.0 MiBpop3 508.0 KiB + 3.1 MiB = 3.6 MiBphp (17) 3.1 MiB + 1.1 MiB = 4.2 MiBimap (6) 4.8 MiB + 46.0 KiB = 4.9 MiBrsyslogd 5.1 MiB + 123.0 KiB = 5.2 MiBqueueprocd - wa 5.5 MiB + 324.5 KiB = 5.8 MiBcPhulkd - proce 7.7 MiB + 140.5 KiB = 7.8 MiBmailmanctl 7.6 MiB + 360.0 KiB = 7.9 MiBleechprotect 12.5 MiB + 143.5 KiB = 12.6 MiBlfd - sleeping 13.1 MiB + 240.5 KiB = 13.3 MiBtailwatchd 16.5 MiB + 333.5 KiB = 16.8 MiBcpdavd - accept 21.5 MiB + 188.0 KiB = 21.7 MiBdnsadmin - serv 15.0 MiB + 14.7 MiB = 29.7 MiBcpsrvd-ssl (5) 26.7 MiB + 12.0 MiB = 38.7 MiB/usr/local/cpan (2) 44.6 MiB + 11.8 MiB = 56.4 MiBspamd child 59.6 MiB + 2.4 MiB = 62.1 MiBpython (8) 92.4 MiB + 105.0 KiB = 92.5 MiBnamed 74.1 MiB + 65.7 MiB = 139.8 MiBhttpd (9) 238.3 MiB + 74.0 KiB = 238.4 MiBclamd 6.3 GiB + 2.0 MiB = 6.3 GiBmysqld --------------------------------- 7.1 GiB ================================= Private + Shared = RAM usedProgram ---------------------------- Server Disk Usage ---------------------------- Filesystem Type Size Used Avail Use% Mounted on /dev/simfs simfs 1.6T 1.2T 334G 79% / none devtmpfs 15G 4.0K 15G 1% /dev none tmpfs 15G 0 15G 0% /dev/shm /dev/simfs simfs 1.6T 1.2T 334G 79% /home ---------------------------- MySQL datadir disk usage ---------------------------- /var/lib/mysql/ uses 20336664 kilobytes of disk space ---------------------------- ulimit -aH ---------------------------- core file size (blocks, -c) 1000000 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 255461 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 4096 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 14335 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited ---------------------------------------------------------- tail -15 /etc/security/limits.conf ---------------------------------------------------------- # - nice - max nice priority allowed to raise to values: [-20, 19] # - rtprio - max realtime priority # # # #* soft core 0 #* hard rss 10000 #@student hard nproc 20 #@faculty soft nproc 20 #@faculty hard nproc 50 #ftp hard nproc 0 #@student - maxlogins 4 # End of file ---------------------------------------------------------- mysqld process limits: cat /proc/31063/limits ---------------------------------------------------------- Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 8388608 bytes Max core file size 0 0 bytes Max resident set unlimited unlimited bytes Max processes 14335 14335 processes Max open files 50000 50000 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 255461 255461 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us ---------------------------------- CPANEL WEB SERVER & PHP Info: ---------------------------------- WHM / Cpanel version: 11.42.0.6 WHM's PHP Handler config: /usr/local/cpanel/bin/rebuild_phpconf --current Available handlers: suphp cgi none DEFAULT PHP: 5 PHP4 SAPI: none PHP5 SAPI: suphp SUEXEC: enabled RUID2: not installed PHP 5.4.24 (cli) (built: Feb 6 2014 07:20:55) Copyright (c) 1997-2013 The PHP Group Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies with the ionCube PHP Loader v4.4.1, Copyright (c) 2002-2013, by ionCube Ltd., and with Zend Guard Loader v3.3, Copyright (c) 1998-2013, by Zend Technologies System => Linux yourserverhostname 2.6.32-042stab083.2 #1 SMP Fri Nov 8 18:08:40 MSK 2013 x86_64 Build Date => Feb 6 2014 07:19:19 Configure Command => './configure' '--enable-bcmath' '--enable-calendar' '--enable-exif' '--enable-ftp' '--enable-gd-native-ttf' '--enable-libxml' '--enable-mbstring' '--enable-pdo=shared' '--enable-soap' '--enable-sockets' '--enable-zip' '--prefix=/usr/local' '--with-curl=/opt/curlssl/' '--with-freetype-dir=/usr' '--with-gd' '--with-gettext' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/usr' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libdir=lib64' '--with-libexpat-dir=/usr' '--with-libxml-dir=/opt/xml2' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-mysqli=/usr/bin/mysql_config' '--with-openssl=/usr' '--with-openssl-dir=/usr' '--with-pcre-regex=/opt/pcre' '--with-pdo-mysql=shared' '--with-pdo-sqlite=shared' '--with-pic' '--with-png-dir=/usr' '--with-tidy=/opt/tidy/' '--with-xmlrpc' '--with-xpm-dir=/usr' '--with-xsl=/opt/xslt/' '--with-zlib' '--with-zlib-dir=/usr' Server API | Command Line Interface Configuration File (php.ini) Path | /usr/local/lib Loaded Configuration File | /usr/local/lib/php.ini PHP API | 20100412 Zend Extension Build | API220100525,NTS PHP Extension Build | API20100525,NTS DOM/XML API Version | 20031129 Client API version | 5.6.15 Client API library version | 5.6.15 Client API header version | 5.6.15 API Extensions | pdo_mysql Client API version | mysqlnd 5.0.10 - 20111026 - $Id: 2d8f599f49e5e8d2f5ba7112b5c444b3e2e48cdf $ Phar API version | 1.1.1 display_errors | Off | Off error_log | error_log | error_log extension_dir | /usr/local/lib/php/extensions/no-debug-non-zts-20100525 | /usr/local/lib/php/extensions/no-debug-non-zts-20100525 file_uploads | On | On log_errors | On | On log_errors_max_len | 1024 | 1024 max_execution_time | 0 | 0 max_file_uploads | 20 | 20 memory_limit | 320M | 320M open_basedir | /home:/usr:/tmp:/var/netenberg | /home:/usr:/tmp:/var/netenberg post_max_size | 20M | 20M realpath_cache_size | 16K | 16K realpath_cache_ttl | 120 | 120 sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i sql.safe_mode | Off | Off upload_max_filesize | 20M | 20M upload_tmp_dir | no value | no value sqlite3.extension_dir | no value | no value cURL support | enabled cURL Information | 7.24.0 ZLib Version | 1.2.3 GD Support | enabled GD Version | bundled (2.1.0 compatible) FreeType Support | enabled FreeType Linkage | with freetype FreeType Version | 2.3.11 GIF Read Support | enabled GIF Create Support | enabled JPEG Support | enabled libJPEG Version | 6b PNG Support | enabled libPNG Version | 1.2.49 WBMP Support | enabled libXML support | active libXML Compiled Version | 2.9.0 libXML Loaded Version | 20900 libXML streams | enabled PCRE (Perl Compatible Regular Expressions) Support | enabled PCRE Library Version | 8.21 2011-12-12 Soap Client | enabled Soap Server | enabled ZLib Support | enabled Server version: Apache/2.2.26 (Unix) Server built: Feb 6 2014 07:14:38 Cpanel::Easy::Apache v3.22.30 rev9999 Server's Module Magic Number: 20051115:33 Server loaded: APR 1.4.8, APR-Util 1.5.2 Compiled using: APR 1.4.8, APR-Util 1.5.2 Architecture: 64-bit Server MPM: Worker threaded: yes (fixed thread count) forked: yes (variable process count) Server compiled with.... -D APACHE_MPM_DIR="server/mpm/worker" -D APR_HAS_SENDFILE -D APR_HAS_MMAP -D APR_HAVE_IPV6 (IPv4-mapped addresses disabled) -D APR_USE_SYSVSEM_SERIALIZE -D APR_USE_PTHREAD_SERIALIZE -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT -D APR_HAS_OTHER_CHILD -D AP_HAVE_RELIABLE_PIPED_LOGS -D DYNAMIC_MODULE_LIMIT=128 -D HTTPD_ROOT="/usr/local/apache" -D SUEXEC_BIN="/usr/local/apache/bin/suexec" -D DEFAULT_SCOREBOARD="logs/apache_runtime_status" -D DEFAULT_ERRORLOG="logs/error_log" -D AP_TYPES_CONFIG_FILE="conf/mime.types" -D SERVER_CONFIG_FILE="conf/httpd.conf" Warning: DocumentRoot [/home/qwebsnet/public_html/www.qwebs.net/sub/interspire] does not exist Warning: DocumentRoot [/home/qwebsnet/public_html/www.qwebs.net/sub/solecart] does not exist Loaded Modules: core_module (static) authn_file_module (static) authn_default_module (static) authz_host_module (static) authz_groupfile_module (static) authz_user_module (static) authz_default_module (static) auth_basic_module (static) file_cache_module (static) cache_module (static) disk_cache_module (static) mem_cache_module (static) include_module (static) filter_module (static) deflate_module (static) log_config_module (static) logio_module (static) env_module (static) expires_module (static) headers_module (static) unique_id_module (static) setenvif_module (static) version_module (static) proxy_module (static) proxy_connect_module (static) proxy_ftp_module (static) proxy_http_module (static) proxy_scgi_module (static) proxy_ajp_module (static) proxy_balancer_module (static) ssl_module (static) mpm_worker_module (static) http_module (static) mime_module (static) dav_module (static) status_module (static) autoindex_module (static) asis_module (static) info_module (static) suexec_module (static) cgid_module (static) dav_fs_module (static) negotiation_module (static) dir_module (static) actions_module (static) speling_module (static) userdir_module (static) alias_module (static) rewrite_module (static) so_module (static) bwlimited_module (shared) suphp_module (shared) security2_module (shared) Syntax OK Compiled in modules: core.c mod_authn_file.c mod_authn_default.c mod_authz_host.c mod_authz_groupfile.c mod_authz_user.c mod_authz_default.c mod_auth_basic.c mod_file_cache.c mod_cache.c mod_disk_cache.c mod_mem_cache.c mod_include.c mod_filter.c mod_deflate.c mod_log_config.c mod_logio.c mod_env.c mod_expires.c mod_headers.c mod_unique_id.c mod_setenvif.c mod_version.c mod_proxy.c mod_proxy_connect.c mod_proxy_ftp.c mod_proxy_http.c mod_proxy_scgi.c mod_proxy_ajp.c mod_proxy_balancer.c mod_ssl.c worker.c http_core.c mod_mime.c mod_dav.c mod_status.c mod_autoindex.c mod_asis.c mod_info.c mod_suexec.c mod_cgid.c mod_dav_fs.c mod_negotiation.c mod_dir.c mod_actions.c mod_speling.c mod_userdir.c mod_alias.c mod_rewrite.c mod_so.c ------------------------------- From /usr/local/apache/conf/httpd.conf ------------------------------- Timeout 300 KeepAlive Off MaxKeepAliveRequests 100 KeepAliveTimeout 5 StartServers 5 MinSpareServers 5 MaxSpareServers 10 MinSpareServers 5 MaxSpareServers 10 ServerLimit 495 MaxClients 480 MaxRequestsPerChild 10000 ---------------------------- mysqlreport output ---------------------------- MySQL 5.6.15-log uptime 7 7:10:58 Fri Feb 14 20:58:26 2014 __ Key _________________________________________________________________ Buffer used 116.78M of 2.44G %Used: 4.67 Current 492.55M %Usage: 19.70 Write hit 99.72% Read hit 99.99% __ Questions ___________________________________________________________ Total 180.89M 286.8/s QC Hits 117.60M 186.5/s %Total: 65.01 DMS 47.08M 74.7/s 26.03 Com_ 20.79M 33.0/s 11.49 -Unknown 10.23M 16.2/s 5.65 COM_QUIT 5.66M 9.0/s 3.13 Slow 100 ms 1.66M 2.6/s 0.92 %DMS: 3.52 Log: DMS 47.08M 74.7/s 26.03 SELECT 34.98M 55.5/s 19.34 74.30 UPDATE 5.72M 9.1/s 3.16 12.15 INSERT 3.05M 4.8/s 1.68 6.47 DELETE 2.56M 4.1/s 1.42 5.44 REPLACE 769.27k 1.2/s 0.43 1.63 Com_ 20.79M 33.0/s 11.49 set_option 5.85M 9.3/s 3.23 change_db 4.36M 6.9/s 2.41 stmt_prepar 2.54M 4.0/s 1.40 __ Rows ________________________________________________________________ Rows 75.96G 120.4k/s Using idx 47.53G 75.4k/s %Index: 62.57 Rows/question 419.93 __ SELECT and Sort _____________________________________________________ Scan 9.96M 15.8/s %SELECT: 28.48 Range 954.10k 1.5/s 2.73 Full join 368.01k 0.6/s 1.05 Range check 1.58k 0.0/s 0.00 Full rng join 3.92k 0.0/s 0.01 Sort scan 5.13M 8.1/s Sort range 3.44M 5.5/s Sort mrg pass 197.20k 0.3/s __ Query Cache _________________________________________________________ Memory usage 120.07M of 150.00M %Usage: 80.05 Block Fragmnt 9.96% Hits 117.60M 186.5/s Inserts 31.27M 49.6/s Insrt:Prune 3.74:1 36.3/s Hit:Insert 3.76:1 __ Table Locks _________________________________________________________ Waited 112.80k 0.2/s %Total: 0.22 Immediate 51.05M 81.0/s __ Tables ______________________________________________________________ Open 8000 of 8000 %Cache: 100.00 Opened 115.45M 183.1/s __ Connections _________________________________________________________ Max used 91 of 200 %Max: 45.50 Total 5.66M 9.0/s __ Created Temp ________________________________________________________ Disk table 1.19M 1.9/s %Disk: 43.39 Table 2.74M 4.3/s Size: 50.0M File 114.19k 0.2/s __ Threads _____________________________________________________________ Running 3 of 5 Created 91 0.0/s Slow 0 0/s Cached 86 of 128 %Hit: 100 __ Aborted _____________________________________________________________ Clients 2.21k 0.0/s Connects 1.08M 1.7/s __ Bytes _______________________________________________________________ Sent 1.58T 2.5M/s Received 26.03G 41.3k/s __ InnoDB Buffer Pool __________________________________________________ Usage 2.88G of 3.00G %Usage: 95.83 Read hit 99.99% Pages Free 8.19k %Total: 4.17 Data 187.19k 95.21 %Drty: 0.04 Misc 1228 0.62 Latched 0 0.00 Reads 1.67G 2.6k/s From disk 130.19k 0.2/s %Disk: 0.01 Ahead Rnd 0 0/s Writes 48.29M 76.6/s Wait Free 0 0/s %Wait: 0.00 Flushes 3.67M 5.8/s __ InnoDB Lock _________________________________________________________ Waits 42769 0.1/s Current 0 Time acquiring Total 13431946 ms Average 314 ms Max 17304 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 183.94k 0.3/s Writes 6.44M 10.2/s fsync 3.41M 5.4/s Pending Reads 0 Writes 0 fsync 1 Pages Created 107.50k 0.2/s Read 147.53k 0.2/s Written 3.67M 5.8/s Rows Deleted 500.90k 0.8/s Inserted 2.66M 4.2/s Read 2.08G 3.3k/s Updated 12.47M 19.8/s ---------------------------- Total Server MyISAM and InnoDB Size ---------------------------- Warning: Using a password on the command line interface can be insecure. *************************** 1. row *************************** innodb_tablespace_utilization_perc: 100 total_size_gb: 11.338629804551601 index_size_gb: 2.6332558505237103 data_size_gb: 8.705373954027891 innodb_total_size_gb: 2.874725341796875 innodb_data_size_gb: 2.228485107421875 innodb_index_size_gb: 0.646240234375 myisam_total_size_gb: 8.461939375847578 myisam_data_size_gb: 6.476059477776289 myisam_index_size_gb: 1.985879898071289 perc_index: 23.223757100 perc_data: 76.776242800 innodb_perc_index: 22.480068700 innodb_perc_data: 77.519931200 myisam_perc_index: 23.468377700 myisam_perc_data: 76.531622200 innodb_perc_total_index: 24.541490400 innodb_perc_total_data: 25.598958900 myisam_perc_total_index: 75.415379600 myisam_perc_total_data: 74.391513900 ---------------------------- MySQL settings ---------------------------- [mysqld] innodb_stats_on_metadata=0 long_query_time=0.1 join_buffer_size=2M table_definition_cache=6000 query_cache_limit=1M key_buffer_size=2500M myisam_use_mmap=1 innodb_old_blocks_time=1000 query_cache_type=1 tmp_table_size=50M thread_cache_size=128 max_user_connections=75 innodb_log_buffer_size=32M innodb_concurrency_tickets=5000 slow_query_log_file=mysql-slow.log max_allowed_packet=32M max_heap_table_size=50M max_connections=200 table_open_cache=8000 query_cache_size=150M innodb_open_files=2000 slow_query_log=1 innodb_file_per_table=1 open_files_limit=50000 innodb_autoextend_increment=64M sort_buffer_size=256K innodb_buffer_pool_size=3G default-storage-engine=MyISAM --- Warning: Using a password on the command line interface can be insecure. +--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 90 | | basedir | /usr | | big_tables | OFF | | bind_address | * | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | NO_CHAIN | | concurrent_insert | AUTO | | connect_timeout | 10 | | core_file | OFF | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_storage_engine | MyISAM | | default_tmp_storage_engine | InnoDB | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | disconnect_on_expired_password | ON | | div_precision_increment | 4 | | end_markers_in_json | OFF | | enforce_gtid_consistency | OFF | | eq_range_index_dive_limit | 10 | | event_scheduler | OFF | | expire_logs_days | 0 | | explicit_defaults_for_timestamp | OFF | | flush | OFF | | flush_time | 0 | | foreign_key_checks | ON | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | general_log | OFF | | general_log_file | /var/lib/mysql/*****.log | | group_concat_max_len | 1024 | | gtid_executed | | | gtid_mode | OFF | | gtid_owned | | | gtid_purged | | | have_compress | YES | | have_crypt | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_openssl | DISABLED | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_symlink | YES | | host_cache_size | 328 | | hostname | yourserverhostname | | ignore_builtin_innodb | OFF | | ignore_db_dirs | | | init_connect | | | init_file | | | init_slave | | | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_max_sleep_delay | 150000 | | innodb_additional_mem_pool_size | 8388608 | | innodb_api_bk_commit_interval | 5 | | innodb_api_disable_rowlock | OFF | | innodb_api_enable_binlog | OFF | | innodb_api_enable_mdl | OFF | | innodb_api_trx_level | 0 | | innodb_autoextend_increment | 1000 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 3221225472 | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | all | | innodb_checksum_algorithm | innodb | | innodb_checksums | ON | | innodb_cmp_per_index_enabled | OFF | | innodb_commit_concurrency | 0 | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_concurrency_tickets | 5000 | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | | innodb_disable_sort_file_cache | OFF | | innodb_doublewrite | ON | | innodb_fast_shutdown | 1 | | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | ON | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_flush_neighbors | 1 | | innodb_flushing_avg_loops | 30 | | innodb_force_load_corrupted | OFF | | innodb_force_recovery | 0 | | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | | innodb_io_capacity | 200 | | innodb_io_capacity_max | 2000 | | innodb_large_prefix | OFF | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 33554432 | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_lru_scan_depth | 1024 | | innodb_max_dirty_pages_pct | 75 | | innodb_max_dirty_pages_pct_lwm | 0 | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_mirrored_log_groups | 1 | | innodb_monitor_disable | | | innodb_monitor_enable | | | innodb_monitor_reset | | | innodb_monitor_reset_all | | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_online_alter_log_max_size | 134217728 | | innodb_open_files | 2000 | | innodb_optimize_fulltext_only | OFF | | innodb_page_size | 16384 | | innodb_print_all_deadlocks | OFF | | innodb_purge_batch_size | 300 | | innodb_purge_threads | 1 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_read_only | OFF | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_rollback_segments | 128 | | innodb_sort_buffer_size | 1048576 | | innodb_spin_wait_delay | 6 | | innodb_stats_auto_recalc | ON | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_array_size | 1 | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 0 | | innodb_thread_sleep_delay | 10000 | | innodb_undo_directory | . | | innodb_undo_logs | 128 | | innodb_undo_tablespaces | 0 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 5.6.15 | | innodb_write_io_threads | 4 | | interactive_timeout | 28800 | | join_buffer_size | 2097152 | | keep_files_on_create | OFF | | key_buffer_size | 2621440000 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | large_files_support | ON | | large_page_size | 0 | | large_pages | OFF | | lc_messages | en_US | | lc_messages_dir | /usr/share/mysql/ | | lc_time_names | en_US | | license | GPL | | local_infile | ON | | lock_wait_timeout | 31536000 | | locked_in_memory | OFF | | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | /var/lib/mysql/yourserverhostname.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | log_throttle_queries_not_using_indexes | 0 | | log_warnings | 1 | | long_query_time | 0.100000 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | master_info_repository | FILE | | master_verify_checksum | OFF | | max_allowed_packet | 33554432 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_connect_errors | 100 | | max_connections | 200 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 52428800 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 | | max_length_for_sort_data | 1024 | | max_prepared_stmt_count | 16382 | | max_relay_log_size | 0 | | max_seeks_for_key | 18446744073709551615 | | max_sort_length | 1024 | | max_sp_recursion_depth | 0 | | max_tmp_tables | 32 | | max_user_connections | 75 | | max_write_lock_count | 18446744073709551615 | | metadata_locks_cache_size | 1024 | | metadata_locks_hash_instances | 8 | | min_examined_row_limit | 0 | | multi_range_count | 256 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | myisam_stats_method | nulls_unequal | | myisam_use_mmap | ON | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old | OFF | | old_alter_table | OFF | | old_passwords | 0 | | open_files_limit | 50000 | | optimizer_prune_level | 1 | | optimizer_search_depth | 62 | | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on | | optimizer_trace | enabled=off,one_line=off | | optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on | | optimizer_trace_limit | 1 | | optimizer_trace_max_mem_size | 16384 | | optimizer_trace_offset | -1 | | performance_schema | ON | | performance_schema_accounts_size | 100 | | performance_schema_digests_size | 10000 | | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_hosts_size | 100 | | performance_schema_max_cond_classes | 80 | | performance_schema_max_cond_instances | 12900 | | performance_schema_max_file_classes | 50 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | 76924 | | performance_schema_max_mutex_classes | 200 | | performance_schema_max_mutex_instances | 62200 | | performance_schema_max_rwlock_classes | 40 | | performance_schema_max_rwlock_instances | 36800 | | performance_schema_max_socket_classes | 10 | | performance_schema_max_socket_instances | 420 | | performance_schema_max_stage_classes | 150 | | performance_schema_max_statement_classes | 168 | | performance_schema_max_table_handles | 16000 | | performance_schema_max_table_instances | 12500 | | performance_schema_max_thread_classes | 50 | | performance_schema_max_thread_instances | 500 | | performance_schema_session_connect_attrs_size | 512 | | performance_schema_setup_actors_size | 100 | | performance_schema_setup_objects_size | 100 | | performance_schema_users_size | 100 | | pid_file | /var/lib/mysql/yourserverhostname.pid | | plugin_dir | /usr/lib64/mysql/plugin/ | | port | 3306 | | preload_buffer_size | 32768 | | profiling | OFF | | profiling_history_size | 15 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 157286400 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log | | | relay_log_basename | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_info_repository | FILE | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | rpl_stop_slave_timeout | 31536000 | | secure_auth | ON | | secure_file_priv | | | server_id | 0 | | server_id_bits | 32 | | server_uuid | 929831b3-8ed0-11e3-a621-c78f76a2c701 | | skip_external_locking | ON | | skip_name_resolve | OFF | | skip_networking | OFF | | skip_show_database | OFF | | slave_allow_batching | OFF | | slave_checkpoint_group | 512 | | slave_checkpoint_period | 300 | | slave_compressed_protocol | OFF | | slave_exec_mode | STRICT | | slave_load_tmpdir | /tmp | | slave_max_allowed_packet | 1073741824 | | slave_net_timeout | 3600 | | slave_parallel_workers | 0 | | slave_pending_jobs_size_max | 16777216 | | slave_rows_search_algorithms | TABLE_SCAN,INDEX_SCAN | | slave_skip_errors | OFF | | slave_sql_verify_checksum | ON | | slave_transaction_retries | 10 | | slave_type_conversions | | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | mysql-slow.log | | socket | /var/lib/mysql/mysql.sock | | sort_buffer_size | 262144 | | sql_auto_is_null | OFF | | sql_big_selects | ON | | sql_buffer_result | OFF | | sql_log_bin | ON | | sql_log_off | OFF | | sql_mode | NO_ENGINE_SUBSTITUTION | | sql_notes | ON | | sql_quote_show_create | ON | | sql_safe_updates | OFF | | sql_select_limit | 18446744073709551615 | | sql_slave_skip_counter | 0 | | sql_warnings | OFF | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | | | storage_engine | MyISAM | | stored_program_cache | 256 | | sync_binlog | 0 | | sync_frm | ON | | sync_master_info | 10000 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | | system_time_zone | CET | | table_definition_cache | 6000 | | table_open_cache | 8000 | | table_open_cache_instances | 1 | | thread_cache_size | 128 | | thread_concurrency | 10 | | thread_handling | one-thread-per-connection | | thread_stack | 262144 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | timed_mutexes | OFF | | tmp_table_size | 52428800 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | tx_read_only | OFF | | unique_checks | ON | | updatable_views_with_limit | YES | | version | 5.6.15-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | | wait_timeout | 28800 | +--------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ +-----------------------------------------------+---------------+ | Variable_name | Value | +-----------------------------------------------+---------------+ | Aborted_clients | 2207 | | Aborted_connects | 1076373 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | | Bytes_received | 26034576505 | | Bytes_sent | 1578648199783 | | Com_admin_commands | 142305 | | Com_assign_to_keycache | 0 | | Com_alter_db | 5 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 387 | | Com_alter_tablespace | 0 | | Com_alter_user | 0 | | Com_analyze | 0 | | Com_begin | 2787 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 4360448 | | Com_change_master | 0 | | Com_check | 675 | | Com_checksum | 0 | | Com_commit | 6311 | | Com_create_db | 32 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 11548 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 2 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 2562579 | | Com_delete_multi | 171 | | Com_do | 0 | | Com_drop_db | 25 | | Com_drop_event | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_procedure | 0 | | Com_drop_server | 0 | | Com_drop_table | 499 | | Com_drop_trigger | 0 | | Com_drop_user | 13 | | Com_drop_view | 0 | | Com_empty_query | 2 | | Com_execute_sql | 0 | | Com_flush | 106 | | Com_get_diagnostics | 0 | | Com_grant | 1795 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 3034630 | | Com_insert_select | 13155 | | Com_install_plugin | 0 | | Com_kill | 2111 | | Com_load | 32 | | Com_lock_tables | 9713 | | Com_optimize | 777 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_release_savepoint | 80088 | | Com_rename_table | 1 | | Com_rename_user | 0 | | Com_repair | 350 | | Com_replace | 769459 | | Com_replace_select | 15 | | Com_reset | 0 | | Com_resignal | 0 | | Com_revoke | 13 | | Com_revoke_all | 2 | | Com_rollback | 21 | | Com_rollback_to_savepoint | 0 | | Com_savepoint | 80089 | | Com_select | 35032052 | | Com_set_option | 5848371 | | Com_signal | 0 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 636 | | Com_show_charsets | 71 | | Com_show_collations | 1 | | Com_show_create_db | 2813 | | Com_show_create_event | 0 | | Com_show_create_func | 0 | | Com_show_create_proc | 9 | | Com_show_create_table | 183234 | | Com_show_create_trigger | 39 | | Com_show_databases | 825 | | Com_show_engine_logs | 0 | | Com_show_engine_mutex | 0 | | Com_show_engine_status | 0 | | Com_show_events | 343 | | Com_show_errors | 0 | | Com_show_fields | 333626 | | Com_show_function_code | 0 | | Com_show_function_status | 3940 | | Com_show_grants | 21190 | | Com_show_keys | 2335 | | Com_show_master_status | 259 | | Com_show_open_tables | 0 | | Com_show_plugins | 66 | | Com_show_privileges | 0 | | Com_show_procedure_code | 0 | | Com_show_procedure_status | 3940 | | Com_show_processlist | 2105 | | Com_show_profile | 0 | | Com_show_profiles | 0 | | Com_show_relaylog_events | 0 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 259 | | Com_show_status | 18835 | | Com_show_storage_engines | 71 | | Com_show_table_status | 329531 | | Com_show_tables | 140525 | | Com_show_triggers | 158862 | | Com_show_variables | 15746 | | Com_show_warnings | 361 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_close | 2479735 | | Com_stmt_execute | 2535285 | | Com_stmt_fetch | 0 | | Com_stmt_prepare | 2535285 | | Com_stmt_reprepare | 0 | | Com_stmt_reset | 1457491 | | Com_stmt_send_long_data | 0 | | Com_truncate | 1550 | | Com_uninstall_plugin | 0 | | Com_unlock_tables | 10265 | | Com_update | 5671550 | | Com_update_multi | 51769 | | Com_xa_commit | 0 | | Com_xa_end | 0 | | Com_xa_prepare | 0 | | Com_xa_recover | 0 | | Com_xa_rollback | 0 | | Com_xa_start | 0 | | Compression | OFF | | Connection_errors_accept | 0 | | Connection_errors_internal | 0 | | Connection_errors_max_connections | 0 | | Connection_errors_peer_address | 4 | | Connection_errors_select | 0 | | Connection_errors_tcpwrap | 0 | | Connections | 5658302 | | Created_tmp_disk_tables | 1189126 | | Created_tmp_files | 114203 | | Created_tmp_tables | 2740493 | | Delayed_errors | 31 | | Delayed_insert_threads | 2 | | Delayed_writes | 22990 | | Flush_commands | 1 | | Handler_commit | 8895268 | | Handler_delete | 801074 | | Handler_discover | 0 | | Handler_external_lock | 102518210 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 4591523 | | Handler_read_key | 3365452171 | | Handler_read_last | 236044 | | Handler_read_next | 44050950505 | | Handler_read_prev | 116079413 | | Handler_read_rnd | 1170704033 | | Handler_read_rnd_next | 25946711682 | | Handler_rollback | 660 | | Handler_savepoint | 79707 | | Handler_savepoint_rollback | 0 | | Handler_update | 1289822968 | | Handler_write | 1827281856 | | Innodb_buffer_pool_dump_status | not started | | Innodb_buffer_pool_load_status | not started | | Innodb_buffer_pool_pages_data | 187188 | | Innodb_buffer_pool_bytes_data | 3066888192 | | Innodb_buffer_pool_pages_dirty | 183 | | Innodb_buffer_pool_bytes_dirty | 2998272 | | Innodb_buffer_pool_pages_flushed | 3665975 | | Innodb_buffer_pool_pages_free | 8192 | | Innodb_buffer_pool_pages_misc | 1228 | | Innodb_buffer_pool_pages_total | 196608 | | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 16687 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 1667348060 | | Innodb_buffer_pool_reads | 130190 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 48293150 | | Innodb_data_fsyncs | 3410017 | | Innodb_data_pending_fsyncs | 1 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 2421493760 | | Innodb_data_reads | 183941 | | Innodb_data_writes | 6438496 | | Innodb_data_written | 124661967360 | | Innodb_dblwr_pages_written | 3665975 | | Innodb_dblwr_writes | 410408 | | Innodb_have_atomic_builtins | ON | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 8619620 | | Innodb_log_writes | 2223808 | | Innodb_os_log_fsyncs | 2309796 | | Innodb_os_log_pending_fsyncs | 1 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 4498861568 | | Innodb_page_size | 16384 | | Innodb_pages_created | 107502 | | Innodb_pages_read | 147534 | | Innodb_pages_written | 3665975 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 13431946 | | Innodb_row_lock_time_avg | 314 | | Innodb_row_lock_time_max | 17304 | | Innodb_row_lock_waits | 42769 | | Innodb_rows_deleted | 500904 | | Innodb_rows_inserted | 2658703 | | Innodb_rows_read | 2084029472 | | Innodb_rows_updated | 12467196 | | Innodb_num_open_files | 2000 | | Innodb_truncated_status_writes | 0 | | Innodb_available_undo_logs | 128 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 2056074 | | Key_blocks_used | 119584 | | Key_read_requests | 9527272524 | | Key_reads | 707736 | | Key_write_requests | 1117274977 | | Key_writes | 3131405 | | Last_query_cost | 0.000000 | | Last_query_partial_plans | 0 | | Max_used_connections | 91 | | Not_flushed_delayed_rows | 0 | | Open_files | 9995 | | Open_streams | 0 | | Open_table_definitions | 7988 | | Open_tables | 8000 | | Opened_files | 110121991 | | Opened_table_definitions | 104405621 | | Opened_tables | 115539825 | | Performance_schema_accounts_lost | 1251553 | | Performance_schema_cond_classes_lost | 0 | | Performance_schema_cond_instances_lost | 0 | | Performance_schema_digest_lost | 0 | | Performance_schema_file_classes_lost | 0 | | Performance_schema_file_handles_lost | 0 | | Performance_schema_file_instances_lost | 5353784 | | Performance_schema_hosts_lost | 0 | | Performance_schema_locker_lost | 0 | | Performance_schema_mutex_classes_lost | 0 | | Performance_schema_mutex_instances_lost | 0 | | Performance_schema_rwlock_classes_lost | 0 | | Performance_schema_rwlock_instances_lost | 0 | | Performance_schema_session_connect_attrs_lost | 0 | | Performance_schema_socket_classes_lost | 0 | | Performance_schema_socket_instances_lost | 0 | | Performance_schema_stage_classes_lost | 0 | | Performance_schema_statement_classes_lost | 0 | | Performance_schema_table_handles_lost | 0 | | Performance_schema_table_instances_lost | 82361297 | | Performance_schema_thread_classes_lost | 0 | | Performance_schema_thread_instances_lost | 0 | | Performance_schema_users_lost | 1251534 | | Prepared_stmt_count | 0 | | Qcache_free_blocks | 9095 | | Qcache_free_memory | 32051688 | | Qcache_hits | 117614741 | | Qcache_inserts | 31328139 | | Qcache_lowmem_prunes | 8369962 | | Qcache_not_cached | 2261802 | | Qcache_queries_in_cache | 37929 | | Qcache_total_blocks | 89272 | | Queries | 187586795 | | Questions | 180967771 | | Select_full_join | 368117 | | Select_full_range_join | 3924 | | Select_range | 954288 | | Select_range_check | 1579 | | Select_scan | 9963643 | | Slave_heartbeat_period | | | Slave_last_heartbeat | | | Slave_open_temp_tables | 0 | | Slave_received_heartbeats | | | Slave_retried_transactions | | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 1656746 | | Sort_merge_passes | 197216 | | Sort_range | 3439386 | | Sort_rows | 1321394765 | | Sort_scan | 5128463 | | Ssl_accept_renegotiates | 0 | | Ssl_accepts | 0 | | Ssl_callback_cache_hits | 0 | | Ssl_cipher | | | Ssl_cipher_list | | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_ctx_verify_depth | 0 | | Ssl_ctx_verify_mode | 0 | | Ssl_default_timeout | 0 | | Ssl_finished_accepts | 0 | | Ssl_finished_connects | 0 | | Ssl_server_not_after | | | Ssl_server_not_before | | | Ssl_session_cache_hits | 0 | | Ssl_session_cache_misses | 0 | | Ssl_session_cache_mode | NONE | | Ssl_session_cache_overflows | 0 | | Ssl_session_cache_size | 0 | | Ssl_session_cache_timeouts | 0 | | Ssl_sessions_reused | 0 | | Ssl_used_session_cache_entries | 0 | | Ssl_verify_depth | 0 | | Ssl_verify_mode | 0 | | Ssl_version | | | Table_locks_immediate | 51110833 | | Table_locks_waited | 112808 | | Table_open_cache_hits | 55821314 | | Table_open_cache_misses | 589831 | | Table_open_cache_overflows | 576960 | | Tc_log_max_pages_used | 0 | | Tc_log_page_size | 0 | | Tc_log_page_waits | 0 | | Threads_cached | 83 | | Threads_connected | 8 | | Threads_created | 91 | | Threads_running | 6 | | Uptime | 630787 | | Uptime_since_flush_status | 630787 | +-----------------------------------------------+---------------+ +---------+------------------+-----------+--------------------+----------------+------+----------------------+----------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------+------------------+-----------+--------------------+----------------+------+----------------------+----------------------------------------------------------------------------+ | 4492633 | eximstats | localhost | eximstats | Sleep | 29 | | | | 5538051 | DELAYED | localhost | eximstats | Delayed insert | 29 | Waiting for INSERT | | | 5614361 | leechprotect | localhost | leechprotect | Sleep | 3524 | | | | 5620070 | DELAYED | localhost | eximstats | Delayed insert | 30 | Waiting for INSERT | | | 5657853 | root | localhost | information_schema | Query | 29 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' | | 5658276 | user7_table2 | localhost | user7_mhsc | Query | 0 | statistics | SELECT im_id FROM nuked_comment WHERE im_id = '1650' AND module = 'news' | | 5658290 | user13_roland | localhost | user13_seconde | Query | 1 | query end | UPDATE superparrain SET win = win +0.2 WHERE pseudo='xx' | | 5658296 | user5_table1 | localhost | user5_user5o | Query | 1 | query end | UPDATE superparrain SET wintotal = wintotal + '6' WHERE pseudo = 'xxx' | | 5658299 | user5_table1 | localhost | user5_user5o | Query | 0 | query end | UPDATE superparrain SET wintotal = wintotal + 2 WHERE pseudo = 'xxx' | | 5658301 | root | localhost | | Query | 0 | init | show processlist | +---------+------------------+-----------+--------------------+----------------+------+----------------------+----------------------------------------------------------------------------+ ---------------------------- MySQL Error Log ---------------------------- 2014-02-13 18:27:18 31063 [Warning] Found invalid password for user: ''; Ignoring user 2014-02-13 18:27:18 31063 [Warning] Found invalid password for user: ''; Ignoring user 2014-02-13 18:27:18 31063 [Warning] Found invalid password for user: ''; Ignoring user 2014-02-14 02:04:57 31063 [Warning] IP address 'xx.xx.xx.xx' could not be resolved: Name or service not known Report Complete: Fri Feb 14 21:00:35 CET 2014 ----------------------------
    ./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt /http://pastebin.com/UsS2793M (sorry, else message is too long for submit it) Here you can find statistics after upgrading to MySQL 5.6 : /https://mega.co.nz/#!aRxDVbqB!BYJHa3x81aBo_Z18UTI94pLWy6cPAlB8NyHp7p_vL3o Thank you again! !
    0
  • thinkbot
    I see you got multiple databases with the same structure user13_roland, user5_table1 You should add there (in all similar database structures, to all users databases) index for 'pseudo' and 'email' columns of tables membres and superparrain and change table_open_cache in my.cnf to 15000 and add there right after table_open_cache value table_open_cache_instances=16 so table_open_cache = 15000 table_open_cache_instances=16 table_definition_cache = 6000 and after [mysqld] add tmpdir=/dev/shm open_files_limit = 65535 so [mysqld] tmpdir=/dev/shm open_files_limit = 65535 and the rest then remove old slow log rm -rf /var/lib/mysql/mysql-slow.log and restart Also do you happen to have backup turned on every day ? couse I see high I/O usage starting somewhere at 2AM
    0
  • ougogo
    Ok all is done (but probably not all index because I don't know all clients who have this structure ...). For backups, until now I was doing every day. Then, there is some time, only one every two days because the place was beginning to fail. The fact is that the backups are increasingly taking more and more time! Sometimes it may take 24 hours to save more than 800 accounts for 700Go. I don't know why ... Before back up 400-500GB were taking 4-6 hours. I think I have to wait a few days to see the effects of changes. Do you think the current configuration, you have so kindly customized me, will be enough? Or do you have some tips aside continue to monitor the logs? Best regards,
    0
  • thinkbot
    Current changes should be enough For the backups part, you should check what is taking so much space, some stuff can be excluded like logs etc. and some doesn't have to backed up daily
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post