No announcement yet.

MySQL 5.1.49 crashing every two days

  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL 5.1.49 crashing every two days

    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.

    Our system parameters and settings:
    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


    port = 3306
    socket = /var/run/mysqld/mysqld.sock

    socket = /var/run/mysqld/mysqld.sock
    nice = 0

    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

    # * 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

    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_bin = /var/log/mysql/mysql-bin.log

    expire_logs_days = 10
    max_binlog_size = 100M


    max_allowed_packet = 16M

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

    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;

      ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
      + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size
      + @@innodb_log_buffer_size
      + @@max_connections * (
      @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
      + @@join_buffer_size + @@binlog_cache_size + @thread_stack
      ) ) / @giga_bytes AS MAX_MEMORY_GB;

      Gives as result:

      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.



      • #4
        Yes, tmp_table_size is per connection, and by default, a connection may create up to 32 temporary tables.

        The variable controls at what size a temporary table is moved from in-memory to on-disk.


        • #5

          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!



          • #6
            Dear Justin,

            after i made the following change (in the configuration file posted above)

            tmp_table_size= 16M
            #max_heap_table_size= 128M

            the server is still "crashing".

            Could you please let me know if you have any idea ? Should i reduce the tmp_table_size more ?

            Thank you,