0

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.

Rick James
  • 80,479
  • 5
  • 52
  • 119
Sid
  • 125
  • 1
  • 6

1 Answers1

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 James
  • 80,479
  • 5
  • 52
  • 119