# MySQL 5.1.49 crashing every two days

**URL:** <https://forums.percona.com/t/mysql-5-1-49-crashing-every-two-days/1502>\
**Category:** Other MySQL® Questions\
**Created:** [September 9, 2010, 1:55pm UTC](https://forums.percona.com/t/mysql-5-1-49-crashing-every-two-days/1502 "2010-09-09T13:55:11Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![maximus](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@maximus](https://forums.percona.com/u/maximus)\
**Post date:** [September 9, 2010, 1:55pm UTC](https://forums.percona.com/t/mysql-5-1-49-crashing-every-two-days/1502/1 "2010-09-09T13:55:11Z")

</div>

Hi all,

our mysql system is “crashing” every two days.  
By crash i mean the following:

- it doesn’t responds to ping
- we can’t login with SSH
- we don’t get any answer from MySQL.
- there is no entry in the error logs ! neither from linux neither from MySQL.
- we have already changed to a completely new hardware, we have the same problem, so it’s definitely not a hardware problem.
- we do not have any other software installed except a firewall (iptables rule)

Could someone help me, by giving me some pointers what could i do to figure out the problem ? I have included every detail about our settings.

Thank you in advance for your help.  
Max.

Our system parameters and settings:  
-System:  
Memory: 12GB,  
Processor: Intel 7-920 Quadcore  
-Operating system:  
Debian 5 (lenny)  
-MySQL 5.1.49  
-Databases: 2  
a) small phpbb forum  
b) 6GB database 3 tables with about 15 million rows

## my.cnf

[client]  
port = 3306  
socket = /var/run/mysqld/mysqld.sock

[mysqld\_safe]  
socket = /var/run/mysqld/mysqld.sock  
nice = 0

[mysqld]  
user = mysql  
pid-file = /var/run/mysqld/mysqld.pid  
socket = /var/run/mysqld/mysqld.sock  
port = 3306  
basedir = /usr  
datadir = /var/lib/mysql  
tmpdir = /tmp  
language = /usr/share/mysql/english  
skip-external-locking

# \* Fine Tuning

key\_buffer = 16M  
max\_allowed\_packet = 16M  
thread\_stack = 256K  
thread\_cache\_size = 32  
max\_connections = 300  
table\_cache = 2048  
#thread\_concurrency = 4

# Used for InnoDB tables recommended to 50%-80% available memory

innodb\_buffer\_pool\_size = 6G

# 20MB sometimes larger

innodb\_additional\_mem\_pool\_size = 20M

# 8M-16M is good for most situations

innodb\_log\_buffer\_size = 8M

# Disable XA support because we do not use it

innodb-support-xa = 0

# 1 is default wich is 100% secure but 2 offers better performance

innodb\_flush\_log\_at\_trx\_commit = 1

innodb\_flush\_method = O\_DIRECT  
#innodb\_thread\_concurency = 8

# Recommended 64M - 512M depending on server size

innodb\_log\_file\_size = 512M

# One file per table

innodb\_file\_per\_table

tmp\_table\_size = 128M  
max\_heap\_table\_size = 128M  
sort\_buffer\_size = 1M  
read\_buffer\_size = 1M  
read\_rnd\_buffer\_size = 1M

# \* Query Cache Configuration

query\_cache\_limit = 1M  
query\_cache\_size = 32M  
query\_cache\_type = 1  
query\_cache\_min\_res\_unit= 2K  
join\_buffer\_size = 1M

# Here you can see queries with especially long duration

log\_slow\_queries = /var/log/mysql/mysql-slow.log  
long\_query\_time = 2  
log-queries-not-using-indexes

log\_bin = /var/log/mysql/mysql-bin.log

expire\_logs\_days = 10  
max\_binlog\_size = 100M

ignore\_builtin\_innodb  
plugin-load=innodb=ha\_innodb\_plugin.so

[mysqldump]  
quick  
quote-names  
max\_allowed\_packet = 16M

[mysql]  
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]  
key\_buffer = 16M

# 

# \* IMPORTANT: Additional settings that can override those from this file!

# 

!includedir /etc/mysql/conf.d/

---

<div class="post-metadata">

**Author:** ![justin.swanhart](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/justin.swanhart/32/16904_2.png) [@justin.swanhart](https://forums.percona.com/u/justin.swanhart)\
**Post date:** [September 9, 2010, 2:08pm UTC](https://forums.percona.com/t/mysql-5-1-49-crashing-every-two-days/1502/2 "2010-09-09T14:08:47Z")

</div>

You are likely exhausting all memory and start using massive amounts of virtual memory which eventually leaves the system unresponsive.

You have almost all per-connection buffers set to 1M, and tmp\_table\_size set to 128MB. You can likely exhaust all resources at 300 connections.

---

<div class="post-metadata">

**Author:** ![maximus](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@maximus](https://forums.percona.com/u/maximus)\
**Post date:** [September 9, 2010, 2:55pm UTC](https://forums.percona.com/t/mysql-5-1-49-crashing-every-two-days/1502/3 "2010-09-09T14:55:35Z")

</div>

Hi Justin,

thanks for your quick reply.

Executing the following query:

SET @kilo\_bytes = 1024;  
SET @mega\_bytes = @kilo\_bytes \* 1024;  
SET @giga\_bytes = @mega\_bytes \* 1024;

SELECT  
( &#64;&#64;key\_buffer\_size + &#64;&#64;query\_cache\_size + &#64;&#64;tmp\_table\_size

- &#64;&#64;innodb\_buffer\_pool\_size + &#64;&#64;innodb\_additional\_mem\_pool\_size
- &#64;&#64;innodb\_log\_buffer\_size
- &#64;&#64;max\_connections \* (  
&#64;&#64;read\_buffer\_size + &#64;&#64;read\_rnd\_buffer\_size + &#64;&#64;sort\_buffer\_size
- &#64;&#64;join\_buffer\_size + &#64;&#64;binlog\_cache\_size + @thread\_stack  
) ) / @giga\_bytes AS MAX\_MEMORY\_GB;

Gives as result:  
7.4535

I am not sure if “tmp\_table\_size” is a per connection value or not, could you please tell me if it is a per connection value ? i couldn’t find any clear answer by googling.

Thank you.

Max.

---

<div class="post-metadata">

**Author:** ![maximus](https://avatars.discourse-cdn.com/v4/letter/m/57b2e6/32.png) [@maximus](https://forums.percona.com/u/maximus)\
**Post date:** [September 9, 2010, 3:23pm UTC](https://forums.percona.com/t/mysql-5-1-49-crashing-every-two-days/1502/4 "2010-09-09T15:23:19Z")

</div>

Justin,

I will reduce the tmp\_table\_size to 16MB (it’s default) and see how our server will behave.

Thank you very much for your help!

Max.
