ougogo

Well-Known Member
Dec 28, 2012
50
1
8
cPanel Access Level
Root Administrator
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...)
Code:
[mysqld]
innodb_file_per_table=1
open_files_limit=50000

./mysqltuner.pl
Code:
[18:44:41] [email protected]* [~]# ./mysqltuner.pl 

 >>  MySQLTuner 1.2.1 mod - Major Hayden <[email protected]>
 >>  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**[email protected]**.**.**.**' has no password set.
[!!] User 'spa**[email protected]**.**.**.**' has no password set.
[!!] User 'spa**[email protected]***.fr' has no password set.
[!!] User 'spa**[email protected]***.fr' has no password set.
[!!] User 'spa**[email protected]' 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
Code:
[19:07:56] [email protected]*** [~/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 <defunct>                                                                                                                   
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 <defunct>                                                                                                                   
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 <defunct>                                                                                                                   
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 <defunct>                                                                                                                   
26696 user2  24   4     0    0    0 Z  0.0  0.0   0:00.11 php <defunct>                                                                                                                   
26698 user2  24   4     0    0    0 Z  0.0  0.0   0:00.14 php <defunct>                                                                                                                   
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
#
#<domain>      <type>  <item>         <value>
#

#*               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

</IfModule>
StartServers 5
<IfModule prefork.c>
MinSpareServers 5
MaxSpareServers 10
</IfModule>
MinSpareServers 5
MaxSpareServers 10
</IfModule>
ServerLimit 500
MaxClients 500
MaxRequestsPerChild 10000
</IfModule>


----------------------------
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 !
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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
 

ougogo

Well-Known Member
Dec 28, 2012
50
1
8
cPanel Access Level
Root Administrator
Hi thinkbot,

I will try it righ now !
But at first glance, CPU usage and memory does not change.

whm-3.png

I'll wait a few hours.

Thanks.


NB : I may be worrying for nothing. I have a lot of sites under Wordpress, Prestashop, etc..
 
Last edited:

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
"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
 

ougogo

Well-Known Member
Dec 28, 2012
50
1
8
cPanel Access Level
Root Administrator
So, here again my results.


./mysqltuner.pl
Code:
[06:41:27] [email protected]**** [~]# ./mysqltuner.pl  

 >>  MySQLTuner 1.2.1 mod - Major Hayden <[email protected]>
 >>  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
Code:
[06:43:42] [email protected]**** [~/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 <defunct>                                                                                                                   
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
#
#<domain>      <type>  <item>         <value>
#

#*               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

</IfModule>
StartServers 5
<IfModule prefork.c>
MinSpareServers 5
MaxSpareServers 10
</IfModule>
MinSpareServers 5
MaxSpareServers 10
</IfModule>
ServerLimit 500
MaxClients 500
MaxRequestsPerChild 10000
</IfModule>


----------------------------
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
Code:
# 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! !
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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
 

ougogo

Well-Known Member
Dec 28, 2012
50
1
8
cPanel Access Level
Root Administrator
Ok, I would put MySQL update after checking the changelog.


Here the 5 top queries
Code:
# 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 ='[email protected]'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select  barre_date = now(), argent = argent +2, tbpointbarreperiode = tbpointbarreperiode +2 from membres where  email ='[email protected]'\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 !
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
1. user1_seconde
select barre_date = now(), argent = argent +2, tbpointbarreperiode = tbpointbarreperiode +2 from membres where email ='[email protected]'

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
 

ougogo

Well-Known Member
Dec 28, 2012
50
1
8
cPanel Access Level
Root Administrator
Ok, I created index for the fields of the tables of users 1, 2, 3 and 4.


Here the details of query 7
Code:
# 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 !
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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
 
Last edited:

cPanelMichael

Technical Support Community Manager
Staff member
Apr 11, 2011
47,911
2,233
363
cPanel Access Level
DataCenter Provider
Twitter
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.
 

ougogo

Well-Known Member
Dec 28, 2012
50
1
8
cPanel Access Level
Root Administrator
Hi,

Sorry for late.
Here you can find results after MySQL 5.6 upgrade.


./mysqltuner.pl
Code:
[20:40:52] [email protected]***** [~]# ./mysqltuner.pl  

 >>  MySQLTuner 1.2.1 mod - Major Hayden <[email protected]>
 >>  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
Code:
[20:58:13] [email protected]***** [~/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 <defunct>                                                                                                                   
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 <defunct>                                                                                                                   
31724 user3  	24   4     0    0    0 Z  2.0  0.0   0:00.01 php <defunct>                                                                                                                   
31726 user4  	24   4     0    0    0 Z  2.0  0.0   0:00.01 php <defunct>                                                                                                                   
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 <defunct>                                                                                                            
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
#
#<domain>      <type>  <item>         <value>
#

#*               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

</IfModule>
StartServers 5
<IfModule prefork.c>
MinSpareServers 5
MaxSpareServers 10
</IfModule>
MinSpareServers 5
MaxSpareServers 10
</IfModule>
ServerLimit 495
MaxClients 480
MaxRequestsPerChild 10000
</IfModule>


----------------------------
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! !
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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
 

ougogo

Well-Known Member
Dec 28, 2012
50
1
8
cPanel Access Level
Root Administrator
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,
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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