We have MariaDB server with single table with image url information. The table is about 400GB on disk and contains probably 400M rows.

Table is partitioned in 1024 partitions.

All queries are similar to this one:

select * from container where id in (1234, 1235 ... );

The sql usually took 2 sec to be executed.

Each row contains a single image url, a title and keywords.

Keybufer is set to 8GB.

This set up works well, until we begin to insert aditioan rows. We tried normal inserts, also low_priority inserts. It is slow in both cases.

I wonder what else we can tweak in order to speed up the selects.

the table has no indexes, except the primary key on a bigint field e.g. primary key(id)

Update 2:
Here is some more information:

Create Table

CREATE TABLE `container` (
  `id` bigint(20) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`id`)
/*!50100 PARTITION BY KEY (id)

data field stores standard JSON. It is UTF8 text, but because of wrong encoding of the input data, we were forced to store it in blob + binary encoding.

Size on disk

# du -h /usr/local/mysql/var/mydb/
371G    /usr/local/mysql/var/mydb/


> select count(*) from container;
| count(*)  |
| 409036295 |
1 row in set (0.04 sec)


server-id = 1



#Flush every 5 min (300 sec)
set-variable = flush_time=900

#Max Clients
set-variable = max_connections=5050
set-variable = max_user_connections=5000
set-variable = back_log=50

set-variable = table_open_cache=1024
set-variable = table_definition_cache=1024

#INSERT While SELECT-ing. Default is 1 (1 = On if have no hole, 2 = On if have hole)
set-variable = concurrent_insert=2

#Interactive timeout 60 min (from console)
set-variable = interactive_timeout=3600

#non-interactive timeout 3 hours
set-variable = wait_timeout=10800

set-variable = key_buffer_size=8192M
set-variable = max_allowed_packet=5M
set-variable = sort_buffer_size=256M

set-variable = tmp_table_size=512M
set-variable = max_heap_table_size=64M

#all updates will wait for selects
set-variable = low_priority_updates=1

set-variable = thread_cache_size=64

#----- SLOW QUERIES -----

set-variable = long_query_time=2
set-variable = log_slow_queries=mysql-slow.log

#----- CASHE -----

set-variable = query_cache_type=0
set-variable = query_cache_limit=1M
set-variable = query_cache_size=128M
  • Some questions: what do you feel slow, reads (SELECT) or writes (INSERT) performance? Can you post your /etc/my.cnf file and server specification? Have you tried to analyze your queries with MySQL integrated performance profiling?
    – shodanshok
    Oct 29, 2015 at 18:08
  • i care only about select's speed. insert can be slow, no probl.
    – Nick
    Oct 29, 2015 at 18:12
  • Something seems vaguely wrong about storing 400 million blobs in a database... Oct 29, 2015 at 18:12
  • is not binary. is json's as txt. I'll add information.
    – Nick
    Oct 29, 2015 at 18:13
  • 1
    Let do some profiling. Execute the following SQL statement: SET profiling = 1; SELECT * from container where id in (1234, 1235 ... ); SHOW profiles; SHOW profile for query 1; SET profiling = 0; and show us the output.
    – shodanshok
    Oct 29, 2015 at 19:25

2 Answers 2


Assuming you are SELECTing only by the PRIMARY KEY, then the following will speed it up:

  • NO PARTITIONing. It only slows down such queries, especially because of 1024 partitions.
  • Switch to InnoDB. The PRIMARY KEY is "clustered" with the data; this will save a disk hit on each row fetched. Shrink key_buffer_size to 50M and raise innodb_buffer_pool_size to 70% of available RAM. InnoDB also avoids table locks.
  • Compress, in the client, the JSON text; the datatype needs to be BLOB (as it mistakenly is now).

Note: InnoDB will expand the disk space by a factor of 2-3; the compression will get that space back. (Please test this with, say, a million rows. Experiment with the different ROW_FORMATs; I don't know which one will work best for your situation. And validate the SELECT speed and the lack of interference between reads and writes.)

Do you really have 5000 connections at the same time? They are probably stumbling over each other and slowing down each other.

You have half turned off the Query cache; also do query_cache_size=0


We did profiling MyISAM without partitioning and it was slower.

We did test InnoDB and we succeed to speed up inserts, but selects were still not as fast as we wanted.

Later, we did migration to TokuDB.

Because of TokuDB, at the moment table is about 100 GB and queries are very very fast.

Your Answer

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

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