Потеря производительности InnoDB при переносе на VPS

[Pirks]

День добрый!
Есть консольный скрипт на PHP, работал на десктопе в офисе. 
На десктопной Ubuntu 16.04 ( i5-7200U 8Gb ) mySQL 5.7 - тестовый скрипт на PHP, банальный INSERT ~ 1000 записей, работает на порядок быстрее, чем на VPS Ubuntu 18.04 ( E5-2630, 24 Gb ) mySQL 8.0. 
my.cfg у 8

innodb_adaptive_flushing  ON
innodb_buffer_pool_instances  8
innodb_buffer_pool_size  1073741824
innodb_flush_log_at_trx_commit  2
innodb_flush_method  fsync
innodb_flush_neighbors  1
innodb_log_file_size  1073741824
innodb_max_dirty_pages_pct  90.000000
innodb_max_dirty_pages_pct_lwm  1.000000
max_allowed_packet  16777216

Куда копать?
Есть утверждения, что это нормально для VPS, но замер производительности дисковой подсистемы показывает что SSD на VPS на порядок быстрее, чем на десктопе обычного HDD, что логично.  

Змінено 26 січня 2020 користувачем Pirks

[Yoda]

1 час назад, Pirks сказал:

День добрый!
Есть консольный скрипт на PHP, работал на десктопе в офисе. 
На десктопной Ubuntu 16.04 ( i5-7200U 8Gb ) mySQL 5.7 - тестовый скрипт на PHP, банальный INSERT ~ 1000 записей, работает на порядок быстрее, чем на VPS Ubuntu 18.04 ( E5-2630, 24 Gb ) mySQL 8.0. 
my.cfg у 8

innodb_adaptive_flushing  ON
innodb_buffer_pool_instances  8
innodb_buffer_pool_size  1073741824
innodb_flush_log_at_trx_commit  2
innodb_flush_method  fsync
innodb_flush_neighbors  1
innodb_log_file_size  1073741824
innodb_max_dirty_pages_pct  90.000000
innodb_max_dirty_pages_pct_lwm  1.000000
max_allowed_packet  16777216

Куда копать?
Есть утверждения, что это нормально для VPS, но замер производительности дисковой подсистемы показывает что SSD на VPS на порядок быстрее, чем на десктопе обычного HDD, что логично.  

все зависит не только от диска. Если к примеру взять очень большую таблицу, и внее делать insert и это mysql а не mariadb и таблицы innodb, то пересчет индекса - это напрямую мощность процессора, который зарезан на vps, а никак не скорость дисковой подсистемы. Это раз.

Два чем больше памяти может пользовать база - тем ей легче.

[Yoda]

1 час назад, Pirks сказал:

День добрый!
Есть консольный скрипт на PHP, работал на десктопе в офисе. 
На десктопной Ubuntu 16.04 ( i5-7200U 8Gb ) mySQL 5.7 - тестовый скрипт на PHP, банальный INSERT ~ 1000 записей, работает на порядок быстрее, чем на VPS Ubuntu 18.04 ( E5-2630, 24 Gb ) mySQL 8.0. 
my.cfg у 8

innodb_adaptive_flushing  ON
innodb_buffer_pool_instances  8
innodb_buffer_pool_size  1073741824
innodb_flush_log_at_trx_commit  2
innodb_flush_method  fsync
innodb_flush_neighbors  1
innodb_log_file_size  1073741824
innodb_max_dirty_pages_pct  90.000000
innodb_max_dirty_pages_pct_lwm  1.000000
max_allowed_packet  16777216

Куда копать?
Есть утверждения, что это нормально для VPS, но замер производительности дисковой подсистемы показывает что SSD на VPS на порядок быстрее, чем на десктопе обычного HDD, что логично.  

все зависит не только от диска. Если к примеру взять очень большую таблицу, и внее делать insert и это mysql а не mariadb и таблицы innodb, то пересчет индекса - это напрямую мощность процессора, который зарезан на vps, а никак не скорость дисковой подсистемы. Это раз.

Два чем больше памяти может пользовать база - тем ей легче.

 

