my.cnf question

[Dmacleo]

so I am looking around at tuning sql, this is my.cnf on CPANEL server (centos 6.2 64bit) located in /etc

[mysqld]

# safe-show-database

log-slow-queries

max_connections=500

default-storage-engine=MyISAM

innodb_file_per_table=1

lol seems a bit bare. I should be able to drop those max connections right?

[Elly]

run

script -f mysql.log


wget mysqltuner.pl


chmod +x mysqltuner.pl


./mysqltuner.pl


exit

Open and paste the contents of mysql.log here :)

[Dmacleo]

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at

http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.5.24-cll

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 451M (Tables: 1152)

[--] Data in InnoDB tables: 224K (Tables: 14)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 33

-------- Security Recommendations -------------------------------------------

[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------

[--] Up for: 2h 15m 23s (47K q [5.888 qps], 1K conn, TX: 1B, RX: 332M)

[--] Reads / Writes: 51% / 49%

[--] Total buffers: 168.0M global + 2.8M per thread (500 max threads)

[OK] Maximum possible memory usage: 1.5G (37% of installed RAM)

[OK] Slow queries: 0% (4/47K)

[OK] Highest usage of available connections: 1% (6/500)

[OK] Key buffer size / total MyISAM indexes: 8.0M/263.2M

[OK] Key buffer hit rate: 99.6% (2M cached / 8K reads)

[!!] Query cache is disabled

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)

[OK] Temporary tables created on disk: 19% (1K on disk / 5K total)

[!!] Thread cache is disabled

[!!] Table cache hit rate: 15% (400 open / 2K opened)

[OK] Open file limit used: 31% (795/2K)

[OK] Table locks acquired immediately: 99% (49K immediate / 49K locks)

[!!] Connections aborted: 6%

[OK] InnoDB data size / buffer pool: 224.0K/128.0M

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Set thread_cache_size to 4 as a starting value

Increase table_cache gradually to avoid file descriptor limits

Your applications are not closing MySQL connections properly

Variables to adjust:

query_cache_size (>= 8M)

thread_cache_size (start at 4)

table_cache (> 400)

server was restarted recently, right now its really small forum.
Your applications are not closing MySQL connections properly

I wonder about that one and the innodb. need to open other domains on the vps and see whats going on there.

[AlexJ]

Add this in your my.cnf

thread_cache_size = 56
query_cache_size = 56M
table_cache = 500

[Dmacleo]

will give that a try, thanks.
never had chance to look into that closing connections error.

[Dmacleo]

running this gives nothing listed in recomendations, need to run it a few days to see.

[mysqld]

# safe-show-database

log-slow-queries

max_connections=500

default-storage-engine=MyISAM

innodb_file_per_table=1

thread_cache_size=56

query_cache_size=56M

query_cache_type=1

table_cache=500

query_cache_limit=10M

key_buffer_size=270M

[AlexJ]

U can also change this on next restart:

max_connections = 100 instead of 500.

and make this key_buffer_size to 512M


Then run MySQL tuner after 2-3 days and post the result back here.

[Dmacleo]

I upped the key buffer size until it did not show warnings then added like 10 or so, will keep eye on that one and see.
I forgot to drop the key connections down though, grr should do that right now I guess LOL

[Kyle F]

In cPanel WHM, don't forget to restart the MySQL service. This worked for me when I had to add those lines in my my.cnf file. :)

[Dmacleo]

oh yeah done that a few times today , lol
got shoutbox sql error log every time too :P