MySQL Server/DB Optimization

note you will be working on an adult website. Issue: - We have a client with large video sharing website running AVS script. Average users online 500. - We notice there was lag in website speed when there in 2000 users online at peak time (11AM-2PM EST) - The issue appears not with web servers or loadbalancer however mysql server shows sign of high cpu usage when this happens. - During peak time 4-6CPU cores being used by mysql and load goes up to 200. We have recently upgraded the mysql server to 2 X Intel(R) Xeon(R) CPU E5-2620, 16GB ram, SSD drive and the issue still there. The issue appears not I/O wait or memory usage. Highest mysql connection peak at 900 at a given time. - MYSQL tuning script statics below -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.95-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 44M (Tables: 69) [!!] Total fragmented tables: 7 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 21h 21m 54s (72M q [445.462 qps], 5M conn, TX: 46B, RX: 7B) [--] Reads / Writes: 78% / 22% [--] Total buffers: 778.0M global + 52.2M per thread (1500 max threads) [!!] Maximum possible memory usage: 77.3G (494% of installed RAM) [OK] Slow queries: 5% (3M/72M) [OK] Highest usage of available connections: 62% (937/1500) [OK] Key buffer size / total MyISAM indexes: 256.0M/35.5M [OK] Key buffer hit rate: 100.0% (1B cached / 19K reads) [OK] Query cache efficiency: 40.4% (19M cached / 48M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (4K temp sorts / 13M sorts) [OK] Temporary tables created on disk: 2% (11K on disk / 449K total) [OK] Thread cache hit rate: 99% (4K created / 5M connections) [OK] Table cache hit rate: 99% (1K open / 1K opened) [OK] Open file limit used: 16% (1K/7K) [!!] Table locks acquired immediately: 90% -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Optimize queries and/or use InnoDB to reduce lock wait Project Goal: The website navigation speed shouldn't lag and should have same speed when the online user is 1000 or 2000. The hardware is not an issue here or the web server. Website growing daily and should be able to handle the growth without a beat. Project Requirement: - Login to mysql server during peak time (11AM-2PM EST) few days to observe the load spike and compare the navigation speed - Mysql server does not have any control panel, you should be able to work through command only via ssh - You must be well versed in linux,mysql, php as you may have to re-write the mysql query that script uses to reduce load or table locks - Downtime is not acceptable, if you want to make changes to sql server can only be done during off peak hours (6PM - 9PM EST) and must be communicated in advance of how long you need - All changes must be documented and presented to us once completed, we will need this when we move to another sql server. - If you have any questions about the infrastructure design, ask before bidding - Project goal should be met (min 90%) Project Budget: $100 Please bid only if you can meet the project requirements!!!