Временные таблицы пишутся на диск

MB
На сайте с 24.02.2009
Offline
170
631

Всем привет.
Решился немного разобраться с запросами создающие временные таблицы на диске.

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

SELECT  wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('49') ORDER BY wp_posts.post_date DESC LIMIT 115500, 10

Создал раздел var/mysqltmp
в my.cnf в секции [mysqld] прописал tmpdir = /var/mysqltmp
в fstab добавил строку
tmpfs /var/mysqltmp tmpfs rw,gid=27,uid=27,size=1024M,nr_inodes=50k,mode=0700 0 0

mount -a
service mysql restart

и, ничего, таблицы, по прежнему создаются на диске.
Что я делаю не так?

rustelekom
На сайте с 20.04.2005
Offline
533
#1
Временные таблицы или обычные? Если первое, то может быть не хватает места для их размещения и стоит увеличить размер RAM-диска.
Виртуальный хостинг, виртуальные и выделенные серверы в Германии и РФ, регистрация доменов, выдача SSL сертификатов https://www.robovps.biz/
LEOnidUKG
На сайте с 25.11.2006
Offline
1747
#2
и, ничего, таблицы, по прежнему создаются на диске.
Как проверяли то? Вы же сами создали виртуальный диск, конечно туда и пишется. 
✅ Мой Телеграм канал по SEO, оптимизации сайтов и серверов: https://t.me/leonidukgLIVE ✅ Качественное и рабочее размещение SEO статей СНГ и Бурж: https://getmanylinks.ru/ ✅ Настройка и оптимизация серверов https://getmanyspeed.ru/
MB
На сайте с 24.02.2009
Offline
170
#3
LEOnidUKG #:
Как проверяли то? Вы же сами создали виртуальный диск, конечно туда и пишется. 
Проверял в phpmyadmin - Created tmp disk tables, размер установил 1гб этого должно хватать с запасом
W1
На сайте с 22.01.2021
Offline
306
#4
Mister_Black :
таблицы, по прежнему создаются на диске

А где они должны создаваться?

Мой форум - https://webinfo.guru –Там я всегда на связи
Евгений Русаченко
На сайте с 17.04.2013
Offline
157
#5
Mister_Black :
Что я делаю не так?

Делаете всё так, просто не понимаете как оно работает. С точки зрения MySQL таблицы как писались на диск, так и пишутся. Однако по факту, так как переместили временный каталог в RAM, то теперь временные таблиц пишутся в RAM.

Почта для домена ( https://lite.host/pochta/dlya-domena ) с безлимитными пользователями и доменами / Хостинг ( https://lite.host/hosting/dlya-blogov ) от 99 рублей / поддержка Let's Encrypt / PHP от 5.2 до 8.3 / SSH / бесплатный перенос,
Евгений Крупченко
На сайте с 27.09.2003
Offline
178
#6

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

Во-вторых, есть переменные tmp_table_size и max_heap_table_size - нужно их сделать размером больше чем те записываемые таблицы. Прописать в my.cnf и проверить:

show variables like '%_table_size';

И главное... с чего взяли что суть вашей проблемы в самом процессе создания таблиц на диск? Может из-за того что когда диск во время бэкапа тормозит, тормозит чтение из базы во время запроса. Если после переноса tmpdir на tmpfs ничего не улучшилось, то врядли здесь надо искать проблему. По хорошему конечно вся база должна сидеть в памяти целиком и с диском обмениваться лишь иногда.

Да и вообще, уверены что зависание именно от нагрузки на диск во время бэкапа, а не нагрузки на cpu например? Бэкап это ведь и архивирование обычно - страшное дело если сделано абы как, не однопоточно :)

MB
На сайте с 24.02.2009
Offline
170
#7
Евгений Крупченко #:

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

Во-вторых, есть переменные tmp_table_size и max_heap_table_size - нужно их сделать размером больше чем те записываемые таблицы. Прописать в my.cnf и проверить:

show variables like '%_table_size';

И главное... с чего взяли что суть вашей проблемы в самом процессе создания таблиц на диск? Может из-за того что когда диск во время бэкапа тормозит, тормозит чтение из базы во время запроса. Если после переноса tmpdir на tmpfs ничего не улучшилось, то врядли здесь надо искать проблему. По хорошему конечно вся база должна сидеть в памяти целиком и с диском обмениваться лишь иногда.

Да и вообще, уверены что зависание именно от нагрузки на диск во время бэкапа, а не нагрузки на cpu например? Бэкап это ведь и архивирование обычно - страшное дело если сделано абы как, не однопоточно :)

tmp_table_size и max_heap_table_size в данный момент установлено значение 128M и там и там, нагрузка во время бекапа идёт и на cpu и на диск, бекап делается средствами ispmanager, в процессах в этот момент висят именно те запросы у которых в explain есть Using temporary
Пока проблему решил изменив запрос, пришлось залезть в ядро вордпресса в query.php, заменил сортировку по дате wp_posts.post_date на сортировку по  wp_term_relationships.object_id для категорий это не критично, тем самым избавился от временных таблиц в этом запросе.

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий