MySQL 5.1.49 crashing every two days

MySQL & MariaDB Other MySQL® Questions
1

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/

2

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.

3

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
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size

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.

4

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.