MySQL Forums
Forum List  »  General

Re: MySQL Server locks up daily
Posted by: Sean Ochester
Date: September 24, 2010 10:51AM

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)

Options: ReplyQuote


Subject
Written By
Posted
September 21, 2010 03:14PM
September 21, 2010 05:07PM
September 23, 2010 01:42PM
September 23, 2010 09:20PM
Re: MySQL Server locks up daily
September 24, 2010 10:51AM
September 24, 2010 10:38PM
September 25, 2010 11:02PM
September 26, 2010 09:35AM
October 05, 2010 08:38AM
October 06, 2010 11:25PM
October 15, 2010 10:46AM
October 15, 2010 10:49AM
October 15, 2010 10:51AM
October 15, 2010 10:52AM
October 16, 2010 10:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.