Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have recently migrated from mysql (5.5.36 MySQL Community Server (GPL)) to MariaDB (10.0.10-MariaDB-1~wheezy), along with switching my cloud hosting provider. Both machines have 2GB RAM and 1GB swap. The new machine has better IO than the previous one, and CPU is also more powerful.

I have a large innodb table (1 billion rows), inserts go well, but I can't execute the selects on mariadb, it eats all RAM then the process is killed (out of memory).

Queries are run are large selects with where clause like columnA in (/* many values here*/) and columnB in (/* many values here*/). I run only one select at a time. The smallest ones actually run (after filling all RAM and 2GB extra swap I have added, but there are a lot of larger ones that I need to run.

In mysql, these selects were running ok, they took time, cpu peaked during table statistics computation, but RAM consumption was always reasonable (never filled 100% of it).

I have tried the following:

  • copy the mysql settings (fine tuning and innodb) in mariadb's my.cnf
  • changing the values of innodb_buffer_pool_size, tmp_table_size, max_allowed_packet
  • remove all fine tuning, cache and innodb settings from my.cnf

, but the issue still happens.

Any idea on how I could prevent mariaDB from consuming all the RAM?

Here is my.cnf:

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
default-character-set=utf8

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /media/dbStorage/mysql
tmpdir      = /media/dbStorage/tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
max_connections     = 100
connect_timeout     = 5
wait_timeout        = 86400
max_allowed_packet  = 16M
thread_cache_size       = 128
sort_buffer_size    = 4M
bulk_insert_buffer_size = 16M
tmp_table_size      = 32M
max_heap_table_size = 32M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover          = BACKUP
key_buffer_size     = 128M
#open-files-limit   = 2000
table_open_cache    = 400
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 2M
read_rnd_buffer_size    = 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit       = 128K
query_cache_size        = 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type       = DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings        = 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit    = 1000
log_slow_verbosity  = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#report_host        = master1
#auto_increment_increment = 2
#auto_increment_offset  = 1
#log_bin            = /var/log/mysql/mariadb-bin
#log_bin_index      = /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog        = 1
#expire_logs_days   = 10
#max_binlog_size         = 100M
# slaves
#relay_log      = /var/log/mysql/relay-bin
#relay_log_index    = /var/log/mysql/relay-bin.index
#relay_log_info_file    = /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode       = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
#innodb_log_file_size   = 50M
innodb_buffer_pool_size = 1G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files   = 400
innodb_io_capacity  = 400
innodb_flush_method = O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
## Character encoding
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8


[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
default-character-set=utf8

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

edit adding the output of : SHOW ENGINE INNODB STATUS\G (when the select queries are not running)

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2014-05-05 13:42:16 7fb5a2fb5700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 26 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 351823 srv_active, 0 srv_shutdown, 35070 srv_idle
srv_master_thread log flush and writes: 386893
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 998248
OS WAIT ARRAY INFO: signal count 933621
Mutex spin waits 643024, rounds 19289365, OS waits 612676
RW-shared spins 364262, rounds 10989380, OS waits 323031
RW-excl spins 9259, rounds 1874485, OS waits 61389
Spin rounds per wait: 30.00 mutex, 30.17 RW-shared, 202.45 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 3496399
Purge done for trx's n:o < 3496399 undo n:o < 0 state: running but idle
History list length 2703
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 30, OS thread handle 0x7fb5a2fb5700, query id 949389 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 3496396, not started
MySQL thread id 27, OS thread handle 0x7fb5a2f6c700, query id 949387 localhost 127.0.0.1 tribalytics_user cleaning up
---TRANSACTION 3496397, not started
MySQL thread id 15, OS thread handle 0x7fb5a2f23700, query id 949388 localhost 127.0.0.1 tribalytics_user cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
12872752 OS file reads, 32212304 OS file writes, 2210155 OS fsyncs
91.15 reads/s, 16384 avg bytes/read, 224.03 writes/s, 10.77 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 11079, free list len 13513, seg size 24593, 11660706 merges
merged operations:
 insert 37099965, delete mark 21161, delete 4727
discarded operations:
 insert 0, delete mark 0, delete 0
250.14 hash searches/s, 1011.04 non-hash searches/s
---
LOG
---
Log sequence number 318653160747
Log flushed up to   318653160719
Pages flushed up to 318644231594
Last checkpoint at  318643916274
Max checkpoint age    80826164
Checkpoint age target 78300347
Modified age          8929153
Checkpoint age        9244473
0 pending log writes, 0 pending chkp writes
862017 log i/o's done, 2.88 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1098907648; in additional pool allocated 0
Total memory allocated by read views 392
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 26561088    (18921928 + 7639160)
    Page hash           139112 (buffer pool 0 only)
    Dictionary cache    5039210     (4426736 + 612474)
    File system         835400  (812272 + 23128)
    Lock system         2658304     (2657176 + 1128)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 612474
Buffer pool size        65528
Buffer pool size, bytes 1073610752
Free buffers            8102
Database pages          56960
Old database pages      20918
Modified db pages       19558
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1138299, not young 42758971
4.77 youngs/s, 213.30 non-youngs/s
Pages read 12872532, created 276234, written 30746224
91.15 reads/s, 1.35 creates/s, 216.95 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 35 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 56960, unzip_LRU len: 0
I/O sum[91720]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            1016
Database pages          7117
Old database pages      2612
Modified db pages       2646
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 146120, not young 5978460
0.77 youngs/s, 66.69 non-youngs/s
Pages read 1626875, created 48549, written 3871433
10.81 reads/s, 0.23 creates/s, 27.54 writes/s
Buffer pool hit rate 993 / 1000, young-making rate 0 / 1000 not 44 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7117, unzip_LRU len: 0
I/O sum[11465]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            1020
Database pages          7104
Old database pages      2604
Modified db pages       2543
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 141375, not young 5112754
0.42 youngs/s, 2.73 non-youngs/s
Pages read 1616699, created 32397, written 3836870
13.65 reads/s, 0.04 creates/s, 26.96 writes/s
Buffer pool hit rate 973 / 1000, young-making rate 0 / 1000 not 5 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7104, unzip_LRU len: 0
I/O sum[11465]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            1007
Database pages          7125
Old database pages      2620
Modified db pages       2437
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 142519, not young 5499622
0.69 youngs/s, 85.77 non-youngs/s
Pages read 1599059, created 32374, written 3833340
11.08 reads/s, 1.00 creates/s, 27.04 writes/s
Buffer pool hit rate 989 / 1000, young-making rate 0 / 1000 not 87 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7125, unzip_LRU len: 0
I/O sum[11465]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            1000
Database pages          7132
Old database pages      2627
Modified db pages       2192
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 138223, not young 4950829
0.31 youngs/s, 37.27 non-youngs/s
Pages read 1604884, created 32367, written 3834934
10.11 reads/s, 0.08 creates/s, 27.00 writes/s
Buffer pool hit rate 976 / 1000, young-making rate 0 / 1000 not 91 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7132, unzip_LRU len: 0
I/O sum[11465]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            1018
Database pages          7112
Old database pages      2609
Modified db pages       2390
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 142123, not young 5378746
0.65 youngs/s, 4.19 non-youngs/s
Pages read 1598091, created 30817, written 3837538
7.27 reads/s, 0.00 creates/s, 26.92 writes/s
Buffer pool hit rate 984 / 1000, young-making rate 1 / 1000 not 9 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7112, unzip_LRU len: 0
I/O sum[11465]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            1024
Database pages          7111
Old database pages      2604
Modified db pages       2249
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 139883, not young 5132052
0.77 youngs/s, 10.54 non-youngs/s
Pages read 1617345, created 33664, written 3828713
13.11 reads/s, 0.00 creates/s, 27.00 writes/s
Buffer pool hit rate 972 / 1000, young-making rate 1 / 1000 not 22 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7111, unzip_LRU len: 0
I/O sum[11465]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            1010
Database pages          7126
Old database pages      2619
Modified db pages       2445
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 137255, not young 5378732
0.54 youngs/s, 5.12 non-youngs/s
Pages read 1623428, created 33477, written 3830305
10.42 reads/s, 0.00 creates/s, 27.15 writes/s
Buffer pool hit rate 967 / 1000, young-making rate 1 / 1000 not 16 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7126, unzip_LRU len: 0
I/O sum[11465]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size        8191
Buffer pool size, bytes 134201344
Free buffers            1007
Database pages          7133
Old database pages      2623
Modified db pages       2656
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 150801, not young 5327776
0.62 youngs/s, 1.00 non-youngs/s
Pages read 1586151, created 32589, written 3873091
14.69 reads/s, 0.00 creates/s, 27.35 writes/s
Buffer pool hit rate 991 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7133, unzip_LRU len: 0
I/O sum[11465]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread process no. 17526, id 140418293421824, state: sleeping
Number of rows inserted 46636337, updated 556820, deleted 0, read 9758941
257.87 inserts/s, 1.27 updates/s, 0.00 deletes/s, 5.46 reads/s
------------
TRANSACTIONS
------------
Trx id counter 3496399
Purge done for trx's n:o < 3496399 undo n:o < 0 state: running but idle
History list length 2703
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 30, OS thread handle 0x7fb5a2fb5700, query id 949389 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 3496396, not started
MySQL thread id 27, OS thread handle 0x7fb5a2f6c700, query id 949387 localhost 127.0.0.1 tribalytics_user cleaning up
---TRANSACTION 3496397, not started
MySQL thread id 15, OS thread handle 0x7fb5a2f23700, query id 949388 localhost 127.0.0.1 tribalytics_user cleaning up
----------------------------
END OF INNODB MONITOR OUTPUT
============================
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.