Optimizing my.cnf Suggestions Needed

[jimlongo]

I wonder if anyone would care to help me optimize mysql on my server.

It is a Centos6.5 VPS with 2MB of RAM.
For the most part mysql seems to run well. However there are times it will slow down, and memory use will increase so that it seems I need to restart it to get it going again. There are maybe 50 email addresses and 10-20 low traffic Wordpress sites (most of them don't allow comments), less than 5 Drupal sites. Most website tables are INNODB.

Even though currently the slow log function is off, when enabled nothing writes to the slow.log even though mysql>status will show that there are slow queries.

Here is my.cnf

[mysqld]
log-warnings=2
symbolic-links=0

net_read_timeout=120
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
local-infile=0

open_files_limit=3480
max_connections=50

max_allowed_packet=32M
wait_timeout=600

innodb_buffer_pool_size=256M
thread_cache_size=4
table_open_cache=2100
table_cache=2250
query_cache_size=64M
query_cache_limit=48M

tmp_table_size=256M
max_heap_table_size=256M

join_buffer_size=148k
key_buffer_size=24M

general_log=1
general_log_file=/var/lib/mysql/general.log
#slow_query_log_file=/var/lib/mysql/slow.log
#long_query_time=1

[mysqld_safe]
log-error=/var/log/mysqld.log

And the latest output of mysqltuner

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.36-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 50M (Tables: 24)
[--] Data in InnoDB tables: 119M (Tables: 1407)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 1410

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 16h 2m 26s (426K q [2.957 qps], 19K conn, TX: 1B, RX: 58M)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 616.0M global + 2.8M per thread (50 max threads)
[OK] Maximum possible memory usage: 754.5M (36% of installed RAM)
[OK] Slow queries: 0% (225/426K)
[OK] Highest usage of available connections: 60% (30/50)
[OK] Key buffer size / total MyISAM indexes: 24.0M/10.4M
[OK] Key buffer hit rate: 99.0% (216K cached / 2K reads)
[OK] Query cache efficiency: 68.4% (230K cached / 336K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5K sorts)
[OK] Temporary tables created on disk: 15% (1K on disk / 12K total)
[OK] Thread cache hit rate: 99% (110 created / 19K connections)
[OK] Table cache hit rate: 56% (2K open / 3K opened)
[OK] Open file limit used: 2% (104/4K)
[OK] Table locks acquired immediately: 100% (106K immediate / 106K locks)
[OK] InnoDB data size / buffer pool: 119.7M/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries

Thanks for any input.

 

[cPanelMichael]

Hello

In addition to optimizing your MySQL configuration, you may also want to use a command such as "mysqladmin processlist" the next time you notice slowness to see any particular databases are the source of the issue.

Thank you.

 

[server10]

I see that in your my.cnf file,general_log is set to 1. I suggest you disable general log because it slows down your server very quickly

 

[jimlongo]

I see that in your my.cnf file,general_log is set to 1. I suggest you disable general log because it slows down your server very quickly

Thanks I've done that. I did some research after you comment and didn't realize the performance implications.