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)
Here is some more information:
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/
> select count(*) from container; +-----------+ | count(*) | +-----------+ | 409036295 | +-----------+ 1 row in set (0.04 sec)
[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