filth80

Well-Known Member
Dec 11, 2009
89
0
56
Hello, i need desperate help in optimizing my.cnf. I tried different solutions for some days but no success. I post here as I have nowhere else to ask for help.

Here is my problem server configuration:

Total processors: 8 - 8 GB RAM

Vendor
GenuineIntel

Name
Intel(R) Xeon(R) CPU E5504 @ 2.00GHz

Speed
1600.000 MHz

Cache
4096 KB

Here is my.cnf:

Code:
[mysqld]
table_cache=256
innodb_file_per_table=1
max_connections=200
query_cache_size=15M
local-infile=0
sort_buffer_size=3M
thread_cache_size=4
read_rnd_buffer_size=312
log-slow-queries="/var/lib/mysql/slow.log"
join_buffer_size=10M
default-storage-engine=MyISAM
log-queries-not-using-indexes
mysqlth.png

Code:
>>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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: 485M (Tables: 2547)
[--] Data in InnoDB tables: 1M (Tables: 58)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 3)
[!!] Total fragmented tables: 99

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 48m 0s (61K q [21.203 qps], 3K conn, TX: 529M, RX: 18M)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 183.0M global + 13.4M per thread (200 max threads)
[OK] Maximum possible memory usage: 2.8G (35% of installed RAM)
[OK] Slow queries: 5% (3K/61K)
[OK] Highest usage of available connections: 4% (8/200)
[OK] Key buffer size / total MyISAM indexes: 8.0M/233.1M
[OK] Key buffer hit rate: 100.0% (62M cached / 12K reads)
[OK] Query cache efficiency: 50.3% (19K cached / 38K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 7% (230 temp sorts / 3K sorts)
[!!] Joins performed without indexes: 677
[OK] Temporary tables created on disk: 13% (415 on disk / 2K total)
[OK] Thread cache hit rate: 99% (11 created / 3K connections)
[!!] Table cache hit rate: 7% (256 open / 3K opened)
[OK] Open file limit used: 48% (500/1K)
[OK] Table locks acquired immediately: 99% (37K immediate / 37K locks)
[OK] InnoDB data size / buffer pool: 1.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 (> 10.0M, or always use indexes with joins)
    table_cache (> 256)

Please give me a my.cnf file based on these specifications. Yes I know I should wait 24 hours, etc. Reffering to join_buffer_size if I increase it, it eants more, and so on, until i receive the "ram memory" warning.
I ask here for help, as i am out of solutions.

P.S Sometiomes the value of "mysql" process increases even tu 150%.

Thanks alot for your help.
 

filth80

Well-Known Member
Dec 11, 2009
89
0
56
Here is my.cnf after 24 hours
>> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>> 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: 499M (Tables: 2603)
[--] Data in InnoDB tables: 1M (Tables: 57)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 3)
[!!] Total fragmented tables: 78

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 54m 17s (1M q [18.296 qps], 108K conn, TX: 9B, RX: 316M)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 418.0M global + 2.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 814.4M (10% of installed RAM)
[OK] Slow queries: 0% (30/1M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 250.0M/236.5M
[OK] Key buffer hit rate: 100.0% (1B cached / 143K reads)
[OK] Query cache efficiency: 53.2% (553K cached / 1M selects)
[!!] Query cache prunes per day: 55025
[OK] Sorts requiring temporary tables: 5% (3K temp sorts / 63K sorts)
[!!] Joins performed without indexes: 14318
[OK] Temporary tables created on disk: 11% (8K on disk / 70K total)
[OK] Thread cache hit rate: 99% (245 created / 108K connections)
[!!] Table cache hit rate: 2% (500 open / 21K opened)
[OK] Open file limit used: 23% (964/4K)
[OK] Table locks acquired immediately: 99% (895K immediate / 896K locks)
[OK] InnoDB data size / buffer pool: 1.0M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 8M)
join_buffer_size (> 384B, or always use indexes with joins)
table_cache (> 500)
 

SB-Nick

Well-Known Member
Aug 26, 2008
175
9
68
cPanel Access Level
Root Administrator
Ask your developer (or hire one) to add indexes for your large database tables and run a mysqlcheck --optimize for the fragmented tables.