Results 1 to 7 of 7
-
01-02-2016, 03:12 PM #1Junior Guru Wannabe
- Join Date
- Dec 2011
- Posts
- 82
Optimise MySQL on VPS (2 CPUs & 3 GB RAM) with WP MU
Hello and happy new year!!
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.
Code:This is 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=10 log_queries_not_using_indexes=1 local-infile=0 join_buffer_size=1M 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=2M tmp_table_size=256M max_heap_table_size=256M table_open_cache=64 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
Code:>> MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net> >> 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: 497M (Tables: 863) [--] Data in InnoDB tables: 560K (Tables: 14) [!!] Total fragmented tables: 38 -------- 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: 1d 2h 8m 45s (1M q [11.379 qps], 22K conn, TX: 34B, RX: 1B) [--] Reads / Writes: 83% / 17% [--] Binary logging is disabled [--] Total buffers: 598.0M global + 4.5M per thread (256 max threads) [OK] Maximum reached memory usage: 815.5M (26.55% of installed RAM) [OK] Maximum possible memory usage: 1.7G (57.23% of installed RAM) [!!] Slow queries: 6% (69K/1M) [OK] Highest usage of available connections: 18% (48/256) [OK] Aborted connections: 0.12% (27/22925) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 152K sorts) [!!] Joins performed without indexes: 405 [!!] Temporary tables created on disk: 42% (45K on disk / 107K total) [OK] Thread cache hit rate: 99% (48 created / 22K connections) [!!] Table cache hit rate: 0% (64 open / 44K opened) [OK] Open file limit used: 1% (125/8K) [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 19.8% (14M used / 73M cache) [OK] Key buffer size / total MyISAM indexes: 70.0M/144.8M [OK] Read Key buffer hit rate: 99.5% (23M cached / 114K reads) [!!] Write Key buffer hit rate: 20.3% (311K cached / 248K writes) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 128.0M/560.0K [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1). [!!] InnoDB Used buffer: 6.65% (545 used/ 8191 total) [OK] InnoDB Read buffer efficiency: 99.23% (64008 hits/ 64505 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 606 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 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 ( 64) Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 1.0M, or always use indexes with joins) table_open_cache (> 64) innodb_buffer_pool_instances (=1)
Code: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/
-
01-03-2016, 08:49 PM #2Web Hosting Master
- Join Date
- May 2012
- Location
- Linux World
- Posts
- 1,137
Did you notice for sure its Mysql causing the slowness?, are the queries waiting on something as a reason for the delay?
do a 'mysqladmin proc' and see the running Mysql queries and understand the pattern.Kevin Cheri : Senior Server Administrator / Freelancer : 13+ years Exp, reach me out for any help
Server Optimization Expert / Mysql Guru / Migration Specialist
Skype : lynxmaestro
Gmail : cheri.kevin@gmail.com
-
01-04-2016, 02:55 AM #3Newbie
- Join Date
- Feb 2013
- Posts
- 11
active query cache and innodb on my.cnf
using Innodb tables and find slow query
-
01-04-2016, 03:24 AM #4Junior Guru Wannabe
- Join Date
- Apr 2013
- Posts
- 78
Bytonet.com - Web Hosting For Every Budget
-
01-04-2016, 05:10 AM #5Junior Guru Wannabe
- Join Date
- Mar 2013
- Location
- Belgrade, Serbia
- Posts
- 49
WPMU is known to cause a lot of slowdowns, that's why it's much better option to install separate WP's. So I would first suggest to start decoupling sites from WPMU and try and see will speed for separated sites improve.
-
01-06-2016, 05:25 AM #6WHT Addict
- Join Date
- Jan 2012
- Posts
- 135
you're using wordpress means, main reason will be some conflicts in database,
Install below plugin and clean your unwanted tables/codes from your database, just 2 clicks (backup your database first)
WPDBSpringClean
https://wordpress.org/plugins/wpdbspringclean/
this plugin delete all the unwanted codes added by already uninstalled plugins (every plugins leave some data in database)
so run that plugin and optimize your database, Post your result after doing this.
Offtopic:
this is database optimization, yep, install that plugin, run it, and optimize database, lot of guys are charging for thisSimple and awesomeGoogle
-
01-10-2016, 04:28 PM #7Junior Guru Wannabe
- Join Date
- Dec 2011
- Posts
- 82
check my cnf
[mysqld]
slow-query-log=1
slow-query-log-file="/var/log/mysql/slow.log"
long_query_time=1
log_queries_not_using_indexes=1
local-infile=0
max_connections=100
key_buffer_size=144M
open_files_limit=10000
max_allowed_packet=268435456
table_open_cache=900
connect_timeout=10
sort_buffer_size=10M
read_buffer_size=1M
key_buffer=16M
myisam_sort_buffer_size=32M
# old_passwords=1
max_connect_errors=10
interactive_timeout=30
wait_timeout=1000
query_cache_type=1
thread_cache_size=286
query_cache_size=200M
query_cache_limit=10M
join_buffer_size=1M
max_heap_table_size=256M
tmp_table_size=256M
user=mysql
socket="/var/lib/mysql/mysql.sock"
datadir="/var/lib/mysql"
default-storage-engine=MyISAM
innodb_file_per_table=1
innodb_buffer_pool_size=1M
innodb_buffer_pool_instances=1
[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
skip-networking
interactive-timeout
[mysqld_safe]
open_files_limit=3000
log-error="/var/log/mysqld.log"
pid-file="/var/run/mysqld/mysqld.pid"
and now shows me this:
>> MySQLTuner 1.6.1 - Major Hayden <major@mhtx.net>
>> 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: 493M (Tables: 826)
[--] Data in InnoDB tables: 656K (Tables: 16)
[!!] Total fragmented tables: 19
-------- 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: 1d 21h 37m 32s (1M q [11.720 qps], 53K conn, TX: 88B, RX: 1B)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is disabled
[--] Total buffers: 621.0M global + 12.5M per thread (100 max threads)
[OK] Maximum reached memory usage: 1.0G (34.08% of installed RAM)
[OK] Maximum possible memory usage: 1.8G (61.01% of installed RAM)
[OK] Slow queries: 2% (51K/1M)
[OK] Highest usage of available connections: 34% (34/100)
[OK] Aborted connections: 0.08% (43/53184)
[OK] Query cache efficiency: 34.7% (785K cached / 2M selects)
[!!] Query cache prunes per day: 1561
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 127K sorts)
[!!] Joins performed without indexes: 766
[!!] Temporary tables created on disk: 48% (59K on disk / 121K total)
[OK] Thread cache hit rate: 99% (34 created / 53K connections)
[!!] Table cache hit rate: 3% (900 open / 25K opened)
[OK] Open file limit used: 54% (1K/3K)
[OK] Table locks acquired immediately: 99% (980K immediate / 983K locks)
-------- MyISAM Metrics -----------------------------------------------------
[OK] Key buffer used: 100.0% (16M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/143.4M
[OK] Read Key buffer hit rate: 98.9% (33M cached / 382K reads)
[!!] Write Key buffer hit rate: 23.3% (660K cached / 506K writes)
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 5.0M/656.0K
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 80.56% (257 used/ 319 total)
[OK] InnoDB Read buffer efficiency: 96.19% (70622 hits/ 73421 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 942 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
Increasing the query_cache size over 128M may reduce performance
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 (3000) variable
should be greater than table_open_cache ( 900)
Variables to adjust:
query_cache_size (> 200M) [see warning above]
join_buffer_size (> 1.0M, or always use indexes with joins)
table_open_cache (> 900)
I will dissagre on some parts, I believe that it has to do more with configurations on the server, optimization, how much ram and cpu you have and the quality of the plugins that you'll use.
I used the Advanced Database Cleaner plugin, and did some cleaning with it.
root@server [~]# mysqladmin proc
+-----+---------------+-----------+---------------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-----+---------------+-----------+---------------+---------+------+-------+------------------+----------+
| 382 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 9 | | | 0.000 |
| 393 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 18 | | | 0.000 |
| 395 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 15 | | | 0.000 |
| 399 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 0 | | | 0.000 |
| 400 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 0 | | | 0.000 |
| 401 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 1 | | | 0.000 |
| 402 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 0 | | | 0.000 |
| 403 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 0 | | | 0.000 |
| 404 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 0 | | | 0.000 |
| 405 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 0 | | | 0.000 |
| 406 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 1 | | | 0.000 |
| 407 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 0 | | | 0.000 |
| 408 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 1 | | | 0.000 |
| 409 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 0 | | | 0.000 |
| 410 | dumdum_dbsp | localhost | dumdum_dbsp | Sleep | 1 | | | 0.000 |
| 411 | root | localhost | | Query | 0 | init | show processlist | 0.000 |
+-----+---------------+-----------+---------------+---------+------+-------+------------------+----------+
Similar Threads
-
[EU/USA] VPS 2 CPUs, 2 GB RAM, PayPal, Payza, Liberty Reserve, Skrill only $6.99/mo.
By WebHostDog in forum VPS Hosting OffersReplies: 0Last Post: 10-09-2012, 06:57 AM -
Can not run mysql on VPS. Node, OS template or other problem
By conlintonb in forum Hosting Security and TechnologyReplies: 1Last Post: 08-06-2011, 07:15 PM -
Need Someone to Set Server (Apache/PHP/MySQL) on VPS
By nikola8 in forum Systems Management RequestsReplies: 10Last Post: 03-26-2011, 10:01 PM -
Installing MySQL on VPS issue
By Shib in forum Hosting Security and TechnologyReplies: 6Last Post: 07-24-2009, 08:34 AM -
Setup apache/ftp/mysql on VPS
By Xlegged in forum Employment / Job OffersReplies: 5Last Post: 06-04-2006, 01:13 PM