Разместите свой проект бесплатно и начните получать предложения от фрилансеров-исполнителей уже спустя минуты после публикации!

Оптимизация mysql сервера и ускорение запросов типа select

истекло время актуальности


Имеется база данных размером 13.4Гб. Диск ssd размером 60Гб и 32RAM. Это облачный сервер serverpilot.io

Максимальный вес одной таблицы 1.2Гб, всего их около 30. 


Работа mysql в основном построена на select/insert/delete, update не используется вообще. 

Поэтому быстродействие запроса типа select должно быть на феноменальном уровне. 


Запрос типа:

select * from api_orders where bonus_card = "00510051100156";

Выполняется за:

Без индекса 

4 rows in set, 6 warnings (2.52 sec)


С индексом bonus_card (BTREE)

4 rows in set, 6 warnings (2.14 sec)

 

Существенного прироста скорости нет. 


Это самый простой запрос, который выполняется очень долго, по одной из таблиц. Если взять запрос на выбор клиентов по периоду покупок с присвоением inner join в этом случаи мы получим отработку в лучшем случаи за 20-30 минут.  Что откровенно говоря не поддается ни какому объяснению. Запросы довольно просты. 


Для понимая происходящего прикладываю текущую конфигурацию сервера с дебагом. 

Конфигурат:

key_buffer_size = 16M

sort_buffer_size = 32K

max_allowed_packet = 512M

read_buffer_size = 128K

read_rnd_buffer_size = 512K

thread_stack = 128K


max_heap_table_size = 16M

tmp_table_size = 16M

query_cache_size = 0

query_cache_type = 0

thread_cache_size = 128K

table_open_cache_instances = 16

open_files_limit = 500000

table_open_cache = 512

join_buffer_size = 64K


max_connections = 1000

net_read_timeout = 3600

net_write_timeout = 3600

wait_timeout = 120

interactive_timeout = 120


innodb_open_files = 4000

innodb_file_per_table = 1

innodb_flush_method=O_DIRECT

innodb_flush_log_at_trx_commit=2

innodb_log_file_size = 4G

innodb_log_buffer_size = 32M

innodb_log_files_in_group = 16

innodb_buffer_pool_size=24G

innodb_buffer_pool_instances=24

innodb_buffer_pool_chunk_size = 50M

innodb_thread_concurrency = 4

innodb_read_io_threads = 4

innodb_write_io_threads = 4

innodb_stats_on_metadata = 0


innodb_io_capacity=2000

innodb_io_capacity_max=4000

innodb_purge_threads=4

innodb_data_file_path=ibdata1:200M:autoextend


innodb_page_cleaners=4

innodb_adaptive_hash_index=0


Дебаг:

-------- Performance Metrics -------------------------------------------------

[--] Up for: 5s (9 q [1.800 qps], 7 conn, TX: 17K, RX: 1K)

[--] Reads / Writes: 100% / 0%

[--] Binary logging is disabled

[--] Total buffers: 24.1G global + 832.0K per thread (500 max threads)

[OK] Maximum reached memory usage: 24.1G (76.58% of installed RAM)

[OK] Maximum possible memory usage: 24.5G (77.84% of installed RAM)

[OK] Slow queries: 0% (0/9)

[OK] Highest usage of available connections: 0% (1/500)

[!!] Aborted connections: 14.29%  (1/7)

[!!] Query cache is disabled

[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)

[OK] Thread cache hit rate: 85% (1 created / 7 connections)

[!!] Table cache hit rate: 9% (10 open / 109 opened)

[OK] Open file limit used: 0% (7/1M)

[OK] Table locks acquired immediately: 100% (100 immediate / 100 locks)

-------- InnoDB Metrics -----------------------------------------------------

[--] InnoDB is enabled.

[OK] InnoDB buffer pool / data size: 24.0G/13.4G

[OK] InnoDB buffer pool instances: 24

[!!] InnoDB Used buffer: 0.62% (9804 used/ 1572768 total)

[!!] InnoDB Read buffer efficiency: 13.50% (1525 hits/ 11295 total)

[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)



Интересует профессиональный взгляд на проблему с учетом ее решения. 

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


Я надеюсь, что вы полностью прочитали описание задачи и можете ответить в предложении к проекту начав предложение со слов: MySQL.  Предложения без этого будут отклонены даже без рассмотрения вашего профиля. 


Итоговая цель задачи ускорить быстродействие запроса, который связывает 7 таблиц и делает where по ним максимум до 2-3х минут. Т.е. в 10 раз. 


