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[FONT=verdana][SIZE=2][COLOR=#000000][COLOR=#000000] [/COLOR][/COLOR][/SIZE][/FONT] Code (markup): 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[FONT=verdana][SIZE=2][COLOR=#000000][COLOR=#000000] [/COLOR][/COLOR][/SIZE][/FONT] Code (markup): 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) Code (markup): Hard Disk Code (markup): 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[FONT=verdana][SIZE=2][COLOR=#000000][COLOR=#000000] [/COLOR][/COLOR][/SIZE][/FONT] Code (markup): 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)[FONT=verdana][SIZE=2][COLOR=#000000][COLOR=#000000] Code (markup): 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. [/COLOR][/COLOR][/SIZE][/FONT]
https://tools.percona.com/ Try this for a good baseline. MySQL 5.5 will have a performance benefit over 5 or 5.1. If you have the ability to install whatever you want, MariaDB or Percona are drop in MySQL replacements and are both significantly improved upon the base MySQL distribution.