Results 1 to 7 of 7
  1. #1
    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
    And this results of MySQLTuner
    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)
    My ls -l /var/lib/mysql
    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/

  2. #2
    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

  3. #3
    active query cache and innodb on my.cnf
    using Innodb tables and find slow query

  4. #4
    Join Date
    Apr 2013
    Posts
    78
    Did you try query caching? Looks like it is disabled.

    Quote Originally Posted by B_Dark View Post
    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
    And this results of MySQLTuner
    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)
    My ls -l /var/lib/mysql
    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/
    Bytonet.com - Web Hosting For Every Budget

  5. #5
    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.

  6. #6
    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 this
    Simple and awesome
    Google

  7. #7
    Join Date
    Dec 2011
    Posts
    82
    Quote Originally Posted by iwhost View Post
    active query cache and innodb on my.cnf
    using Innodb tables and find slow query
    Quote Originally Posted by ByteChef View Post
    Did you try query caching? Looks like it is disabled.
    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)

    Quote Originally Posted by Nemke View Post
    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.
    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.
    Quote Originally Posted by feastboy View Post
    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 this
    I used the Advanced Database Cleaner plugin, and did some cleaning with it.

    Quote Originally Posted by kevincheri View Post
    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.
    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

  1. Replies: 0
    Last Post: 10-09-2012, 06:57 AM
  2. Can not run mysql on VPS. Node, OS template or other problem
    By conlintonb in forum Hosting Security and Technology
    Replies: 1
    Last Post: 08-06-2011, 07:15 PM
  3. Need Someone to Set Server (Apache/PHP/MySQL) on VPS
    By nikola8 in forum Systems Management Requests
    Replies: 10
    Last Post: 03-26-2011, 10:01 PM
  4. Installing MySQL on VPS issue
    By Shib in forum Hosting Security and Technology
    Replies: 6
    Last Post: 07-24-2009, 08:34 AM
  5. Setup apache/ftp/mysql on VPS
    By Xlegged in forum Employment / Job Offers
    Replies: 5
    Last Post: 06-04-2006, 01:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •