Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
Привет.
Не могу найти объяснение такому долгому выполнению выполнению запроса, 1.5 сек.
И так себя ведут все таблицы, практически, и при update, и при insert.
В среднем 1 секунда и больше - запрос.
Из файла slow_query.log:

# Thread_id: 10379 Schema: exchanges QC_hit: No
# Query_time: 1.534451 Lock_time: 0.000103 Rows_sent: 0 Rows_examined: 35
# Rows_affected: 1
SET timestamp=1528628365;
UPDATE `courses_from_glases` SET btce_ask = null, cex_bid = null, cex_ask = null, bittrex_bid = null, bittrex_ask = null, kraken_bid = null, kraken_ask = null, bitfinex_bid = null, bitfinex_ask = null, quoine_bid = null, quoine_ask = null, btcc_bid = null, btcc_ask = null, bitstamp_bid = null, bitstamp_ask = null, exmo_bid = null, exmo_ask = null, okcoin_bid = null, okcoin_ask = null, gdax_bid = null, gdax_ask = null, hitbtc_bid = null, hitbtc_ask = null, poloniex_bid = null, poloniex_ask = null, livecoin_bid = null, livecoin_ask = null, therocktrading_bid = null, therocktrading_ask = null, lakebtc_bid = null, lakebtc_ask = null, itbit_bid = null, itbit_ask = null, gemini_bid = null, gemini_ask = null, huobi_bid = null, huobi_ask = null, quadrigacx_bid = null, quadrigacx_ask = null, bleutrade_bid = null, bleutrade_ask = null, liqui_bid = null, liqui_ask = null, c_cex_bid = null, c_cex_ask = null, ecoin_bid = null, ecoin_ask = null, yobit_bid = null, yobit_ask = null, bter_bid = null, bter_ask = null WHERE pair = 'btc_usd';

в таблице неизменно 35 строк, можно добавить UNIQUE INDEX на колонку pair, но при таком количестве строк, по моему производительность не повысит.

