MYSQL на SSD оптимизация?

Добрый день!
На прошлом хостинге находились на NVMe дисках, был размещен Битрикс24 коробка на BitrixENV ,все было сносно и приемлемо.
Переехали на новый с SSD дисками, 6 ядер 12 гб оперативки и понеслась, тормоза долгое выполнение запросов, 504\502 все очень долго.
База весит 47 гб
Как исвестно у Битриксса есть такая штука как bvat ,это автоматический скрипт который сам настраивает параметры mysql в зависимости от конфигурации, так вот он сделал их тамкими.
[mysqld]
    query_cache_type = 1
    query_cache_size = 128M
    query_cache_limit = 16M
    innodb_buffer_pool_size = 6144M
    max_connections = 85
    table_open_cache = 14336
    thread_cache_size = 128
    max_heap_table_size = 128M
    tmp_table_size = 128M
    key_buffer_size = 96M
    join_buffer_size = 18M
    sort_buffer_size = 18M
    bulk_insert_buffer_size = 2M
    myisam_sort_buffer_size = 18M


Прогнал mysqltunner который такие дал рекомендации

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control error line(s) into /var/log/mysql/error.log file
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/1mi7c4C
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (5000) variable
    should be greater than table_open_cache (2452)
    Thread pool size for InnoDB usage (6)
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 128M) [see warning above]
    join_buffer_size (> 18.0M, or always use indexes with JOINs)
    table_open_cache (> 2452)
    thread_pool_size between 16 and 36 for InnoDB usage
    innodb_buffer_pool_size (>= 39.3G) if possible.
    innodb_log_file_size should be (=768M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=6)


Выставление данных рекомендаций только ухудшает ситуацию.
Сильное влияние тут имеет дисковая подсистема, почему то и SSD работает медленнно, все это крутиться на VDS не на выделенном сервере и RAID создать нельзя и NVMe тоже хотсер не может сделать,но он дает гарантии uptime поэтому тут и находимся

Кто то может помоч с советом что еще можно попробовать?

Скорость ЗАПИСИ
sync; dd if=/dev/zero of=tempfile bs=1M count=1024; sync
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 2.79635 s, 384 MB/s

Скорость ЧТЕНИЯ из буфера
dd if=tempfile of=/dev/null bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 0.362921 s, 3.0 GB/s

Реальная СКОРОСТЬ чтения
/sbin/sysctl -w vm.drop_caches=3
vm.drop_caches = 3
dd if=tempfile of=/dev/null bs=1M count=1024
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB) copied, 26.643 s, 40.3 MB/s


hdparm -Tt /dev/vda2
/dev/vda2:
 Timing cached reads:   13680 MB in  1.98 seconds = 6894.48 MB/sec
 Timing buffered disk reads:  38 MB in  3.56 seconds =  10.68 MB/sec
  • Вопрос задан
  • 609 просмотров
Пригласить эксперта
Ответы на вопрос 2
dimonchik2013
@dimonchik2013
...а ну-ка пыль сдуй отсюда...
1073741824 bytes (1.1 GB) copied, 26.643 s, 40.3 MB/s

это приговор
железо не то, что в ваших ожиданиях или в заявленном маркетинге
Ответ написан
65536
@65536
query_cache_type = 1

там myisam что ли? если да, и если таблицы большие, то надо менять на иннодб. кэш этот вырубать, тормозов от него больше чем пользы, особенно когда много записи идет. потом в зависимости от размера оперативки подобрать значения иннодбшных параметров

меня вот эта статья спасла https://ruhighload.com/%D0%9E%D0%BF%D1%82%D0%B8%D0...

для 8 гб выглядит так

innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_buffer_pool_instances = 4

innodb_flush_log_at_trx_commit я поставил 0, так быстрее всего но типа ненадежно, но незнаю где там эта грань надежности и как надо базу нагрузить чтоб проломить эту грань, никаких проблем с данными больше чем за год не случилось.
https://ruhighload.com/innodb_flush_log_at_trx_com...

думаю что всё очень индивидуально, зависит от кол-ва столбцов и их типов, но от маисама лучше уходить. он начинает конкретно тормозить на таблицах от каких-нить 100к записей. перед уходом желательно убедиться не используются ли в нем какие то фичи которые еще не появились в иннодб. начиная с 5.7 вроде ничего такого не осталось. до неё были различия в работе с переменными

майсклтюнер дал не самые лучшие советы кстати, но близкие
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы