EN ----------------------------------------------- EN
 
Symptoms 
 
The whole server is beeing laggy and clues lead to SQL operation causing this. Such lags may occur during extensive UPDATE, INSERT or SELECT clauses. One of the possible ways to identify such long requests is to analyze your servers' logs. Search for Warning messages from DB:
 
WARN 2017-05-30 17:39:30.665 {} <<Thread>> [130875] DB::noRS(427 ms) UPDATE `character` SET HungerRate=8434 WHERE ID=3; [T:DBIInvHelper:0x15B4]
 
Values higher than 1000 ms can be already treated as extremely long ones.
 
 
Preparations
 
IMPORTANT: MariaDB version 5.5 doesn't contain tables like 'events_statements%' which are used to gather data. MariaDB was upgraded to the latest stable version at this moment 10.2.6 (2017-05-30) during this article preparation without any issues
 
First of all, we have to turn on a function which will allow us to gather performance data 
Navigate to your SQL installation folder,
(Example with a default MariaDB path: C:\Program Files (x86)\MariaDB 5.5\data\my.ini)
and add performance_schema=on right after [mysqld]
DB server has to be restarted after making such changes in a configuration file. Here is an example from Windows 10. MySQL service restart operation was enough.  
Data becomes available right after service restart, but you'll require some time to gather data for analysis. About 1-2 days should enough to collect needed information. There is no need to load your server with any additional actions, just regular game activity.

Take into consideration that any additional operations with the database such as DB/table export or import will be included into performance log and will reduce the accuracy of assessment.

Here is a SQL command to flush log data and start data gathering once again from a blank sheet. It will require 1-2 days as stated earlier.
 
TRUNCATE table performance_schema.events_statements_summary_by_digest
 
 
Data gathering
 
There is no need to shut down LiF server before running scripts to performance_schema. Example:
 
SELECT
  schema_name
, digest_text
, count_star
, sum_timer_wait/1000000000000 as sum_timer_wait
, avg_timer_wait/1000000000000 as avg_timer_wait
, max_timer_wait/1000000000000 as max_timer_wait
, min_timer_wait/1000000000000 as min_timer_wait
, sum_lock_time/1000000000000 as sum_lock_time
, sum_rows_sent
, sum_rows_examined
, sum_rows_affected
, first_seen
, last_seen
FROM performance_schema.events_statements_summary_by_digest
ORDER BY count_star DESC
LIMIT 10;
 
You will receive information about the most frequent clauses, their time is taken for execution and possible locks done. We should focus on the top UPDATE, SELECT and INSERT clauses.
 
You will receive negligible values for INSERT in a sum_timer_wait/count_star (should be calculated manually) in case DB is already optimized. Value from our example is just 6,998e-5.
Before DB was optimised values were around 0,023 - 0,027.
 
Acceptable values for fields
avg_timer_wait
max_timer_wait
min_timer_wait
are between 0,000 and 0,003
 
 
Recommendations
For changes in my.ini which should be done between [mysqld] and [client]
 
innodb_log_file_size
#this parameter can be used to reduce input/output costs innodb_log_file_size = 150M
 
innodb_buffer_pool_size
#should be set to 75-90% from a server total memory, 90% if this is a dedicated database server or just set that amount which is available for a database server. innodb_buffer_pool_size =50000M
 
query_cache_type
query_cache_size
#turning off queries cache. query_cache_type = OFF query_cache_size = 0
 
innodb_io_capacity
innodb_io_capacity_max
innodb_log_buffer_size
#here are approximate values for SSD in a dedicated database server configuration for input/output operations
  HDD SSD
innodb_io_capacity 200 3000
innodb_io_capacity_max 50-1000 5000
innodb_log_buffer_size 8M-40M 8M

These values can't be set similar for everyone as they strongly depend on a hardware manufacturer and performance. The best values are individual and can only be obtained experimentally.


P.S. Do not forget to update my.ini with performance_schema=off after you finish with performance review as additional logging can slightly reduce database speed.

 
 
RU ----------------------------------------------- RU
 
 
Симптомы 
 
Сервер лагает. Подозрения админа или даже игроков (периодичность возникновения лагов) на скорость работы базы данных, а точнее долгое выполнение основных запросов UPDATE, INSERT, SELECT. Один из способов подтвердить подозрение - анализ серверных логов. Ищите по строкам типа Warning и операциям с базой данных DB::
WARN 2017-05-30 17:39:30.665 {} <<Thread>> [130875] DB::noRS(427 ms) UPDATE `character` SET HungerRate=8434 WHERE ID=3; [T:DBIInvHelper:0x15B4]
Значения более 1000 мс считаются катастрофически долгими.
 
 
Подготовка
 
