MySQL crashing randomly

Question

We have a sever running a website Joomla 2.5. We are facing frequent mysql crashes that occur when website has medium visitor load like a few thousands. It appears that mysql consumes more memory and is killed by OOM killer. Currently if I look for memory consumption for mysql using top command, it says that mysql process is consuming 17% of memory. Normally the server shows upto 45% of total memory used. But when mysql crashes, system memory is recorded to be between 70% to 95% or sometimes 100%.

I have a feeling that its mysql memory settings (I think we have set huge values for different variables) which cause it to consume that much memory at peak traffic hours. Or it is the myisam tables in Joomla 2.5 that are causing it to crash due to locking of tables. Please suggest your thoughts.

Error from mysql.log is as follows,

140720 17:07:42 [Note] Plugin 'FEDERATED' is disabled.
140720 17:07:42  InnoDB: Initializing buffer pool, size = 512.0M
140720 17:07:42  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140720 17:07:42  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140720 17:07:42  InnoDB: Started; log sequence number 2 2605701970
140720 17:07:42 [Note] Event Scheduler: Loaded 0 events
140720 17:07:42 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.73-0ubuntu0.10.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Our server:

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000  
RAM : 1Gb  
Hdd : 50Gb  
OS Ubuntu 10.04.4 LTS

my.cnf :

max_connections = 250
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
key_buffer = 16M
read_buffer_size = 128K
join_buffer_size = 128K
connect_timeout = 10
max_connect_errors = 10
max_allowed_packet = 16M
tmp_table_size = 16M
innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=40M
innodb_log_buffer_size=40M
innodb_thread_concurrency=2
innodb_flush_log_at_trx_commit=1

dmesg:

[276924.125161] Out of memory in UB 506518: OOM killed process 281 (mysqld) score 0 vm:983808kB, rss:119628kB, swap:57936kB

[276925.533666] Out of memory in UB 506518: OOM killed process 3473 (mysqld) score 0 vm:787876kB, rss:109864kB, swap:0kB

[305509.131154] Out of memory in UB 506518: OOM killed process 3553 (mysqld) score 0 vm:975140kB, rss:171960kB, swap:2068kB

[305619.694154] Out of memory in UB 506518: OOM killed process 10708 (mysqld) score 0 vm:973964kB, rss:161364kB, swap:0kB

[305621.875432] Out of memory in UB 506518: OOM killed process 11511 (mysqld) score 0 vm:841912kB, rss:152136kB, swap:0kB

[305622.938979] Out of memory in UB 506518: OOM killed process 11564 (mysqld) score 0 vm:841912kB, rss:120208kB, swap:0kB

[305623.061948] Out of memory in UB 506518: OOM killed process 11887 (mysqld) score 0 vm:789944kB, rss:115932kB, swap:0kB

[402005.199523] Out of memory in UB 506518: OOM killed process 12428 (mysqld) score 0 vm:982588kB, rss:162872kB, swap:11432kB

Answer 1

the Out-of-memory process (oom) killer terminated your mysql process because it was trying to use more memory than was available. Calculating memory usage in MySQL exactly is very difficult, but once you understand how MySQL allocates memory system-wide and to each connection, you can get a pretty good idea of how much memory is likely to be used. Keep in mind that a big challenge here is figuring out how many connections are open simultaneously and how much each one is doing, since the amount of time a connection hangs around waiting to be reused is settable with server system variables as well.

There are a few tools out there that can help you get a good overview of what the server memory allocation should look like and where potential problems may lie in your current configuration. So long as you're not 100% reliant on any one. mysqltuner.pl will give you a great starting point, and possibly get you close enough to resolve your issue completely. Percona's MySQL Configuration Wizard is very good at generating optimal initial configurations. Run the wizard, generate a configuration, install it, and see how mysqltuner.pl disagrees with it.

You will immediately see that you're miles ahead of where you started in terms of a good configuration, and that it's a complex enough topic that there are many different preferences, priorities, levels of expertise, and areas of focus in deciding what a good configuration looks like, but there's also some numbers that are unquestionably better than others.

As others have mentioned, the reality is that to get good performance you will need a server with more RAM, but setting it up will be easier with the Percona wizard as well as mysqltuner.pl.

Finally, here is a great introduction to what MySQL does with memory. It's from some MySQL documentation I saved years ago, but I could not find the original source just now:

    Mysql Server Memory Usage = Sum of Global Buffers + 
        (number of Connection * Per thread memory variables).
    Global buffers include:
        key_buffer_size: key_buffer_size is the size of the buffer used 
            for index blocks.
        innodb_buffer_pool_size: The size in bytes of the memory buffer 
            InnoDB uses to cache data and indexes of its tables.
        innodb_additional_mem_pool_size: The size in bytes of a memory pool
            InnoDB uses to store data dictionary information and other 
            internal data structures.
        innodb_log_buffer_size: The size in bytes of the buffer that InnoDB 
            uses to write to the log files on disk.
        query_cache_limit: The amount of memory allocated for caching query 
            results.
    Each thread for client connection uses:
        thread_stack – The stack size for each thread.
        net_buffer_length – connection buffer
        max_allowed_packet – up to this size net_buffer_length can grow
        read_buffer_size – used for sequential table scan
        rean_rnd_buffer_size – used for random read buffer / sorting
        tmp_table_size – temporary / hash tables in mysql
        sort_buffer_size – for sorting
    Per thread variables include:
        read_buffer_size: Buffer memory used for sequential table scan.
        read_rnd_buffer_size: Memory used for random read buffer / sorting.
        sort_buffer_size: Memory allocated for sorting, Group By, Order By.
        join_buffer_size: The size of the buffer that is used for plain index 
            scans, range index scans, and joins that do not use indexes and 
            thus perform full table scans.
        thread_stack: The stack size for each thread.
        net_buffer_length: Connection buffer
        max_allowed_packet: Up to this size net_buffer_length can grow.
    Note that,
        If size increases or if table have blob columns, instead of heap 
            tables on-disk tables created.
        Memory for variables read_buffer_size, sort_buffer_size, 
            read_rnd_buffer_size, tmp_table_size are allocated as & when 
            required. They are also de-allocated once the task is 
            accomplished.

Answer 2

Your current settings over-commit memory. Try echo 1 > /proc/sys/vm/overcommit_memory or lower mysql memory usage