Поиск быстрой базы данных

Какие запросы, какие подсчеты? Тип таблиц? Могу предположить, что вам нужно не базу менять, а запросы оптимизировать.

не является многопоточной

Она может распаралеливать разные одновременные запросы на разные ядра.

Все зависит от реализации алгоритма и задачи.

Если вычисления могут сводиться к иммутабельности обработки, грануляции или проще говоря можно разбить сложный расчет на итеративный через планировщик (cron), то можно несколько снизить нагрузку, если мощности позволяют. Но делать это нужно так, чтобы не добавлялось лишней энтропии, в идеале, исходя из имеющихся возможностей.

можно вытянуть запросы, которые 20 часов крутятся и позапускать руками, посмотреть план запроса, чтобы понять как запрос выполняется и почему так долго. 10гб не выглядит как большая бд.

Попробуй индексы создать.

Запросов всего 2

$tb = $db->query("SELECT * FROM ‘DB’ WHERE data1=‘data1’ and data2=‘data2’ …. data5=‘data5’ ");

далее выполняю расчеты - берется 5 переменных и базы и 30 констант множатся делятся 1 на другую - вывод записывается в базу данных

$tb = $db->query(«INSERT INTO «DB» (‘data1’,‘data2’…‘data20’) VALUES (‘data1’,‘data2’…‘data20’)»);

как именно можно их оптимизировать?

индексы есть - все необходимые оптимизации провел что только смог найти в интернете - проблема в том что сервер загружен не на максимум так как mysql юзет только 1 ядро

вычисления проходят мгновенно так как они не сложные - проблема в том что база данных не успевает записывать результаты

я с сиквелом почти 10 лет не работал уже, но думается мне, что если инсертить не по 1ой записи а сразу по несколько, то может быть быстрее. проверяли?
И еще момент, чтобы убедиться, что мы на одной волне: это реально две разные бд или две таблицы одной бд(исходная и результирующая)?

как именно можно их оптимизировать?

Сколько времени выполняются запросы? Посмотри в show processlist, какие запросы тормозят.

Могу сходу сказать, что можно оптимизировать используя составные индексы (по нескольким полям), если конечно тормозят селекты. Ну а если тормозят вставки, то выше уже написали про пакетные инсерты.

нет не проверял - сделав так нужно будет нагружать скрипт чтобы он запоминал результаты в массиве и потом через несколько циклов делать запись в базу данных - а если захотеть масштабироваться то тогда нужно запоминать больше данных.

а по поводу postgresql и greenplum - они могут работать на всех ядрах?

сервер загружен не на максимум так как mysql юзет только 1 ядро

Может быть тип таблиц не тот? Myisam, Innodb?

10+гб

Считай в памяти.

InnoDB

запрос занимает 0.00043392181396484 - 0.00092196464538574 сек

индексы есть по всем полям из которых идет выборка через WHERE

вычисления проходят мгновенно так как они не сложные - проблема в том что база данных не успевает записывать результаты

prepare делается до insert ?

запрос занимает 0.00043392181396484 - 0.00092196464538574 сек

Это select? А insert?

0.00020694732666016 - 0.00091886520385742

нет

Эм, тогда что же тормозит 20 часов?)

оно не тормозит - просто строк для записи огромное к-во примерно 300-400 млн, как я понял все это идет через 1 ядро (чтение запись) и подумал что нужно найти инструмент который мог бы юзать все ресурсы сервера.

если взять подсчитать, то оно на то и выходит

0.0002*400000000/60/60 = 22 часа если оно записывает последовательно

Я же запускал 7 разных скриптов паралельно, но от этого скорость подсчетов не увеличивалась особо, и вот сейчас при выполнении подсчетов сервер загружен на 20%

http://images.vfl.ru/ii/1643301873/4e7bb8a0/37757635_m.png

чтобы произвести расчеты тебе хватит данных, что в исходной бд или надо что-то извне(пхп скриптов)?

хватит данных в исходной бд

Схема выглядит простенькой, попробуй затаращить всё в in-memory БД (да хоть бы даже sqlite убогий).

Тут бы еще посмотреть загрузку диска, может это он бутылочное горлышко. Ну и убедится, что подключаешься к мускулу через сокет, а не 127.0.0.1, на мелких запросах оверхед будет большой. Еще есть советы запустить мускул с –skip-grant-tables, это выключит проверку прав доступа на каждый запрос.

хватит данных в исходной бд

Если можно все сделать запросом insert … select, то это будет самое быстрое.

не успевает

Отключи индексы и прочие проверки в принимающей БД?)

Чтобы mysql так не делал надо купить лицензию.