ВАЖНО: MariaDB версии 5.5 не содержит таблицы вида 'events_statements%', которые используются для анализа производительности базы. При подготовке этого материала MariaDB была обновлена до последней стабильной версии на данный момент 10.2.6 (2017-05-30) Проблем с запуском сервера после обновления не возникло
 
В первую очередь необходимо включить сбор статистики на сервере базы данных
Пример с MariaDB и папкой в которую она устанавливается по умолчанию
C:\Program Files (x86)\MariaDB 5.5\data\my.ini
Cделать это можно вставив строку
performance_schema=on
в файл my.ini сразу после строки [mysqld] 
После этого понадобится рестарт сервера БД. В примере с сервером на ОС Windows 10 достаточно перезапуска сервиса mySQL
 
Анализ и просмотр статистики доступен сразу после рестарта, но для более верной оценки необходимо накопить некий объем данных, на основании которого уже можно делать выводы. 1-2 дней должно быть достаточно. Необходимости специально нагружать сервер, нет - только обычная игровая активность. 
 
Если в течении этого времени будут производится снятия дампов базы или другие подобные действия, то это может повлиять на достоверность данных в худшую сторону. 
 
 
Cбросить данные по производительности можно при помощи SQL запроса. После этой операции, рекомендуется дать серверу поработать все те же 1-2 дня. 
 
TRUNCATE table performance_schema.events_statements_summary_by_digest
 
 
Сбор данных
Гасить игровой сервер LiF не обязательно. Варианта запроса:
 
SELECT
  schema_name
, digest_text
, count_star
, sum_timer_wait/1000000000000 as sum_timer_wait
, avg_timer_wait/1000000000000 as avg_timer_wait
, max_timer_wait/1000000000000 as max_timer_wait
, min_timer_wait/1000000000000 as min_timer_wait
, sum_lock_time/1000000000000 as sum_lock_time
, sum_rows_sent
, sum_rows_examined
, sum_rows_affected
, first_seen
, last_seen
FROM performance_schema.events_statements_summary_by_digest
ORDER BY count_star DESC
LIMIT 10;
 
В этом запросе извлекается информация о наиболее частых запросах, времени их выполнения и возможных блокировках создаваемых этими запросами. Нас интересуют три запроса: топовый UPDATE, SELECT и INSERT.
 
В случае приемлемой оптимизации, калькуляция запроса INSERT по колонкам sum_timer_wait/count_star (в запросе не выводится, следует считать отдельно) должна составлять пренебрежительно малую величина величину. В нашем примере, получилось число порядка 6,998e-5.
В случае не оптимальных настроек БД, результат может быть таким (из нашего примера) 0,023 - 0,027.
 
Нормальные показатели для полей
avg_timer_wait
max_timer_wait
min_timer_wait
находятся в диапазоне от 0,000 до 0,003
 
 
Рекомендации по настройке
Правки в файле my.ini между [mysqld] и [client]
 
innodb_log_file_size
#этот параметр необходим для снижения затрат на ввод/вывод innodb_log_file_size = 150M
 
innodb_buffer_pool_size
#устанавливается в 75-90% памяти сервера, 90% если это отдельная машина под базу данных. Или просто на то количество памяти которое гарантированно доступно для сервера БД. innodb_buffer_pool_size =50000M
 
query_cache_type
query_cache_size
#отключение кеша запросов. query_cache_type = OFF query_cache_size = 0
 
innodb_io_capacity
innodb_io_capacity_max
innodb_log_buffer_size
#примерные значения для SSD в выделенном сервере, количество операций ввода/вывода
  HDD SSD
innodb_io_capacity 200 3000
innodb_io_capacity_max 50-1000 5000
innodb_log_buffer_size 8M-40M 8M
Эти значения могут отличаться и зависят как от производителя железа, так и от его производительности. Настройка, к сожалению, индивидуальна и правильные для вас значения можно получить лишь экспериментальным путем. 


P.S. Не забывайте выключать performance_schema=off после внесения всех необходимых правок, так как она может незначительно замедлить производительность базы.

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Article is closed for comments.