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.
Update:
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`)
) ENGINE=MyISAM DEFAULT CHARSET=binary
/*!50100 PARTITION BY KEY (id)
PARTITIONS 1024 */
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/
Count(*)
> select count(*) from container;
+-----------+
| count(*) |
+-----------+
| 409036295 |
+-----------+
1 row in set (0.04 sec)
my.ini
[mysqld]
server-id = 1
port=3306
socket=/tmp/mysql.sock
skip_name_resolve
open-files-limit=64000
#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
#preforking
set-variable = thread_cache_size=64
#----- SLOW QUERIES -----
set-variable = long_query_time=2
set-variable = log_slow_queries=mysql-slow.log
#----- CASHE -----
# SELECT SQL_CACHE * from x
set-variable = query_cache_type=0
set-variable = query_cache_limit=1M
set-variable = query_cache_size=128M
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.