Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

  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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.