Latest Announcemnent
New thread

my.cnf optimimization

Status
Not open for further replies.
S

schranzhans

Active Member
74
2011
4
0
hey
i have an vps with the following specs
4 CPU Cores
Code: 
vendor_id       : GenuineIntel
model name      : Intel(R) Xeon(R) CPU           E5620  @ 2.40GHz
2Gb RAM
4Gb RAM with Burst
OS CentOs 5.6 64bit
management with CPanel


i have running 3 domains on it
1 board (ipboard) and 2 wordpress blogs
the 3 sites are often slow like hell, not good
i not really have a clue , how to optimize the my.cnf
i read tutorials about it , but it get more worse then better :D
so , if somebody could help me to optimize this thing , would be cool.
Small payment can be made if you want ;)

my.cnf
Code: 
[mysqld]
innodb_file_per_table=1
open_files_limit=2454
max_allowed_packet=12M
thread_cache_size =4
table_cache=12000
query_cache_size=16M
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=3
max_connections=100
table_definition_cache=2048

if you need more infos ,write me , i will add it
 
5 comments
hey Lock Down,
i tried it before , but i think it just got worse than before :/

Big thanks to ServicePolice who tried to help me :)
Good Guys:friends:


But i'm still searching
i installed percona mysql , i think it got little better , but now mysql is eating all my RAM }:-)

mysqltuner says
Code: 
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.15-56
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 402M (Tables: 298)
[--] Data in InnoDB tables: 67M (Tables: 111)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 32

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 44m 9s (2M q [28.940 qps], 287K conn, TX: 64B, RX: 831M)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 800.0M global + 1.1M per thread (400 max threads)
[OK] Maximum possible memory usage: 1.2G (30% of installed RAM)
[OK] Slow queries: 0% (79/2M)
[OK] Highest usage of available connections: 12% (50/400)
[OK] Key buffer size / total MyISAM indexes: 256.0M/132.1M
[OK] Key buffer hit rate: 99.8% (127M cached / 223K reads)
[!!] Query cache efficiency: 0.0% (0 cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 5% (11K temp sorts / 215K sorts)
[!!] Joins performed without indexes: 16237
[!!] Temporary tables created on disk: 44% (171K on disk / 386K total)
[OK] Thread cache hit rate: 98% (4K created / 287K connections)
[OK] Table cache hit rate: 46% (824 open / 1K opened)
[OK] Open file limit used: 18% (919/5K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 67.9M/384.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
 
Last edited:
Status
Not open for further replies.
Back
Top