Спасибо за уделенное время на вникание в суть задачи! 




  1. 2 дняскрыта
    Дмитро Лаврик
     2524  проверен   104   0

    MySQL - такая штука... 🙂
    В общем нужно смотреть. Обещать ничего не получится - пока маловато вводных. Нужно смотреть на систему в целом.
    Опыт администрирования 17 лет.
    __________
    С уважением, Дмитрий Лаврик
    WWW: https://dl.sm.ua
    Facebook: https://www.facebook.com/dmytro.lavryk
    E-mail: [email protected]
    Skype: dmytro.lavryk
    Telegram: dlsumy
    Тел. (viber): +380506037953

    Украина Сумы | 6 августа в 08:41 |
  1. 30 днейскрыта
    Алексей Кирдин
     264   1   0

    Добрый день.

    Имею опыт оптимизации баз mysql с сотнями миллионов записей.
    Готов посмотреть схему, запросы и планы. Возможно, что-то из моих работавших ранее методов подойдет. Для этого нужен некий срез данных, чтобы пробовать, самые частые запросы, которые нужно оптимизировать, рассмотреть разбиение данных на составляющие (возможно, составные ключи спасут ситуацию), возможно, нужно прибегнуть к агрегатным таблицам, что-то еще.

    Хотел бы посмотреть на вашу базу и данные в условиях, приближенных к боевым.

    Для своего проекта на выросших данных достигал увеличения скорости запросов до 100 раз, зная всю их статистику и методы заполнения таблиц.

    Украина Харьков | 6 августа в 10:09 |
  • привет.

    на одном из моих проектов для заказчика я делал партицирование таблиц, что дало существенный прирост производительности для селектов...

    вы в данном направлении не думали?

  • Александр Солоницкий — заказчик проекта
    Пожаловаться | 5 августа в 20:34 |

    Добрый день! 

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

  • партицирование возможно ж делать не только по датам... а в принципе по любым полям... вон у вас те же номера бонусных карт... можно по ним разбить большие таблицы...

    ну а вообще надо конечно смотреть все связи и всю логику... ну и эксперименты, эксперименты и т.п. 

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

  • 6 warnings -в логе .кто базу проектировал и писал запросы.

  • select bonus_card = "00510051100156" from имя таблицы;


    чего не такой запрос

  • Александр Солоницкий — заказчик проекта
    Пожаловаться | 5 августа в 20:24 |

    Проектировали подрядчики, и к сожалению другой ждать не придется. 

    Запрос использовался для демонстрации. 

  • быстро здесь не сделать.увеличение буфера тоже не всегда решает проблему.плохо что движки разные myisam считается не надежным со временем по каким-то причинам эти таблицы разваливаются.innodb взят целиком с oracle и есть самый стабильный в принципе на нем весь mysql держится

  • да и 6 warning для такого запроса много.это не конец света но лучше знать откуда они

  • С таким проблемами на фриланс не ходят и копеечными методами ее не решить пока вы не вложете пару сотен тысяч баксов в оборудование, оптимизировать вы можете хоть до упаду

  • Александр Солоницкий — заказчик проекта
    Пожаловаться | 6 августа в 12:48 |

    Вы вообще читали, что там за параметры, чтобы такое описывать? Там вопрос не стоит в оборудовании вообще, при том, что ПРОСТЫЕ ЗАПРОСЫ ПРИ НЕ БОЛЬШОЙ БД НЕ РАБОТАЮТ! 

  • привіт

    тут і справді потрібен полігон для тестів.... 

    варіантів є декілька, від оптимізвції структури самої БД і до підбору параметрів самої СУБД...

    конфіденційність інформації на совісті виконаців, звичайно...  або можна модифікувати дані, які не беруть прямої участі у запитах

    готовий спробувати, досвід є

  • Александр Солоницкий — заказчик проекта
    Пожаловаться | 6 августа в 12:51 |

    Добрый день! 

    По поводу конфиденциальности вы конечно же ошибаетесь, с исполнителем подписывается договор, который имеет юридическую силу. 

    Пробовать не нужно, нужно сделать

  • про "юридичну силу" ми залишимося кожен при своїй думці

    за спробу оптимізувати я не хочу оплати.. то буде мій ризик

    порада1: спробуйте самі протестувати швидкодію запиту на іншому сервері 

  • ничего понять нельзя. не владею языками


Заказчик
Проект опубликован
5 августа в 16:13
116 просмотров
Метки