А еще, судя по всему, если вы говорите про mysql8, она у вас задеплоена через докер из ISP  панели, и есть большая проблема с базами которые работют в докере, у них реально очень большой лаг  с производительностью, используйте родную базу и будет все хорошо.

[esculapra]

Я заметил, шо табы innodb сильно грузят память. сделай экспорт и смени innodb на другую - я счас не помню

[Pirks]

4 часа назад, Yoda сказал:

все зависит не только от диска. Если к примеру взять очень большую таблицу, и внее делать insert и это mysql а не mariadb и таблицы innodb, то пересчет индекса - это напрямую мощность процессора, который зарезан на vps, а никак не скорость дисковой подсистемы. Это раз.

Два чем больше памяти может пользовать база - тем ей легче.

 

А еще, судя по всему, если вы говорите про mysql8, она у вас задеплоена через докер из ISP  панели, и есть большая проблема с базами которые работют в докере, у них реально очень большой лаг  с производительностью, используйте родную базу и будет все хорошо.

Нет, здесь без докера. Все ставится на пустую Ubuntu 18.04, никаких ISP или других GUI для управления.  Мониторится через Telegraf в InfluxDB и затем Grafana, все видно  - ресурсов море, но в INSERT не такой как хотелось бы.
И дисковую подсистему тестил -

dd if=/dev/zero of=sb-io-test bs=1M count=1k conv=fdatasync; rm -rf sb-io-test

1073741824 bytes (1.1 GB, 1.0 GiB) copied, 1.52426 s, 704 MB/s - это VPS.
скопировано 1073741824 байта (1,1 GB), 15,1642 c, 70,8 MB/c - это десктоп.

Змінено 26 січня 2020 користувачем Pirks

[Pirks]

2 часа назад, esculapra сказал:

Я заметил, шо табы innodb сильно грузят память. сделай экспорт и смени innodb на другую - я счас не помню

Пробовал - на MyISAM, аналогично.

[100napb]

7 hours ago, Pirks said:

Куда копать?

если с ходу, то:

а) банальный INSERT ~ 1000 записей - это 1000х инсертов или один инсерт вида insert into table_name (id, val) values(1,1),(1,2),(1,3),… ?

б) установленный уровень фиксации транзаций (innodb_flush_log_at_trx_commit  2) накладывает серьезные ограничения на скорость изменения данных; если нет жестких требований, обусловливающих необходимость именно такого уровня, то смело ставьте 0. Если у Вас не кассовое \ банковское ПО, конечно

в) у Вас включен bin log (настроена репликация)? если нет, то убедитесь, что он не включен

г) при установленном размере innodb_buffer_pool_size в 1гиг рекомендуется в общем случае использовать innodb_log_file_size = 128m и innodb_log_buffer_size = 32m

д) почитайте за возможные значения innodb_flush_method и их отличия.

е) зачем трогать довольно тонкие механизмы вроде innodb_max_dirty_pages_pct_* ? из коробки вроде иные значения. оставьте их - дефолтные.

ж) mysqltuner говорит что-то внятное?

 

Змінено 26 січня 2020 користувачем 100napb

[Pirks]

2 часа назад, 100napb сказал:

если с ходу, то:

а) банальный INSERT ~ 1000 записей - это 1000х инсертов или один инсерт вида insert into table_name (id, val) values(1,1),(1,2),(1,3),… ?

б) установленный уровень фиксации транзаций (innodb_flush_log_at_trx_commit  2) накладывает серьезные ограничения на скорость изменения данных; если нет жестких требований, обусловливающих необходимость именно такого уровня, то смело ставьте 0. Если у Вас не кассовое \ банковское ПО, конечно

в) у Вас включен bin log (настроена репликация)? если нет, то убедитесь, что он не включен

г) при установленном размере innodb_buffer_pool_size в 1гиг рекомендуется в общем случае использовать innodb_log_file_size = 128m и innodb_log_buffer_size = 32m

д) почитайте за возможные значения innodb_flush_method и их отличия.

е) зачем трогать довольно тонкие механизмы вроде innodb_max_dirty_pages_pct_* ? из коробки вроде иные значения. оставьте их - дефолтные.