CREATE TABLE `courses_from_glases` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`pair` text,
`btce_bid` float(20,8) DEFAULT NULL,
`btce_ask` float(20,8) DEFAULT NULL,
`cex_bid` float(20,8) DEFAULT NULL,
`cex_ask` float(20,8) DEFAULT NULL,
`bittrex_bid` float(20,8) DEFAULT NULL,
`bittrex_ask` float(20,8) DEFAULT NULL,
`kraken_bid` float(20,8) DEFAULT NULL,
`kraken_ask` float(20,8) DEFAULT NULL,
`bitfinex_bid` float(20,8) DEFAULT NULL,
`bitfinex_ask` float(20,8) DEFAULT NULL,
`quoine_bid` float(20,8) DEFAULT NULL,
`quoine_ask` float(20,8) DEFAULT NULL,
`btcc_bid` float(20,8) DEFAULT NULL,
`btcc_ask` float(20,8) DEFAULT NULL,
`bitstamp_bid` float(20,8) DEFAULT NULL,
`bitstamp_ask` float(20,8) DEFAULT NULL,
`exmo_bid` float(20,8) DEFAULT NULL,
`exmo_ask` float(20,8) DEFAULT NULL,
`okcoin_bid` float(20,8) DEFAULT NULL,
`okcoin_ask` float(20,8) DEFAULT NULL,
`gdax_bid` float(20,8) DEFAULT NULL,
`gdax_ask` float(20,8) DEFAULT NULL,
`hitbtc_bid` float(20,8) DEFAULT NULL,
`hitbtc_ask` float(20,8) DEFAULT NULL,
`poloniex_bid` float(20,8) DEFAULT NULL,
`poloniex_ask` float(20,8) DEFAULT NULL,
`livecoin_bid` float(20,8) DEFAULT NULL,
`livecoin_ask` float(20,8) DEFAULT NULL,
`therocktrading_bid` float(20,8) DEFAULT NULL,
`therocktrading_ask` float(20,8) DEFAULT NULL,
`lakebtc_bid` float(20,8) DEFAULT NULL,
`lakebtc_ask` float(20,8) DEFAULT NULL,
`itbit_bid` float(20,8) DEFAULT NULL,
`itbit_ask` float(20,8) DEFAULT NULL,
`gemini_bid` float(20,8) DEFAULT NULL,
`gemini_ask` float(20,8) DEFAULT NULL,
`huobi_bid` float(20,8) DEFAULT NULL,
`huobi_ask` float(20,8) DEFAULT NULL,
`quadrigacx_bid` float(20,8) DEFAULT NULL,
`quadrigacx_ask` float(20,8) DEFAULT NULL,
`bleutrade_bid` float(20,8) DEFAULT NULL,
`bleutrade_ask` float(20,8) DEFAULT NULL,
`liqui_bid` float(20,8) DEFAULT NULL,
`liqui_ask` float(20,8) DEFAULT NULL,
`c_cex_bid` float(20,8) DEFAULT NULL,
`c_cex_ask` float(20,8) DEFAULT NULL,
`ecoin_bid` float(20,8) DEFAULT NULL,
`ecoin_ask` float(20,8) DEFAULT NULL,
`yobit_bid` float(20,8) DEFAULT NULL,
`yobit_ask` float(20,8) DEFAULT NULL,
`bter_bid` float(20,8) DEFAULT NULL,
`bter_ask` float(20,8) DEFAULT NULL,
`binance_bid` float(15,8) DEFAULT NULL,
`binance_ask` float(15,8) DEFAULT NULL,
`date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8

пробовал выполнить похожий запрос в таком цикле
for ($a = 0; $a < 100000; $a++) {
$stringMysql = "UPDATE result_for_trading_only_selected_birges SET ct = $a, `amount`= $a,`birge_buy`=null , btce_profit_percent = $totaltime,
cex_profit_percent = null, bittrex_profit_percent = null, kraken_profit_percent = null, bitfinex_profit_percent = null, quoine_profit_percent = null,
btcc_profit_percent = null, bitstamp_profit_percent = null, exmo_profit_percent = null, okcoin_profit_percent = null, gdax_profit_percent = null,
hitbtc_profit_percent = null, poloniex_profit_percent = null, livecoin_profit_percent = null, therocktrading_profit_percent = null,
lakebtc_profit_percent = null, itbit_profit_percent = null, gemini_profit_percent = null, huobi_profit_percent = null, quadrigacx_profit_percent = null,
bleutrade_profit_percent = null, liqui_profit_percent = null, c_cex_profit_percent = null, ecoin_profit_percent = null, yobit_profit_percent = null,
bter_profit_percent = null, binance_profit_percent = null WHERE pair = '$pair';";
try {
if (! $mysqli->query($stringMysql)) {
echo $mysqli->error . "<br>" . $stringMysql . "<br>";
}
} catch (Exception $ex) {
echo "ошибка, " . $ex->getMessage();
}
}
время выполнения 0.1 - 0.2 секунды, но это тоже очень много
никаких ошибок, кроме того, что база падает, если запустить сбор котировок со всех бирж, опять таки, причину падения не пишет в лог, но с учетом того, что комп отдельная машина,
processor Intel(R) Xeon(R) CPU X3440 @ 2.53GHz
memory 16GiB System Memory
и полностью исчезает память, причина только в mysql.

А вот в чем дело, не могу понять, есть мысли?
Спасибо.
10 июн 18, 14:32    [21483490]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
miksoft
Member

Откуда:
Сообщений: 37156
petrovitch
можно добавить UNIQUE INDEX на колонку pair
Лучше бы сменить тип этого поля на более компактный (TINYINT или VARCHAR(M)) и сделать первичным ключом.
10 июн 18, 16:48    [21483645]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
miksoft
Member

Откуда:
Сообщений: 37156
Указание спецификаций точности для float(20,8) тоже вряд ли имеет смысл. Все равно они все 4-байтовые. Но добавляет расходы на контроль значения и, возможно, округление со снижением точности.
10 июн 18, 16:51    [21483647]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
miksoft
Member

Откуда:
Сообщений: 37156
petrovitch
если запустить сбор котировок со всех бирж
Как именно происходит этот процесс?

petrovitch
memory 16GiB System Memory
и полностью исчезает память, причина только в mysql.
Указанными табличкой и запросом не съесть столько ресурсов. Что-то не так происходит где-то за пределами показанного нам.
10 июн 18, 16:53    [21483650]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
по cron выполняется 200 запросов к апи бирж каждую секунду
в ответах возвращаются json строки
пишутся в базу
перед записью json конвертируется в массив, лишние поля удаляются, остается по 50 полей в массивах, потом снова преобразуется в строки
профилировщиком
такой код в скрипте, найдены 3 запроса, выполняющихся больше 1 секунды:
$allMethods->writeToDB("SET profiling = 1;", $mysqli, "SET profiling = 1;");
код ....
$res = '';
$array = $mysqli->query("show profiles;");
$a = 1;
while ($array_2 = mysqli_fetch_assoc($array)) {
$res .= serialize($array_2) . "\r\n";
$res .= "show profile for query $a;" . "\r\n";
$array_3 = mysqli_fetch_assoc($mysqli->query("show profile for query $a;"));
$res .= serialize($array_3) . "\r\n";
$a++;
}
$allMethods->writeToLogDebagInfo($res, "serialize(array)");

найдены 3 запроса, которые выполняются больше секунды:
UPDATE result_for_trading_only_selected_birges SET `amount`= null,`birge_buy`=null , birge_buy = null, btce_profit_percent = null, cex_profit_percent = null, bittrex_profit_percent = null, kraken_profit_percent = null, bitfinex_profit_percent = null, quoine_profit_percent = null, btcc_profit_percent = null, bitstamp_profit_percent = null, exmo_profit_percent = null, okcoin_profit_percent = null, gdax_profit_percent = null, hitbtc_profit_percent = null, poloniex_profit_percent = null, livecoin_profit_percent = null, therocktrading_profit_percent = null, lakebtc_profit_percent = null, itbit_profit_percent = null, gemini_profit_percent = null, huobi_profit_percent = null, quadrigacx_profit_percent = null, bleutrade_profit_percent = null, liqui_profit_percent = null, c_cex_profit_percent = null, ecoin_profit_percent = null, yobit_profit_percent = null, bter_profit_percent = null, binance_profit_percent = null , `timestamp`=UNIX_TIMESTAMP(now()), `datetime`=now(), `time_execute`=0.21234673 WHERE pair = 'usd_btc'

UPDATE `courses_for_trading` SET buy_courses= null , buy_courses = null, btce_courses = null, cex_courses = null, bittrex_courses = null, kraken_courses = null, bitfinex_courses = null, quoine_courses = null, btcc_courses = null, bitstamp_courses = null, exmo_courses = null, okcoin_courses = null, gdax_courses = null, hitbtc_courses = null, poloniex_courses = null, livecoin_courses = null, therocktrading_courses = null, lakebtc_courses = null, itbit_courses = null, gemini_courses = null, huobi_courses = null, quadrigacx_courses = null, bleutrade_courses = null, liqui_courses = null, c_cex_courses = null, ecoin_courses = null, yobit_courses = null, bter_courses = null, binance_courses = null , `timestamp`=UNIX_TIMESTAMP(now()), `datetime`=now() WHERE pair = 'usd_btc'

UPDATE `courses_from_glases` SET btce_ask = null, cex_bid = null, cex_ask = null, bittrex_bid = null, bittrex_ask = null, kraken_bid = null, kraken_ask = null, bitfinex_bid = null, bitfinex_ask = null, quoine_bid = null, quoine_ask = null, btcc_bid = null, btcc_ask = null, bitstamp_bid = null, bitstamp_ask = null, exmo_bid = null, exmo_ask = null, okcoin_bid = null, okcoin_ask = null, gdax_bid = null, gdax_ask = null, hitbtc_bid = null, hitbtc_ask = null, poloniex_bid = null, poloniex_ask = null, livecoin_bid = null, livecoin_ask = null, therocktrading_bid = null, therocktrading_ask = null, lakebtc_bid = null, lakebtc_ask = null, itbit_bid = null, itbit_ask = null, gemini_bid = null, gemini_ask = null, huobi_bid = null, huobi_ask = null, quadrigacx_bid = null, quadrigacx_ask = null, bleutrade_bid = null, bleutrade_ask = null, liqui_bid = null, liqui_ask = null, c_cex_bid = null, c_cex_ask = null, ecoin_bid = null, ecoin_ask = null, yobit_bid = null, yobit_ask = null, bter_bid = null, bter_ask = null WHERE pair = 'usd_btc'

вот только почему так долго, непонятно, по моему для таких запросов время выполнения 0.001 сек. нормально, а 1 сек. абсолютно не нормально.
10 июн 18, 18:05    [21483735]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
Теоретически не съесть.
А на практике получается.
10 июн 18, 18:06    [21483737]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
miksoft,

Тип поля изменил на varchar(15), на первичный ключ изменить не получается,
ALTER TABLE `result_for_trading_only_selected_birges` ADD PRIMARY KEY(`pair`);
ошибка
Некорректное определение таблицы: может существовать только один автоинкрементный столбец, и он должен быть определен как ключ.
Изменений во времени исполнения нет.
По моему проблема во времени
| updating | 0.135657 |
| query end | 0.291808 |
и query end по моему висят в памяти в большом количестве.
10 июн 18, 19:06    [21483798]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
miksoft
Member

Откуда:
Сообщений: 37156
petrovitch
на первичный ключ изменить не получается,
Так и не получится. Лучше дропнуть таблицу и создать ее заново.

Кстати, раз вы постоянно переписываете таблицу, то имеет смысл сделать ее на движке MEMORY.
11 июн 18, 13:04    [21484527]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
Сделал таблицы на движке MEMORY.
С тремя проблемными запросами проблема решилась.
Большое спасибо!
Осталась проблема с таблицами, которые пока не переделать на MEMORY из-за типов полей.
Думаю, как решить.
11 июн 18, 23:26    [21485250]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
miksoft
Member

Откуда:
Сообщений: 37156
petrovitch
Осталась проблема с таблицами, которые пока не переделать на MEMORY из-за типов полей.
Поменять типы не предлагать?
12 июн 18, 00:21    [21485300]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
Дегтярев Евгений
Member

Откуда: Барнаул
Сообщений: 1398
petrovitch
по cron выполняется 200 запросов к апи бирж каждую секунду

я все правильно понял, каждую секунду делается 200 запросов к стороннему API и все сохраняется в БД?
12 июн 18, 09:01    [21485454]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
tip78
Member

Откуда: Москва
Сообщений: 986
а может всё-таки нормализацию сделать:
id type bid_ask value
?

а ещё вариант - в редиске это всё держать, если значения только последние нужны
12 июн 18, 13:48    [21485885]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
miksoft,

рад бы поменять, но никак
12 июн 18, 19:19    [21486583]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
Дегтярев Евгений,

совершенно верно, есть варианты?
как в память минуя базу грузить и там делать вычисления?
на самом деле нужны мгновенные данные, через секунду они уже неактуальны, в базе будут перезаписаны update
12 июн 18, 19:21    [21486588]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
tip78,

в редиске это всё держать,
отлично
у нее быстродействие намного выше mysql
есть примеры?
12 июн 18, 19:22    [21486592]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
tip78
Member

Откуда: Москва
Сообщений: 986
petrovitch
есть примеры?

в гугле полно
12 июн 18, 20:17    [21486667]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
miksoft
Member

Откуда:
Сообщений: 37156
petrovitch
как в память минуя базу грузить и там делать вычисления?
на самом деле нужны мгновенные данные, через секунду они уже неактуальны, в базе будут перезаписаны update
А в таком случае нужна ли вообще база?
12 июн 18, 20:28    [21486686]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
miksoft,

нужна, но часть таблиц, поля в которых только обновляются, и нужны в текущую секунду, их то как раз можно держать в оперативке, как с таблицами на движке memory
12 июн 18, 21:28    [21486794]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
Дегтярев Евгений
Member

Откуда: Барнаул
Сообщений: 1398
petrovitch
совершенно верно, есть варианты?
как в память минуя базу грузить и там делать вычисления?
на самом деле нужны мгновенные данные, через секунду они уже неактуальны, в базе будут перезаписаны update

по мне так rest интерфейс странное решение для поставщика ликвидности... зато стильномодномолодежно
плюс, подозреваю, что содержимое БД перезаписывает без проверки, а поменялись ли реально данные

доставка до клиента текущих котировок должна быть без участия БД, типа
- сервис, который забирает котировки у поставщика, какая-то предобработка, запись истории (если нужно), плюс интерфейс для других сервисов, собственно основная задача сервиса привести форматы поставщиков к общему знаменателю
- сервис(ы) доставки до клиента (tcp, rest, websocket и т.д.), которые в реальном времени получают данные от первого сервиса конвертируют в формат клиента и отправляют клиенту... тот же могут быть другие обработки, например, отправка раз в секунду, или отправка только нужных клиенту котировок...
13 июн 18, 07:08    [21487208]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
petrovitch
Member

Откуда:
Сообщений: 48
Дегтярев Евгений,

Здравствуйте,
нет ли примера?
13 июн 18, 10:59    [21487659]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
Дегтярев Евгений
Member

Откуда: Барнаул
Сообщений: 1398
petrovitch, не видел чтобы такое выкладывали
13 июн 18, 11:30    [21487765]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
tip78
Member

Откуда: Москва
Сообщений: 986
Дегтярев Евгений
petrovitch
совершенно верно, есть варианты?
как в память минуя базу грузить и там делать вычисления?
на самом деле нужны мгновенные данные, через секунду они уже неактуальны, в базе будут перезаписаны update

по мне так rest интерфейс странное решение для поставщика ликвидности... зато стильномодномолодежно
плюс, подозреваю, что содержимое БД перезаписывает без проверки, а поменялись ли реально данные

доставка до клиента текущих котировок должна быть без участия БД, типа
- сервис, который забирает котировки у поставщика, какая-то предобработка, запись истории (если нужно), плюс интерфейс для других сервисов, собственно основная задача сервиса привести форматы поставщиков к общему знаменателю
- сервис(ы) доставки до клиента (tcp, rest, websocket и т.д.), которые в реальном времени получают данные от первого сервиса конвертируют в формат клиента и отправляют клиенту... тот же могут быть другие обработки, например, отправка раз в секунду, или отправка только нужных клиенту котировок...

зачем всё усложнять
проверку вообще-то делает UPDATE, если данные те же, он не происходит. А в случае с редиской он вообще не нужен.
доставки до клиента нет, есть веб-страница
13 июн 18, 13:09    [21488136]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
Дегтярев Евгений
Member

Откуда: Барнаул
Сообщений: 1398
tip78
зачем всё усложнять

для наколенной поделки - да усложнение, в реальном продукте, поверьте, нет

автор
проверку вообще-то делает UPDATE, если данные те же, он не происходит.

а что происходит до того как update непосредственно начнет делать проверку и сразу после?

автор
доставки до клиента нет, есть веб-страница

1 это тоже доставка
2 не будем фантазировать за автора
13 июн 18, 14:13    [21488406]     Ответить | Цитировать Сообщить модератору
 Re: долгое время выполнения всех запросов, падение базы  [new]
Дегтярев Евгений
Member

Откуда: Барнаул
Сообщений: 1398
petrovitch,

0. rest запросы последовательно выполняются или параллельно?
1. один rest запрос сколько запросов в БД рожает?
2. Если несколько, все апдейты в одной транзакции или нет?
13 июн 18, 14:22    [21488444]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить