I have a vps server with os centos 6.7 64x, 2 cpu's and 3gb ram, and I use it exclusively for a WP MU (Memory limit: 512 MByte/Memory usage: 53.97 MByte).
I use mostly myisam and have about 30 subsites, most of them are blogs and there some e-shops. The visits in all of these sites everydays is about 100 to 250 people on each site. And sometimes, some sites might 400 to 800 visits. I have added the plugin w3tc and stil I feel that it goes slow for the guests and for the logged in users too.
How can I understand how much memory and cpu I need everytime so I won't have any problems and what else do you need so I can give you a better image of my server?
My nano /etc/my.cnf
[myisamchk]
read_buffer=16M
key_buffer=32M
write_buffer=16M
sort_buffer=32M
[mysqld_safe]
open_files_limit=8192
log-error="/var/log/mysqld.log"
pid-file="/var/run/mysqld/mysqld.pid"
[mysqld]
slow-query-log=1
slow-query-log-file="/var/log/mysql/slow.log"
long_query_time=4
log_queries_not_using_indexes=1
local-infile=0
join_buffer_size=10M
connect_timeout=10
read_buffer_size=1M
key_buffer=16M
key_buffer_size=70M
# old_passwords=1
max_allowed_packet=268435456
interactive_timeout=25
max_connect_errors=10
max_connections=256
wait_timeout=1000
query_cache_size=0
query_cache_type=OFF
user=mysql
thread_cache_size=286
sort_buffer_size=10M
tmp_table_size=256M
max_heap_table_size=256M
table_open_cache=500
query_cache_limit=128M
socket="/var/lib/mysql/mysql.sock"
myisam_sort_buffer_size=32M
datadir="/var/lib/mysql"
default-storage-engine=MyISAM
innodb_file_per_table=1
innodb_buffer_pool_size=134217728
open_files_limit=10000
[isamchk]
read_buffer=16M
key_buffer=32M
write_buffer=16M
sort_buffer=32M
[mysqlhotcopy]
skip-networking
interactive-timeout
And this results of MySQLTuner
>> MySQLTuner 1.6.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.0.22-MariaDB-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM
[--] Data in MyISAM tables: 501M (Tables: 869)
[--] Data in InnoDB tables: 656K (Tables: 16)
[!!] Total fragmented tables: 37
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 28m 37s (1M q [12.900 qps], 36K conn, TX: 42B, RX: 1B)
[--] Reads / Writes: 82% / 18%
[--] Binary logging is disabled
[--] Total buffers: 598.0M global + 21.5M per thread (256 max threads)
[OK] Maximum reached memory usage: 1.0G (33.48% of installed RAM)
[!!] Maximum possible memory usage: 6.0G (198.89% of installed RAM)
[!!] Slow queries: 7% (86K/1M)
[OK] Highest usage of available connections: 7% (20/256)
[OK] Aborted connections: 0.18% (65/36559)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 143K sorts)
[!!] Joins performed without indexes: 324
[!!] Temporary tables created on disk: 43% (37K on disk / 87K total)
[OK] Thread cache hit rate: 99% (20 created / 36K connections)
[!!] Table cache hit rate: 2% (500 open / 23K opened)
[OK] Open file limit used: 12% (998/8K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.8% (13M used / 73M cache)
[OK] Key buffer size / total MyISAM indexes: 70.0M/145.8M
[OK] Read Key buffer hit rate: 99.4% (23M cached / 131K reads)
[!!] Write Key buffer hit rate: 30.6% (347K cached / 241K writes)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/656.0K
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 6.36% (521 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 97.13% (17216 hits/ 17724 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 21 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (8192) variable
should be greater than table_open_cache ( 500)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (>= 8M)
join_buffer_size (> 10.0M, or always use indexes with joins)
table_open_cache (> 500)
innodb_buffer_pool_instances (=1)
When it says "Add RAM before increasing MySQL buffer variables", what do I have to do exactly?
My tail -n 100 /var/log/mysql-slow-queries.log
tail: cannot open `/var/log/mysql-slow-queries.log' for reading: No such file or directory
My tail -n 100 /var/log/mysqld.log (On 0:03:45 and after I gives me data that belong on ip's and sites of mys server. How is this possible?)
160102 19:22:19 [Note] InnoDB: Waiting for purge to start
160102 19:22:19 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.26-74.0 started; log sequence number 27983072465
160102 19:22:19 [Note] Plugin 'FEEDBACK' is disabled.
160102 19:22:19 [Note] Server socket created on IP: '::'.
160102 19:22:19 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.22-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
160102 21:13:47 [Note] /usr/sbin/mysqld: Normal shutdown
160102 21:13:47 [Note] Event Scheduler: Purging the queue. 0 events
160102 21:13:47 [Note] InnoDB: FTS optimize thread exiting.
160102 21:13:47 [Note] InnoDB: Starting shutdown...
160102 21:13:49 [Note] InnoDB: Shutdown completed; log sequence number 27983072475
160102 21:13:49 [Note] /usr/sbin/mysqld: Shutdown complete
160102 21:13:49 mysqld_safe mysqld from pid file /var/lib/mysql/server.mywebsite.com.com.pid ended
160102 21:13:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160102 21:13:50 [Note] /usr/sbin/mysqld (mysqld 10.0.22-MariaDB-log) starting as process 2670 ...
160102 21:13:50 [Note] InnoDB: Using mutexes to ref count buffer pool pages
160102 21:13:50 [Note] InnoDB: The InnoDB memory heap is disabled
160102 21:13:50 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
160102 21:13:50 [Note] InnoDB: Memory barrier is not used
160102 21:13:50 [Note] InnoDB: Compressed tables use zlib 1.2.3
160102 21:13:50 [Note] InnoDB: Using Linux native AIO
160102 21:13:50 [Note] InnoDB: Using CPU crc32 instructions
160102 21:13:50 [Note] InnoDB: Initializing buffer pool, size = 128.0M
160102 21:13:50 [Note] InnoDB: Completed initialization of buffer pool
160102 21:13:50 [Note] InnoDB: Highest supported file format is Barracuda.
160102 21:13:50 [Note] InnoDB: 128 rollback segment(s) are active.
160102 21:13:50 [Note] InnoDB: Waiting for purge to start
160102 21:13:50 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.26-74.0 started; log sequence number 27983072475
160102 21:13:50 [Note] Plugin 'FEEDBACK' is disabled.
160102 21:13:50 [Note] Server socket created on IP: '::'.
160102 21:13:50 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.22-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
160102 23:13:09 [Note] /usr/sbin/mysqld: Normal shutdown
160102 23:13:09 [Note] Event Scheduler: Purging the queue. 0 events
160102 23:13:09 [Note] InnoDB: FTS optimize thread exiting.
160102 23:13:09 [Note] InnoDB: Starting shutdown...
160102 23:13:11 [Note] InnoDB: Shutdown completed; log sequence number 27983072485
160102 23:13:11 [Note] /usr/sbin/mysqld: Shutdown complete
160102 23:13:11 mysqld_safe mysqld from pid file /var/lib/mysql/server.mywebsite.com.pid ended
160102 23:13:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160102 23:13:11 [Note] /usr/sbin/mysqld (mysqld 10.0.22-MariaDB-log) starting as process 8751 ...
160102 23:13:11 [Note] InnoDB: Using mutexes to ref count buffer pool pages
160102 23:13:11 [Note] InnoDB: The InnoDB memory heap is disabled
160102 23:13:11 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
160102 23:13:11 [Note] InnoDB: Memory barrier is not used
160102 23:13:11 [Note] InnoDB: Compressed tables use zlib 1.2.3
160102 23:13:11 [Note] InnoDB: Using Linux native AIO
160102 23:13:11 [Note] InnoDB: Using CPU crc32 instructions
160102 23:13:11 [Note] InnoDB: Initializing buffer pool, size = 128.0M
160102 23:13:11 [Note] InnoDB: Completed initialization of buffer pool
160102 23:13:11 [Note] InnoDB: Highest supported file format is Barracuda.
160102 23:13:11 [Note] InnoDB: 128 rollback segment(s) are active.
160102 23:13:11 [Note] InnoDB: Waiting for purge to start
160102 23:13:11 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.26-74.0 started; log sequence number 27983072485
160102 23:13:11 [Note] Plugin 'FEEDBACK' is disabled.
160102 23:13:11 [Note] Server socket created on IP: '::'.
160102 23:13:11 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.22-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
160102 23:16:09 [Note] /usr/sbin/mysqld: Normal shutdown
160102 23:16:09 [Note] Event Scheduler: Purging the queue. 0 events
160102 23:16:09 [Note] InnoDB: FTS optimize thread exiting.
160102 23:16:09 [Note] InnoDB: Starting shutdown...
160102 23:16:11 [Note] InnoDB: Shutdown completed; log sequence number 27983072495
160102 23:16:11 [Note] /usr/sbin/mysqld: Shutdown complete
160102 23:16:11 mysqld_safe mysqld from pid file /var/lib/mysql/server.mywebsite.com.pid ended
160102 23:16:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
160102 23:16:11 [Note] /usr/sbin/mysqld (mysqld 10.0.22-MariaDB-log) starting as process 10571 ...
160102 23:16:11 [Note] InnoDB: Using mutexes to ref count buffer pool pages
160102 23:16:11 [Note] InnoDB: The InnoDB memory heap is disabled
160102 23:16:11 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
160102 23:16:11 [Note] InnoDB: Memory barrier is not used
160102 23:16:11 [Note] InnoDB: Compressed tables use zlib 1.2.3
160102 23:16:11 [Note] InnoDB: Using Linux native AIO
160102 23:16:11 [Note] InnoDB: Using CPU crc32 instructions
160102 23:16:11 [Note] InnoDB: Initializing buffer pool, size = 128.0M
160102 23:16:11 [Note] InnoDB: Completed initialization of buffer pool
160102 23:16:11 [Note] InnoDB: Highest supported file format is Barracuda.
160102 23:16:11 [Note] InnoDB: 128 rollback segment(s) are active.
160102 23:16:11 [Note] InnoDB: Waiting for purge to start
160102 23:16:11 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.26-74.0 started; log sequence number 27983072495
160102 23:16:11 [Note] Plugin 'FEEDBACK' is disabled.
160102 23:16:11 [Note] Server socket created on IP: '::'.
160102 23:16:11 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.22-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
160103 0:03:45 [Warning] IP address '222.186.30.119' could not be resolved: Name or service not known
160103 4:45:12 [Warning] IP address '222.186.50.221' could not be resolved: Name or service not known
160103 8:02:49 [Warning] IP address '123.57.67.126' could not be resolved: Name or service not known
160103 10:22:34 [Warning] Hostname 'mx.campprice.com' does not resolve to '216.99.158.156'.
160103 10:22:34 [Note] Hostname 'mx.campprice.com' has the following IP addresses:
160103 10:22:34 [Note] - 5.2.16.155
160103 11:37:24 [Warning] IP address '1.93.1.91' could not be resolved: Name or service not known
160103 14:38:25 [Warning] IP address '171.92.207.61' could not be resolved: Name or service not known
160103 15:06:41 [Warning] IP address '221.231.139.150' could not be resolved: Name or service not known
160103 16:41:46 [Warning] IP address '5.39.222.253' could not be resolved: Name or service not known
My tail -n 100 /var/log/mysql/slow.log
My ls -l /var/lib/mysql
total 125260
drwxr-x--x 16 mysql mysql 4096 Dec 29 03:27 ./
drwxr-xr-x 20 root root 4096 Dec 29 03:12 ../
drwx------ 2 mysql mysql 57344 Dec 14 19:15 tsimis_vemal/
-rw-rw---- 1 mysql mysql 16384 Dec 28 01:49 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Dec 28 01:49 aria_log_control
-rw-rw---- 1 mysql mysql 56 Dec 11 15:23 auto.cnf
drwx------ 2 mysql mysql 4096 Dec 7 23:46 cphulkd/
drwx------ 2 mysql mysql 4096 Dec 28 05:30 eximstats/
-rw-rw---- 1 mysql mysql 27262976 Dec 28 20:43 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Dec 28 20:43 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Dec 11 15:23 ib_logfile1
drwx------ 2 mysql mysql 4096 Dec 7 23:46 tsarmil_wordpress/
drwx------ 2 mysql mysql 4096 Dec 7 23:46 leechprotect/
drwx------ 2 mysql mysql 4096 Dec 7 23:46 modsec/
-rw-rw---- 1 mysql mysql 0 Dec 11 15:25 multi-master.info
drwx--x--x 2 mysql mysql 4096 Dec 11 15:25 mysql/
-rw-rw---- 1 mysql mysql 0 Nov 21 22:17 mysql-bin.index
-rw-r----- 1 mysql mysql 18229 Nov 21 22:18 mysql-error.log
srwxrwxrwx 1 mysql mysql 0 Dec 28 01:49 mysql.sock=
-rw-r--r-- 1 mysql mysql 15 Dec 11 15:25 mysql_upgrade_info
drwx------ 2 mysql mysql 4096 Dec 11 15:25 performance_schema/
drwx------ 2 mysql mysql 4096 Dec 7 23:46 roundcube/
-rw-r--r-- 1 mysql mysql 1021 Dec 11 15:24 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql 526 Dec 11 15:24 RPM_UPGRADE_MARKER-LAST
-rw-r----- 1 mysql mysql 80432 Nov 15 13:35 server.mydomain.com.err
-rw-rw---- 1 mysql mysql 5 Dec 28 01:49 server.mydomain.com.pid
-rw-rw---- 1 mysql mysql 0 Nov 21 01:33 slow.log
drwx------ 2 mysql mysql 36864 Dec 28 01:41 dumdum_dbsp/
drwx------ 2 mysql mysql 36864 Dec 23 00:28 dumdum_demoplayer/
drwx------ 2 mysql mysql 4096 Dec 10 01:21 dumdum_mudemodb/
drwx------ 2 mysql mysql 4096 Dec 7 23:46 dumdum_ttvelmor/
drwx------ 2 mysql mysql 4096 Dec 15 03:27 whmxfer/
My top
top - 22:53:01 up 5 days, 18:39, 1 user, load average: 1.07, 0.85, 0.59
Tasks: 72 total, 2 running, 67 sleeping, 0 stopped, 3 zombie
Cpu(s): 22.3%us, 1.5%sy, 0.0%ni, 76.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 3145728k total, 2348472k used, 797256k free, 0k buffers
Swap: 3145728k total, 175068k used, 2970660k free, 2097268k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
31449 account1 20 0 304m 129m 74m R 32.3 4.2 1:58.94 php
32640 account1 20 0 314m 135m 72m S 10.3 4.4 0:17.33 php
10571 mysql 20 0 988m 105m 8112 S 1.0 3.4 31:31.90 mysqld
582 root 20 0 99800 4340 3256 S 0.3 0.1 0:00.08 sshd
1111 root 20 0 112m 440 360 S 0.3 0.0 0:01.20 crond
1138 root 20 0 108m 752 648 S 0.3 0.0 0:07.40 varnishd
23350 nobody 20 0 63572 4404 2104 S 0.3 0.1 0:00.70 httpd
25143 nobody 20 0 63564 4396 2112 S 0.3 0.1 0:00.45 httpd
1 root 20 0 19236 984 844 S 0.0 0.0 0:01.97 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd/1227
3 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper/1227
147 root 16 -4 10648 352 348 S 0.0 0.0 0:00.00 udevd
451 dovecot 20 0 29576 2228 1672 S 0.0 0.1 0:00.01 auth
554 root 20 0 0 0 0 Z 0.0 0.0 0:00.00 cpsrvd (SSL) - <defunct>
555 root 20 0 0 0 0 Z 0.0 0.0 0:00.00 cpsrvd (SSL) - <defunct>
583 sshd 20 0 67784 1652 848 S 0.0 0.1 0:00.00 sshd
595 root 20 0 67784 3256 2500 S 0.0 0.1 0:00.00 sshd
596 sshd 20 0 67784 1560 804 S 0.0 0.0 0:00.00 sshd
728 root 20 0 179m 4380 800 S 0.0 0.1 0:24.25 rsyslogd
749 named 20 0 233m 3388 1548 S 0.0 0.1 0:16.58 named
767 nscd 20 0 941m 1420 984 S 0.0 0.0 0:36.59 nscd
792 root 20 0 64336 560 460 S 0.0 0.0 0:09.77 sshd
802 root 20 0 20040 364 360 S 0.0 0.0 0:00.00 xinetd
1034 root 20 0 18212 844 676 S 0.0 0.0 0:01.74 dovecot
1039 dovenull 20 0 42644 2748 2544 S 0.0 0.1 0:00.25 pop3-login
1040 dovenull 20 0 42648 3028 2592 S 0.0 0.1 0:00.43 imap-login
1041 dovecot 20 0 13472 916 816 S 0.0 0.0 0:00.52 anvil
1042 root 20 0 13608 1032 872 S 0.0 0.0 0:00.89 log
1044 dovenull 20 0 42740 2812 2580 S 0.0 0.1 0:00.70 pop3-login
1045 dovenull 20 0 42788 3056 2620 S 0.0 0.1 0:01.52 imap-login
1049 root 20 0 14636 1764 948 S 0.0 0.1 0:01.88 config
1054 root 20 0 66412 284 280 S 0.0 0.0 0:00.00 saslauthd
1056 root 20 0 66412 32 28 S 0.0 0.0 0:00.00 saslauthd
1068 mailnull 20 0 70564 2536 2396 S 0.0 0.1 0:01.06 exim
1077 root 20 0 194m 7072 2752 S 0.0 0.2 0:59.42 /usr/local/cpan
1089 root 20 0 62448 5596 4212 S 0.0 0.2 0:10.45 httpd
1098 root 20 0 133m 1432 1364 S 0.0 0.0 0:00.50 pure-ftpd
1100 root 20 0 132m 1352 1304 S 0.0 0.0 0:00.44 pure-authd
1183 root 20 0 19428 384 364 S 0.0 0.0 0:00.00 atd
1540 root 20 0 105m 3092 1120 S 0.0 0.1 0:06.81 cpsrvd (SSL) -
1546 root 20 0 32692 1680 1424 S 0.0 0.1 0:01.13 queueprocd - wa
1562 root 20 0 78132 6704 1672 S 0.0 0.2 1:11.63 tailwatchd
1577 root 20 0 34240 2512 1780 S 0.0 0.1 0:47.76 cPhulkd - proce
1606 root 20 0 119m 1844 664 S 0.0 0.1 0:02.48 cpdavd - accept
1617 root 38 18 23664 1684 1128 S 0.0 0.1 0:00.94 cpanellogd - sl
1639 nobody 20 0 321m 732 556 S 0.0 0.0 0:08.56 memcached
1646 root 20 0 4068 496 492 S 0.0 0.0 0:00.00 mingetty
1647 root 20 0 4068 496 492 S 0.0 0.0 0:00.00 mingetty
7355 root 20 0 219m 75m 3424 S 0.0 2.4 0:42.96 spamd child
10391 root 20 0 9416 1120 1116 S 0.0 0.0 0:00.00 mysqld_safe
20373 root 20 0 13468 1124 932 S 0.0 0.0 0:00.00 ssl-params
23347 root 20 0 76528 11m 2896 S 0.0 0.4 0:00.09 leechprotect
23348 nobody 20 0 62584 2296 760 S 0.0 0.1 0:00.06 httpd
