SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 10.06.2018 14:23:15

Urri
Участник
Зарегистрирован: 09.06.2018
Сообщений: 8

долгое время выполнения всех запросов, падение базы

Привет.
Не могу найти объяснение такому долгому выполнению выполнению запроса, 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.

А вот в чем дело, не могу понять, есть мысли?
Спасибо.

Неактивен

 

#2 10.06.2018 15:29:29

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5516

Re: долгое время выполнения всех запросов, падение базы

что показывает профилирование запроса? на что тратится время?

Неактивен

 

#3 10.06.2018 15:59:23

Urri
Участник
Зарегистрирован: 09.06.2018
Сообщений: 8

Re: долгое время выполнения всех запросов, падение базы

Что то не то делаю

set profiling=1;

MariaDB [exchanges]> show profiles;
+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.00020477 | SELECT DATABASE() |
|        2 | 0.30240185 | show databases    |
|        3 | 0.00041044 | show tables       |
|        4 | 0.00015141 | set profiling=1   |
|        5 | 0.00820053 | show variables    |
+----------+------------+-------------------+

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

Неактивен

 

#4 10.06.2018 16:09:38

Urri
Участник
Зарегистрирован: 09.06.2018
Сообщений: 8

Re: долгое время выполнения всех запросов, падение базы

Если запрос выполнить в консоли
MariaDB [exchanges]> 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'
    -> ;
Query OK, 1 row affected (0.45 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [exchanges]> show profiles;
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                                                                                                                                                                                        |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00020477 | SELECT DATABASE()                                                                                                                                                                                                                                                                                            |
|        2 | 0.30240185 | show databases                                                                                                                                                                                                                                                                                               |
|        3 | 0.00041044 | show tables                                                                                                                                                                                                                                                                                                  |
|        4 | 0.00015141 | set profiling=1                                                                                                                                                                                                                                                                                              |
|        5 | 0.00820053 | show variables                                                                                                                                                                                                                                                                                               |
|        6 | 0.00011058 | set profiling=1                                                                                                                                                                                                                                                                                              |
|        7 | 0.44357887 | 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, bitst |
+----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

MariaDB [exchanges]> show profile for query 7;
+------------------------------+----------+
| Status                       | Duration |
+------------------------------+----------+
| starting                     | 0.000087 |
| checking permissions         | 0.000007 |
| Opening tables               | 0.000014 |
| After opening tables         | 0.000007 |
| System lock                  | 0.000005 |
| Table lock                   | 0.000004 |
| init                         | 0.000127 |
| updating                     | 0.084908 |
| end                          | 0.000015 |
| Waiting for query cache lock | 0.000025 |
| end                          | 0.000017 |
| query end                    | 0.358251 |
| closing tables               | 0.000023 |
| Unlocking tables             | 0.000026 |
| freeing items                | 0.000019 |
| updating status              | 0.000036 |
| cleaning up                  | 0.000005 |
+------------------------------+----------+
17 rows in set (0.00 sec)

получается что | query end                    | 0.358251 |
самое длинное
я пробовал для таких процессов - query end
их очень много висит в ответе на запрос show processlist
написать событие
BEGIN
select @q:=CONCAT('KILL ',ID, ';') 
FROM information_schema.processlist where State = 'query end' or
Command = 'Sleep';
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
после нескольких минут работы база рухнула так, что восстанавливать пришлось из бэкапов

Неактивен

 

#5 10.06.2018 16:28:19

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5516

Re: долгое время выполнения всех запросов, падение базы

Неактивен

 

#6 10.06.2018 17:26:57

Urri
Участник
Зарегистрирован: 09.06.2018
Сообщений: 8

Re: долгое время выполнения всех запросов, падение базы

Такой код в скрипте, в котором 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)");
выдает странный результат
a:3:{s:8:"Query_ID";s:1:"1";s:8:"Duration";s:10:"0.00008751";s:5:"Query";s:82:"SELECT birga AS name, allow_disallow FROM trade_management where birga is not null";}
show profile for query 1;
a:2:{s:6:"Status";s:8:"starting";s:8:"Duration";s:8:"0.000022";}
Duration в обоих строках должно быть одинаковое, а время не совпадает, (так же и для остальных запросов).
Есть соображения?

Неактивен

 

#7 10.06.2018 19:28:38

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5516

Re: долгое время выполнения всех запросов, падение базы

каковы значения query cache, innodb_file_per_table, innodb_flush_log_at_trx_commit, sync_binlog ?

Неактивен

 

#8 10.06.2018 20:11:21

Urri
Участник
Зарегистрирован: 09.06.2018
Сообщений: 8

Re: долгое время выполнения всех запросов, падение базы

query_cache_limit       = 1M
query_cache_size        = 16M
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
sync_binlog - в конфиге не прописан, значит дефолтное значение

Неактивен

 

#9 10.06.2018 21:08:42

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5516

Re: долгое время выполнения всех запросов, падение базы

Неактивен

 

#10 11.06.2018 11:29:46

Urri
Участник
Зарегистрирован: 09.06.2018
Сообщений: 8

Re: долгое время выполнения всех запросов, падение базы

добавил в конфиг
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

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

# Thread_id: 6800  Schema: exchanges  QC_hit: No
# Query_time: 57.966748  Lock_time: 55.195027  Rows_sent: 0  Rows_examined: 0
# Rows_affected: 0
SET timestamp=1528702564;

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.36343383789062 WHERE pair = 'btc_usd';

Неактивен

 

#11 11.06.2018 11:33:53

Urri
Участник
Зарегистрирован: 09.06.2018
Сообщений: 8

Re: долгое время выполнения всех запросов, падение базы

show variables;
https://www.sendspace.com/file/1igjoy
show global status;
https://www.sendspace.com/file/cg36ev
select version();
10.1.26-MariaDB-0+deb9u1

Неактивен

 

#12 11.06.2018 11:38:43

Urri
Участник
Зарегистрирован: 09.06.2018
Сообщений: 8

Re: долгое время выполнения всех запросов, падение базы

конфиг
50-server.cnf
https://www.sendspace.com/file/q49djq

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson