Мне нужно сохранить около 7.8 миллиардов записей в таблице Mysql. Таблица является интенсивной как чтение, так и запись. Я должен поддерживать не менее 0,02 миллиарда записей в час скорости ввода. Хотя поиск в таблице не должен занимать более 10 секунд. У нас есть пользовательский интерфейс, из которого пользователи могут искать на основе свойства разных колонок.
В основном поисковый запрос может выглядеть следующим образом:
select * from mytable where prop1='sip:+100008521149' and
prop2='asdsa'
order by event_timestamp desc limit 10;
select * from mytable where prop1='sip:+100008521149'
order by event_timestamp desc limit 10;
select * from mytable where prop2='asdsa'
order by event_timestamp desc limit 10;
В настоящее время в таблице есть 2 индекса:
1- idx_1(prop1,event_timestamp)
2- idx_2(prop2,event_timestamp)
Настройки InnoDB следующие:
innodb_buffer_pool_size = 70G
innodb_log_file_size = 4G
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_autoinc_lock_mode = 2
bulk_insert_buffer_size=33554432
query_cache_type=1
query_cache_size=64M
innodb_flush_neighbors=0
expire_logs_days=10
max-connections=500
long_query_time = 5
read_buffer_size=16M
sort_buffer_size=16M
read_rnd_buffer_size=16M
innodb_doublewrite = 0
innodb_flush_method=O_DIRECT
Machine RAM size is 99 GB.
После запуска система была быстрой, но производительность значительно сократилась, когда запись достигла 0,22 миллиарда. Хотя мы используем LOAD INFILE, скорость ввода была очень медленной. Поиск был довольно быстрым при поиске по индексированным параметрам. Похоже, пул буферов недостаточно.
У меня мало вопросов:
Возможно ли поддерживать этот вид данных с помощью этой конфигурации.
Какой должен быть идеальный и практичный размер пула буферов для 7 миллиард записей.
UPADTE: 1
Q- Таблица намного больше, чем оперативная память, правильно? Буфер_пул не может быть достаточно большим - он должен быть меньше, чем у бара, иначе производительность будет страдать.
A - Размер оперативной памяти - 100 ГБ, буферный пул - 70 G. Да, размер данных слишком большой, чем ОЗУ.
Q- Пожалуйста, предоставьте SHOW CREATE TABLE; есть несколько вопросов, которые мне нужно изучить. (типы данных, размеры полей и т.д.)
A- Все поля являются строковыми. мы использовали varchar (127) для всех. PK является автогенерированным id bigint (20).
Q- Сколько записей в LOAD DATA INFILE? Вы НАГРУЗИТЕ прямо в таблице? Как часто происходит LOAD?
A - 100000 записей на файл. Несколько потоков загружают данные из CSV файла в DB. При первоначальной миграции мы должны постоянно ее загружать до 0,65 миллиарда записей. После этого частота уменьшится примерно на 15 минут.
Q-Master + Slave: Имейте в виду, что все записи также выполняются на Slave. Если у вас много чтений, более одного ведомого будет распространять чтение, тем самым получая некоторое масштабирование.
A- В настоящее время мы тестируем подход MASTER/SLAVE.
Мы сделали MASTER с MYISAM и без индексов. MASTER будет использоваться для вставок. SLAVE с INNODB и двумя индексами. На этом будет выполнен поиск. Оба являются разными машинами и не используют RAM или CPU. Приложение находится на третьей машине.
Q- У вас есть вращающиеся диски? Или SSD? A- Как это проверить?
Q- Ваши ряды кажутся довольно большими. Есть ли ТЕКСТЫ или БЛОКИ? Если это так, SELECT * может быть серьезной нагрузкой.
A-yes строки имеют 50 столбцов, но данные находятся в пределах 15-20 столбцов. Мы не можем уменьшить размер типов данных, так как все поля могут содержать любое количество буквенно-цифровых данных. Все ТЕКСТЫ без BLOBS.
Я достиг этого требования, заменив MYSQL DB на Elasticsearch. Он отлично подходит для быстрой скорости вставки и быстрого поиска. Более того, полнотекстовые возможности Lucene делают его идеальным инструментом. Наилучшая часть ES заключается в том, что у нее очень низкие требования к оборудованию. Он масштабируется горизонтально, а не вертикально.
Отключите кеш запросов: он должен очищать все записи в КК каждый раз, когда происходит INSERT - это 5555 раз в секунду!
query_cache_type = 0
query_cache_size = 0
Для первого запроса требуется INDEX(prop1, prop2, event_timestamp). (Прокси и prop2 могут быть заменены.)
С этим добавленным индексом каждый из трех запросов коснется не более 10 строк в индексе и сделает не более 10 случайных (?) выборок в данные. В худшем случае это всего лишь около 11 дисков. И @Bernd 'lazy eval' не станет лучше.
Таблица намного больше, чем оперативная память, правильно? Буфер_пул не может быть достаточно большим - он должен быть меньше, чем у бара, иначе производительность будет страдать.
Предоставьте SHOW CREATE TABLE; есть несколько вопросов, которые мне нужно изучить. (типы данных, размеры полей и т.д.)
Сколько записей в LOAD DATA INFILE? Вы LOAD прямо в таблицу? Как часто бывает LOAD?
Мастер + Раб: Имейте в виду, что все записи также выполняются на Slave. Если у вас много чтений, более одного ведомого будет распространять чтение, тем самым получая некоторое масштабирование.
У вас есть вращающиеся диски? Или SSD?
Ваши ряды кажутся довольно большими. Существуют ли TEXTs или BLOBs? Если да, SELECT * может быть серьезной нагрузкой.
Это не ответ, но я не могу его форматировать в комментарии
Вы можете попробовать, чтобы узнать, быстрее ли это. поэтому MySQL не должен сортировать строки отверстий только с идентификатором (Primary KEY)
SELECT r.*
FROM (
SELECT id
FROM mytable
WHERE
prop1='sip:+100008521149'
AND
prop2='asdsa'
ORDER BY event_timestamp DESC
LIMIT 10
) AS r
LEFT JOIN mytable m ON m.id =r.id
ORDER BY r.event_timestamp DESC;