Optimization of server for shared hosting 32gb RAM
Hi,
I have a E3-1240 v3 dedicated server, with 32GB RAM, running CENTOS 6.5 x86_64 standard and WHM WHM 11.42.0 (build 8)
Total processors: 8
Processor #1
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E3-1240 v3 @ 3.40GHz
Speed
3399.891 MHz
Cache
8192 KB
I would like to optimize and clean my config my.cnf to get the best perfomance with 34GB RAM here is my actual my.cnf:
and the mysqltuner report:
thank you very much.
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 32M
table_open_cache = 5096
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
max_connections = 800
local-infile=0
innodb_file_per_table=1
open_files_limit = 16384
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host =
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user =
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port =
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
#
# binary logging format - mixed recommended
#binlog_format=mixed
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
and the mysqltuner report:
>> MySQLTuner 1.2.0 - Major Hayden
>> 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.35-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 873M (Tables: 1188)
[--] Data in InnoDB tables: 122M (Tables: 1100)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 30)
[!!] Total fragmented tables: 37
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 12h 58m 15s (13M q [102.030 qps], 380K conn, TX: 41B, RX: 1B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 608.0M global + 8.8M per thread (800 max threads)
[OK] Maximum possible memory usage: 7.4G (23% of installed RAM)
[OK] Slow queries: 0% (3/13M)
[OK] Highest usage of available connections: 60% (484/800)
[OK] Key buffer size / total MyISAM indexes: 384.0M/87.5M
[OK] Key buffer hit rate: 99.9% (72M cached / 41K reads)
[OK] Query cache efficiency: 73.7% (7M cached / 10M selects)
[!!] Query cache prunes per day: 10362
[OK] Sorts requiring temporary tables: 0% (171 temp sorts / 105K sorts)
[!!] Joins performed without indexes: 11543
[!!] Temporary tables created on disk: 41% (264K on disk / 631K total)
[OK] Thread cache hit rate: 91% (31K created / 380K connections)
[OK] Table cache hit rate: 81% (4K open / 5K opened)
[OK] Open file limit used: 22% (3K/16K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 122.8M/128.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
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
Variables to adjust:
query_cache_size (> 64M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)thank you very much.
-
yeah this is a new server :) [COLOR="silver">- - - Updated - - - how i can clean that my.cnf ? have too much ## comments and other things that i dont use i think 0 -
[client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 32M table_open_cache = 2000 sort_buffer_size = 256K read_buffer_size = 128K join_buffer_size = 1M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache_size = 16 query_cache_size = 64M max_connections = 300 local-infile=0 open_files_limit = 16384 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 #skip-networking server-id = 1 innodb_buffer_pool_size = 150M innodb_file_per_table=1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash #safe-updates [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout0 -
thank you so much i will run in for 24h to get back with the slow queries log thank you 0 -
[quote="alexistkd, post: 1574741">how i can clean that my.cnf ? have too much ## comments and other things that i dont use i think
I just wanted to note that, in general, you can simply delete the lines that are commented out if you no longer plan on using them. Thank you.0 -
thank you michael, so in theory doesnt matters i have 32gb if the clients websites are not optimized they will have slow loading true? 0 -
as i dont have control over slow queries because its a shared environment what else i can do besides that? optimize apache, install varnish nginx or that wont make any difference? 0 -
Nope, that won't make much of a difference if your load is MySQL bound but you can add after [mysqld] tmpdir=/dev/shm This will tell MySQL to place temp files on "RAM disk", this will speed queries with temp tables on disk a bit 0 -
and those tmp files erases automatically right? 0 -
yes, those are only temporary files, when query finishes, they are removed 0 -
heres my new mysqltuner report after 24 hours: >> MySQLTuner 1.2.0 - Major Hayden >> 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.35-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query [--] Data in MyISAM tables: 885M (Tables: 1188) [--] Data in InnoDB tables: 125M (Tables: 1228) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 0B (Tables: 30) Argument "" isn't numeric in numeric gt (>) at ./mysqltuner.pl line 564 (#1) (W numeric) The indicated string was fed as an argument to an operator that expected a numeric value instead. If you're fortunate the message will identify which operator was so unfortunate. [OK] Total fragmented tables: -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query Argument "" isn't numeric in numeric eq (==) at ./mysqltuner.pl line 623 (#1) -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 4h 30m 6s (10M q [98.409 qps], 362K conn, TX: 28B, RX: 1B) [--] Reads / Writes: 74% / 26% [--] Total buffers: 630.0M global + 3.6M per thread (800 max threads) [OK] Maximum possible memory usage: 3.4G (11% of installed RAM) [OK] Slow queries: 0% (6K/10M) [OK] Highest usage of available connections: 48% (386/800) [!!] Cannot calculate MyISAM index size - re-run script as root user [OK] Query cache efficiency: 69.3% (5M cached / 7M selects) [!!] Query cache prunes per day: 6235 [OK] Sorts requiring temporary tables: 0% (112 temp sorts / 82K sorts) [!!] Joins performed without indexes: 13062 [!!] Temporary tables created on disk: 40% (133K on disk / 331K total) [OK] Thread cache hit rate: 96% (13K created / 362K connections) [!!] Table cache hit rate: 4% (2K open / 45K opened) [OK] Open file limit used: 13% (2K/16K) [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks) [OK] InnoDB data size / buffer pool: 125.0M/150.0M -------- Recommendations ----------------------------------------------------- General recommendations: 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: query_cache_size (> 64M) join_buffer_size (> 1.0M, or always use indexes with joins) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_cache (> 2000)
and the slow queries report:# 2s user time, 20ms system time, 31.02M rss, 186.45M vsz # Current date: Wed Feb 19 13:00:17 2014 # Hostname: panama.dbspma.com # Files: /var/lib/mysql/mysql-slow.log # Overall: 11.61k total, 462 unique, 0.07 QPS, 0.02x concurrency _________ # Time range: 2014-02-17 13:18:13 to 2014-02-19 12:59:52 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 3519s 100ms 24s 303ms 501ms 963ms 163ms # Lock time 1s 0 153ms 110us 167us 2ms 36us # Rows sent 2.01M 0 430.09k 181.82 0.99 6.71k 0 # Rows examine 36.68M 0 430.09k 3.24k 107.34 19.77k 0.99 # Query size 18.32M 6 58.06k 1.62k 6.63k 4.61k 124.25 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ====== ===== ============== # 1 0x11F8C8E8FCD8E300 450.3709 12.8% 1668 0.2700 3.57 UPDATE session # 2 0x380EE625009E26A1 345.6335 9.8% 866 0.3991 6.69 INSERT t?vzh_session # 3 0x88800186FD4B8B8B 251.3790 7.1% 1407 0.1787 0.46 UPDATE dko?i_session # 4 0x55563D0553F47299 216.3399 6.1% 1212 0.1785 0.05 UPDATE session # 5 0x50B012B84A236154 210.1844 6.0% 252 0.8341 4.21 UPDATE jos_session # 6 0xB2B39EC6F304DBC6 207.7586 5.9% 373 0.5570 9.65 DELETE t?vzh_session # 7 0xFBC33BE3D16F31B6 203.1360 5.8% 425 0.4780 0.14 SELECT u?lgq_k?_comments # 8 0xDB8EC7592A6CE3B5 196.8510 5.6% 849 0.2319 0.99 INSERT dko?i_session # 9 0xA916BBE720639679 158.1094 4.5% 136 1.1626 5.90 UPDATE jos_docman # 10 0xF1824AF3AF04444E 113.9175 3.2% 334 0.3411 5.08 DELETE dko?i_session # 11 0xFACF4A388DB9ADC6 104.4848 3.0% 543 0.1924 2.67 UPDATE dko?i_content # 12 0x3B9C6FE11E91628B 98.5919 2.8% 473 0.2084 1.95 UPDATE t?vzh_session # 13 0x05872ECC22546540 74.9624 2.1% 75 0.9995 2.15 DELETE jos_session # 14 0x52B71FB04AF3BC23 52.2180 1.5% 233 0.2241 0.58 UPDATE t?vzh_redirect_links # 15 0x0B887BC35EC09BAD 35.3063 1.0% 231 0.1528 0.02 UPDATE log_users # 16 0x7194135125B38CDE 29.8269 0.8% 12 2.4856 4.96 SELECT information_schema.tables # 17 0x7481BC1F9F3D893B 29.0353 0.8% 56 0.5185 6.28 INSERT jos_session # 18 0xC7491367E5334C98 22.4719 0.6% 26 0.8643 5.70 INSERT lw?_session # 19 0x8AE5000CAF43D53F 21.3816 0.6% 25 0.8553 7.17 INSERT UPDATE wp_options # 20 0x67A347A2812914DF 19.1429 0.5% 26 0.7363 3.24 SELECT u?lgq_k?_comments # 21 0xFE648E9166EAEC3F 14.9131 0.4% 14 1.0652 1.00 CREATE TABLE cp_kronolith_sharesng # 29 0x5BAB3AD0459A8897 10.7339 0.3% 14 0.7667 0.21 CREATE TABLE cp_turba_sharesng # MISC 0xMISC 652.1674 18.5% 2357 0.2767 0.0 <440 ITEMS> # Query 1: 0.01 QPS, 0.00x concurrency, ID 0x11F8C8E8FCD8E300 at byte 11965150 # This item is included in the report because it matches --limit. # Scores: V/M = 3.57 # Time range: 2014-02-17 13:18:13 to 2014-02-19 12:59:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 14 1668 # Exec time 12 450s 100ms 20s 270ms 339ms 982ms 171ms # Lock time 5 69ms 19us 150us 41us 52us 7us 40us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 1.63k 1 1 1 1 0 1 # Query size 0 148.23k 91 91 91 91 0 91 # String: # Databases roundcube # Hosts localhost # Users roundcube # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # # Tables # SHOW TABLE STATUS FROM `roundcube` LIKE 'session'\G # SHOW CREATE TABLE `roundcube`.`session`\G UPDATE session SET changed='2014-02-19 04:13:29' WHERE sess_id='bvc58kua3p8m2urjh8t92gq680'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select changed='2014-02-19 04:13:29' from session where sess_id='bvc58kua3p8m2urjh8t92gq680'\G # Query 2: 0.01 QPS, 0.00x concurrency, ID 0x380EE625009E26A1 at byte 7853457 # This item is included in the report because it matches --limit. # Scores: V/M = 6.69 # Time range: 2014-02-17 13:29:54 to 2014-02-19 12:56:38 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 7 866 # Exec time 9 346s 100ms 24s 399ms 477ms 2s 155ms # Lock time 1 18ms 13us 63us 20us 28us 4us 18us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 0 105.71k 125 125 125 125 0 125 # String: # Databases radioref_2013 # Hosts localhost # Users radioref_cesard # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # # Tables # SHOW TABLE STATUS FROM `radioref_2013` LIKE 't6vzh_session'\G # SHOW CREATE TABLE `radioref_2013`.`t6vzh_session`\G INSERT INTO `t6vzh_session` (`session_id`, `client_id`, `time`) VALUES ('8728dfe6dee33a20ecbbc4d6193658dd', 0, '1392720812')\G # Query 3: 0.01 QPS, 0.00x concurrency, ID 0x88800186FD4B8B8B at byte 11850707 # This item is included in the report because it matches --limit. # Scores: V/M = 0.46 # Time range: 2014-02-17 13:19:20 to 2014-02-19 12:42:24 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 12 1407 # Exec time 7 251s 100ms 10s 179ms 293ms 286ms 148ms # Lock time 13 168ms 27us 424us 119us 247us 90us 60us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 1.37k 1 1 1 1 0 1 # Query size 66 12.21M 1.01k 22.56k 8.89k 22.45k 9.10k 2.06k # String: # Databases radioref_presi2013 # Hosts localhost # Users radioref_2013 # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # # Tables # SHOW TABLE STATUS FROM `radioref_presi2013` LIKE 'dko1i_session'\G # SHOW CREATE TABLE `radioref_presi2013`.`dko1i_session`\G UPDATE `dko1i_session` SET `data` = '__default|a:8:{s:15:\"session.counter\";i:1;s:19:\"session.timer.start\";i:1392806436;s:18:\"session.timer.last\";i:1392806436;s:17:\"session.timer.now\";i:1392806436;s:22:\"session.client.browser\";s:11:\"Mozilla/4.0\";s:8:\"registry\";O:9:\"JRegistry\":1:{s:7:\"\0*\0data\";O:8:\"stdClass\":0:{}}s:4:\"user\";O:5:\"JUser\":25:{s:9:\"\0*\0isRoot\";N;s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:6:\"groups\";a:0:{}s:5:\"guest\";i:1;s:13:\"lastResetTime\";N;s:10:\"resetCount\";N;s:10:\"\0*\0_params\";O:9:\"JRegistry\":1:{s:7:\"\0*\0data\";O:8:\"stdClass\":0:{}}s:14:\"\0*\0_authGroups\";N;s:14:\"\0*\0_authLevels\";a:2:{i:0;i:1;i:1;i:1;}s:15:\"\0*\0_authActions\";N;s:12:\"\0*\0_errorMsg\";N;s:10:\"\0*\0_errors\";a:0:{}s:3:\"aid\";i:0;}s:13:\"session.token\";s:32:\"49127bb8ec7b48f158d3ab4bb11864d7\";}' , `time` = '1392806437' WHERE `session_id` = 'f28913be8e51c90a5051d5c0f418547b'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `data` = '__default|a:8:{s:15:\"session.counter\";i:1;s:19:\"session.timer.start\";i:1392806436;s:18:\"session.timer.last\";i:1392806436;s:17:\"session.timer.now\";i:1392806436;s:22:\"session.client.browser\";s:11:\"Mozilla/4.0\";s:8:\"registry\";O:9:\"JRegistry\":1:{s:7:\"\0*\0data\";O:8:\"stdClass\":0:{}}s:4:\"user\";O:5:\"JUser\":25:{s:9:\"\0*\0isRoot\";N;s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:6:\"groups\";a:0:{}s:5:\"guest\";i:1;s:13:\"lastResetTime\";N;s:10:\"resetCount\";N;s:10:\"\0*\0_params\";O:9:\"JRegistry\":1:{s:7:\"\0*\0data\";O:8:\"stdClass\":0:{}}s:14:\"\0*\0_authGroups\";N;s:14:\"\0*\0_authLevels\";a:2:{i:0;i:1;i:1;i:1;}s:15:\"\0*\0_authActions\";N;s:12:\"\0*\0_errorMsg\";N;s:10:\"\0*\0_errors\";a:0:{}s:3:\"aid\";i:0;}s:13:\"session.token\";s:32:\"49127bb8ec7b48f158d3ab4bb11864d7\";}' , `time` = '1392806437' from `dko1i_session` where `session_id` = 'f28913be8e51c90a5051d5c0f418547b'\G # Query 4: 0.01 QPS, 0.00x concurrency, ID 0x55563D0553F47299 at byte 8830222 # This item is included in the report because it matches --limit. # Scores: V/M = 0.05 # Time range: 2014-02-17 13:19:16 to 2014-02-19 12:21:34 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 10 1212 # Exec time 6 216s 100ms 1s 178ms 293ms 91ms 155ms # Lock time 5 73ms 25us 168us 60us 80us 12us 57us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 1.18k 1 1 1 1 0 1 # Query size 14 2.70M 1.15k 7.13k 2.28k 4.49k 993.09 1.86k # String: # Databases roundcube # Hosts localhost # Users roundcube # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `roundcube` LIKE 'session'\G # SHOW CREATE TABLE `roundcube`.`session`\G UPDATE session SET vars='bGFuZ3VhZ2V8czo1OiJlc19FUyI7c2tpbnxzOjU6ImxhcnJ5IjtpbWFwX25hbWVzcGFjZXxhOjQ6e3M6ODoicGVyc29uYWwiO2E6MTp7aTowO2E6Mjp7aTowO3M6NjoiSU5CT1guIjtpOjE7czoxOiIuIjt9fXM6NToib3RoZXIiO047czo2OiJzaGFyZWQiO047czo2OiJwcmVmaXgiO3M6NjoiSU5CT1guIjt9aW1hcF9kZWxpbWl0ZXJ8czoxOiIuIjt1c2VyX2lkfHM6MToiNiI7dXNlcm5hbWV8czoxODoidHZibGFzdEB0dmJsYXN0LnR2IjtzdG9yYWdlX2hvc3R8czo5OiJsb2NhbGhvc3QiO3N0b3JhZ2VfcG9ydHxpOjE0MztzdG9yYWdlX3NzbHxOO3Bhc3N3b3JkfHM6MzI6IlV2TTVuVTVwZFVOS0wvUnZJdERSYWN1alE3WHBzcG51Ijtsb2dpbl90aW1lfGk6MTM5MjczMTgzMzt0YXNrfHM6NDoibWFpbCI7aW1hcF9ob3N0fHM6OToibG9jYWxob3N0IjttYm94fHM6NToiSU5CT1giO3NvcnRfY29sfHM6MDoiIjtzb3J0X29yZGVyfHM6NDoiREVTQyI7U1RPUkFHRV9USFJFQUR8YTozOntpOjA7czoxMDoiUkVGRVJFTkNFUyI7aToxO3M6NDoiUkVGUyI7aToyO3M6MTQ6Ik9SREVSRURTVUJKRUNUIjt9U1RPUkFHRV9RVU9UQXxiOjE7U1RPUkFHRV9MSVNULUVYVEVOREVEfGI6MTtxdW90YV9kaXNwbGF5fHM6NDoidGV4dCI7bGlzdF9hdHRyaWJ8YTo0OntzOjQ6Im5hbWUiO3M6ODoibWVzc2FnZXMiO3M6MjoiaWQiO3M6MTE6Im1lc3NhZ2VsaXN0IjtzOjU6ImNsYXNzIjtzOjI0OiJyZWNvcmRzLXRhYmxlIHNvcnRoZWFkZXIiO3M6MTU6Im9wdGlvbnNtZW51aWNvbiI7czo0OiJ0cnVlIjt9c2tpbl9wYXRofHM6MTE6InNraW5zL2xhcnJ5Ijtmb2xkZXJzfGE6NTp7czo1OiJJTkJPWCI7YToyOntzOjM6ImNudCI7aTo0NjI7czo2OiJtYXh1aWQiO2k6NTExODt9czoxMjoiSU5CT1guRHJhZnRzIjthOjI6e3M6MzoiY250IjtpOjU7czo2OiJtYXh1aWQiO2k6OTY7fXM6MTA6IklOQk9YLlNlbnQiO2E6Mjp7czozOiJjbnQiO2k6Nzg7czo2OiJtYXh1aWQiO2k6MTE2MTt9czoxMDoiSU5CT1guSnVuayI7YToyOntzOjM6ImNudCI7aTowO3M6NjoibWF4dWlkIjtpOjA7fXM6MTE6IklOQk9YLlRyYXNoIjthOjI6e3M6MzoiY250IjtpOjMxMjg7czo2OiJtYXh1aWQiO2k6NTY3Njt9fXVuc2Vlbl9jb3VudHxhOjU6e3M6NToiSU5CT1giO2k6MDtzOjEyOiJJTkJPWC5EcmFmdHMiO2k6MDtzOjEwOiJJTkJPWC5TZW50IjtpOjA7czoxMDoiSU5CT1guSnVuayI7aTowO3M6MTE6IklOQk9YLlRyYXNoIjtpOjA7fWNvbXBvc2VfZGF0YV8xNDc5MDg3NjkwNTMwMzY2YmY4ZThjOXxhOjM6e3M6MjoiaWQiO3M6MjM6IjE0NzkwODc2OTA1MzAzNjZiZjhlOGM5IjtzOjU6InBhcmFtIjthOjI6e3M6NDoibWJveCI7czo1OiJJTkJPWCI7czo5OiJzZW50X21ib3giO3M6MTA6IklOQk9YLlNlbnQiO31zOjc6Im1haWxib3giO3M6NToiSU5CT1giO31TVE9SQUdFX0FDTHxiOjA7YWRkcmVzc2Jvb2tzX2NvdW50fGk6MTthZGRyZXNzYm9va3NfY291bnRfd3JpdGVhYmxlfGk6MTtjb250YWN0Y291bnRkaXNwbGF5fHM6MTE6ImZyb210b3Nob3J0Ijticm93c2VyX2NhcHN8YTozOntzOjM6InBkZiI7czoxOiIxIjtzOjU6ImZsYXNoIjtzOjE6IjEiO3M6MzoidGlmIjtzOjE6IjAiO31zYWZlX21lc3NhZ2VzfGE6Mjp7czoxMDoiSU5CT1g6NTA5MSI7YjowO3M6MTA6IklOQk9YOjUxMTgiO2I6MTt9d3JpdGVhYmxlX2Fib29rfGI6MTtwbHVnaW5zfGE6MTp7czoyMjoiZmlsZXN5c3RlbV9hdHRhY2htZW50cyI7YTowOnt9fWNvbXBvc2VfZGF0YV8xNzgxMjkyMjE5NTMwMzcxNDdhM2NkMnxhOjM6e3M6MjoiaWQiO3M6MjM6IjE3ODEyOTIyMTk1MzAzNzE0N2EzY2QyIjtzOjU6InBhcmFtIjthOjI6e3M6NDoibWJveCI7czo1OiJJTkJPWCI7czo5OiJzZW50X21ib3giO3M6MTA6IklOQk9YLlNlbnQiO31zOjc6Im1haWxib3giO3M6NToiSU5CT1giO30=', changed='2014-02-18 07:43:04' WHERE sess_id='fn51v601durbaulbb6jmg7q225'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select vars='bGFuZ3VhZ2V8czo1OiJlc19FUyI7c2tpbnxzOjU6ImxhcnJ5IjtpbWFwX25hbWVzcGFjZXxhOjQ6e3M6ODoicGVyc29uYWwiO2E6MTp7aTowO2E6Mjp7aTowO3M6NjoiSU5CT1guIjtpOjE7czoxOiIuIjt9fXM6NToib3RoZXIiO047czo2OiJzaGFyZWQiO047czo2OiJwcmVmaXgiO3M6NjoiSU5CT1guIjt9aW1hcF9kZWxpbWl0ZXJ8czoxOiIuIjt1c2VyX2lkfHM6MToiNiI7dXNlcm5hbWV8czoxODoidHZibGFzdEB0dmJsYXN0LnR2IjtzdG9yYWdlX2hvc3R8czo5OiJsb2NhbGhvc3QiO3N0b3JhZ2VfcG9ydHxpOjE0MztzdG9yYWdlX3NzbHxOO3Bhc3N3b3JkfHM6MzI6IlV2TTVuVTVwZFVOS0wvUnZJdERSYWN1alE3WHBzcG51Ijtsb2dpbl90aW1lfGk6MTM5MjczMTgzMzt0YXNrfHM6NDoibWFpbCI7aW1hcF9ob3N0fHM6OToibG9jYWxob3N0IjttYm94fHM6NToiSU5CT1giO3NvcnRfY29sfHM6MDoiIjtzb3J0X29yZGVyfHM6NDoiREVTQyI7U1RPUkFHRV9USFJFQUR8YTozOntpOjA7czoxMDoiUkVGRVJFTkNFUyI7aToxO3M6NDoiUkVGUyI7aToyO3M6MTQ6Ik9SREVSRURTVUJKRUNUIjt9U1RPUkFHRV9RVU9UQXxiOjE7U1RPUkFHRV9MSVNULUVYVEVOREVEfGI6MTtxdW90YV9kaXNwbGF5fHM6NDoidGV4dCI7bGlzdF9hdHRyaWJ8YTo0OntzOjQ6Im5hbWUiO3M6ODoibWVzc2FnZXMiO3M6MjoiaWQiO3M6MTE6Im1lc3NhZ2VsaXN0IjtzOjU6ImNsYXNzIjtzOjI0OiJyZWNvcmRzLXRhYmxlIHNvcnRoZWFkZXIiO3M6MTU6Im9wdGlvbnNtZW51aWNvbiI7czo0OiJ0cnVlIjt9c2tpbl9wYXRofHM6MTE6InNraW5zL2xhcnJ5Ijtmb2xkZXJzfGE6NTp7czo1OiJJTkJPWCI7YToyOntzOjM6ImNudCI7aTo0NjI7czo2OiJtYXh1aWQiO2k6NTExODt9czoxMjoiSU5CT1guRHJhZnRzIjthOjI6e3M6MzoiY250IjtpOjU7czo2OiJtYXh1aWQiO2k6OTY7fXM6MTA6IklOQk9YLlNlbnQiO2E6Mjp7czozOiJjbnQiO2k6Nzg7czo2OiJtYXh1aWQiO2k6MTE2MTt9czoxMDoiSU5CT1guSnVuayI7YToyOntzOjM6ImNudCI7aTowO3M6NjoibWF4dWlkIjtpOjA7fXM6MTE6IklOQk9YLlRyYXNoIjthOjI6e3M6MzoiY250IjtpOjMxMjg7czo2OiJtYXh1aWQiO2k6NTY3Njt9fXVuc2Vlbl9jb3VudHxhOjU6e3M6NToiSU5CT1giO2k6MDtzOjEyOiJJTkJPWC5EcmFmdHMiO2k6MDtzOjEwOiJJTkJPWC5TZW50IjtpOjA7czoxMDoiSU5CT1guSnVuayI7aTowO3M6MTE6IklOQk9YLlRyYXNoIjtpOjA7fWNvbXBvc2VfZGF0YV8xNDc5MDg3NjkwNTMwMzY2YmY4ZThjOXxhOjM6e3M6MjoiaWQiO3M6MjM6IjE0NzkwODc2OTA1MzAzNjZiZjhlOGM5IjtzOjU6InBhcmFtIjthOjI6e3M6NDoibWJveCI7czo1OiJJTkJPWCI7czo5OiJzZW50X21ib3giO3M6MTA6IklOQk9YLlNlbnQiO31zOjc6Im1haWxib3giO3M6NToiSU5CT1giO31TVE9SQUdFX0FDTHxiOjA7YWRkcmVzc2Jvb2tzX2NvdW50fGk6MTthZGRyZXNzYm9va3NfY291bnRfd3JpdGVhYmxlfGk6MTtjb250YWN0Y291bnRkaXNwbGF5fHM6MTE6ImZyb210b3Nob3J0Ijticm93c2VyX2NhcHN8YTozOntzOjM6InBkZiI7czoxOiIxIjtzOjU6ImZsYXNoIjtzOjE6IjEiO3M6MzoidGlmIjtzOjE6IjAiO31zYWZlX21lc3NhZ2VzfGE6Mjp7czoxMDoiSU5CT1g6NTA5MSI7YjowO3M6MTA6IklOQk9YOjUxMTgiO2I6MTt9d3JpdGVhYmxlX2Fib29rfGI6MTtwbHVnaW5zfGE6MTp7czoyMjoiZmlsZXN5c3RlbV9hdHRhY2htZW50cyI7YTowOnt9fWNvbXBvc2VfZGF0YV8xNzgxMjkyMjE5NTMwMzcxNDdhM2NkMnxhOjM6e3M6MjoiaWQiO3M6MjM6IjE3ODEyOTIyMTk1MzAzNzE0N2EzY2QyIjtzOjU6InBhcmFtIjthOjI6e3M6NDoibWJveCI7czo1OiJJTkJPWCI7czo5OiJzZW50X21ib3giO3M6MTA6IklOQk9YLlNlbnQiO31zOjc6Im1haWxib3giO3M6NToiSU5CT1giO30=', changed='2014-02-18 07:43:04' from session where sess_id='fn51v601durbaulbb6jmg7q225'\G # Query 5: 0.00 QPS, 0.00x concurrency, ID 0x50B012B84A236154 at byte 11949463 # This item is included in the report because it matches --limit. # Scores: V/M = 4.21 # Time range: 2014-02-17 13:21:18 to 2014-02-19 12:57:39 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 252 # Exec time 5 210s 100ms 13s 834ms 4s 2s 148ms # Lock time 3 39ms 24us 29ms 155us 60us 2ms 38us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 252 1 1 1 1 0 1 # Query size 2 425.11k 467 4.50k 1.69k 2.06k 443.64 1.46k # String: # Databases phr_web # Hosts localhost # Users phr_web # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ############ # 10s+ # # Tables # SHOW TABLE STATUS FROM `phr_web` LIKE 'jos_session'\G # SHOW CREATE TABLE `phr_web`.`jos_session`\G UPDATE `jos_session` SET `time`='1392808026',`userid`='0',`usertype`='',`username`='',`gid`='0',`guest`='1',`client_id`='0',`data`='__default|a:8:{s:15:\"session.counter\";i:15;s:19:\"session.timer.start\";i:1392806976;s:18:\"session.timer.last\";i:1392807654;s:17:\"session.timer.now\";i:1392807998;s:22:\"session.client.browser\";s:162:\"Mozilla/5.0 (Linux; Android 4.3; es-us; SAMSUNG GT-I9500 Build/JSS15J) AppleWebKit/537.36 (KHTML, like Gecko) Version/1.5 Chrome/28.0.1500.94 Mobile Safari/537.36\";s:8:\"registry\";O:9:\"JRegistry\":3:{s:17:\"_defaultNameSpace\";s:7:\"session\";s:9:\"_registry\";a:1:{s:7:\"session\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}}s:7:\"_errors\";a:0:{}}s:4:\"user\";O:5:\"JUser\":19:{s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:3:\"gid\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:3:\"aid\";i:0;s:5:\"guest\";i:1;s:7:\"_params\";O:10:\"JParameter\":7:{s:4:\"_raw\";s:0:\"\";s:4:\"_xml\";N;s:9:\"_elements\";a:0:{}s:12:\"_elementPath\";a:1:{i:0;s:61:\"/home/phr/public_html/libraries/joomla/html/parameter/element\";}s:17:\"_defaultNameSpace\";s:8:\"_default\";s:9:\"_registry\";a:1:{s:8:\"_default\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}}s:7:\"_errors\";a:0:{}}s:9:\"_errorMsg\";N;s:7:\"_errors\";a:0:{}}s:13:\"session.token\";s:32:\"0ca88c8cdda314b76915db297eaa1cf9\";}' WHERE session_id='8f95adc85360af92b1a66045fb019da4'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `time`='1392808026',`userid`='0',`usertype`='',`username`='',`gid`='0',`guest`='1',`client_id`='0',`data`='__default|a:8:{s:15:\"session.counter\";i:15;s:19:\"session.timer.start\";i:1392806976;s:18:\"session.timer.last\";i:1392807654;s:17:\"session.timer.now\";i:1392807998;s:22:\"session.client.browser\";s:162:\"Mozilla/5.0 (Linux; Android 4.3; es-us; SAMSUNG GT-I9500 Build/JSS15J) AppleWebKit/537.36 (KHTML, like Gecko) Version/1.5 Chrome/28.0.1500.94 Mobile Safari/537.36\";s:8:\"registry\";O:9:\"JRegistry\":3:{s:17:\"_defaultNameSpace\";s:7:\"session\";s:9:\"_registry\";a:1:{s:7:\"session\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}}s:7:\"_errors\";a:0:{}}s:4:\"user\";O:5:\"JUser\":19:{s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:3:\"gid\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:3:\"aid\";i:0;s:5:\"guest\";i:1;s:7:\"_params\";O:10:\"JParameter\":7:{s:4:\"_raw\";s:0:\"\";s:4:\"_xml\";N;s:9:\"_elements\";a:0:{}s:12:\"_elementPath\";a:1:{i:0;s:61:\"/home/phr/public_html/libraries/joomla/html/parameter/element\";}s:17:\"_defaultNameSpace\";s:8:\"_default\";s:9:\"_registry\";a:1:{s:8:\"_default\";a:1:{s:4:\"data\";O:8:\"stdClass\":0:{}}}s:7:\"_errors\";a:0:{}}s:9:\"_errorMsg\";N;s:7:\"_errors\";a:0:{}}s:13:\"session.token\";s:32:\"0ca88c8cdda314b76915db297eaa1cf9\";}' from `jos_session` where session_id='8f95adc85360af92b1a66045fb019da4'\G # Query 6: 0.00 QPS, 0.00x concurrency, ID 0xB2B39EC6F304DBC6 at byte 11953624 # This item is included in the report because it matches --limit. # Scores: V/M = 9.65 # Time range: 2014-02-17 13:34:49 to 2014-02-19 12:56:43 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 3 373 # Exec time 5 208s 101ms 24s 557ms 640ms 2s 155ms # Lock time 0 6ms 10us 57us 17us 26us 4us 15us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 1.37k 1 23 3.76 13.83 4.38 1.96 # Query size 0 20.40k 56 56 56 56 0 56 # String: # Databases radioref_2013 # Hosts localhost # Users radioref_cesard # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # # Tables # SHOW TABLE STATUS FROM `radioref_2013` LIKE 't6vzh_session'\G # SHOW CREATE TABLE `radioref_2013`.`t6vzh_session`\G DELETE FROM `t6vzh_session` WHERE `time` < '1392807125'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select * from `t6vzh_session` WHERE `time` < '1392807125'\G # Query 7: 0.00 QPS, 0.00x concurrency, ID 0xFBC33BE3D16F31B6 at byte 7621181 # This item is included in the report because it matches --limit. # Scores: V/M = 0.14 # Time range: 2014-02-17 13:18:21 to 2014-02-19 12:58:05 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 3 425 # Exec time 5 203s 429ms 6s 478ms 477ms 259ms 455ms # Lock time 0 13ms 20us 85us 29us 44us 7us 26us # Rows sent 0 425 1 1 1 1 0 1 # Rows examine 79 29.03M 7.93k 138.29k 69.94k 136.54k 46.79k 62.55k # Query size 0 29.05k 70 70 70 70 0 70 # String: # Databases dbspma_innova # Hosts localhost # Users dbspma_innova # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `dbspma_innova` LIKE 'u9lgq_k2_comments'\G # SHOW CREATE TABLE `dbspma_innova`.`u9lgq_k2_comments`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT COUNT(*) FROM u9lgq_k2_comments WHERE itemID=23 AND published=1\G # Query 8: 0.00 QPS, 0.00x concurrency, ID 0xDB8EC7592A6CE3B5 at byte 7924478 # This item is included in the report because it matches --limit. # Scores: V/M = 0.99 # Time range: 2014-02-17 13:22:22 to 2014-02-19 12:54:08 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 7 849 # Exec time 5 197s 100ms 7s 232ms 339ms 480ms 155ms # Lock time 1 17ms 13us 53us 19us 25us 3us 18us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 0 103.64k 125 125 125 125 0 125 # String: # Databases radioref_presi2013 # Hosts localhost # Users radioref_2013 # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `radioref_presi2013` LIKE 'dko1i_session'\G # SHOW CREATE TABLE `radioref_presi2013`.`dko1i_session`\G INSERT INTO `dko1i_session` (`session_id`, `client_id`, `time`) VALUES ('2f51ef0d0fad749c87b21eff32f87c1a', 0, '1392721567')\G # Query 9: 0.00 QPS, 0.00x concurrency, ID 0xA916BBE720639679 at byte 11964907 # This item is included in the report because it matches --limit. # Scores: V/M = 5.90 # Time range: 2014-02-17 13:21:18 to 2014-02-19 12:51:02 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 136 # Exec time 4 158s 100ms 16s 1s 5s 3s 189ms # Lock time 0 4ms 18us 56us 29us 47us 8us 25us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 136 1 1 1 1 0 1 # Query size 0 7.57k 56 57 56.98 56.92 0.40 56.92 # String: # Databases phr_web # Hosts localhost # Users phr_web # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ############## # 10s+ # # Tables # SHOW TABLE STATUS FROM `phr_web` LIKE 'jos_docman'\G # SHOW CREATE TABLE `phr_web`.`jos_docman`\G UPDATE jos_docman SET dmcounter=dmcounter+1 WHERE id=1724\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select dmcounter=dmcounter+1 from jos_docman where id=1724\G # Query 10: 0.00 QPS, 0.00x concurrency, ID 0xF1824AF3AF04444E at byte 11937349 # This item is included in the report because it matches --limit. # Scores: V/M = 5.08 # Time range: 2014-02-17 13:19:15 to 2014-02-19 12:39:19 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 334 # Exec time 3 114s 101ms 17s 341ms 374ms 1s 148ms # Lock time 0 6ms 12us 43us 17us 28us 5us 15us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 1.46k 1 80 4.47 14.52 8.67 1.96 # Query size 0 18.27k 56 56 56 56 0 56 # String: # Databases radioref_presi2013 # Hosts localhost # Users radioref_2013 # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # # Tables # SHOW TABLE STATUS FROM `radioref_presi2013` LIKE 'dko1i_session'\G # SHOW CREATE TABLE `radioref_presi2013`.`dko1i_session`\G DELETE FROM `dko1i_session` WHERE `time` < '1392805511'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select * from `dko1i_session` WHERE `time` < '1392805511'\G # Query 11: 0.00 QPS, 0.00x concurrency, ID 0xFACF4A388DB9ADC6 at byte 7925346 # This item is included in the report because it matches --limit. # Scores: V/M = 2.67 # Time range: 2014-02-17 13:22:39 to 2014-02-19 12:46:09 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 4 543 # Exec time 2 104s 100ms 17s 192ms 241ms 716ms 141ms # Lock time 1 19ms 22us 74us 34us 44us 5us 31us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 543 1 1 1 1 0 1 # Query size 0 29.17k 55 55 55 55 0 55 # String: # Databases radioref_presi2013 # Hosts localhost # Users radioref_2013 # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # # Tables # SHOW TABLE STATUS FROM `radioref_presi2013` LIKE 'dko1i_content'\G # SHOW CREATE TABLE `radioref_presi2013`.`dko1i_content`\G UPDATE dko1i_content SET hits = hits + 1 WHERE id = 103\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select hits = hits + 1 from dko1i_content where id = 103\G # Query 12: 0.00 QPS, 0.00x concurrency, ID 0x3B9C6FE11E91628B at byte 8084105 # This item is included in the report because it matches --limit. # Scores: V/M = 1.95 # Time range: 2014-02-17 13:27:11 to 2014-02-19 12:56:49 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 4 473 # Exec time 2 99s 100ms 11s 208ms 323ms 637ms 134ms # Lock time 1 24ms 28us 114us 50us 69us 12us 47us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 473 1 1 1 1 0 1 # Query size 3 657.75k 1.01k 3.81k 1.39k 3.19k 668.85 1.09k # String: # Databases radioref_2013 # Hosts localhost # Users radioref_cesard # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # # Tables # SHOW TABLE STATUS FROM `radioref_2013` LIKE 't6vzh_session'\G # SHOW CREATE TABLE `radioref_2013`.`t6vzh_session`\G UPDATE `t6vzh_session` SET `data` = '__default|a:7:{s:15:\"session.counter\";i:1;s:19:\"session.timer.start\";i:1392725506;s:18:\"session.timer.last\";i:1392725506;s:17:\"session.timer.now\";i:1392725506;s:22:\"session.client.browser\";s:96:\"Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; chromeframe/19.0.1084.52)\";s:8:\"registry\";O:9:\"JRegistry\":1:{s:7:\"\0*\0data\";O:8:\"stdClass\":0:{}}s:4:\"user\";O:5:\"JUser\":25:{s:9:\"\0*\0isRoot\";N;s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:6:\"groups\";a:0:{}s:5:\"guest\";i:1;s:13:\"lastResetTime\";N;s:10:\"resetCount\";N;s:10:\"\0*\0_params\";O:9:\"JRegistry\":1:{s:7:\"\0*\0data\";O:8:\"stdClass\":0:{}}s:14:\"\0*\0_authGroups\";N;s:14:\"\0*\0_authLevels\";a:2:{i:0;i:1;i:1;i:1;}s:15:\"\0*\0_authActions\";N;s:12:\"\0*\0_errorMsg\";N;s:10:\"\0*\0_errors\";a:0:{}s:3:\"aid\";i:0;}}' , `time` = '1392725512' WHERE `session_id` = '61984e5e485126078393bce5c562dbeb'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `data` = '__default|a:7:{s:15:\"session.counter\";i:1;s:19:\"session.timer.start\";i:1392725506;s:18:\"session.timer.last\";i:1392725506;s:17:\"session.timer.now\";i:1392725506;s:22:\"session.client.browser\";s:96:\"Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; chromeframe/19.0.1084.52)\";s:8:\"registry\";O:9:\"JRegistry\":1:{s:7:\"\0*\0data\";O:8:\"stdClass\":0:{}}s:4:\"user\";O:5:\"JUser\":25:{s:9:\"\0*\0isRoot\";N;s:2:\"id\";i:0;s:4:\"name\";N;s:8:\"username\";N;s:5:\"email\";N;s:8:\"password\";N;s:14:\"password_clear\";s:0:\"\";s:8:\"usertype\";N;s:5:\"block\";N;s:9:\"sendEmail\";i:0;s:12:\"registerDate\";N;s:13:\"lastvisitDate\";N;s:10:\"activation\";N;s:6:\"params\";N;s:6:\"groups\";a:0:{}s:5:\"guest\";i:1;s:13:\"lastResetTime\";N;s:10:\"resetCount\";N;s:10:\"\0*\0_params\";O:9:\"JRegistry\":1:{s:7:\"\0*\0data\";O:8:\"stdClass\":0:{}}s:14:\"\0*\0_authGroups\";N;s:14:\"\0*\0_authLevels\";a:2:{i:0;i:1;i:1;i:1;}s:15:\"\0*\0_authActions\";N;s:12:\"\0*\0_errorMsg\";N;s:10:\"\0*\0_errors\";a:0:{}s:3:\"aid\";i:0;}}' , `time` = '1392725512' from `t6vzh_session` where `session_id` = '61984e5e485126078393bce5c562dbeb'\G # Query 13: 0.00 QPS, 0.00x concurrency, ID 0x05872ECC22546540 at byte 11670294 # This item is included in the report because it matches --limit. # Scores: V/M = 2.15 # Time range: 2014-02-17 14:42:08 to 2014-02-19 12:45:48 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 75 # Exec time 2 75s 101ms 7s 999ms 4s 1s 141ms # Lock time 0 1ms 13us 47us 18us 30us 5us 16us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 146 1 13 1.95 4.96 1.82 0.99 # Query size 0 3.88k 53 53 53 53 0 53 # String: # Databases phr_web # Hosts localhost # Users phr_web # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ####################### # 10s+ # Tables # SHOW TABLE STATUS FROM `phr_web` LIKE 'jos_session'\G # SHOW CREATE TABLE `phr_web`.`jos_session`\G DELETE FROM jos_session WHERE ( time < '1392800295' )\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select * from jos_session WHERE ( time < '1392800295' )\G # Query 14: 0.00 QPS, 0.00x concurrency, ID 0x52B71FB04AF3BC23 at byte 11911870 # This item is included in the report because it matches --limit. # Scores: V/M = 0.58 # Time range: 2014-02-17 13:27:10 to 2014-02-19 12:54:16 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 233 # Exec time 1 52s 100ms 4s 224ms 339ms 362ms 148ms # Lock time 0 4ms 13us 44us 19us 23us 3us 18us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 233 1 1 1 1 0 1 # Query size 0 15.08k 66 68 66.26 65.89 0 65.89 # String: # Databases radioref_2013 # Hosts localhost # Users radioref_cesard # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `radioref_2013` LIKE 't6vzh_redirect_links'\G # SHOW CREATE TABLE `radioref_2013`.`t6vzh_redirect_links`\G UPDATE `t6vzh_redirect_links` SET `hits` = `hits` + 1 WHERE id = 1\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `hits` = `hits` + 1 from `t6vzh_redirect_links` where id = 1\G # Query 15: 0.08 QPS, 0.01x concurrency, ID 0x0B887BC35EC09BAD at byte 2011478 # This item is included in the report because it matches --limit. # Scores: V/M = 0.02 # Time range: 2014-02-17 16:09:14 to 16:56:43 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 231 # Exec time 1 35s 100ms 487ms 153ms 230ms 50ms 134ms # Lock time 0 4ms 12us 49us 18us 28us 5us 16us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 231 1 1 1 1 0 1 # Query size 0 17.37k 77 77 77 77 0 77 # String: # Databases compraya_blesta # Hosts localhost # Users compraya_blesta # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `compraya_blesta` LIKE 'log_users'\G # SHOW CREATE TABLE `compraya_blesta`.`log_users`\G UPDATE `log_users` SET `date_updated`='2014-02-17 23:09:20' WHERE `id` = '1'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `date_updated`='2014-02-17 23:09:20' from `log_users` where `id` = '1'\G # Query 16: 0.00 QPS, 0.00x concurrency, ID 0x7194135125B38CDE at byte 12015812 # This item is included in the report because it matches --limit. # Scores: V/M = 4.96 # Time range: 2014-02-17 16:30:02 to 2014-02-19 12:30:03 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 12 # Exec time 0 30s 588ms 12s 2s 9s 4s 857ms # Lock time 0 660us 45us 71us 55us 63us 7us 54us # Rows sent 0 840 70 70 70 70 0 70 # Rows examine 0 31.29k 2.58k 2.61k 2.61k 2.50k 0 2.50k # Query size 0 1.48k 126 126 126 126 0 126 # String: # Databases mysql # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s #################################### # 10s+ ######### # Tables # SHOW TABLE STATUS FROM `information_schema` LIKE 'tables'\G # SHOW CREATE TABLE `information_schema`.`tables`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA\G # Query 17: 0.00 QPS, 0.00x concurrency, ID 0x7481BC1F9F3D893B at byte 11961475 # This item is included in the report because it matches --limit. # Scores: V/M = 6.28 # Time range: 2014-02-17 13:42:19 to 2014-02-19 12:27:38 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 56 # Exec time 0 29s 100ms 12s 518ms 412ms 2s 116ms # Lock time 0 1ms 17us 47us 21us 28us 4us 20us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 0 8.75k 160 160 160 160 0 160 # String: # Databases phr_web # Hosts localhost # Users phr_web # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ## # 10s+ # # Tables # SHOW TABLE STATUS FROM `phr_web` LIKE 'jos_session'\G # SHOW CREATE TABLE `phr_web`.`jos_session`\G INSERT INTO `jos_session` ( `session_id`,`time`,`username`,`gid`,`guest`,`client_id` ) VALUES ( 'db0c079491562efd62e9b02e0953a16c','1392808204','','0','1','0' )\G # Query 18: 0.00 QPS, 0.00x concurrency, ID 0xC7491367E5334C98 at byte 7893743 # This item is included in the report because it matches --limit. # Scores: V/M = 5.70 # Time range: 2014-02-17 14:17:06 to 2014-02-19 12:07:20 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 26 # Exec time 0 22s 103ms 12s 864ms 1s 2s 155ms # Lock time 0 5ms 14us 5ms 204us 38us 886us 18us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 0 3.17k 125 125 125 125 0 125 # String: # Databases dxcrewco_2013 # Hosts localhost # Users dxcrewco_marco # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ######## # 10s+ ## # Tables # SHOW TABLE STATUS FROM `dxcrewco_2013` LIKE 'lw532_session'\G # SHOW CREATE TABLE `dxcrewco_2013`.`lw532_session`\G INSERT INTO `lw532_session` (`session_id`, `client_id`, `time`) VALUES ('fce2cb949ef9d95c922d2952dbe98710', 0, '1392721056')\G # Query 19: 0.00 QPS, 0.00x concurrency, ID 0x8AE5000CAF43D53F at byte 7865312 # This item is included in the report because it matches --limit. # Scores: V/M = 7.17 # Time range: 2014-02-17 13:28:25 to 2014-02-19 12:58:19 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 25 # Exec time 0 21s 100ms 13s 855ms 2s 2s 134ms # Lock time 0 636us 20us 44us 25us 28us 4us 23us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 0 6.98k 272 412 285.88 271.23 25.38 271.23 # String: # Databases beatflow_p... (24/96%), tvblast_ce... (1/4%) # Hosts localhost # Users beatflow_a... (24/96%), tvblast_ad... (1/4%) # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ##### # 10s+ ## # Tables # SHOW TABLE STATUS FROM `beatflow_panama` LIKE 'wp_options'\G # SHOW CREATE TABLE `beatflow_panama`.`wp_options`\G INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1392720953.1819350719451904296875', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)\G # Query 20: 0.00 QPS, 0.00x concurrency, ID 0x67A347A2812914DF at byte 11640824 # This item is included in the report because it matches --limit. # Scores: V/M = 3.24 # Time range: 2014-02-18 02:01:23 to 2014-02-19 04:25:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 26 # Exec time 0 19s 109ms 6s 736ms 672ms 2s 225ms # Lock time 0 0 0 0 0 0 0 0 # Rows sent 99 1.99M 7 430.09k 78.55k 233.54k 119.45k 25.99k # Rows examine 5 1.99M 7 430.09k 78.55k 233.54k 119.45k 25.99k # Query size 0 1.42k 47 66 55.92 65.89 7.33 56.92 # String: # Databases computer_d... (9/34%), dxcrewco_2... (9/34%)... 6 more # Hosts localhost # Users root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ##### # 10s+ # Tables # SHOW TABLE STATUS FROM `dbspma_innova` LIKE 'u9lgq_k2_comments'\G # SHOW CREATE TABLE `dbspma_innova`.`u9lgq_k2_comments`\G SELECT /*!40001 SQL_NO_CACHE */ * FROM `u9lgq_k2_comments`\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ SELECT /*!40001 SQL_NO_CACHE */ * FROM `u9lgq_k2_comments`\G # Query 21: 0.00 QPS, 0.00x concurrency, ID 0xFE648E9166EAEC3F at byte 11630562 # This item is included in the report because it matches --outliers. # Scores: V/M = 1.00 # Time range: 2014-02-18 02:01:14 to 2014-02-19 04:25:43 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 14 # Exec time 0 15s 508ms 5s 1s 2s 1s 580ms # Lock time 0 0 0 0 0 0 0 0 # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 0 952 68 68 68 68 0 68 # String: # Databases horde # Hosts localhost # Users horde # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ######################### # 10s+ # Tables # SHOW TABLE STATUS FROM `horde` LIKE 'cp_kronolith_sharesng'\G # SHOW CREATE TABLE `horde`.`cp_kronolith_sharesng`\G create temporary table cp_kronolith_sharesng like kronolith_sharesng\G # Query 29: 0.00 QPS, 0.00x concurrency, ID 0x5BAB3AD0459A8897 at byte 11817465 # This item is included in the report because it matches --outliers. # Scores: V/M = 0.21 # Time range: 2014-02-18 02:01:22 to 2014-02-19 04:25:48 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 14 # Exec time 0 11s 477ms 2s 767ms 1s 400ms 609ms # Lock time 0 0 0 0 0 0 0 0 # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 0 840 60 60 60 60 0 60 # String: # Databases horde # Hosts localhost # Users horde # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ################# # 10s+ # Tables # SHOW TABLE STATUS FROM `horde` LIKE 'cp_turba_sharesng'\G # SHOW CREATE TABLE `horde`.`cp_turba_sharesng`\G create temporary table cp_turba_sharesng like turba_sharesng\G
[COLOR="silver">- - - Updated - - - should i add this tmp_table_size (> 16M) max_heap_table_size (> 16M) ? i dont see that in my config right now is missing what do u recommend me to do0 -
what u think about the slow queries report 0 -
It's good, no super slow queries there I won't go into details, since we are not providing "in depth" analysis here on forums, since it would take a lot of time 0 -
u think the problem here are the websites that are not optimized right? because mainly the clients use joomla wordpress, theres no way i can optimize all ? example installing varnish, nginx or litespeed something like that? 0 -
It's not "bad", it's good but there can be better You got many people, many different sites, so in your case results are good They use CMSes, they can install caching plugins etc. 0 -
yeah thats what im talking about maybe i should try varnish cache or litespeed so it caches the slow websites for example? it may or may not work 0 -
It doesn't work this way, each site would have to be individually configured if you use nginx/varnish/litespeed Your users/sites should use cache plugins 0 -
yeah but i mean if clients dont install anything i can configure nginx varnish individually per website it may work right? 0 -
Yes, but it's much more complicated, and you would have to update your varnish/nginx etc config if they make some major changes in their scripts Couse each CMS needs different varnish/nginx setup 0 -
and what do u think about installing mariadb to replace mysql? will make any difference? 0 -
Not much In shared environment, where you don't have access to the code of each site, you won't make it perfect Your current mysql settings are good, You should install Munin WHM plugin, and there you would have graphs with cpu, load, memory, disk usage, in general whole server resources usage 0 -
[quote="thinkbot, post: 1574762"> [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 32M table_open_cache = 2000 sort_buffer_size = 256K read_buffer_size = 128K join_buffer_size = 1M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache_size = 16 query_cache_size = 64M max_connections = 300 local-infile=0 open_files_limit = 16384 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 #skip-networking server-id = 1 innodb_buffer_pool_size = 150M innodb_file_per_table=1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash #safe-updates [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
Hi thinkbot Would it be realistic to halve the values in youir suggested config for a 16GB server? If not, which variables would you suggest should remain the same regardless? Norman0 -
you need to run mysqltuner.pl and post the results here Its more about the data, size of it, structure etc. then goes RAM, it needs to be adjusted to the data 0
Please sign in to leave a comment.
Comments
24 comments