Обновление таблицы базы данных Mysql миллионами записей
я имею user стол с innoDB Двигатель, который имеет около миллиона водителей
CREATE TABLE user (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Column2` varchar(14) NOT NULL,
`Column3` varchar(14) NOT NULL,
`lat` double NOT NULL,
`lng` double NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB
И у меня есть мобильное приложение, которое отслеживает местонахождение пользователей, отправляет его на сервер и сохраняет.
Теперь я уверен, что когда выйдут в эфир и миллионы водителей отправят свои местоположения … база данных будет недоступна или будет работать очень медленно.
Как я могу избежать низкой производительности базы данных Mysql, когда обычные пользователи используют приложение (чтение / запись записей)
Я думал о создании новой базы данных только для отслеживания местоположения драйверов, а затем у меня есть основная база данных будет обновляться через Cronjob, например, для обновления users таблица с широтой / долготой каждый конкретный момент
У меня есть некоторые ограничения здесь … я не могу переключиться на базу данных без SQL на этом этапе
Решение
3333 строки вставляются в секунду. Обязательно «пакетируйте» вкладыши каким-либо образом. Для еще более высоких показателей вставки см. http://mysql.rjweb.org/doc.php/staging_table
DOUBLE является избыточным для широты / долготы и тратит впустую пространство. Размер таблицы может привести к проблемам с производительностью (когда таблица становится «огромной»). Для поиска автомобиля, FLOAT вероятно, лучше — 8 байтов для 2-х чисел против 16 байтов для 2-х двойных. Разрешение составляет 1,7 м (5,6 фута). Ref:
http://mysql.rjweb.org/doc.php/latlng#representation_choices
С другой стороны, если есть только один широта / длина на пользователя, миллион строк будет меньше 100 МБ, а не очень большая таблица.
Какие запросы должны быть выполнены? Миллион строк против таблицы может быть дорогостоящим. «Найти всех пользователей в пределах 10 миль (или км)» потребует сканирования таблицы. Рекомендую заглянуть в ограничительную рамку, а также пару вторичных индексов.
Больше
Призывы к обновлению местоположения должны подключаться, обновляться, отключаться. Это займет долю секунды, и может не перегружен max_connections, Это значение не должно быть слишком высоким; это может вызвать проблемы. Также установлено back_log примерно до того же значения.
Рассмотрим «пул соединений», детали которого зависят от языка вашего приложения, веб-сервера, версии MySQL и т. Д.
Вместе с «ограничительной рамкой» в WHERE, иметь INDEX(lat), INDEX(lng); Оптимизатор будет выбирать между ними.
Теперь много процессорных ядер на вашем сервере? Ограничьте количество потоков веб-сервера примерно вдвое. Это обеспечивает еще один механизм дросселирования, позволяющий избежать «громового стадного синтрома».
Отключите кеш запросов, используя оба query_cache_size=0 а также query_cache_type=0, В противном случае контроль качества стоит некоторых накладных расходов, хотя по существу никогда не дает никакой выгоды.
дозирующий INSERTs возможно. Но вам нужно заморозить UPDATEs, Это сложнее. Это должно быть практично, собирая обновления в таблице, а затем создавая одну, несколько таблиц, UPDATE скопировать из этой таблицы в основную таблицу. Эта дополнительная таблица будет работать как пинг-понг, который я обсуждаю в своей ссылке «staging_table». Но … Сначала посмотрим, достаточно ли других исправлений.
использование innodb_flush_log_at_trx_commit = 2 , В противном случае узким местом будет регистрация транзакций. Недостаток (потеря обновлений за 1 секунду), вероятно, не является проблемой для вашего приложения — поскольку вы скоро получите еще один широту / долготу.
Поиск близлежащих транспортных средств — это даже лучше, чем ограничивающий прямоугольник, но он более сложный: http://mysql.rjweb.org/doc.php/latlng . Как часто ищите «Nearbys». Я надеюсь, что это не 3333 / сек; это не практично на одном сервере. (Несколько ведомых могут предоставить решение.) В любом случае, набор результатов меняется не очень быстро.
Другие решения
Здесь есть что выбрать …
Во-первых, рассмотрите возможность использования типы пространственных данных для хранения лат и лонг. Это, в свою очередь, позволит вам использовать пространственные индексы, которые оптимизированы для поиска людей в ограничивающих прямоугольниках.
Во-вторых, если вы ожидаете такой высокий трафик, вам могут потребоваться экзотические решения.
Во-первых, настройте испытательный стенд, максимально приближенный к производственному оборудованию, чтобы вы могли искать узкие места. Если вы ожидаете 100K вставок в течение 5 минут, вы смотрите в среднем 100.000 / 5/60 = 333 вставки в секунду. Но масштабирование для среднего значения обычно плохая идея — вам нужно масштабировать для пиков. Мое эмпирическое правило заключается в том, что вы должны иметь возможность передавать в 10 раз больше среднего, если среднее значение находится в диапазоне 1–10 минут, поэтому вам нужно около 3000 вставок в секунду.
Я бы использовал инструмент нагрузочного тестирования (JMeter отлично) — и убедитесь, что узкое место находится не в инфраструктуре нагрузочного тестирования, а на целевом сервере. Определите, при какой нагрузке ваша целевая система начинает достигать приемлемых границ времени отклика — для простого оператора вставки я бы установил это на 1 секунду. Если вы используете современное оборудование, без триггеров и хорошо спроектированной таблицы, я ожидаю, что скорость записи составит не менее 500 вставок в секунду (мой Macbook приближается к этому).
Используйте этот тестовый стенд для оптимизации схемы и индексов базы данных — вы можете получить МНОГО производительности от MySQL!
Следующим шагом является болезненный — вы мало что можете сделать, чтобы увеличить сырую производительность вставок MySQL (много памяти, быстрый SSD-диск, быстрый процессор; вы можете использовать промежуточную таблицу без индексов для получения еще пара процентов улучшения). Если вы не можете достичь своей целевой производительности с помощью «ванильного» MySQL, теперь вам нужно взглянуть на более экзотические решения.
Первый — самый простой — сделайте свои приложения менее общительными. Это поможет масштабируемости всего решения (я полагаю, у вас есть веб-серверы / приложения между приложениями и базой данных — им тоже понадобится масштабирование). Например, вместо отправки обновлений в режиме реального времени, приложения могут хранить данные за 1, 5, 10, 60, 2400 минут и отправлять их в виде пакета. Если у вас 1 миллион активных пользователей в день с пиками в 100 000 активных пользователей, гораздо проще масштабировать до 1 миллиона транзакций в день, чем до 100 000 транзакций каждые 5 минут.
Второй вариант — поставить сервер очереди сообщений перед вашей базой данных. Системы очереди сообщений масштабируются гораздо легче, чем базы данных, но вы значительно усложняете архитектуру.
Третий вариант кластеризация. Это позволяет распределить нагрузку по нескольким физическим серверам баз данных, но опять-таки вносит дополнительную сложность и стоимость.