ж) mysqltuner говорит что-то внятное?

 

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

[ibond]

9 часов назад, Pirks сказал:

mySQL 8.0

 

7 часов назад, Yoda сказал:

она у вас задеплоена через докер из ISP  панели

 

Changes in MySQL 8.0.19 (2020-01-13, General Availability)

..... обновляйтесь.

[Pirks]

7 часов назад, ibond сказал:

 

 

Changes in MySQL 8.0.19 (2020-01-13, General Availability)

..... обновляйтесь.

Я неделю назад устанавливался ...

[Pirks]

11 часов назад, 100napb сказал:

в) у Вас включен bin log (настроена репликация)? если нет, то убедитесь, что он не включен

Да! Это был он, скорость добавления записей значительно выросла.
Еще обратил внимание на наличие файлов  binlog.* в  /var/lib/mysql , но не стал разбираться.

100napb, спасибо за подсказку!
 

[Pirks]

11 часов назад, 100napb сказал:

ж) mysqltuner говорит что-то внятное?

 

За mysqltuner, отдельно спасибо! )
 

[Pirks]

После выполнения рекомендаций  mysqltuner, получил прирост скорости записи в 3-и раза. Но, скорость записи на VPS, все еще ниже скорости записи на десктопе ...  в 3-и раза.
Вот результаты sysbench
sysbench /usr/share/sysbench/oltp_write_only.lua --threads=4 --events=0 --time=30 --mysql-host=localhost --mysql-user=user --mysql-password=pass --mysql-port=3306 --tables=5 --table-size=10000 --range_selects=off --db-ps-mode=disable --report-interval=1 --db-driver=mysql run

SQL statistics:
    queries performed:
        read:                            0
        write:                           500850
        other:                           259226
        total:                           760076
    transactions:                        126679 (4221.99 per sec.)
    queries:                             760076 (25332.00 per sec.)
    ignored errors:                      1      (0.03 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          30.0028s
    total number of events:              126679

Latency (ms):
         min:                                  0.55
         avg:                                  0.94
         max:                                 17.17
         95th percentile:                      1.21
         sum:                             119326.31

Threads fairness:
    events (avg/stddev):           31669.7500/19.31
    execution time (avg/stddev):   29.8316/0.00

Если кто в теме, прокомментируйте ... 

Змінено 27 січня 2020 користувачем Pirks

[100napb]

47 minutes ago, Pirks said:

все еще ниже скорости записи на десктопе ...  в 3-и раза.

виртуализация она такая =\

 

не знаю конфигурации сервера и нюансов, но стоит попробовать

skip-innodb-doublewrite

innodb_file_per_table

tmpdir = /dev/shm
tmp_table_size  = 128m
max_heap_table_size = 128m
bulk_insert_buffer_size = 128m

 

больше особо нечего ковырять.

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

 

Spoiler

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

 

Змінено 27 січня 2020 користувачем 100napb

[Pirks]

1 час назад, 100napb сказал:

виртуализация она такая =\

 

  Показать контент

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

 

Ну вообще, если 
InnoDB Write Log efficiency: 88.9% (5286 hits/ 5946 total) - это наверное не плохо.
Гулю тему и вижу люди жалуются на этот показатель меньше 10%
Жду ваш sysbench )
 

[Yoda]

а что у вас там в innodb_flush_log_at_trx_commit  ?

[Pirks]

16 часов назад, Yoda сказал:

а что у вас там в innodb_flush_log_at_trx_commit  ?

2

Я уже переустановил MySQL - 5.7 чтобы конфигурация была идентична десктопной.
По прежнему скорость ниже в три раза в тестовом скрипте - 1000 запросов INSERT, VPS ~0.3 s Desctop ~0.1 s
Но! Если сделать пакетное добавление - 100000 записей, пакетами по 100 INSERT в запросе, время ~1.2 s на десктопе ~1.1 s
Делаем вывод - неверная архитектура приложения сводит на нет вычислительные мощности VPS.

 

Змінено 28 січня 2020 користувачем Pirks