Table cache hit rate: 0%

A

AlexCl

Active Member
Feb 19, 2013
42
0
6
cPanel Access Level
Root Administrator
Hello,

I've recently installed a new server with these specs:

CPU Intel(R) Xeon(R) CPU X3440 @ 2.53GHz
16 GB RAM
2x1500 GB HDD's in Software RAID 1
1x60 GB SSD drive for MySQL
Cloudlinux + Cpanel

Hosting around 80 accounts, out of which probably 30-40 are using mysql ( they're based on Joomla or Wordpress ). The websites don't get a lot of traffic. As an example, last month the server had around 250 GB of traffic usage.

I've moved the mysql datadir to the SSD drive and replaced MySQL with MariaDB.

I've used Percona's my.cnf tool which worked out ok, apart for some fine tuning which I did with Mysqltuner and Tuning Primer .

This is my current my.cnf:
Code: 
[mysql]

# CLIENT #
port                           = 3306
socket                         = /mnt/disk1/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /mnt/disk1/mysql/mysql.sock
pid_file                       = /mnt/disk1/mysql/mysql.pid

# MyISAM #
key_buffer_size                = 512M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000

# DATA STORAGE #
datadir                        = /mnt/disk1/mysql

# BINARY LOGGING #
log_bin                        = /mnt/disk1/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 3G
max_heap_table_size            = 3G
query_cache_type               = 1
query_cache_size               = 384M
query_cache_limit              = 8M
max_connections                = 150
thread_cache_size              = 64M
open_files_limit               = 65535
table_definition_cache         = 250240
table_open_cache               = 250240
join_buffer_size               = 3M

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 10G
concurrent_insert              = ALWAYS
low_priority_updates           = 1
thread_concurrency             = 8


# LOGGING #
log_error                      = /mnt/disk1/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /mnt/disk1/mysql/mysql-slow.log
The problem is that Mysqltuner reported 0% table cache hit rate. Below you can see the output from mysqltuner.

Code: 
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.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.30-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3720)
[--] Data in InnoDB tables: 359M (Tables: 9428)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 2M (Tables: 291)
[!!] Total fragmented tables: 73

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 9h 9m 25s (17M q [37.631 qps], 192K conn, TX: 33B, RX: 1B)
[--] Reads / Writes: 84% / 16%
[--] Total buffers: 13.9G global + 5.7M per thread (150 max threads)
[!!] Maximum possible memory usage: 14.7G (94% of installed RAM)
[OK] Slow queries: 2% (377K/17M)
[OK] Highest usage of available connections: 9% (14/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/185.6M
[OK] Key buffer hit rate: 99.8% (40M cached / 67K reads)
[OK] Query cache efficiency: 62.1% (5M cached / 8M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (101 temp sorts / 268K sorts)
[!!] Joins performed without indexes: 48433
[!!] Temporary tables created on disk: 32% (542K on disk / 1M total)
[OK] Thread cache hit rate: 99% (14 created / 192K connections)
[!!] Table cache hit rate: 0% (26K open / 20M opened)
[OK] Open file limit used: 2% (11K/500K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 359.5M/10.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 3.0M, or always use indexes with joins)
    table_cache (> 250240)
Does anyone have any idea on how to fix this ? Besides that, should I be looking at anything else?

Thank you.
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Assuming you go

250240/100 = 2502,4
if you got less than 2502 cached tables, value 0 would be correct, but your table cache is huge, insane


Your config is wrong, correct those values:
max_connect_errors = 1000000
tmp_table_size = 3G
max_heap_table_size = 3G
query_cache_size = 384M
query_cache_limit = 8M
table_definition_cache = 250240
table_open_cache = 250240

[!!] Joins performed without indexes: 48433
You should enable slow log, set it to like 0.2 and check join queries, seems like many of them not using indexes properly

[!!] Temporary tables created on disk: 32% (542K on disk / 1M total)
You got some very uneffective queries, slow queries requiring temp tables, since you mentioned wordpress or joomla, those slow queries are most likely coming from some modules/plugins or custom scripts


Correct upper values to these:
max_connect_errors = 10
tmp_table_size = 128M
max_heap_table_size = 128M
query_cache_size = 50M
query_cache_limit = 1M
table_definition_cache = 3000
table_open_cache = 5000

Regards
 
A

AlexCl

Active Member
Feb 19, 2013
42
0
6
cPanel Access Level
Root Administrator
Thanks for your reply.

Regarding the table cache, according to tuning primer i have 13480 tables. So wouldn't the values 3000 and 5000 you said to set be too small ?

Here's the output form tuning primer.
Code: 
MySQL Version 5.5.30-MariaDB-log x86_64

Uptime = 6 days 0 hrs 45 min 13 sec
Avg. qps = 36
Total Questions = 18874946
Threads Connected = 3

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10.000000 sec.
You have 440861 out of 18874967 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled

WORKER THREADS
Current thread_cache_size = 16384
Current threads_cached = 11
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 150
Current threads_connected = 3
Historic max_used_connections = 14

The number of used connections is 9% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 354 M
Current InnoDB data space = 360 M
Current InnoDB buffer pool free = 88 %
Current innodb_buffer_pool_size = 10.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 10.96 G
Configured Max Per-thread Buffers : 853 M
Configured Max Global Buffers : 10.89 G
Configured Max Memory Limit : 11.72 G
Physical Memory : 15.53 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 187 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 565
Key buffer free ratio = 74 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 384 M
Current query_cache_used = 30 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 7.97 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 3.00 M
You have had 55348 queries where a join could not use an index properly
You have had 1745 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 500641 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 250240 tables
Current table_definition_cache = 250240 tables
You have a total of 13480 tables
You have 26925 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 3.00 G
Current tmp_table_size = 3.00 G
Of 1342556 temp tables, 32% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 236 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 816
You may benefit from selective use of InnoDB.
Thank you very much for the help.
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
I've suggested smaller table_cache_size, becouse of:
1. scallability issues - known performance problem in MySQL - table_cache negative scalability - MySQL Performance Blog
2. table_cache is required for open tables, not all 13 480 tables are open at once, and not all of them are used very often
so to table_cache we only need to fit the most used ones


Also:

Lower thread_cache_size to 128
you probably dont have more than 1-4 threads running at once anyway

Lower long_query_time from 10 seconds to something like 0.2s so you can track slow queries that runs longer than 0.2s
Not only those running more than 10 seconds

Change read_rnd_buffer_size to 4M from current 256K
Change sort_buffer_size to 256K from current 2M (yes, make it smaller)

And add log-queries-not-using-indexes so you can track queries not using indexes

You can also lower innodb_buffer_pool_size from 10G to something smaller like 5G or 7G, since most of that is not used now, but MySQL takes only RAM for that
You can increase it later when you have Innodb data more than 5G

Regards
 
A

AlexCl

Active Member
Feb 19, 2013
42
0
6
cPanel Access Level
Root Administrator
Hi thinkbot,

Thanks for all you recommendations.

I do have one question. Could leaving the buffers high, like they are now, affect mysql performance at this usage ? The reason I set everything so high is that the server has 16 GB of RAM, but it's never had more la 6.5 GB used, so I thought why not let mysql use as much RAM as it needs? I was under the impression that this would increase performance. Please correct me if I'm mistaken.

Thank you.
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
You are correct, but you gave 10GB for InnoDB data buffer, when it says
"Current InnoDB buffer pool free = 88 %"

So its used only by 12%, based on tuning primer
And 359.5M out of 10GB counting only pure innodb data pages

Lowering it to like 7G would give 3GB free for other server services (and other mysql buffers), when needed, and mysql won't lose anything on it, since its not using most of it 10GB buffer anyway


Anyway, having innodb_buffer_pool_size variable high, when no other service needs memory, and server is not swapping, won't affect performance. It doesn't have negative scallability issue like table_cache does ;)
 
A

AlexCl

Active Member
Feb 19, 2013
42
0
6
cPanel Access Level
Root Administrator
Hello again,

I've adjusted the variables according to your recommendations. I'll give it 48 hours to run and re-run the tests and update this thread.

Thank you very much for our help :)
 
