MySQL Memory and Speed Issues - Slow Query Log and MySQLTuner
I have 4Gb and I am positive this is a MySQL issue because i can login via SSH and stop the mysql service and my memory usage drops from 3995mb to 1487mb. I have tried many different my.cnf configurations but nothing changes the memory issue. I finally used the my.cnf below but it is still the same result, MySQL hog until the system comes to a standstill.
I do have a lot of these warnings in my mysql_error log for many users/databases, this is just one.
131029 15:49:07 [Warning] 'user' entry 'lvdb_wpi188@vps.domain.com' ignored in --skip-name-resolve mode.
131029 15:49:07 [Warning] 'db' entry 'lvdb\_wpi188 lvdbuser@vps.domain.com' ignored in --skip-name-resolve mode.
My Server Stats
Total processors: 4
Hard Drive: 250G (Total) 122G (Used)
Name: Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz
Speed: 2000.072 MHz
Cache: 15360 KB
Memory: 4Gb
Current Memory Usage
total used free shared buffers cached
Mem: 4195200 1588776 2606424 0 0 0
-/+ buffers/cache: 1588776 2606424
Swap: 0 0 0
Total: 4195200 1588776 2606424
MySQLMyMonLite Log Results
MySQL-Slow.log Results
MySQLTuner Results
Current my.cnf file
TOP Results
Any help would be greatly appreciated as I am running out of options.
-------------------------------------------------------------
System MySQL monitoring stats
mysqlmymonlite.sh - 0.5.0 WHM mysqlmymon.com
compiled by George Liu (eva2000) vbtechsupport.com
-------------------------------------------------------------
Report Generated:
Tue Oct 29 16:39:44 CDT 2013
----------------------------
MySQL Uptime:
----------------------------
MySQL server was started 2013-10-29 16:31:11
Uptime: 8 min 33 sec
Uptime 513
Uptime_since_flush_status 513
Max Used Connections: 1/500 (0.20%)
Key Buffer Used: 16.00k/32.00M (0.05%)
Current Key Buffer Usage: 5.85M (18.29%)
InnoDB Buffer Pool: 6.59M/2.00G (0.32%)
Table Locks Waited: 0 Rate: 0/s (0.00%)
--------------------
Query Cache Limit (bytes): 1048576
Query Cache Min Unit (bytes): 4096
Query Cache Size (bytes): 0
Query Cache Free Mem (bytes): 0
Query Cache Used Mem (bytes): 0
Queries in Query Cache: 1
Query Cache Avg Size (bytes): 0
----------------------------
Hardware Info:
----------------------------
Processors physical = 1, cores = 6, virtual = 4, hyperthreading = no
4 2000.072
4 Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz
4 15360 KB
----------------------------
TOP stats
----------------------------
Linux 2.6.18-348.4.1.el5.028stab107.2 ((yourserverhostname)) 10/29/2013 _x86_64_ (4 CPU)
04:39:44 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle
04:39:46 PM all 0.26 0.00 0.13 0.64 0.00 0.00 0.00 0.00 98.97
04:39:46 PM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
04:39:46 PM 1 0.57 0.00 0.00 2.29 0.00 0.00 0.00 0.00 97.14
04:39:46 PM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
04:39:46 PM 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
04:39:46 PM CPU %usr %nice %sys %iowait %irq %soft %steal %guest %idle
04:39:48 PM all 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
04:39:48 PM 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
04:39:48 PM 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
04:39:48 PM 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
04:39:48 PM 3 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 0.13 0.00 0.06 0.32 0.00 0.00 0.00 0.00 99.49
Average: 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
Average: 1 0.27 0.00 0.00 1.07 0.00 0.00 0.00 0.00 98.67
Average: 2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
Average: 3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00
top - 16:39:49 up 1:14, 1 user, load average: 0.88, 0.50, 0.21
Tasks: 43 total, 1 running, 35 sleeping, 4 stopped, 3 zombie
Cpu(s): 0.5%us, 0.4%sy, 0.1%ni, 96.9%id, 2.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4195200k total, 4038512k used, 156688k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 16 0 19232 1444 1208 S 0.0 0.0 0:00.97 init
1778 root 14 -4 10644 576 312 S 0.0 0.0 0:00.00 udevd
3621 root 24 0 243m 1436 1020 S 0.0 0.0 0:00.06 rsyslogd
3634 named 25 0 493m 152m 2580 S 0.0 3.7 0:00.76 named
3678 root 18 0 148m 12m 1444 S 0.0 0.3 0:02.10 lfd - sleeping
3689 root 15 0 66256 1200 520 S 0.0 0.0 0:00.00 sshd
5303 root 17 0 98308 4260 3040 S 0.0 0.1 0:00.15 sshd
5313 root 15 0 57488 2416 1592 S 0.0 0.1 0:00.19 sftp-server
5328 root 18 0 153m 47m 3056 S 0.0 1.2 0:02.23 /usr/local/cpan
5339 root 18 0 79928 9828 7268 S 0.0 0.2 0:00.34 httpd
5345 root 15 0 135m 1908 1280 S 0.0 0.0 0:00.00 pure-ftpd
5347 root 18 0 133m 1748 1304 S 0.0 0.0 0:00.00 pure-authd
5355 root 15 0 114m 1264 648 S 0.0 0.0 0:00.03 crond
5365 root 18 0 21500 480 300 S 0.0 0.0 0:00.00 atd
7813 root 15 0 30856 3132 1224 S 0.0 0.1 0:00.01 cPhulkd - proce
7825 root 15 0 97464 17m 5596 S 0.0 0.4 0:00.42 cpsrvd-ssl
7828 root 18 0 97420 13m 512 S 0.0 0.3 0:00.00 cpdavd - accept
7840 root 15 0 34544 4860 1456 S 0.0 0.1 0:00.06 queueprocd - wa
7869 root 18 0 34904 7052 1128 S 0.0 0.2 0:00.15 tailwatchd
7874 root 33 18 27072 3456 1168 S 0.0 0.1 0:00.00 cpanellogd - sl
8144 root 15 0 153m 45m 1164 S 0.0 1.1 0:00.00 spamd child
9394 root 15 0 98896 5272 3348 S 0.0 0.1 0:00.87 sshd
9406 root 15 0 105m 2016 1544 S 0.0 0.0 0:00.08 bash
15780 root 16 0 98836 3992 3028 S 0.0 0.1 0:00.03 sshd
15784 root 15 0 57488 2236 1588 S 0.0 0.1 0:00.01 sftp-server
17867 root 17 0 50576 8684 2308 S 0.0 0.2 0:00.11 leechprotect
17868 nobody 15 0 2012m 15m 6768 S 0.0 0.4 0:01.06 httpd
17870 nobody 19 0 2012m 15m 6764 S 0.0 0.4 0:01.07 httpd
17871 nobody 15 0 2012m 15m 6764 S 0.0 0.4 0:01.02 httpd
17958 nobody 16 0 2012m 15m 6768 S 0.0 0.4 0:01.04 httpd
18067 root 18 0 103m 1412 1196 T 0.0 0.0 0:00.00 bash
18116 root 15 0 0 0 0 Z 0.0 0.0 0:01.93 yum
19987 root 18 0 103m 1412 1196 T 0.0 0.0 0:00.00 mysqlmymonlite.
20014 root 15 0 0 0 0 Z 0.0 0.0 0:00.21 yum
20139 root 18 0 103m 1412 1196 T 0.0 0.0 0:00.00 mysqlmymonlite.
20166 root 15 0 0 0 0 Z 0.0 0.0 0:00.12 yum
22527 root 18 0 11308 1532 1240 S 0.0 0.0 0:00.04 mysqld_safe
24080 mysql 15 0 2642m 215m 5788 S 0.0 5.3 0:00.94 mysqld
24219 root 19 0 97972 4000 3028 S 0.0 0.1 0:00.05 sshd
24230 root 15 0 57488 2244 1592 S 0.0 0.1 0:00.06 sftp-server
25919 root 16 0 100m 832 716 T 0.0 0.0 0:00.82 locate
26051 root 18 0 104m 1944 1220 S 0.0 0.0 0:00.01 mysqlmymonlite.
26222 root 18 0 14892 1108 888 R 0.0 0.0 0:00.00 top
total used free shared buffers cached
Mem: 4096 3943 153 0 0 0
Low: 4096 3943 153
High: 0 0 0
-/+ buffers/cache: 3943 153
Swap: 0 0 0
MemTotal: 4195200 kB
MemFree: 156940 kB
Buffers: 0 kB
Cached: 0 kB
SwapCached: 0 kB
Active: 0 kB
Inactive: 0 kB
HighTotal: 0 kB
HighFree: 0 kB
LowTotal: 4195200 kB
LowFree: 156940 kB
SwapTotal: 0 kB
SwapFree: 0 kB
Dirty: 0 kB
Writeback: 0 kB
AnonPages: 0 kB
Mapped: 0 kB
Slab: 0 kB
PageTables: 0 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 0 kB
Committed_AS: 0 kB
VmallocTotal: 0 kB
VmallocUsed: 0 kB
VmallocChunk: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
Hugepagesize: 2048 kB
--------------------------
ps_mem.py output:
http://www.pixelbeat.org/scripts/ps_mem.py
--------------------------
Private + Shared = RAM used Program
152.0 KiB + 43.5 KiB = 195.5 KiB locate
204.0 KiB + 38.0 KiB = 242.0 KiB atd
296.0 KiB + 24.5 KiB = 320.5 KiB udevd
292.0 KiB + 141.5 KiB = 433.5 KiB mysqld_safe
456.0 KiB + 127.0 KiB = 583.0 KiB pure-authd
660.0 KiB + 48.5 KiB = 708.5 KiB crond
676.0 KiB + 56.5 KiB = 732.5 KiB init
832.0 KiB + 53.0 KiB = 885.0 KiB rsyslogd
712.0 KiB + 204.5 KiB = 916.5 KiB pure-ftpd
868.0 KiB + 284.5 KiB = 1.1 MiB bash (2)
1.1 MiB + 389.0 KiB = 1.5 MiB mysqlmymonlite. (3)
1.9 MiB + 154.5 KiB = 2.0 MiB cPhulkd - proce
2.2 MiB + 141.0 KiB = 2.4 MiB cpanellogd - sl
2.1 MiB + 520.5 KiB = 2.6 MiB sftp-server (3)
3.4 MiB + 175.5 KiB = 3.6 MiB queueprocd - wa
5.8 MiB + 141.0 KiB = 5.9 MiB tailwatchd
6.7 MiB + 241.0 KiB = 7.0 MiB leechprotect
5.9 MiB + 2.0 MiB = 7.9 MiB sshd (5)
11.5 MiB + 190.5 KiB = 11.7 MiB lfd - sleeping
13.3 MiB + 116.5 KiB = 13.4 MiB cpdavd - accept
15.5 MiB + 298.5 KiB = 15.8 MiB cpsrvd-ssl
2.3 MiB + 21.5 MiB = 23.8 MiB spamd child
2.7 MiB + 21.6 MiB = 24.4 MiB /usr/local/cpan
27.7 MiB + 8.6 MiB = 36.3 MiB httpd (5)
151.7 MiB + 113.0 KiB = 151.8 MiB named
214.7 MiB + 107.5 KiB = 214.8 MiB mysqld
---------------------------------
530.8 MiB
=================================
Private + Shared = RAM used Program
----------------------------
Server Disk Usage
----------------------------
Filesystem Type Size Used Avail Use% Mounted on
/dev/simfs simfs 250G 122G 129G 49% /
none tmpfs 2.1G 4.0K 2.1G 1% /dev
----------------------------
MySQL datadir disk usage
----------------------------
/var/lib/mysql/ uses 2044580 kilobytes of disk space
----------------------------
ulimit -aH
----------------------------
core file size (blocks, -c) 1000000
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 384976
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 4096
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 14335
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
----------------------------------------------------------
tail -15 /etc/security/limits.conf
----------------------------------------------------------
# - nice - max nice priority allowed to raise to values: [-20, 19]
# - rtprio - max realtime priority
#
#
#
#* soft core 0
#* hard rss 10000
#@student hard nproc 20
#@faculty soft nproc 20
#@faculty hard nproc 50
#ftp hard nproc 0
#@student - maxlogins 4
# End of file
----------------------------------------------------------
mysqld process limits:
cat /proc/24080/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 0 bytes
Max resident set unlimited unlimited bytes
Max processes 384976 384976 processes
Max open files 65535 65535 files
Max locked memory 32768 32768 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 384976 384976 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
----------------------------------
CPANEL WEB SERVER & PHP Info:
----------------------------------
WHM / Cpanel version: 11.38.2.12
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: suphp
SUEXEC: enabled
RUID2: not installed
PHP 5.4.21 (cli) (built: Oct 25 2013 10:51:26)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies
with the ionCube PHP Loader v4.4.1, Copyright (c) 2002-2013, by ionCube Ltd.
System => Linux yourserverhostname 2.6.18-348.4.1.el5.028stab107.2 #1 SMP Mon Jul 1 22:13:01 MSD 2013 x86_64
Build Date => Oct 25 2013 10:48:06
Configure Command => './configure' '--disable-fileinfo' '--disable-path-info-check' '--disable-posix' '--enable-ares=/usr/local/src/c-ares-1.10.0' '--enable-bcmath' '--enable-calendar' '--enable-exif' '--enable-fastcgi' '--enable-fpm' '--enable-ftp' '--enable-libxml' '--enable-mbstring' '--enable-pdo=shared' '--enable-soap' '--enable-sockets' '--enable-zip' '--prefix=/usr/local' '--with-config-file-path=/usr/local/lib' '--with-config-file-scan-dir=/usr/local/lib/php.ini.d' '--with-curl=/opt/curlssl/' '--with-curl=/usr/local/src/curl-7.32.0' '--with-curlwrappers' '--with-gd' '--with-gssapi' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/usr' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libdir=lib64' '--with-libexpat-dir=/usr' '--with-libxml-dir=/opt/xml2' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-mysqli=/usr/bin/mysql_config' '--with-openssl=/usr' '--with-openssl-dir=/usr' '--with-pcre-regex=/opt/pcre' '--with-pdo-mysql=shared' '--with-pdo-sqlite=shared' '--with-pic' '--with-png-dir=/usr' '--with-xmlrpc' '--with-xpm-dir=/usr' '--with-zlib' '--with-zlib-dir=/usr'
Configure Command | './configure' '--disable-fileinfo' '--disable-path-info-check' '--disable-posix' '--enable-ares=/usr/local/src/c-ares-1.10.0' '--enable-bcmath' '--enable-calendar' '--enable-exif' '--enable-fastcgi' '--enable-fpm' '--enable-ftp' '--enable-libxml' '--enable-mbstring' '--enable-pdo=shared' '--enable-soap' '--enable-sockets' '--enable-zip' '--prefix=/usr/local' '--with-config-file-path=/usr/local/lib' '--with-config-file-scan-dir=/usr/local/lib/php.ini.d' '--with-curl=/opt/curlssl/' '--with-curl=/usr/local/src/curl-7.32.0' '--with-curlwrappers' '--with-gd' '--with-gssapi' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/usr' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libdir=lib64' '--with-libexpat-dir=/usr' '--with-libxml-dir=/opt/xml2' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-mysqli=/usr/bin/mysql_config' '--with-openssl=/usr' '--with-openssl-dir=/usr' '--with-pcre-regex=/opt/pcre' '--with-pdo-mysql=shared' '--with-pdo-sqlite=shared' '--with-pic' '--with-png-dir=/usr' '--with-xmlrpc' '--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
Scan this dir for additional .ini files | /usr/local/lib/php.ini.d
PHP API | 20100412
Zend Extension Build | API220100525,NTS
PHP Extension Build | API20100525,NTS
DOM/XML API Version | 20031129
Client API version | 5.5.32
Client API library version | 5.5.32
Client API header version | 5.5.32
API Extensions | pdo_mysql
Client API version | mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $
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-20100525 | /usr/local/lib/php/extensions/no-debug-non-zts-20100525
file_uploads | On | On
log_errors | On | On
log_errors_max_len | 1024 | 1024
max_execution_time | 0 | 0
max_file_uploads | 20 | 20
memory_limit | 128M | 128M
open_basedir | no value | no value
post_max_size | 12M | 12M
realpath_cache_size | 16K | 16K
realpath_cache_ttl | 120 | 120
sendmail_path | /usr/sbin/sendmail -t -i | /usr/sbin/sendmail -t -i
sql.safe_mode | Off | Off
upload_max_filesize | 20M | 20M
upload_tmp_dir | no value | no value
sqlite3.extension_dir | no value | no value
cURL support | enabled
cURL Information | 7.32.0
ZLib Version | 1.2.3
GD Support | enabled
GD Version | bundled (2.1.0 compatible)
GIF Read Support | enabled
GIF Create Support | enabled
JPEG Support | enabled
libJPEG Version | 6b
PNG Support | enabled
libPNG Version | 1.2.49
WBMP Support | enabled
libXML support | active
libXML Compiled Version | 2.9.0
libXML Loaded Version | 20900
libXML streams | enabled
PCRE (Perl Compatible Regular Expressions) Support | enabled
PCRE Library Version | 8.21 2011-12-12
Soap Client | enabled
Soap Server | enabled
ZLib Support | enabled
apc
APC Support | disabled
APC Debugging | Disabled
MMAP File Mask | /tmp/apc-accountname.XXXXXX
apc.cache_by_default | Off | Off
apc.canonicalize | On | On
apc.coredump_unmap | Off | Off
apc.enable_cli | Off | Off
apc.enabled | On | On
apc.file_md5 | Off | Off
apc.file_update_protection | 2 | 2
apc.filters | no value | no value
apc.gc_ttl | 3600 | 3600
apc.include_once_override | Off | Off
apc.lazy_classes | Off | Off
apc.lazy_functions | Off | Off
apc.max_file_size | 2M | 2M
apc.mmap_file_mask | /tmp/apc-accountname.XXXXXX | /tmp/apc-accountname.XXXXXX
apc.num_files_hint | 2500 | 2500
apc.preload_path | no value | no value
apc.report_autofilter | Off | Off
apc.rfc1867 | Off | Off
apc.rfc1867_freq | 0 | 0
apc.rfc1867_name | APC_UPLOAD_PROGRESS | APC_UPLOAD_PROGRESS
apc.rfc1867_prefix | upload_ | upload_
apc.rfc1867_ttl | 3600 | 3600
apc.serializer | default | default
apc.shm_segments | 1 | 1
apc.shm_size | 32M | 32M
apc.shm_strings_buffer | 4M | 4M
apc.slam_defense | Off | Off
apc.stat | On | On
apc.stat_ctime | Off | Off
apc.ttl | 7200 | 7200
apc.use_request_time | On | On
apc.user_entries_hint | 3048 | 3048
apc.user_ttl | 7200 | 7200
apc.write_lock | On | On
Server Version: Apache/2.4.6 (Unix) OpenSSL/1.0.0-fips
Server Built: Oct 25 2013 10:37:55
Current Time: Tuesday, 29-Oct-2013 16:39:44 CDT
Restart Time: Tuesday, 29-Oct-2013 15:25:32 CDT
Total accesses: 1958 - Total Traffic: 80.6 MB
CPU Usage: u2.22 s1.94 cu1.46 cs.16 - .13% CPU load
.44 requests/sec - 18.5 kB/second - 42.1 kB/request
2 requests currently being processed, 98 idle workers
Server version: Apache/2.4.6 (Unix)
Server built: Oct 25 2013 10:37:55
Cpanel::Easy::Apache v3.22.17 rev9999
Server's Module Magic Number: 20120211:23
Server loaded: APR 1.4.8, APR-UTIL 1.5.2
Compiled using: APR 1.4.8, APR-UTIL 1.5.2
Architecture: 64-bit
Server MPM: worker
threaded: yes (fixed thread count)
forked: yes (variable process count)
Server compiled with....
-D 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=256
-D HTTPD_ROOT="/usr/local/apache"
-D SUEXEC_BIN="/usr/local/apache/bin/suexec"
-D DEFAULT_PIDLOG="logs/httpd.pid"
-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_core_module (static)
authz_host_module (static)
authz_groupfile_module (static)
authz_user_module (static)
authz_core_module (static)
access_compat_module (static)
auth_basic_module (static)
cache_module (static)
cache_disk_module (static)
socache_shmcb_module (static)
socache_dbm_module (static)
so_module (static)
ext_filter_module (static)
include_module (static)
filter_module (static)
deflate_module (static)
http_module (static)
mime_module (static)
log_config_module (static)
logio_module (static)
env_module (static)
mime_magic_module (static)
expires_module (static)
headers_module (static)
ident_module (static)
unique_id_module (static)
setenvif_module (static)
version_module (static)
proxy_module (static)
proxy_connect_module (static)
proxy_http_module (static)
slotmem_shm_module (static)
ssl_module (static)
mpm_worker_module (static)
unixd_module (static)
status_module (static)
autoindex_module (static)
asis_module (static)
info_module (static)
suexec_module (static)
cgi_module (static)
negotiation_module (static)
dir_module (static)
imagemap_module (static)
actions_module (static)
userdir_module (static)
alias_module (static)
rewrite_module (static)
bwlimited_module (shared)
suphp_module (shared)
security2_module (shared)
Compiled in modules:
core.c
mod_authn_file.c
mod_authn_core.c
mod_authz_host.c
mod_authz_groupfile.c
mod_authz_user.c
mod_authz_core.c
mod_access_compat.c
mod_auth_basic.c
mod_cache.c
mod_cache_disk.c
mod_socache_shmcb.c
mod_socache_dbm.c
mod_so.c
mod_ext_filter.c
mod_include.c
mod_filter.c
mod_deflate.c
http_core.c
mod_mime.c
mod_log_config.c
mod_logio.c
mod_env.c
mod_mime_magic.c
mod_expires.c
mod_headers.c
mod_ident.c
mod_unique_id.c
mod_setenvif.c
mod_version.c
mod_proxy.c
mod_proxy_connect.c
mod_proxy_http.c
mod_slotmem_shm.c
mod_ssl.c
worker.c
mod_unixd.c
mod_status.c
mod_autoindex.c
mod_asis.c
mod_info.c
mod_suexec.c
mod_cgi.c
mod_negotiation.c
mod_dir.c
mod_imagemap.c
mod_actions.c
mod_userdir.c
mod_alias.c
mod_rewrite.c
-------------------------------
From /usr/local/apache/conf/httpd.conf
-------------------------------
Timeout 300
KeepAlive On
MaxKeepAliveRequests 120
KeepAliveTimeout 5
StartServers 3
MinSpareServers 2
MaxSpareServers 10
ServerLimit 256
----------------------------
mysqlreport output
----------------------------
MySQL 5.5.32-cll uptime 0 0:8:39 Tue Oct 29 16:39:50 2013
__ Key _________________________________________________________________
Buffer used 16.00k of 32.00M %Used: 0.05
Current 5.85M %Usage: 18.29
Write hit 0.00%
Read hit 86.78%
__ Questions ___________________________________________________________
Total 131 0.3/s
Com_ 129 0.2/s %Total: 98.47
-Unknown 97 0.2/s 74.05
DMS 76 0.1/s 58.02
COM_QUIT 23 0.0/s 17.56
Slow 10 s 41 0.1/s 31.30 %DMS: 53.95 Log: ON
DMS 76 0.1/s 58.02
SELECT 61 0.1/s 46.56 80.26
DELETE 14 0.0/s 10.69 18.42
INSERT 1 0.0/s 0.76 1.32
REPLACE 0 0/s 0.00 0.00
UPDATE 0 0/s 0.00 0.00
Com_ 129 0.2/s 98.47
stmt_prepar 32 0.1/s 24.43
stmt_close 32 0.1/s 24.43
stmt_execut 32 0.1/s 24.43
__ Rows ________________________________________________________________
Rows 7.87k 15.2/s
Using idx 62 0.1/s %Index: 0.79
Rows/question 60.09
__ SELECT and Sort _____________________________________________________
Scan 66 0.1/s %SELECT: 108.20
Range 0 0/s 0.00
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 0 0/s
Sort range 0 0/s
Sort mrg pass 0 0/s
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 68 0.1/s
__ Tables ______________________________________________________________
Open 32 of 4096 %Cache: 0.78
Opened 39 0.1/s
__ Connections _________________________________________________________
Max used 1 of 500 %Max: 0.20
Total 25 0.0/s
__ Created Temp ________________________________________________________
Disk table 0 0/s %Disk: 0.00
Table 64 0.1/s Size: 32.0M
File 6 0.0/s
__ Threads _____________________________________________________________
Running 1 of 1
Created 1 0.0/s
Slow 0 0/s
Cached 0 of 50 %Hit: 96
__ Aborted _____________________________________________________________
Clients 0 0/s
Connects 0 0/s
__ Bytes _______________________________________________________________
Sent 181.65k 350.0/s
Received 10.16k 19.6/s
__ InnoDB Buffer Pool __________________________________________________
Usage 6.59M of 2.00G %Usage: 0.32
Read hit 97.58%
Pages
Free 130.65k %Total: 99.68
Data 422 0.32 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 17.46k 33.6/s
From disk 423 0.8/s %Disk: 2.42
Ahead Rnd 0 0/s
Writes 0 0/s
Wait Free 0 0/s %Wait: 0.00
Flushes 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 434 0.8/s
Writes 3 0.0/s
fsync 3 0.0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 0 0/s
Read 422 0.8/s
Written 0 0/s
Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s
----------------------------
Total Server MyISAM and InnoDB Size
----------------------------
----------------------------
MySQL settings
----------------------------
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 2G
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
---
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | NO_CHAIN |
| concurrent_insert | AUTO |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_storage_engine | InnoDB |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | ON |
| event_scheduler | OFF |
| expire_logs_days | 14 |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | ON |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| general_log | OFF |
| general_log_file | /var/lib/mysql/vps.log |
| group_concat_max_len | 1024 |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_partitioning | YES |
| have_profiling | YES |
| have_query_cache | YES |
| have_rtree_keys | YES |
| have_ssl | DISABLED |
| have_symlink | YES |
| hostname | yourserverhostname |
| ignore_builtin_innodb | OFF |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 2147483648 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 200 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 134217728 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_strict_mode | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.5.32 |
| innodb_write_io_threads | 4 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| keep_files_on_create | OFF |
| key_buffer_size | 33554432 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_messages | en_US |
| lc_messages_dir | /usr/share/mysql/ |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /var/lib/mysql/mysql-error.log |
| log_output | FILE |
| log_queries_not_using_indexes | ON |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16777216 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 1000000 |
| max_connections | 500 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 33554432 |
| 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 | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| metadata_locks_cache_size | 1024 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | 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 |
| new | OFF |
| 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,engine_condition_pushdown=on |
| performance_schema | OFF |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
| pid_file | /var/lib/mysql/mysql.pid |
| plugin_dir | /usr/lib64/mysql/plugin/ |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 1 |
| skip_external_locking | ON |
| skip_name_resolve | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097152 |
| sql_auto_is_null | OFF |
| sql_big_selects | ON |
| sql_big_tables | OFF |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_low_priority_updates | OFF |
| sql_max_join_size | 18446744073709551615 |
| sql_mode | ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | 0 |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_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 | CDT |
| table_definition_cache | 4096 |
| table_open_cache | 4096 |
| thread_cache_size | 50 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
| thread_statistics | OFF |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| 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.32-cll |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| wait_timeout | 28800 |
+---------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 15 |
| Bytes_received | 12325 |
| Bytes_sent | 210367 |
| Com_admin_commands | 0 |
| 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 | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 14 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| 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 | 63 |
| Com_set_option | 4 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_client_statistics | 0 |
| Com_show_collations | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 2 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_index_statistics | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 1 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 10 |
| Com_show_storage_engines | 0 |
| Com_show_table_statistics | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_thread_statistics | 0 |
| Com_show_triggers | 0 |
| Com_show_user_statistics | 0 |
| Com_show_variables | 19 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 32 |
| Com_stmt_execute | 32 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 32 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| 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 | 27 |
| Created_tmp_disk_tables | 9 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 114 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 15 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 5 |
| Handler_read_key | 27 |
| Handler_read_last | 0 |
| Handler_read_next | 30 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10664 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 8979 |
| Innodb_buffer_pool_pages_data | 1729 |
| Innodb_buffer_pool_bytes_data | 28327936 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 129340 |
| Innodb_buffer_pool_pages_misc | 3 |
| Innodb_buffer_pool_pages_total | 131072 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 29856 |
| Innodb_buffer_pool_reads | 1730 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 30527488 |
| Innodb_data_reads | 1958 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 1729 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Innodb_truncated_status_writes | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 26776 |
| Key_blocks_used | 16 |
| Key_read_requests | 121 |
| Key_reads | 16 |
| Key_write_requests | 3 |
| Key_writes | 3 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 1 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 3209 |
| Open_streams | 0 |
| Open_table_definitions | 1838 |
| Open_tables | 1838 |
| Opened_files | 5161 |
| Opened_table_definitions | 1838 |
| Opened_tables | 1845 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 202 |
| Questions | 138 |
| Rpl_status | AUTH_MASTER |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 70 |
| 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 | 42 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 68 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
| Uptime | 520 |
| Uptime_since_flush_status | 520 |
+------------------------------------------+-------------+
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----+---------+------+-------+------------------+
| 26 | root | localhost | | Query | 0 | | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
------------------------------------
INNODB Buffer Pool Stats
------------------------------------
*************************** 1. row ***************************
POOL_ID: 0
POOL_SIZE: 131072
FREE_BUFFERS: 129340
DATABASE_PAGES: 1729
OLD_DATABASE_PAGES: 658
MODIFIED_DATABASE_PAGES: 0
PENDING_DECOMPRESS: 0
PENDING_READS: 0
PENDING_FLUSH_LRU: 0
PENDING_FLUSH_LIST: 0
PAGES_MADE_YOUNG: 0
PAGES_NOT_MADE_YOUNG: 0
PAGES_MADE_YOUNG_RATE: 0
PAGES_MADE_NOT_YOUNG_RATE: 0
NUMBER_PAGES_READ: 1729
NUMBER_PAGES_CREATED: 0
NUMBER_PAGES_WRITTEN: 0
PAGES_READ_RATE: 40.84247367269773
PAGES_CREATE_RATE: 0
PAGES_WRITTEN_RATE: 0
NUMBER_PAGES_GET: 29856
HIT_RATE: 895
YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
NUMBER_PAGES_READ_AHEAD: 0
NUMBER_READ_AHEAD_EVICTED: 0
READ_AHEAD_RATE: 0
READ_AHEAD_EVICTED_RATE: 0
LRU_IO_TOTAL: 0
LRU_IO_CURRENT: 160
UNCOMPRESS_TOTAL: 0
UNCOMPRESS_CURRENT: 0
----------------------------
INNODB Status
----------------------------
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
131029 16:39:51 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 32 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 1_second, 1 sleeps, 0 10_second, 1 background, 1 flush
srv_master_thread log flush and writes: 1
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4, signal count 4
Mutex spin waits 2, rounds 60, OS waits 2
RW-shared spins 2, rounds 60, OS waits 2
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 5E1600
Purge done for trx's n:o < 5E101E undo n:o < 0
History list length 2104
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 28, OS thread handle 0x2ba8735da700, query id 209 localhost root
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0], aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1958 OS file reads, 3 OS file writes, 3 OS fsyncs
47.62 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 74, seg size 76, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 3 buffer(s)
10.41 hash searches/s, 38.28 non-hash searches/s
---
LOG
---
Log sequence number 51271534625
Log flushed up to 51271534625
Last checkpoint at 51271534625
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 1414281
Buffer pool size 131072
Free buffers 129340
Database pages 1729
Old database pages 658
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1729, created 0, written 0
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: 1729, unzip_LRU len: 0
I/O sum[0]:cur[160], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 24080, id 48002441000704, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
----------------------------
MySQL Error Log
----------------------------
131029 16:31:12 [Note] Event Scheduler: Loaded 0 events
131029 16:31:12 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.32-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
Report Complete:
Tue Oct 29 16:39:51 CDT 2013
----------------------------
MySQL-Slow.log Results
# Time: 131029 14:56:25
# Time: 131029 14:56:54
# User@Host: cphulkd[cphulkd] @ localhost []
# Query_time: 0.004395 Lock_time: 0.000013 Rows_sent: 0 Rows_examined: 21
use cphulkd;
SET timestamp=1383076614;
DELETE FROM logins WHERE USER='root' AND IP='192.198.16.203' AND SERVICE='system' AND STATUS='0';
# User@Host: cphulkd[cphulkd] @ localhost []
# Query_time: 0.000903 Lock_time: 0.000013 Rows_sent: 0 Rows_examined: 21
SET timestamp=1383076614;
DELETE FROM logins WHERE USER='root' AND IP='192.198.16.203' AND SERVICE='system' AND STATUS='0';
# Time: 131029 15:02:55
# User@Host: cphulkd[cphulkd] @ localhost []
# Query_time: 0.000839 Lock_time: 0.000013 Rows_sent: 0 Rows_examined: 21
SET timestamp=1383076975;
DELETE FROM logins WHERE USER='root' AND IP='192.198.16.203' AND SERVICE='system' AND STATUS='0';
# User@Host: cphulkd[cphulkd] @ localhost []
# Query_time: 0.002754 Lock_time: 0.000010 Rows_sent: 0 Rows_examined: 21
SET timestamp=1383076975;
DELETE FROM logins WHERE USER='root' AND IP='192.198.16.203' AND SERVICE='system' AND STATUS='0';
# Time: 131029 15:03:15
# User@Host: root[root] @ localhost []
# Query_time: 7.822666 Lock_time: 0.000127 Rows_sent: 0 Rows_examined: 0
SET timestamp=1383076995;
SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
# Time: 131029 15:03:58
# User@Host: root[root] @ localhost []
# Query_time: 31.759080 Lock_time: 0.000095 Rows_sent: 3 Rows_examined: 1841
SET timestamp=1383077038;
SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
# User@Host: root[root] @ localhost []
# Query_time: 0.654029 Lock_time: 0.000105 Rows_sent: 1 Rows_examined: 1835
SET timestamp=1383077038;
SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';
# Time: 131029 15:03:59
# User@Host: root[root] @ localhost []
# Query_time: 0.600454 Lock_time: 0.000179 Rows_sent: 1 Rows_examined: 1859
SET timestamp=1383077039;
SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'MyISAM';
# Time: 131029 15:03:15
# User@Host: root[root] @ localhost []
# Query_time: 7.822666 Lock_time: 0.000127 Rows_sent: 0 Rows_examined: 0
SET timestamp=1383076995;
SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
# Time: 131029 15:03:58
# User@Host: root[root] @ localhost []
# Query_time: 31.759080 Lock_time: 0.000095 Rows_sent: 3 Rows_examined: 1841
SET timestamp=1383077038;
SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
# User@Host: root[root] @ localhost []
# Query_time: 0.654029 Lock_time: 0.000105 Rows_sent: 1 Rows_examined: 1835
SET timestamp=1383077038;
SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';
# Time: 131029 15:03:59
# User@Host: root[root] @ localhost []
# Query_time: 0.600454 Lock_time: 0.000179 Rows_sent: 1 Rows_examined: 1859
SET timestamp=1383077039;
SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'MyISAM';
# Time: 131029 15:11:11
# User@Host: reviewdb[reviewdb] @ [192.198.16.203]
# Query_time: 0.437985 Lock_time: 0.000092 Rows_sent: 446 Rows_examined: 446
use reviewdb_Wp2125;
SET timestamp=1383077471;
select * from `reviewdb_Wp2125`.`wp_shwp_options` limit 0, 1000;
# Time: 131029 15:11:12
# User@Host: reviewdb[reviewdb] @ [192.198.16.203]
# Query_time: 0.001164 Lock_time: 0.000814 Rows_sent: 0 Rows_examined: 0
SET timestamp=1383077472;
SELECT `EVENT_NAME`, `EVENT_SCHEMA`, `EVENT_DEFINITION` FROM `INFORMATION_SCHEMA`.`EVENTS`;
/usr/sbin/mysqld, Version: 5.5.32-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
MySQLTuner Results
>> MySQLTuner 1.2.0_1 - 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.32-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 231M (Tables: 1599)
[--] Data in InnoDB tables: 8M (Tables: 219)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 34
-------- Performance Metrics -------------------------------------------------
[--] Up for: 7m 8s (81 q [0.189 qps], 18 conn, TX: 589K, RX: 6K)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 2.1G global + 2.8M per thread (500 max threads)
[OK] Maximum possible memory usage: 3.4G (85% of installed RAM)
[!!] Slow queries: 7% (6/81)
[OK] Highest usage of available connections: 0% (3/500)
[!!] Key buffer size / total MyISAM indexes: 32.0M/44.6M
[!!] Key buffer hit rate: 80.2% (131 cached / 26 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] Temporary tables created on disk: 11% (1 on disk / 9 total)
[OK] Thread cache hit rate: 83% (3 created / 18 connections)
[OK] Table cache hit rate: 98% (387 open / 394 opened)
[OK] Open file limit used: 6% (691/10K)
[OK] Table locks acquired immediately: 100% (80 immediate / 80 locks)
[OK] InnoDB data size / buffer pool: 8.2M/2.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
key_buffer_size (> 44.6M)
query_cache_size (>= 8M)
Current my.cnf file
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE
innodb-strict-mode = 1
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 2G
# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
open_files_limit=10920
TOP Results
top - 16:03:52 up 38 min, 1 user, load average: 0.00, 0.04, 0.00
Tasks: 33 total, 1 running, 32 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.5%id, 0.5%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4195200k total, 4023560k used, 171640k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5489 nobody 18 0 2012m 10m 2612 S 0.3 0.3 0:01.17 httpd
1 root 18 0 19232 1444 1208 S 0.0 0.0 0:00.97 init
1778 root 14 -4 10644 576 312 S 0.0 0.0 0:00.00 udevd
3621 root 24 0 243m 1428 1020 S 0.0 0.0 0:00.04 rsyslogd
3634 named 25 0 493m 152m 2568 S 0.0 3.7 0:00.53 named
3678 root 18 0 148m 12m 1444 S 0.0 0.3 0:01.25 lfd - sleeping
3689 root 15 0 66256 1200 520 S 0.0 0.0 0:00.00 sshd
5303 root 15 0 98116 4172 3028 S 0.0 0.1 0:00.07 sshd
5313 root 18 0 57488 2408 1596 S 0.0 0.1 0:00.04 sftp-server
5328 root 18 0 153m 47m 3056 S 0.0 1.2 0:02.20 /usr/local/cpan
5339 root 15 0 79928 8848 6292 S 0.0 0.2 0:00.21 httpd
5345 root 15 0 135m 1908 1280 S 0.0 0.0 0:00.00 pure-ftpd
5347 root 18 0 133m 1748 1304 S 0.0 0.0 0:00.00 pure-authd
5355 root 15 0 114m 1264 648 S 0.0 0.0 0:00.01 crond
5365 root 25 0 21500 480 300 S 0.0 0.0 0:00.00 atd
5485 root 19 0 50576 8684 2308 S 0.0 0.2 0:00.09 leechprotect
5487 nobody 20 0 2012m 14m 6732 S 0.0 0.4 0:01.26 httpd
5488 nobody 20 0 2012m 14m 6752 S 0.0 0.3 0:01.32 httpd
5684 nobody 20 0 2012m 13m 6756 S 0.0 0.3 0:01.21 httpd
7813 root 18 0 30856 3132 1224 S 0.0 0.1 0:00.00 cPhulkd - proce
7825 root 18 0 97464 17m 5596 S 0.0 0.4 0:00.34 cpsrvd-ssl
7828 root 18 0 97420 13m 512 S 0.0 0.3 0:00.00 cpdavd - accept
7840 root 15 0 34544 4860 1456 S 0.0 0.1 0:00.04 queueprocd - wa
7869 root 15 0 34904 7052 1128 S 0.0 0.2 0:00.08 tailwatchd
7874 root 33 18 27072 3456 1168 S 0.0 0.1 0:00.00 cpanellogd - sl
8144 root 15 0 153m 45m 1164 S 0.0 1.1 0:00.00 spamd child
9394 root 18 0 97944 4284 3332 S 0.0 0.1 0:00.09 sshd
9406 root 15 0 105m 1988 1520 S 0.0 0.0 0:00.03 bash
15780 root 16 0 98836 3992 3028 S 0.0 0.1 0:00.03 sshd
15784 root 15 0 57488 2236 1588 S 0.0 0.1 0:00.01 sftp-server
15869 root 18 0 11308 1532 1240 S 0.0 0.0 0:00.04 mysqld_safe
17424 mysql 18 0 2642m 214m 5024 S 0.0 5.2 0:00.63 mysqld
17679 root 15 0 14896 1240 1012 R 0.0 0.0 0:00.00 top
Any help would be greatly appreciated as I am running out of options.
-
Hello :) I recommend letting MySQL run for at least 24 hours before running the tuner script to get more accurate results. Thank you. 0 -
Update those: key-buffer-size = 45M # CACHES AND LIMITS # query-cache-type = 1 query-cache-size = 30M max-connections = 200 table-definition-cache = 2000 table-open-cache = 2000 innodb-buffer-pool-size = 200M It will make quite a difference since you config was quite bad but you dont have much queries to compare (mysql run only 7 min) 0
Please sign in to leave a comment.
Comments
2 comments