1. Home
  2. Forums Index
  3. Server Side / Databases 2:53 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

Best optimization for my.cnf, need help.

         

hp_solomon

7:23 am on Jun 14, 2012 (gmt 0)

10+ Year Member



Hello everyone,

I need the best optimization/configuration for "my.cnf". Linux CENTOS 5.8, Mysql 5.0.95

Currently for my.cnf I have the following: 
set-variable = max_connections=800
log-slow-queries
safe-show-database
query_cache_limit=512K
wait_timeout=3600
query_cache_size=512M
table_cache=16384
key_buffer=512M
myisam_sort_buffer_size=128M
join_buffer=16M
read_buffer=16M
sort_buffer=4M
tmp_table_size=256M
thread_cache_size=16K


Processor Information
Total processors: 8
Processor #1
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
Speed
2000.169 MHz
Cache
4096 KB
Processor #2
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
Speed
2000.169 MHz
Cache
4096 KB
Processor #3
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
Speed
2000.169 MHz
Cache
4096 KB
Processor #4
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
Speed
2000.169 MHz
Cache
4096 KB
Processor #5
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
Speed
2000.169 MHz
Cache
4096 KB
Processor #6
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
Speed
2000.169 MHz
Cache
4096 KB
Processor #7
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
Speed
2000.169 MHz
Cache
4096 KB
Processor #8
Vendor
GenuineIntel
Name
Intel(R) Xeon(R) CPU E5335 @ 2.00GHz
Speed
2000.169 MHz
Cache
4096 KB


Memory Information: 
Memory for crash kernel (0x0 to 0x0) notwithin permissible range
Memory: 8306784k/9175040k available (2192k kernel code, 79888k reserved, 913k data, 232k init, 7470464k highmem)


Hard Disk 
SCSI device sda: 937433088 512-byte hdwr sectors (479966 MB)
sda: Write Protect is off
sda: Mode Sense: 23 00 00 00
SCSI device sda: drive cache: write back, no read (daft)
SCSI device sda: 937433088 512-byte hdwr sectors (479966 MB)
sda: Write Protect is off
sda: Mode Sense: 23 00 00 00
SCSI device sda: drive cache: write back, no read (daft)
sd 0:0:0:0: Attached scsi disk sda


mysqltuner.pl returns 
 /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

 >> MySQLTuner 1.1.2 - 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.0.95-community-log
[!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 1104)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!] Total fragmented tables: 58

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18h 38m 26s (4M q [60.426 qps], 177K conn, TX: 3B, RX: 1B)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 1.0G global + 36.4M per thread (800 max threads)
[!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!] Maximum possible memory usage: 29.5G (372% of installed RAM)
[OK] Slow queries: 0% (2/4M)
[OK] Highest usage of available connections: 10% (80/800)
[OK] Key buffer size / total MyISAM indexes: 512.0M/1.2G
[OK] Key buffer hit rate: 100.0% (3B cached / 100K reads)
[OK] Query cache efficiency: 33.4% (1M cached / 3M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 985K sorts)
[!] Joins performed without indexes: 22154
[OK] Temporary tables created on disk: 0% (3K on disk / 1M total)
[OK] Thread cache hit rate: 99% (80 created / 177K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 8% (2K/33K)
[OK] Table locks acquired immediately: 96% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 208.0K/8.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
Variables to adjust:
 *** MySQL's maximum memory usage is dangerously high ***
 *** Add RAM before increasing MySQL buffer variables ***
 join_buffer_size (> 16.0M, or always use indexes with joins)


Website is typical phpfox with forums, blogs, etc. I would also like to ask if upgrading mysql to latest version will help improve performance?

Thanks in advance.

Frank_Rizzo

10:10 am on Jun 14, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



You only have 18hrs use there but if those mysqltuner figures are typical then you have some settings that are way out:

* You only have 2 slow queries out of 4M. That's good.

* Available connections 80 out of 800. That 800 is way too high

* Any memory saving should be redirected to Key Buffer. Ideally you want it to match the 1.2G

* Query Cache is a pointless feature. Best to turn it off.

* You don't seem to be using InnoDB. The 208K is probably just test / background stuff. Disable InnoDB in your my.cnf, restart, check all works, then delete any of the innodb logfiles in the root of your mysql directory

* Lower the wait_timeout

* Join buffer and sort buffer are way too high. Some resources state this works best with as low as 384K. You will need to experiment but start with something like 2M

Other things to consider:

You are using 32-bit and not PAE memory? The 8Gb is not being used effectively. Ideally you want to upgrade to Centos 64-bit version and 64-bit mysql but this is not an easy job.

A later mysql version will be better (for bug fixes and security) but watch that there are now major differences in my.cnf settings with newer versions compared to old. Some statements are deprecated, and some such as disabling innodb, slow log settings require different statements.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 2:53 am May 21, 2026