Not the answer you need?
Register and ask your own question!

MySQL 5.1.49 crashing every two days

maximusmaximus EntrantInactive User Role Participant
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/

Comments

  • justin.swanhartjustin.swanhart MySQL Sage Inactive User Role Advisor
    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.
  • maximusmaximus Entrant Inactive User Role Participant
    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
    ( [EMAIL="@@key_buffer_size"]@@key_buffer_size[/EMAIL] + [EMAIL="@@query_cache_size"]@@query_cache_size[/EMAIL] + [EMAIL="@@tmp_table_size"]@@tmp_table_size[/EMAIL]
    + [EMAIL="@@innodb_buffer_pool_size"]@@innodb_buffer_pool_size[/EMAIL] + [EMAIL="@@innodb_additional_mem_pool_size"]@@innodb_additional_mem_pool_size[/EMAIL]
    + [EMAIL="@@innodb_log_buffer_size"]@@innodb_log_buffer_size[/EMAIL]
    + [EMAIL="@@max_connections"]@@max_connections[/EMAIL] * (
    [EMAIL="@@read_buffer_size"]@@read_buffer_size[/EMAIL] + [EMAIL="@@read_rnd_buffer_size"]@@read_rnd_buffer_size[/EMAIL] + [EMAIL="@@sort_buffer_size"]@@sort_buffer_size[/EMAIL]
    + [EMAIL="@@join_buffer_size"]@@join_buffer_size[/EMAIL] + [EMAIL="@@binlog_cache_size"]@@binlog_cache_size[/EMAIL] + @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.
  • maximusmaximus Entrant Inactive User Role Participant
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.