LINUX.ORG.RU
ФорумAdmin

в тред призываются ниндзя mysql


0

1

зачем в мускуле используется Created_tmp_tables (хочется услышать гораздо более развёрнутый ответ, чем банальное «для выборки»)?
как можно сооптимизировать запрос, чтобы временных таблиц создавалось по-минимуму?
насколько нормальна ситуация, когда в «show processlist» 90% запросов (100 параллельных запросов, почти все - селекты) находится в статусе «Copying to tmp table»

была проблема - все селекты уходили в ожидании блокировки из-за 1-2 апдейтов в таблице (селекты нацелены на ту же таблицу), перевод таблицы на иннодб уменьшил ожидания блокировки, но зато увеличилось очень сильно «Copying to tmp table». Хочу услышать комментарии.

Спасиб)

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

GHhost ()
mysql> show status like 'Created_tmp_disk_tables';

есть чо? если есть - вертеть перменные tmp_table_size и max_heap_table_size. поставить innodb_buffer_pool_size побольше независимо от чего-либо. 100 запросов не показатель. запросы разные бывают. сделать им explain, проверить используются ли индексы. если запрос сложный с join и group by по полям разных таблиц одновременно, то без временной таблицы тут не обойдется.

fjoe ()
Ответ на: комментарий от fjoe

Created_tmp_disk_tables - есть; темповая директория под мускуль сейчас живёт на tmpfs, но насколько увеличится производительность мускуля если он вообще не будет создавать темпы на диске (хоть и используется tmpfs, но накладные расходы всё же есть, каков будет выигрыш в производительности, если при 100 потоках на сайт Created_tmp_disk_tables увеличивается на 5000 ?)


индесы используются почти везде. запросы в group by имеются.

loginrl103 ()
Ответ на: комментарий от loginrl103

а каков выигрыш создавать костыль типа tmpfs когда есть для этого соответствующие настройки нативно использующие ту же память?

100 потоков - непонятно. надо например так: при 100 запросов в секунду Created_tmp_disk_tables увелчивается на 5000 в секунду. и дальше уже крутить настройки/запросы наблюдая за соотношением.

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

fjoe ()
Ответ на: комментарий от fjoe

>а каков выигрыш создавать костыль типа tmpfs когда есть для этого соответствующие настройки нативно использующие ту же память?

я конечно дико извиняюсь, но вы в курсе при каких условиях темповый файл создаётся на диске а не в памяти? подскажу: размер, text/blob.

100 потоков - непонятно. надо например так: при 100 запросов в секунду Created_tmp_disk_tables увелчивается на 5000 в секунду. и дальше уже крутить настройки/запросы наблюдая за соотношением.


эээ...кэп, но я вроде об этом и говорил.

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


бред.

капитан, дайте дельные советы, хватит из пустого в порожнее.


fjoe, запросы сложные. с использование group/order , возможно ли их соотимизироовать? explain показывает на скан 50к строк, сервер 24 ядра 8гб памяти.

loginrl103 ()
Ответ на: комментарий от loginrl103

>запросы сложные. с использование group/order , возможно ли их соотимизироовать?

В особо запущенных случаях можно делать вспомогательные информационные таблицы.

KRoN73 ★★★★★ ()
Ответ на: комментарий от loginrl103

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

сервер 4 ядра 8 гб памяти - до 20К запросов в секунду. а всё потому, что нет сложных и кривых запросов. такие дела.

fjoe ()
Ответ на: комментарий от fjoe

>сервер 4 ядра 8 гб памяти - до 20К запросов в секунду. а всё потому, что нет сложных и кривых запросов

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

под потоками подразумевается - потоков на сайт - аля 100 запросов к сайту, а оттуда к бд.

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

loginrl103 ()
Ответ на: комментарий от loginrl103

>насколько сильно просидает производительность при большом кол-ве временных таблиц?

Очень сильно. Нормальный нетормозной запрос:
— нет файловых сортировок
— нет временных таблиц

KRoN73 ★★★★★ ()
Ответ на: комментарий от KRoN73