A

AlexCl

Active Member
Feb 19, 2013
42
0
6
cPanel Access Level
Root Administrator
And I'm back :)

Just ran mysqltuner and this is the output:

Code: 
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.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.30-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3720)
[--] Data in InnoDB tables: 367M (Tables: 9443)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 291)
[!!] Total fragmented tables: 67

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 16h 13m 37s (3M q [27.433 qps], 70K conn, TX: 12B, RX: 624M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 5.8G global + 7.7M per thread (150 max threads)
[OK] Maximum possible memory usage: 6.9G (44% of installed RAM)
[OK] Slow queries: 3% (128K/3M)
[OK] Highest usage of available connections: 8% (13/150)
[OK] Key buffer size / total MyISAM indexes: 512.0M/197.8M
[OK] Key buffer hit rate: 96.9% (8M cached / 260K reads)
[OK] Query cache efficiency: 61.5% (1M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (236 temp sorts / 79K sorts)
[!!] Joins performed without indexes: 17089
[!!] Temporary tables created on disk: 33% (209K on disk / 630K total)
[OK] Thread cache hit rate: 99% (13 created / 70K connections)
[!!] Table cache hit rate: 0% (5K open / 13M opened)
[OK] Open file limit used: 6% (4K/65K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 367.8M/5.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better 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
Variables to adjust:
    join_buffer_size (> 3.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    table_cache (> 5000)
So should I increase those variables, according to the recommendations? That's what I did last time and how I got to those huge values that I had before. Right now it's configured just how you recommended.

Thanks.
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
No, don't increase those values higher; for now it's good,

but:
"Table cache hit rate: 0% (5K open / 13M opened)"

13 Milion opened tables :|
thats pretty weird, you dont have much traffic

Open tables : The number of tables that are open.
Opened_tables : The number of tables that have been opened.

But you got quite much temp tables

can you run mysqlreport and tuning primer also ?
so we can see more info on tables, locks, and temp tables
 
A

AlexCl

Active Member
Feb 19, 2013
42
0
6
cPanel Access Level
Root Administrator
Ok here it is:

tuning primer:

Code: 
MySQL Version 5.5.30-MariaDB-log x86_64

Uptime = 1 days 19 hrs 18 min 19 sec
Avg. qps = 27
Total Questions = 4245402
Threads Connected = 4

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 10.000000 sec.
You have 135039 out of 4245439 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled

WORKER THREADS
Current thread_cache_size = 128
Current threads_cached = 10
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 150
Current threads_connected = 3
Historic max_used_connections = 13
The number of used connections is 8% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 355 M
Current InnoDB data space = 368 M
Current InnoDB buffer pool free = 77 %
Current innodb_buffer_pool_size = 5.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 5.73 G
Configured Max Per-thread Buffers : 1.12 G
Configured Max Global Buffers : 5.64 G
Configured Max Memory Limit : 6.76 G
Physical Memory : 15.53 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 198 M
Current key_buffer_size = 512 M
Key cache miss rate is 1 : 32
Key buffer free ratio = 81 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 35 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 27.36 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 4 M
Sort buffer seems to be fine

JOINS
tuning-primer.sh: line 402: export: `2097152': not a valid identifier
Current join_buffer_size = 3.00 M
You have had 16719 queries where a join could not use an index properly
You have had 621 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 65535 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 5000 tables
Current table_definition_cache = 3000 tables
You have a total of 13496 tables
You have 5000 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 430365 temp tables, 33% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not im
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 325 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 810
You may benefit from selective use of InnoDB.
I also ran the full report from mysqlmymonlite. Here's the output:

Code: 
MySQL server was started 2013-05-14 16:32:13
Uptime: 1 day 19 hours 40 min 12 sec
Uptime 157212
Uptime_since_flush_status 157212

--------------------
Query Cache Limit (bytes): 1048576
Query Cache Min Unit (bytes): 4096
Query Cache Size (bytes): 134217728
Query Cache Free Mem (bytes): 98883280
Query Cache Used Mem (bytes): 35334448
Queries in Query Cache: 1
Query Cache Avg Size (bytes): 35334448



Linux 2.6.32-379.22.1.lve1.2.17.el6.x86_64 ((yourserverhostname)) 	05/16/2013 	_x86_64_	(8 CPU)

12:12:25 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle
12:12:27 PM  all    3.06    0.00    0.56    0.38    0.00    0.13    0.00    0.00   95.87
12:12:27 PM    0    0.00    0.50    0.50    0.00    0.00    0.00    0.00    0.00   99.00
12:12:27 PM    1    9.09    0.00    1.52    1.52    0.00    0.00    0.00    0.00   87.88
12:12:27 PM    2    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00
12:12:27 PM    3    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00
12:12:27 PM    4   14.43    0.00    2.99    1.00    0.00    1.00    0.00    0.00   80.60
12:12:27 PM    5    0.50    0.00    0.00    0.00    0.00    0.00    0.00    0.00   99.50
12:12:27 PM    6    0.50    0.00    0.00    0.00    0.00    0.00    0.00    0.00   99.50
12:12:27 PM    7    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00

12:12:27 PM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle
12:12:29 PM  all    1.12    0.00    0.56    3.06    0.00    0.19    0.00    0.00   95.07
12:12:29 PM    0    3.00    0.00    0.50    1.00    0.00    0.00    0.00    0.00   95.50
12:12:29 PM    1    1.00    0.00    0.50   16.92    0.00    0.50    0.00    0.00   81.09
12:12:29 PM    2    0.50    0.00    0.00    0.00    0.00    0.00    0.00    0.00   99.50
12:12:29 PM    3    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00
12:12:29 PM    4    4.02    0.00    1.51    6.03    0.00    1.01    0.00    0.00   87.44
12:12:29 PM    5    0.50    0.00    1.00    0.50    0.00    0.00    0.00    0.00   98.01
12:12:29 PM    6    0.00    0.00    0.51    0.00    0.00    0.00    0.00    0.00   99.49
12:12:29 PM    7    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00

Average:     CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest   %idle
Average:     all    2.09    0.00    0.56    1.72    0.00    0.16    0.00    0.00   95.47
Average:       0    1.50    0.25    0.50    0.50    0.00    0.00    0.00    0.00   97.26
Average:       1    5.01    0.00    1.00    9.27    0.00    0.25    0.00    0.00   84.46
Average:       2    0.25    0.00    0.00    0.00    0.00    0.00    0.00    0.00   99.75
Average:       3    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00
Average:       4    9.25    0.00    2.25    3.50    0.00    1.00    0.00    0.00   84.00
Average:       5    0.50    0.00    0.50    0.25    0.00    0.00    0.00    0.00   98.76
Average:       6    0.25    0.00    0.25    0.00    0.00    0.00    0.00    0.00   99.50
Average:       7    0.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00

top - 12:12:30 up 12 days,  9:44,  2 users,  load average: 0.61, 0.51, 0.32
Tasks: 287 total,   1 running, 286 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.9%us,  1.1%sy,  1.2%ni, 93.1%id,  2.8%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  16288496k total, 15914256k used,   374240k free,   460932k buffers
Swap:  2097136k total,   321436k used,  1775700k free,  9638892k cached

    

             total       used       free     shared    buffers     cached
Mem:         15906      15540        365          0        450       9412
Low:         15906      15540        365
High:            0          0          0
-/+ buffers/cache:       5677      10228
Swap:         2047        313       1734

MemTotal:       16288496 kB
MemFree:          374984 kB
Buffers:          460932 kB
Cached:          9638892 kB
SwapCached:        74276 kB
MemCommitted:   120512512 kB
VirtualSwap:           0 kB
Active:          5516220 kB
Inactive:        9258384 kB
Active(anon):    3857360 kB
Inactive(anon):   924192 kB
Active(file):    1658860 kB
Inactive(file):  8334192 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       2097136 kB
SwapFree:        1775700 kB
Dirty:              1480 kB
Writeback:             0 kB
AnonPages:       4637124 kB
Mapped:           143796 kB
Shmem:            106840 kB
Slab:            1045212 kB
SReclaimable:     972928 kB
SUnreclaim:        72284 kB
KernelStack:        3976 kB
PageTables:        27468 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    10241384 kB
Committed_AS:          4 kB
VmallocTotal:   34359738367 kB
VmallocUsed:       64468 kB
VmallocChunk:   34359608108 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:        7872 kB
DirectMap2M:    16760832 kB

--------------------------
ps_mem.py output:
http://www.pixelbeat.org/scripts/ps_mem.py
--------------------------
 Private  +   Shared  =  RAM used	Program 

  4.0 KiB +  27.5 KiB =  31.5 KiB	dbus-daemon
  4.0 KiB +  34.0 KiB =  38.0 KiB	screen
  0.0 KiB +  57.0 KiB =  57.0 KiB	udevd (3)
 64.0 KiB +  15.5 KiB =  79.5 KiB	proxyexec
 72.0 KiB +  30.5 KiB = 102.5 KiB	atd
 24.0 KiB +  81.0 KiB = 105.0 KiB	mingetty (6)
  4.0 KiB + 118.5 KiB = 122.5 KiB	mysqld_safe
 56.0 KiB +  71.0 KiB = 127.0 KiB	pure-authd
124.0 KiB +  22.5 KiB = 146.5 KiB	tee
140.0 KiB +  13.5 KiB = 153.5 KiB	mdadm
200.0 KiB +  19.5 KiB = 219.5 KiB	rhnsd
240.0 KiB +  32.0 KiB = 272.0 KiB	auditd
296.0 KiB +  19.5 KiB = 315.5 KiB	dovecot
336.0 KiB +  33.0 KiB = 369.0 KiB	init
360.0 KiB +  33.5 KiB = 393.5 KiB	dovecot-auth
392.0 KiB +  44.0 KiB = 436.0 KiB	ntpd
456.0 KiB +  32.5 KiB = 488.5 KiB	crond
464.0 KiB +  67.5 KiB = 531.5 KiB	cpanellogd - sl
612.0 KiB +  51.0 KiB = 663.0 KiB	top
740.0 KiB + 146.0 KiB = 886.0 KiB	mysqlmymonlite.
  1.0 MiB + 394.0 KiB =   1.4 MiB	pop3-login (2)
  1.2 MiB + 239.0 KiB =   1.4 MiB	cPhulkd - proce
896.0 KiB + 598.0 KiB =   1.5 MiB	bash (3)
968.0 KiB + 573.0 KiB =   1.5 MiB	pure-ftpd (7)
  1.5 MiB + 411.0 KiB =   1.9 MiB	imap-login (2)
  1.4 MiB + 654.5 KiB =   2.0 MiB	pop3 (4)
  2.3 MiB + 123.0 KiB =   2.4 MiB	queueprocd - wa
  3.3 MiB + 119.0 KiB =   3.4 MiB	cpdavd - accept
  3.4 MiB + 102.5 KiB =   3.5 MiB	lvestats-server
  2.2 MiB +   2.0 MiB =   4.2 MiB	exim (4)
  2.1 MiB +   2.6 MiB =   4.7 MiB	sshd (13)
  4.9 MiB +  41.0 KiB =   5.0 MiB	rsyslogd
  5.6 MiB + 227.5 KiB =   5.9 MiB	tailwatchd
  6.2 MiB + 140.5 KiB =   6.3 MiB	mailmanctl
  6.3 MiB + 311.0 KiB =   6.6 MiB	leechprotect
  7.9 MiB +  92.0 KiB =   8.0 MiB	named
  7.1 MiB +   1.2 MiB =   8.3 MiB	imap (17)
 11.4 MiB + 119.5 KiB =  11.6 MiB	lfd - sleeping
  4.6 MiB +   7.1 MiB =  11.8 MiB	/usr/local/cpan (2)
 13.1 MiB +   1.4 MiB =  14.5 MiB	python (8)
 13.7 MiB +   6.0 MiB =  19.8 MiB	cpsrvd-ssl (6)
 40.7 MiB +  10.5 KiB =  40.7 MiB	inotifywait
 42.9 MiB +  15.6 MiB =  58.5 MiB	httpd (9)
 53.7 MiB +  12.1 MiB =  65.8 MiB	spamd child (2)
186.5 MiB +  73.5 KiB = 186.6 MiB	clamd
176.0 MiB +  12.4 MiB = 188.4 MiB	php (6)
  3.9 GiB + 251.0 KiB =   3.9 GiB	mysqld
---------------------------------
                          4.6 GiB
=================================

 Private  +   Shared  =  RAM used	Program 


----------------------------
Server Disk Usage
----------------------------
Filesystem    Type    Size  Used Avail Use% Mounted on
/dev/md1      ext4    1.4T  323G  982G  25% /
tmpfs        tmpfs    7.8G     0  7.8G   0% /dev/shm
/dev/md0      ext2    496M   64M  407M  14% /boot
/dev/sdc1     ext4     56G  7.5G   45G  15% /mnt/disk1
/usr/tmpDSK   ext3    4.0G  296M  3.5G   8% /tmp

----------------------------
MySQL datadir disk usage
----------------------------
/mnt/disk1/mysql/ uses 7593376 kilobytes of disk space

----------------------------
ulimit -aH
----------------------------
core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 127113
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) unlimited
cpu time               (seconds, -t) unlimited
max user processes              (-u) 127113
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/304554/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            10485760             unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             127113               127113               processes 
Max open files            65535                65535                files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       127113               127113               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.36.1.6

WHM's PHP Handler config:
/usr/local/cpanel/bin/rebuild_phpconf --current
Available handlers: suphp fcgi cgi none
DEFAULT PHP: 5
PHP4 SAPI: none
PHP5 SAPI: fcgi
SUEXEC: enabled
RUID2: not installed

PHP 5.3.24 (cli) (built: May  8 2013 17:47:08) 
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2013 Zend Technologies
    with eAccelerator v0.9.6.1, Copyright (c) 2004-2010 eAccelerator, by eAccelerator
    with the ionCube PHP Loader v4.2.2, Copyright (c) 2002-2012, by ionCube Ltd., and
    with Zend Guard Loader v3.3, Copyright (c) 1998-2010, by Zend Technologies
    with Suhosin v0.9.33, Copyright (c) 2007-2012, by SektionEins GmbH

System => Linux yourserverhostname 2.6.32-379.22.1.lve1.2.17.el6.x86_64 #1 SMP Wed Apr 3 12:05:42 EEST 2013 x86_64
Build Date => May  8 2013 17:46:14

Configure Command =>  './configure'  '--disable-cgi' '--disable-fileinfo' '--enable-bcmath' '--enable-calendar' '--enable-ftp' '--enable-gd-native-ttf' '--enable-libxml' '--enable-magic-quotes' '--enable-mbstring' '--enable-pdo=shared' '--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-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-sqlite=shared' '--with-xpm-dir=/usr' '--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 | 20090626
Zend Extension Build | API220090626,NTS
PHP Extension Build | API20090626,NTS
DOM/XML API Version | 20031129
Client API version | 5.5.30-MariaDB
Client API library version | 5.5.30-MariaDB
Client API header version | 5.5.30-MariaDB
Client API version | 5.5.30-MariaDB
Phar API version | 1.1.1

display_errors | STDOUT | STDOUT
error_log | error_log | error_log
extension_dir | /usr/local/lib/php/extensions/no-debug-non-zts-20090626 | /usr/local/lib/php/extensions/no-debug-non-zts-20090626
file_uploads | On | On
log_errors | On | On
log_errors_max_len | 1024 | 1024
magic_quotes_gpc | On | On
magic_quotes_sybase | Off | Off
max_execution_time | 0 | 0
max_file_uploads | 20 | 20
memory_limit | 256M | 256M
open_basedir | no value | no value
post_max_size | 8M | 8M
realpath_cache_size | 16K | 16K
realpath_cache_ttl | 120 | 120
safe_mode | Off | Off
safe_mode_exec_dir | /usr/local/php/bin | /usr/local/php/bin
safe_mode_gid | Off | Off
safe_mode_include_dir | no value | no value
sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i
sql.safe_mode | Off | Off
upload_max_filesize | 10M | 10M
upload_tmp_dir | no value | no value
sqlite3.extension_dir | no value | no value
safe_mode_allowed_env_vars | PHP_ | PHP_
safe_mode_protected_env_vars | LD_LIBRARY_PATH | LD_LIBRARY_PATH
suhosin.disable.display_errors | Off | Off
suhosin.memory_limit | 0 | 0

cURL support | enabled
cURL Information | 7.24.0
ZLib Version | 1.2.3
GD Support | enabled
GD Version | bundled (2.0.34 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
ZLib Support | enabled

suhosin.apc_bug_workaround | Off | Off

suhosin.cookie.encrypt | Off | Off
suhosin.executor.disable_eval | Off | Off
suhosin.get.max_value_length | 512 | 512
suhosin.get.max_vars | 100 | 100
suhosin.memory_limit | 0 | 0
suhosin.post.max_value_length | 1000000 | 1000000
suhosin.post.max_vars | 1000 | 1000
suhosin.request.max_value_length | 1000000 | 1000000
suhosin.request.max_vars | 1000 | 1000
suhosin.upload.disallow_binary | 0 | 0
suhosin.upload.disallow_elf | 1 | 1
suhosin.upload.max_uploads | 25 | 25
suhosin.upload.remove_binary | 0 | 0
suhosin.upload.verification_script | no value | no value



   Server Version: Apache/2.2.24 (Unix) mod_ssl/2.2.24 OpenSSL/1.0.0-fips
   Server Built: May 8 2013 17:42:08
   Current Time: Thursday, 16-May-2013 12:12:25 CEST
   Restart Time: Friday, 10-May-2013 20:39:51 CEST
   Parent Server Generation: 83
   Total accesses: 1404269 - Total Traffic: 29.8 GB
   CPU Usage: u38.03 s12.09 cu0 cs0 - .0103% CPU load
   2.88 requests/sec - 64.0 kB/second - 22.2 kB/request
   8 requests currently being processed, 142 idle workers

Server version: Apache/2.2.24 (Unix)
Server built:   May  8 2013 17:42:08
Cpanel::Easy::Apache v3.18.13 rev9999 +cloudlinux
Server's Module Magic Number: 20051115:31
Server loaded:  APR 1.4.6, APR-Util 1.4.1
Compiled using: APR 1.4.6, APR-Util 1.4.1
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 enabled)
 -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"

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)
 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)
 status_module (static)
 autoindex_module (static)
 asis_module (static)
 info_module (static)
 suexec_module (static)
 cgid_module (static)
 negotiation_module (static)
 dir_module (static)
 actions_module (static)
 userdir_module (static)
 alias_module (static)
 rewrite_module (static)
 so_module (static)
 hostinglimits_module (shared)
 bwlimited_module (shared)
 fcgid_module (shared)
 security2_module (shared)

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_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_status.c
  mod_autoindex.c
  mod_asis.c
  mod_info.c
  mod_suexec.c
  mod_cgid.c
  mod_negotiation.c
  mod_dir.c
  mod_actions.c
  mod_userdir.c
  mod_alias.c
  mod_rewrite.c
  mod_so.c


-------------------------------
From /usr/local/apache/conf/httpd.conf
-------------------------------
Timeout 300

</IfModule>
<IfModule prefork.c>
</IfModule>


----------------------------
mysqlreport output
----------------------------


----------------------------
Total Server MyISAM and InnoDB Size
----------------------------
*************************** 1. row ***************************
innodb_tablespace_utilization_perc: 100
                     total_size_gb: 2.4890562435612082
                     index_size_gb: 0.5412976741790771
                      data_size_gb: 1.947758569382131
              innodb_total_size_gb: 0.7069854736328125
               innodb_data_size_gb: 0.3602752685546875
              innodb_index_size_gb: 0.346710205078125
              myisam_total_size_gb: 1.7796668549999595
               myisam_data_size_gb: 1.585595085285604
              myisam_index_size_gb: 0.19407176971435547
                        perc_index: 21.7471
                         perc_data: 78.2529
                 innodb_perc_index: 49.0406
                  innodb_perc_data: 50.9594
                 myisam_perc_index: 10.9049
                  myisam_perc_data: 89.0951
           innodb_perc_total_index: 64.0517
            innodb_perc_total_data: 18.4969
           myisam_perc_total_index: 35.8531
            myisam_perc_total_data: 81.4061

----------------------------
MySQL settings
----------------------------

[mysql]

# CLIENT #
port                           = 3306
socket                         = /mnt/disk1/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /mnt/disk1/mysql/mysql.sock
pid_file                       = /mnt/disk1/mysql/mysql.pid

# MyISAM #
key_buffer_size                = 512M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 10

# DATA STORAGE #
datadir                        = /mnt/disk1/mysql

# BINARY LOGGING #
log_bin                        = /mnt/disk1/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 128M
max_heap_table_size            = 128M
query_cache_type               = 1
query_cache_size               = 128M
query_cache_limit              = 1M
max_connections                = 150
thread_cache_size              = 128
open_files_limit               = 65535
table_definition_cache         = 3000
table_open_cache               = 5000
join_buffer_size	       = 3M
read_rnd_buffer_size           = 4M
sort_buffer_size               = 256K

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 5G
concurrent_insert              = ALWAYS
low_priority_updates           = 1
thread_concurrency             = 8


# LOGGING #
log_error                      = /mnt/disk1/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /mnt/disk1/mysql/mysql-slow.log


| aria_block_size | 8192 |
| aria_checkpoint_interval | 30 |
| aria_checkpoint_log_activity | 1048576 |
| aria_force_start_after_recovery_failures | 0 |
| aria_group_commit | none |
| aria_group_commit_interval | 0 |
| aria_log_file_size | 1073741824 |
| aria_log_purge_type | immediate |
| aria_max_sort_file_size | 9223372036853727232 |
| aria_page_checksum | ON |
| aria_pagecache_age_threshold | 300 |
| aria_pagecache_buffer_size | 134217728 |
| aria_pagecache_division_limit | 100 |
| aria_recover | NORMAL |
| aria_repair_threads | 1 |
| aria_sort_buffer_size | 134217728 |
| aria_stats_method | nulls_unequal |
| aria_sync_log_dir | NEWFILE |
| aria_used_for_temp_tables | ON |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr |
| big_tables | OFF |
| binlog_annotate_row_events | OFF |
| binlog_cache_size | 32768 |
| binlog_checksum | NONE |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_optimize_thread_scheduling | ON |
| 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 | ALWAYS |
| connect_timeout | 10 |
| datadir | /mnt/disk1/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| deadlock_search_depth_long | 15 |
| deadlock_search_depth_short | 4 |
| deadlock_timeout_long | 50000000 |
| deadlock_timeout_short | 10000 |
| debug_no_thread_alarm | OFF |
| 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 | OFF |
| event_scheduler | DISABLED |
| expensive_subquery_limit | 100 |
| expire_logs_days | 14 |
| extra_max_connections | 1 |
| extra_port | 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 | hxxx3.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 |
| ignore_db_dirs | |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_method | estimate |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_partitions | 1 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_blocking_buffer_pool_restore | OFF |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_populate | OFF |
| innodb_buffer_pool_restore_at_startup | 0 |
| innodb_buffer_pool_shm_checksum | ON |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 5368709120 |
| innodb_change_buffering | all |
| innodb_checkpoint_age_target | 0 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_corrupt_table_action | assert |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_dict_size_limit | 0 |
| innodb_doublewrite | ON |
| innodb_doublewrite_file | |
| innodb_fake_changes | OFF |
| innodb_fast_checksum | OFF |
| 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 | O_DIRECT |
| innodb_flush_neighbor_pages | area |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_ibuf_accel_rate | 100 |
| innodb_ibuf_active_contract | 1 |
| innodb_ibuf_max_size | 2684338176 |
| innodb_import_table_from_xtrabackup | 0 |
| innodb_io_capacity | 200 |
| innodb_kill_idle_transaction | 0 |
| innodb_large_prefix | OFF |
| innodb_lazy_drop_table | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locking_fake_changes | ON |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 268435456 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_bitmap_file_size | 104857600 |
| innodb_max_changed_pages | 1000000 |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_merge_sort_block_size | 1048576 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead | linear |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_recovery_stats | OFF |
| innodb_recovery_update_relay_log | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_update | 1 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_stats_update_need_lock | 1 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_concurrency_timer_based | OFF |
| innodb_thread_sleep_delay | 10000 |
| innodb_track_changed_pages | OFF |
| innodb_use_global_flush_log_at_trx_commit | ON |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_use_sys_stats_table | OFF |
| innodb_version | 5.5.30-MariaDB-30.1 |
| innodb_write_io_threads | 4 |
| interactive_timeout | 28800 |
| join_buffer_size | 3145728 |
| join_buffer_space_limit | 2097152 |
| join_cache_level | 2 |
| keep_files_on_create | OFF |
| key_buffer_size | 536870912 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| key_cache_segments | 0 |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_messages | en_US |
| lc_messages_dir | |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /mnt/disk1/mysql/mysql-error.log |
| log_output | FILE |
| log_queries_not_using_indexes | ON |
| log_slave_updates | OFF |
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries | ON |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| log_warnings | 1 |
| long_query_time | 10.000000 |
| low_priority_updates | ON |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| master_verify_checksum | OFF |
| max_allowed_packet | 16777216 |
| max_binlog_cache_size | 4294963200 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 4294963200 |
| max_connect_errors | 10 |
| max_connections | 150 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 134217728 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_long_data_size | 16777216 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| metadata_locks_cache_size | 1024 |
| min_examined_row_limit | 0 |
| mrr_buffer_size | 262144 |
| multi_range_count | 256 |
| myisam_block_size | 1024 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | BACKUP,FORCE |
| 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 |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | OFF |
| open_files_limit | 65535 |
| 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,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off |
| 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 | /mnt/disk1/mysql/yourserverhostname.pid |
| plugin_dir | /usr/lib64/mysql/plugin/ |
| plugin_maturity | unknown |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| progress_report_time | 56 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_strip_comments | OFF |
| 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 | 4194304 |
| 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 |
| replicate_annotate_row_events | OFF |
| replicate_do_db | |
| replicate_do_table | |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| rowid_merge_buff_size | 8388608 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 1 |
| 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_sql_verify_checksum | ON |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /mnt/disk1/mysql/mysql-slow.log |
| socket | /mnt/disk1/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 | ON |
| 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 | 1 |
| sync_frm | ON |
| sync_master_info | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
| system_time_zone | CEST |
| table_definition_cache | 3000 |
| table_open_cache | 5000 |
| thread_cache_size | 128 |
| thread_concurrency | 8 |
| thread_handling | one-thread-per-connection |
| thread_pool_idle_timeout | 60 |
| thread_pool_max_threads | 500 |
| thread_pool_oversubscribe | 3 |
| thread_pool_size | 8 |
| thread_pool_stall_limit | 500 |
| thread_stack | 294912 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 134217728 |
| 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.30-MariaDB-log |
| version_comment | MariaDB Server |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| wait_timeout | 28800 |

+------------------------------------------+------------------+
| Variable_name | Value |
+------------------------------------------+------------------+
| Aborted_clients | 26 |
| Aborted_connects | 4 |
| Access_denied_errors | 33 |
| Aria_pagecache_blocks_not_flushed | 0 |
| Aria_pagecache_blocks_unused | 15737 |
| Aria_pagecache_blocks_used | 8 |
| Aria_pagecache_read_requests | 1890165 |
| Aria_pagecache_reads | 185561 |
| Aria_pagecache_write_requests | 697538 |
| Aria_pagecache_writes | 0 |
| Aria_transaction_log_syncs | 0 |
| Binlog_commits | 222736 |
| Binlog_group_commits | 222630 |
| Binlog_snapshot_file | mysql-bin.000011 |
| Binlog_snapshot_position | 384824616 |
| Binlog_bytes_written | 384824371 |
| Binlog_cache_disk_use | 489 |
| Binlog_cache_use | 53339 |
| Binlog_stmt_cache_disk_use | 2 |
| Binlog_stmt_cache_use | 169397 |
| Busy_time | 0.000000 |
| Bytes_received | 678858817 |
| Bytes_sent | 12678926227 |
| Com_admin_commands | 12408 |
| 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 | 1064 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 477499 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 28 |
| Com_create_db | 3 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 25 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 11821 |
| Com_delete | 112993 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 1 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 5 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 11824 |
| Com_flush | 16 |
| Com_grant | 23 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 74098 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 11329 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 11824 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 28353 |
| Com_replace | 10 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 882396 |
| Com_set_option | 849454 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 4 |
| Com_show_charsets | 0 |
| Com_show_client_statistics | 0 |
| Com_show_collations | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 445 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 54100 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 22 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 2 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 84680 |
| Com_show_function_status | 4131 |
| Com_show_grants | 2431 |
| Com_show_keys | 185 |
| Com_show_index_statistics | 0 |
| Com_show_master_status | 4 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 4 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 1 |
| Com_show_processlist | 530 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 4 |
| Com_show_status | 2302 |
| Com_show_storage_engines | 4 |
| Com_show_table_statistics | 0 |
| Com_show_table_status | 85457 |
| Com_show_tables | 9422 |
| Com_show_triggers | 84088 |
| Com_show_user_statistics | 0 |
| Com_show_variables | 279 |
| Com_show_warnings | 3 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 247697 |
| Com_stmt_execute | 247700 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 247700 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 3099 |
| Com_update | 33066 |
| Com_update_multi | 2621 |
| 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 | 79189 |
| Cpu_time | 0.000000 |
| Created_tmp_disk_tables | 212379 |
| Created_tmp_files | 807 |
| Created_tmp_tables | 431789 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Empty_queries | 398542 |
| Executed_events | 0 |
| Executed_triggers | 0 |
| Feature_dynamic_columns | 0 |
| Feature_fulltext | 0 |
| Feature_gis | 0 |
| Feature_locale | 0 |
| Feature_subquery | 14954 |
| Feature_timezone | 17502 |
| Feature_trigger | 0 |
| Feature_xml | 0 |
| Flush_commands | 2 |
| Handler_commit | 642932 |
| Handler_delete | 20448 |
| Handler_discover | 0 |
| Handler_icp_attempts | 5730122 |
| Handler_icp_match | 5718837 |
| Handler_mrr_init | 0 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 92726 |
| Handler_read_first | 21614 |
| Handler_read_key | 11516561 |
| Handler_read_last | 340 |
| Handler_read_next | 18495475 |
| Handler_read_prev | 1256158 |
| Handler_read_rnd | 627759 |
| Handler_read_rnd_deleted | 1229921 |
| Handler_read_rnd_next | 286445741 |
| Handler_rollback | 18 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_tmp_update | 1547188 |
| Handler_tmp_write | 20574315 |
| Handler_update | 31167 |
| Handler_write | 74204 |
| Innodb_adaptive_hash_cells | 10624951 |
| Innodb_adaptive_hash_heap_buffers | 186 |
| Innodb_adaptive_hash_hash_searches | 6533806 |
| Innodb_adaptive_hash_non_hash_searches | 4620535 |
| Innodb_background_log_sync | 146843 |
| Innodb_buffer_pool_pages_data | 74624 |
| Innodb_buffer_pool_bytes_data | 1222639616 |
| Innodb_buffer_pool_pages_dirty | 15 |
| Innodb_buffer_pool_bytes_dirty | 245760 |
| Innodb_buffer_pool_pages_flushed | 117946 |
| Innodb_buffer_pool_pages_LRU_flushed | 0 |
| Innodb_buffer_pool_pages_free | 252868 |
| Innodb_buffer_pool_pages_made_not_young | 0 |
| Innodb_buffer_pool_pages_made_young | 9 |
| Innodb_buffer_pool_pages_misc | 187 |
| Innodb_buffer_pool_pages_old | 27526 |
| Innodb_buffer_pool_pages_total | 327679 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 190 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 283171992 |
| Innodb_buffer_pool_reads | 72394 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 516462 |
| Innodb_checkpoint_age | 19809 |
| Innodb_checkpoint_max_age | 434154333 |
| Innodb_checkpoint_target_age | 420587011 |
| Innodb_data_fsyncs | 119914 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 1191432192 |
| Innodb_data_reads | 82024 |
| Innodb_data_writes | 225576 |
| Innodb_data_written | 4072198144 |
| Innodb_dblwr_pages_written | 117946 |
| Innodb_dblwr_writes | 4875 |
| Innodb_deadlocks | 1 |
| Innodb_dict_tables | 9452 |
| Innodb_have_atomic_builtins | ON |
| Innodb_history_list_length | 1504 |
| Innodb_ibuf_discarded_delete_marks | 0 |
| Innodb_ibuf_discarded_deletes | 0 |
| Innodb_ibuf_discarded_inserts | 0 |
| Innodb_ibuf_free_list | 0 |
| Innodb_ibuf_merged_delete_marks | 1 |
| Innodb_ibuf_merged_deletes | 0 |
| Innodb_ibuf_merged_inserts | 4 |
| Innodb_ibuf_merges | 3 |
| Innodb_ibuf_segment_size | 2 |
| Innodb_ibuf_size | 1 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 326977 |
| Innodb_log_writes | 97544 |
| Innodb_lsn_current | 1502751005 |
| Innodb_lsn_flushed | 1502751005 |
| Innodb_lsn_last_checkpoint | 1502731196 |
| Innodb_master_thread_1_second_loops | 84223 |
| Innodb_master_thread_10_second_loops | 7680 |
| Innodb_master_thread_background_loops | 7614 |
| Innodb_master_thread_main_flush_loops | 7614 |
| Innodb_master_thread_sleeps | 84222 |
| Innodb_max_trx_id | 2386698 |
| Innodb_mem_adaptive_hash | 88067632 |
| Innodb_mem_dictionary | 102800869 |
| Innodb_mem_total | 5515509760 |
| Innodb_mutex_os_waits | 862 |
| Innodb_mutex_spin_rounds | 29818 |
| Innodb_mutex_spin_waits | 7857 |
| Innodb_oldest_view_low_limit_trx_id | 2386698 |
| Innodb_os_log_fsyncs | 102115 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 205002752 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 2039 |
| Innodb_pages_read | 72585 |
| Innodb_pages_written | 117946 |
| Innodb_purge_trx_id | 2386683 |
| Innodb_purge_undo_no | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_current_row_locks | 0 |
| Innodb_row_lock_time | 56 |
| Innodb_row_lock_time_avg | 1 |
| Innodb_row_lock_time_max | 12 |
| Innodb_row_lock_waits | 47 |
| Innodb_rows_deleted | 6360 |
| Innodb_rows_inserted | 14481 |
| Innodb_rows_read | 240535797 |
| Innodb_rows_updated | 25936 |
| Innodb_s_lock_os_waits | 4603 |
| Innodb_s_lock_spin_rounds | 138755 |
| Innodb_s_lock_spin_waits | 4719 |
| Innodb_truncated_status_writes | 0 |
| Innodb_x_lock_os_waits | 26 |
| Innodb_x_lock_spin_rounds | 1420 |
| Innodb_x_lock_spin_waits | 368 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 428684 |
| Key_blocks_used | 3986 |
| Key_blocks_warm | 0 |
| Key_read_requests | 9284753 |
| Key_reads | 287802 |
| Key_write_requests | 550114 |
| Key_writes | 540813 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 13 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 4332 |
| Open_streams | 0 |
| Open_table_definitions | 5000 |
| Open_tables | 5000 |
| Opened_files | 17276183 |
| Opened_table_definitions | 12111037 |
| Opened_tables | 14781455 |
| Opened_views | 16959 |
| 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 | 3183 |
| Qcache_free_memory | 98770440 |
| Qcache_hits | 1421434 |
| Qcache_inserts | 628093 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 106421 |
| Qcache_queries_in_cache | 11190 |
| Qcache_total_blocks | 26314 |
| Queries | 4799340 |
| Questions | 4327588 |
| Rows_read | 301573844 |
| Rows_sent | 25225780 |
| Rows_tmp_read | 12977778 |
| Rpl_status | AUTH_MASTER |
| Select_full_join | 16794 |
| Select_full_range_join | 0 |
| Select_range | 26917 |
| Select_range_check | 635 |
| Select_scan | 404843 |
| 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 | 136041 |
| Sort_merge_passes | 278 |
| Sort_range | 14137 |
| Sort_rows | 811254 |
| Sort_scan | 72907 |
| 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 | |
| Subquery_cache_hit | 0 |
| Subquery_cache_miss | 151 |
| Syncs | 256809 |
| Table_locks_immediate | 1124358 |
| Table_locks_waited | 1407 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threadpool_idle_threads | 0 |
| Threadpool_threads | 0 |
| Threads_cached | 10 |
| Threads_connected | 3 |
| Threads_created | 13 |
| Threads_running | 1 |
| Uptime | 157220 |
| Uptime_since_flush_status | 157220 |
+------------------------------------------+------------------+
+-------+--------------+-----------+--------------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+--------------+-----------+--------------+---------+------+-------+------------------+----------+
| 4 | eximstats | localhost | eximstats | Sleep | 4 | | | 0.000 |
| 75807 | leechprotect | localhost | leechprotect | Sleep | 4232 | | | 0.000 |
| 79188 | root | localhost | | Query | 0 | | show processlist | 0.000 |
+-------+--------------+-----------+--------------+---------+------+-------+------------------+----------+

------------------------------------
INNODB Buffer Pool Stats
------------------------------------
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 327679
                    FREE_BUFFERS: 252868
                  DATABASE_PAGES: 74624
              OLD_DATABASE_PAGES: 27526
         MODIFIED_DATABASE_PAGES: 15
              PENDING_DECOMPRESS: 0
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 9
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 72585
            NUMBER_PAGES_CREATED: 2039
            NUMBER_PAGES_WRITTEN: 117946
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 3.062308605712143
                NUMBER_PAGES_GET: 283171992
                        HIT_RATE: 1000
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 190
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0

----------------------------
INNODB Status
----------------------------
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
130516 12:12:33 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 84223 1_second, 84222 sleeps, 7680 10_second, 7614 background, 7614 flush
srv_master_thread log flush and writes: 146843
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5493, signal count 5595
Mutex spin waits 7857, rounds 29818, OS waits 862
RW-shared spins 4719, rounds 138755, OS waits 4603
RW-excl spins 368, rounds 1420, OS waits 26
Spin rounds per wait: 3.80 mutex, 29.40 RW-shared, 3.86 RW-excl
--------
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
82024 OS file reads, 225576 OS file writes, 119914 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 3.87 writes/s, 1.12 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 3 merges
merged operations:
 insert 4, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 10624951, node heap has 187 buffer(s)
6.50 hash searches/s, 13.69 non-hash searches/s
---
LOG
---
Log sequence number 1502751005
Log flushed up to   1502751005
Last checkpoint at  1502731196
Max checkpoint age    434154333
Checkpoint age target 420587011
Modified age          19809
Checkpoint age        19809
0 pending log writes, 0 pending chkp writes
102121 log i/o's done, 0.75 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 5515509760; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 88067632 	(84999608 + 3068024)
    Page hash           5313416 (buffer pool 0 only)
    Dictionary cache    102800869 	(21251312 + 81549557)
    File system         4163344 	(82672 + 4080672)
    Lock system         13282352 	(13281976 + 376)
    Recovery system     0 	(0 + 0)
Dictionary memory allocated 81549557
Buffer pool size        327679
Buffer pool size, bytes 5368692736
Free buffers            252868
Database pages          74624
Old database pages      27526
Modified db pages       15
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 72585, created 2039, written 117946
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: 74624, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 246B0A
Read view up limit trx id 246B0A
Read view low limit trx id 246B0A
Read view individually stored trx ids:
-----------------
Main thread process no. 304554, id 139924141737728, state: sleeping
Number of rows inserted 14481, updated 25936, deleted 6360, read 240535797
0.12 inserts/s, 0.00 updates/s, 0.12 deletes/s, 61.12 reads/s
------------------------
LATEST DETECTED DEADLOCK
------------------------
130515  0:30:20
*** (1) TRANSACTION:
TRANSACTION 1EB193, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 11780, OS thread handle 0x7f4424a60700, query id 600498 localhost roundcube update
INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('c4f858777560f1d2aede89151f0af488', 'bGFuZ3VhZ2V8czo1OiJyb19STyI7dGVtcHxiOjE7c2tpbnxzOjU6ImxhcnJ5Ijs=', '79.115.171.29', '2013-05-15 00:30:20', '2013-05-15 00:30:20')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7924 page no 3 n bits 80 index `PRIMARY` of table `roundcube`.`session` trx id 1EB193 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1EB194, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 11779, OS thread handle 0x7f4424985700, query id 600497 localhost roundcube update
INSERT INTO session (sess_id, vars, ip, created, changed) VALUES ('c4f858777560f1d2aede89151f0af488', 'bGFuZ3VhZ2V8czo1OiJyb19STyI7dGVtcHxiOjE7c2tpbnxzOjU6ImxhcnJ5Ijs=', '79.115.171.29', '2013-05-15 00:30:20', '2013-05-15 00:30:20')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7924 page no 3 n bits 80 index `PRIMARY` of table `roundcube`.`session` trx id 1EB194 lock mode S locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7924 page no 3 n bits 80 index `PRIMARY` of table `roundcube`.`session` trx id 1EB194 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 246B0A
Purge done for trx's n:o < 246AFB undo n:o < 0
History list length 1504
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 79190, OS thread handle 0x7f4424a17700, query id 4799347 localhost root
show engine innodb status
----------------------------
END OF INNODB MONITOR OUTPUT
============================


----------------------------
MySQL Error Log
----------------------------

130516 12:08:46 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:08:47 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:08:49 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:08:49 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:08:56 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:09:05 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:09:08 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_all_the_cool_cats', '8', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)
130516 12:09:22 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1368698962.0137031078338623046875', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)
130516 12:09:37 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:09:50 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:10:06 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:11:07 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:120:{s:14:\"ai1ec_event/?$\";s:31:\"index.php?post_type=ai1ec_event\";s:44:\"ai1ec_event/feed/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:39:\"ai1ec_event/(feed|rdf|rss|rss2|atom)/?$\";s:48:\"index.php?post_type=ai1ec_event&feed=$matches[1]\";s:31:\"ai1ec_event/page/([0-9]{1,})/?$\";s:49:\"index.php?post_type=ai1ec_event&paged=$matches[1]\";s:12:\"slideshow/?$\";s:29:\"index.php?post_type=slideshow\";s:54:\"slideshow/feed/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:49:\"slideshow/(feed|rdf|rss|rss2|atom|ai1ec_event)/?$\";s:46:\"index.php?post_type=slideshow&feed=$matches[1]\";s:29:\"slideshow/page/([0-9]{1,})/?$\";s:47:\
130516 12:12:15 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1368699135.3032839298248291015625', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)
130516 12:12:20 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_all_the_cool_cats', '8', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)
130516 12:12:28 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('rewrite_rules', 'a:68:{s:47:\"category/(.+?)/feed/(feed|rdf|rss|rss2|atom)/?$\";s:52:\"index.php?category_name=$matches[1]&feed=$matches[2]\";s:42:\"category/(.+?)/(feed|rdf|rss|rss2|atom)/?$\";s:52:\"index.php?category_name=$matches[1]&feed=$matches[2]\";s:35:\"category/(.+?)/page/?([0-9]{1,})/?$\";s:53:\"index.php?category_name=$matches[1]&paged=$matches[2]\";s:17:\"category/(.+?)/?$\";s:35:\"index.php?category_name=$matches[1]\";s:44:\"tag/([^/]+)/feed/(feed|rdf|rss|rss2|atom)/?$\";s:42:\"index.php?tag=$matches[1]&feed=$matches[2]\";s:39:\"tag/([^/]+)/(feed|rdf|rss|rss2|atom)/?$\";s:42:\"index.php?tag=$matches[1]&feed=$matches[2]\";s:32:\"tag/([^/]+)/page/?([0-9]{1,})/?$\";s:43:\"index.php?tag=$matches[1]&paged=$matches[2]\";s:14:\"tag/

Report Complete:
Thu May 16 12:12:33 CEST 2013
----------------------------
Hope you can help.

Thanks again.
 
E

Evolve

Well-Known Member
Jan 31, 2007
47
0
156
I am also seeing my table cache hit rate hitting 0% and the opened tables continuously climbing. I'm curious to see if you find a solution.
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Sorry for delay, I was out for some time

It seems like the reason for that is becouse you got some complicated, not indexed queries that create a lot of temporary tables
So please run pt-query digest for slow qeuries

wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log

so we can see the queries which takes the most time (usually the ones creating temp tables)
after that you can run explain SQL on those queries, and we will see how they are executed

edit: After checking the variables on your results, Im sure the problem are slow/inefficient queries and temporary tables:

| Connections | 79189 |
| Empty_queries | 398542 |
| Questions | 4327588 |

| Open_table_definitions | 5000 |
| Open_tables | 5000 |
| Opened_files | 17276183 |
| Opened_table_definitions | 12111037 | - The number of .frm files that have been cached.
| Opened_tables | 14781455 | - The number of tables that have been opened

| Created_tmp_disk_tables | 212379 | - The number of internal on-disk temporary tables created by the server while executing statements.
| Created_tmp_files | 807 |
| Created_tmp_tables | 431789 | - The number of internal temporary tables created by the server while executing statements.

You can see over 212k on-disk tables on disk, thats almost 3 every conn, thats bad
431k temporary tables, thats almost 5,5 every conn, thats extremely bad

as I said in my first post here
"
[!!] Temporary tables created on disk: 32% (542K on disk / 1M total)
You got some very uneffective queries, slow queries requiring temp tables, since you mentioned wordpress or joomla, those slow queries are most likely coming from some modules/plugins or custom scripts"
 
Last edited:
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
C WordPress Table Prefix Auto Fill Domain Name Workarounds and Optimization 4
M Change table_definition_cache limits question Workarounds and Optimization 1
W MySQL 5.6, table_open_cache & swapping Workarounds and Optimization 9
A Table cache hit rate 0% Workarounds and Optimization 5
S mysqltuner result help/info Table cache hit rate: 0% Workarounds and Optimization 3
Similar threads
WordPress Table Prefix Auto Fill Domain Name
Change table_definition_cache limits question
MySQL 5.6, table_open_cache & swapping
Table cache hit rate 0%
mysqltuner result help/info Table cache hit rate: 0%
Top Bottom