Jump to content
Pirks

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

Recommended Posts

День добрый!
Есть консольный скрипт на 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, что логично.  

Edited by Pirks

Share this post


Link to post
Share on other sites
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, а никак не скорость дисковой подсистемы. Это раз.

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

Share this post


Link to post
Share on other sites
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  панели, и есть большая проблема с базами которые работют в докере, у них реально очень большой лаг  с производительностью, используйте родную базу и будет все хорошо.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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 - это десктоп.

Edited by Pirks

Share this post


Link to post
Share on other sites
2 часа назад, esculapra сказал:

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

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

Share this post


Link to post
Share on other sites
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 говорит что-то внятное?

 

Edited by 100napb
  • +1 4

Share this post


Link to post
Share on other sites
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 отдельных запросов. Да, я  в курсе, как сделать одним запросом добавление нескольких записей. Но цель теста имитировать работу основного скрипта, который переносится с десктопа, он не оптимален, но он работает. Но это уже не моя тема а разработчика.
б,в,г,д,е,ж ) Спасибо, погугулю про эти параметры, попробую   

Share this post


Link to post
Share on other sites
9 часов назад, Pirks сказал:

mySQL 8.0

 

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

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

 

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

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

Share this post


Link to post
Share on other sites
7 часов назад, ibond сказал:

 

 

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

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

Я неделю назад устанавливался ...
image.png.900b40f4c447745227a0d1bef08fecc8.png

Share this post


Link to post
Share on other sites
11 часов назад, 100napb сказал:

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

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

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

  • +1 1

Share this post


Link to post
Share on other sites
11 часов назад, 100napb сказал:

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

 

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

Share this post


Link to post
Share on other sites

После выполнения рекомендаций  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

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

Edited by Pirks

Share this post


Link to post
Share on other sites
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

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

 

Edited by 100napb

Share this post


Link to post
Share on other sites
1 час назад, 100napb сказал:

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

 

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

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

 

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

 

Edited by Pirks

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
You are posting as a guest. If you have an account, please sign in.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×

Important Information

On our site, cookies are used and personal data is processed to improve the user interface. To find out what and what personal data we are processing, please go to the link. If you click "I agree," it means that you understand and accept all the conditions specified in this Privacy Notice.