У тебя 5 полей с индексами. Каждый раз, когда ты вставляешь запись в бд, индексы перестраиваются. И ты своими единичными инсертами 300 миллионов раз перестраиваешь индексы на 70 гигабайт. На постгресе будет тоже самое, и на любой реляционной базе Либо не страдай херней, и переписывай свой скрипт. Делай вставки по 100-1000 записей за один запрос, как уже выше писали. Либо бери time series базу данных

строк для записи огромное к-во примерно 300-400 млн

Кликхаус, например, вставляет данные чуть ли не со скоростью чтения csv с диска…

Это не то, чтобы я агитирую, но может стоить посмотреть в его сторону или как уже чуть выше написали time series БД.

Вставляй по 100 строк за раз, вот пример: https://stackoverflow.com/a/5526937

Ну, и продублирую вопрос: зачем вообще эти расчеты в базу записывать? Почему нельзя их рассчитывать «на лету» и сразу же использовать?

У тебя 5 полей с индексами. Каждый раз, когда ты вставляешь запись в бд, индексы перестраиваются. И ты своими единичными инсертами 300 миллионов раз перестраиваешь индексы на 70 гигабайт.

Прочти внимательно условие задачи - из это БД только читают, в нее ничего не пишут. Никакие индексы не перестраиваются! Пишут в другую БД 20 полей. И вот там индексов быть не должно.

А вообще, как тебе уже сказали ранее - нужно использовать транзакции и записывать результаты пачками. Размер пачек нужно подобрать экспериментально, но начать хотя бы с сотни строк за раз.

START TRANSACTION
for(i=0; i<100; i++)
{
  SELECT from bd1...
  calculate();
  INSERT into bd2... 
}
COMMIT

это называется DBA

Распараллеливать вычисления имеет смысл только в том случае, если они выполняются существенно медленее, чем идет чтение данных.

Если результаты расчетов никуда не писать, то сколько это займет времени? 10 Гигов - это влезает в RAM. Прочитать всё, подсчитывать и сразу писать во вторую базу.

Есть ли во второй базе какие-нибудь constraint и индексы?

Если не ограничений, то нужно грохнуть индексы во второй базе, запустить подсчет, а индексы построить после окончания расчетов.

В постгресе можно записывать insert с множеством value().

В постгресе можно записывать insert с множеством value().

В мускуле вроде тоже

Хм, мб так можно сделать - буду траить переписывать скрипт

Всем спасибо за ответы и советы по поводу оптимизации!

как именно можно их оптимизировать

Для начала, делать bulk insert а не по одному. Ещё, делай всё внутри транзакции, иначе после каждого запроса будет autocommit.

нет не проверял - сделав так нужно будет нагружать скрипт чтобы он запоминал результаты в массиве и потом через несколько циклов делать запись в базу данных

Совершенно верно. Не ленись. Два prepared insert: один на вставку максимума строк сколько влезет в SQL-строку (для примера – 20), другой – на одну строку для вставки оставшихся данных (которых меньше 20 строк). Я так загрузку ФИАС ускорил, помнится, в десятки раз (уж не помню во сколько именно, т.к. бывало и круче).

Впрочем то было на жаве, а пэхэпэ куда тормознее, но выигрыш всё равно должен быть существенный.

Про транзакции правильно напомнили; у меня была одна транзакция на сколько-то там сотен вставок.

Куски выгружай больше, обрабатывай и делай batch insert (или как его там, bulk?) ну или вообще делай csv, который потом можно быстро экспортировать средствами базы. 200М записей - это жирно для mysql, но не должно быть смертельно настолько, чтобы по 20 часов считать.
Хотя, в оракл я накидываю 3М записей за полчаса. Т.е. 180М за 20 часов. Но там намного больше запросов вычислений, конечно.

далее выполняю расчеты - берется 5 переменных и базы и 30 констант множатся делятся 1 на другую - вывод записывается в базу данных

Может это вообще делать запросом на стороне бд? Судя по описанию какая-то элементарщина, решаемая одним апдейтом/insert into table x (...) select ... from y

Может это вообще делать запросом на стороне бд? Судя по описанию какая-то элементарщина, решаемая одним апдейтом/insert into table x

Я тоже сначала подумал, а не сделать ли view вместо второй базы, но вот вопрос - как из второй базы делается выборка?

Если для результатов вычислений во второй базе строятся индексы и запросы их эффективно используют, то вычисления на ходу во view могут ухудшить производительность выборки из второй базы.

Если вторая база всегда перестраивается с нуля, то нужно удалять все индексы, генерить базу, а потом добавлять индексы.