During lock up I'm not able to run show processlist; The db server complained about too many connections.
The OS is FreeBSD 7.1-RELEASE-p4 amd64 (64 bit)
Hardware - 2 CPUs (2.5 GHz, 8GB RAM)
I'm primarly using InnoDB but have some MyISAM
Below is are the results from a mysqltuner script.
mysql> SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 81500 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Q%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 4270 |
| Qcache_free_memory | 14138656 |
| Qcache_hits | 53683919 |
| Qcache_inserts | 4676008 |
| Qcache_lowmem_prunes | 901685 |
| Qcache_not_cached | 1571719 |
| Qcache_queries_in_cache | 8513 |
| Qcache_total_blocks | 22238 |
| Queries | 71886872 |
| Questions | 71880238 |
+-------------------------+----------+
10 rows in set (0.00 sec)
------------------------------------------------------------------------------------
>> MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at
http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.33-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 3301)
[--] Data in InnoDB tables: 3G (Tables: 702)
[--] Data in MEMORY tables: 0B (Tables: 3)
[!!] Total fragmented tables: 756
-------- Performance Metrics -------------------------------------------------
[--] Up for: 22h 53m 42s (72M q [884.009 qps], 1M conn, TX: 233B, RX: 10B)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 3.3G global + 36.2M per thread (300 max threads)
[!!] Maximum possible memory usage: 13.9G (154% of installed RAM)
[OK] Slow queries: 0% (45K/72M)
[OK] Highest usage of available connections: 52% (157/300)
[OK] Key buffer size / total MyISAM indexes: 128.0M/539.2M
[OK] Key buffer hit rate: 100.0% (9B cached / 240K reads)
[OK] Query cache efficiency: 80.5% (54M cached / 67M selects)
[!!] Query cache prunes per day: 948192
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 3246
[OK] Temporary tables created on disk: 0% (27K on disk / 3M total)
[OK] Thread cache hit rate: 99% (6K created / 1M connections)
[!!] Table cache hit rate: 6% (2K open / 29K opened)
[OK] Open file limit used: 30% (2K/8K)
[OK] Table locks acquired immediately: 99% (25M immediate / 25M locks)
[!!] InnoDB data size / buffer pool: 3.5G/3.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 32M)
join_buffer_size (> 16.0M, or always use indexes with joins)
table_cache (> 2000)
innodb_buffer_pool_size (>= 3G)