long execution time of all requests, drop base
Hello.
I can not find an explanation for such a long implementation of the query, 1.5 sec.
And this is how all tables behave, in practice, with both update and insert.
An average of 1 second or more is a request.
From file 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';
in the table consistently 35 lines, I can add UNIQUE INDEX to the column pair, but with so many rows, in my performance will not increase.
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
tried to execute a similar request in such a loop
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();
}
}
the execution time is 0.1 - 0.2 seconds, but this is also very much
no errors, except that the base falls, if you run the collection of quotes from all exchanges, again, the cause of the fall does not write to the log, but given that the computer is a separate machine,
processor Intel(R) Xeon(R) CPU X3440 @ 2.53GHz
memory 16GiB System Memory
and memory completely disappears, the reason is only in mysql.
But what's the matter, I can not understand, have thoughts?
Thank you.