ок, есть запросы с использование order/group которые заставляют создавать временные таблицы. КАК можно сооптимизировать запрос так, чтобы не было создания временных табличек?

очень хочется увидеть живой пример, а лучше 2)

loginrl103 ()
Ответ на: комментарий от loginrl103

ок, есть запросы с использование order/group которые заставляют создавать временные таблицы. КАК можно сооптимизировать запрос так, чтобы не было создания временных табличек?

mysql> explain select poster_id, count(*) from topics group by poster_id having count(*) > 1000 order by poster_id desc;
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                           |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
|  1 | SIMPLE      | topics | ALL  | NULL          | NULL | NULL    | NULL | 63995 | Using temporary; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+
1 row in set (0.16 sec)

mysql> alter table topics add key poster_id(`poster_id`);
Query OK, 62836 rows affected (1 min 4.54 sec)
Records: 62836  Duplicates: 0  Warnings: 0


mysql> explain select poster_id, count(*) from topics group by poster_id having count(*) > 1000 order by poster_id desc;
+----+-------------+--------+-------+---------------+-----------+---------+------+-------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows  | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+-------+-------------+
|  1 | SIMPLE      | topics | index | NULL          | poster_id | 4       | NULL | 62848 | Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Но при этом:

mysql> explain select poster_id, count(*) from topics group by poster_id having count(*) > 1000 order by count(*) desc;
+----+-------------+--------+-------+---------------+-----------+---------+------+-------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows  | Extra                                        |
+----+-------------+--------+-------+---------------+-----------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | topics | index | NULL          | poster_id | 4       | NULL | 62848 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+-----------+---------+------+-------+----------------------------------------------+
1 row in set (0.00 sec)

Увы, count(*) приходится вычисять, тут без временных — никак. Только если хранить счётчик в отдельной вспомогательной таблице. Я обычно в таких случаях так и поступаю.

KRoN73 ★★★★★ ()
Ответ на: комментарий от KRoN73

в моём случае нету count, но запрос огромный, разработчики сами не умеют оптимизировать, а я хочу получить неплохой опыт по работе с базами

к примеру:
SELECT pc.products_id, p.products_image, pd.products_name
FROM products_to_categories pc, products p, products_description pd
WHERE categories_id IN (1,2,3,4,5,0)
and p.products_id=pc.products_id
and p.products_id = pd.products_id
and pd.language_id = '1'
and p.products_status=1
ORDER BY p.products_ordered;

вот его как можно разнести на отдельные запросы? и есть ли смысл? на пример для исходного запроса профайлинг показывает таки циферки

+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000247 |
| checking permissions | 0.000003 |
| checking permissions | 0.000003 |
| checking permissions | 0.000004 |
| Opening tables | 0.000040 |
| System lock | 0.000011 |
| init | 0.000091 |
| optimizing | 0.000029 |
| statistics | 0.001178 |
| preparing | 0.000052 |
| Creating tmp table | 0.000027 |
| executing | 0.000002 |
| Copying to tmp table | 0.142823 |
| Sorting result | 0.006833 |
| Sending data | 0.010757 |
| end | 0.000004 |
| removing tmp table | 0.000013 |
| end | 0.000003 |
| query end | 0.000005 |
| closing tables | 0.000013 |
| freeing items | 0.002486 |
| logging slow query | 0.000002 |
| logging slow query | 0.000101 |
| cleaning up | 0.000004 |
+----------------------+----------+

те сортировка и копирование во временную таблицу жрёт больше всего времени (статистика и отправка данных не в счёт, ибо профайлинг и медленный терминал)

вот как этот запрос разнести на части?

loginrl103 ()
Ответ на: комментарий от loginrl103

подразумевается - потоков на сайт - аля 100 запросов к сайту

казалось бы, при чем тут mysql... может быть при таком подходе оставить базу в покое и посмотреть в сторону кэширования сайта? nginx там или varnish? zend cache тоже ничего себе для сайта на php. если данные в базе обновляются не часто будет самое то.

fjoe ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.