0

Hello serverfault community,

I have a medium sized VPS on which I run 2 Magento installations, a few static pages as well as several WordPress / Joomla installation - all personal websites and project - overall nothing extremely fancy or with extreme traffic.

My VPS Specifications:

Memory: 12288 MB, CPU cores: 4 × 2.4 Ghz, OS: CentOS, Extras: WHM / cPanel

Generally the speed was always there and I could not complain, however recently I noticed websites loading slower, to be more specific it's all websites that require a MySQL database.

I asked someone for advice and I was told it's most likely related to the growth of my databases and / or a bad my.cnf configuration file.

My current my.cnf looks as follows:

    [mysqld]
    open_files_limit=64000
    local-infile=0
    query_cache_size=512M
    query_cache_limit=2M
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    thread_cache_size=4
    join_buffer_size=4M
    key_buffer_size=128M
    table_cache=640

    log-slow-queries=/home/mysql-slow-queries.log
    long_query_time=1

I was recommended to run mysqltuner, which I did with the following output:

    -------- 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: 165M (Tables: 1034)
    [--] Data in InnoDB tables: 92M (Tables: 1101)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 372K (Tables: 55)
    [!!] Total fragmented tables: 25

    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 4h 39m 42s (571K q [34.034 qps], 15K conn, TX: 786M, RX: 113M)
    [--] Reads / Writes: 54% / 46%
    [--] Total buffers: 800.0M global + 6.6M per thread (151 max threads)
    [OK] Maximum possible memory usage: 1.8G (14% of installed RAM)
    [OK] Slow queries: 0% (2/571K)
    [OK] Highest usage of available connections: 7% (12/151)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/40.3M
    [OK] Key buffer hit rate: 96.5% (548K cached / 18K reads)
    [OK] Query cache efficiency: 93.2% (440K cached / 472K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)
    [!!] Joins performed without indexes: 946
    [OK] Temporary tables created on disk: 11% (2K on disk / 22K total)
    [OK] Thread cache hit rate: 99% (63 created / 15K connections)
    [!!] Table cache hit rate: 0% (634 open / 268K opened)
    [OK] Open file limit used: 1% (980/64K)
    [OK] Table locks acquired immediately: 99% (121K immediate / 121K locks)
    [OK] InnoDB data size / buffer pool: 93.0M/128.0M


    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits

    Variables to adjust:
        join_buffer_size (> 4.0M, or always use indexes with joins)
        table_cache (> 640)

After supplying this output I was recommended to apply the following changes:

        thread_cache_size=16
        query_cache_size=1024M
        join_buffer_size=8M
        table_cache=4096

My questions:

  1. Are the recommendations I was given safe? I did not apply them yet, but from what I read throughout other my.cnf related posts they seem a little bit over the top.

  2. I reckon that my current my.cnf file is rather "slim" if I am to compare it with other my.cnf files I've stumbled upon the web. Is there anything in particular that you think I am missing out?

I am definitely not an expert or system administrator but merely an amateur, therefore it would be greatly appreciated if someone can share a little insight on how I can improve / enhance my current configuration. Thank you very much.

1 Answer 1

0
  1. Tune kernel.shmmax to the 10G (change /etc/sysctl.conf, run sysctl -p /etc/sysctl.conf)

  2. Yes, it looks safe, will eat about 2G more RAM, but depends on other options too

It is safe to apply changes, reload server and then run mysqltuner, check this string:

[OK] Maximum possible memory usage: 1.8G (14% of installed RAM)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .