I have VPS with following setup:

(4 x 2.40 GHz), 8192 MB RAM MEMORY, 20GB HDD space.

My web service is making almost: 30 mln inserts/day or ±350 inserts per second. In near future amounts will be probably bigger (that will require more CPU).

Can you guys give me advices how to tune current MariaDB InnoDB engine to make the machine work better, in more optimized way?

Here are some current parameters:

aria_pagecache_buffer_size  134217728
aria_sort_buffer_size   268434432
bulk_insert_buffer_size 8388608
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 100
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    8
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_populate OFF
innodb_buffer_pool_size 134217728
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_log_buffer_size  8388608
innodb_sort_buffer_size 1048576

If you need more parameters, just leave an comment below.

  • 1
    Does your workload consist almost entirely of INSERTs, or do you SELECT, UPDATE etcetera as well? Which version of MariaDB is this? And the storage is HDD, not SSD?
    – dbdemon
    Commented Mar 22, 2018 at 16:15
  • @dbdemon its 10.0.33-MariaDB. Storage is HDD and yes, workload mostly consist of INSERTS. Later at night CRON jobs comes to game and makes few hundred selects, but 99% of all DB work is based on INSERTS.
    – Sid
    Commented Mar 23, 2018 at 8:29
  • @dbdemon well i just was very surprised when i truncated my main table (with 10GBs of data) and this action actually not free up those 10GB but only 8GB. When i contacted hosting support they told me that this is specific way how InnoDB works - it make copies of data to "ibdata" file and there are no easy way to completely free up the space after truncating table. Is there any alternative storage engine for Mysql which will be suitable for my current project then? Thanks
    – Sid
    Commented Mar 23, 2018 at 8:37
  • 1
    Am I right in thinking you're mostly using MariaDB to aggregate the data? And run some kind of analytics once per day to gather statistics? And then throw away the previous data?
    – dbdemon
    Commented Mar 23, 2018 at 9:05
  • Completely right! Users are making POST requests (with their data) to my PHP file, that PHP file takes their data and inserts into tables into MariaDB (with InnoDB engine). Later after 24H i am making some aggregation processes with CRON and inserting that "agregated" data to third table into the same DB. After the that i am truncating those 2 tables and the process is starting from the beginning. Thanks @dbdemon!
    – Sid
    Commented Mar 23, 2018 at 9:26

1 Answer 1

  • Batch inserts into a single INSERT. Optimal is to gather 100-1000 at a time and build a single, large, INSERT, then perform it. It will run about 10 times as fast.
  • Build a CSV file and use LOAD DATA. Also much faster than single-row inserts.

For tossing the table and starting over, have innodb_file_per_table=ON. Caution: You have probably already put the table in ibdata1, it is too late to take advantage of that flag. Do not change it.

DROP TABLE or TRUNCATE TABLE will free up space for reuse tomorrow. (With it ON, it would also give the space back to the OS.) But, if you will simply be reloading it, then there is no real need to give the space back. Simply TRUNCATE TABLE.

Yeah, 10GB vs 8GB is feasible. But you won't lose another 2GB every day.

10GB for 30M rows? That's 300 bytes per row? That sounds high. Let's see the schema; there are probably some improvements to be made. Most likely is TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT -- sizes 1/2/3/4/8 bytes, but with different max values.

innodb_buffer_pool_size should probably be 5G if you have 8GB of RAM (and you are running only MySQL on it). More CPU cores won't help anything.

If your 30M will grow, you probably cannot survive in a tiny 20GB of disk. However... If you could summarize the data either as you go, or hourly, then you won't need to store all 30M. Give us more details; we can help further.

  • Rick hey, thanks for such informative answer! A little bit below i will describe how it works and what have been done until my last post. There are a lot of AJAX POST request (±1kb) coming from around the web to my backend node file , let say "node.php". The amounts of requests for current moment are ±100 per second. When the request is done, that node.php takes that arrived POST data and writes it as single line into temp.csv file. That .csv file is filled this way with other requests data for next 20s, after 20s CRON comes and makes one BIG insert of all those rows into DB.
    – Sid
    Commented Mar 26, 2018 at 21:05
  • After that .csv file is flushed and next 20s filled from beginning in the same manner. (By the way - I've changed MariaDB storage engine from InnoDB to MyISAM. ) All seems to work ok, but today i had accident when requests from internet to node.php file increased by 20% (120-130 requests per second) and the whole VPS freezed and load spiked 10-20x times, same with CPU... i can't get it why it happened? Because when there were 80-100 requests per second VPS was using only 30% of CPU and 1.02 load level (while max is 4). I can't get it how increasing 20% of incoming traffic made those spikes?
    – Sid
    Commented Mar 26, 2018 at 21:09
  • 1
    @Sid - What is the setting of key_buffer_size? Were things swapping? Even higher speed ingestion -- like your cron job, but with some improvements.
    – Rick James
    Commented Mar 26, 2018 at 21:22
  • hey @Rick James, key_buffer_size = 536870912 Yep, VPS started to lag horribly at that moment.
    – Sid
    Commented Mar 26, 2018 at 22:05
  • @Sid - "at that moment" -- namely when it started swapping? Decrease some settings to avoid swapping. What does your current my.cnf contain? (I did not see key_buffer)
    – Rick James
    Commented Mar 26, 2018 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.