LINUX.ORG.RU
решено Форум — General  

Почему так по-разному работает COUNT(*) в MySQL?

 ,


1

4

Прекрасно знаю, что COUNT(*) в InnoDB не торт... Но не до такой же степени!

Есть две таблицы: 

| ...               | tab1 | tab 2|
|-------------------|------|------|
| Число строк, млн  |  3.2 |  4.0 |
| Объём на диске,ГБ |  6.0 |  7.6 |
| Число полей       |    4 |   41 |
| Число индексов    |    1 |   27 |

Вроде как никаких чудес, таблицы почти идентичны (не считая сложности второй), работа с ними идёт равно активно (они, вообще, для двух частей одних и тех же данных и 99% обращений идёт к обеим таблицам сразу).

Так вот, COUNT(*) во второй выполняется за ожидаемые 0.47 сек на холоде. Первая же таблица выполняет COUNT(*) за 7-10 минут!

Дело не в дисках, не в фрагментации — недавно вся БД переносилась на другой раздел, ничего не меняется...

Остальная работа (извлечение записей по индексам, JOIN, сортировки) работает отлично.

Есть мысли с чем такое поведение может быть связано?

★★★★★

Дело не в дисках, не в фрагментации — недавно вся БД переносилась на другой раздел, ничего не меняется...

дык... план что показывает? fullscan по единственному индексу?

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

дык… план что показывает?

Хм. Как-то не подумал я план для COUNT(*) посмотреть. Но в обоих случаях — Using index:

MariaDB [AB_FORUMS]> explain select count(*) from posts;
±-----±------------±------±------±--------------±-----------±--------±-----±--------±------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows    | Extra       |
±-----±------------±------±------±--------------±-----------±--------±-----±--------±------------+
|    1 | SIMPLE      | posts | index | NULL          | is_deleted | 1       | NULL | 3316686 | Using index |
±-----±------------±------±------±--------------±-----------±--------±-----±--------±------------+
1 row in set (0.03 sec)

MariaDB [AB_FORUMS]> explain select count(*) from posts_cache;
±-----±------------±------------±------±--------------±--------±--------±-----±--------±------------+
| id   | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
±-----±------------±------------±------±--------------±--------±--------±-----±--------±------------+
|    1 | SIMPLE      | posts_cache | index | NULL          | PRIMARY | 4       | NULL | 2377671 | Using index |
±-----±------------±------------±------±--------------±--------±--------±-----±--------±------------+
1 row in set (0.00 sec)

Вот что отличается — это длина ключа. 1 в «быстрой» таблице, 4 — в «медленной». Может, в этом дело? Типа, влезает/не влезает в какой-то из кешей… query_cache_limit у меня 4Мб… Но как-то не представляю, чтобы он был связан с запросом COUNT(*)…

KRoN73 ★★★★★
() автор топика

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

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

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

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

Иногда, эту хрень (mysql/mariadb) надо просто перезапустить.

Проблема очень давняя, перезапуски были не раз :) Она не критична, так как count(*) под нагрузкой нигде не используется, только в ручной работе, но стало интересно, почему так.

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

быстрым, он будет если только сразу возьмёт количество из какойнить внутренней табличной переменной (которая в теории может быть недостоверной)

count(*) [в MySQL] в отличие от иных описаний таблиц считает точно. Потому и медленно. Есть ряд других способов оценить число строк приблизительно, они — да, быстрые.

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

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

Все поля, что упомянуты в explain (и PRIMARY id, и бинарное is_deleted) идентичны — NULL там нет.

Вот что может быть, так это в индексе is_deleted, в принципе, может быть информация сколько записей «0», а сколько «1». И тогда COUNT(*) может быстро сложить эти значения. А в PRIMARY подобной информации может не быть.

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

Покажи show create table на обеих

«Быстрая»:

show create table posts\G
*************************** 1. row ***************************
Table: posts
Create Table: CREATE TABLE posts (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
poster varchar(32) NOT NULL DEFAULT ‘’,
poster_id int(10) unsigned DEFAULT ‘1’,
avatar_id int(11) DEFAULT NULL,
poster_ip varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
poster_ua varchar(255) NOT NULL,
poster_email varchar(50) DEFAULT NULL,
source longtext NOT NULL,
source_html longtext NOT NULL,
hide_smilies tinyint(1) NOT NULL DEFAULT ‘0’,
posted int(10) unsigned NOT NULL DEFAULT ‘0’,
edited int(10) unsigned DEFAULT NULL,
edited_by varchar(40) DEFAULT NULL,
topic_id int(10) unsigned NOT NULL DEFAULT ‘0’,
page smallint(5) unsigned DEFAULT NULL,
add_type varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT ‘’,
original_id int(11) DEFAULT NULL,
answer_to_post_id int(11) NOT NULL DEFAULT ‘0’,
answer_to_user_id int(11) NOT NULL,
have_answers int(11) DEFAULT NULL,
answers_count int(11) NOT NULL COMMENT ‘Сколько всего ответов на данное сообщение в дереве ответов. Для первых сообщений топиков считаются все ответы темы.’,
have_attach int(11) DEFAULT NULL COMMENT ‘NULL/0, если аттача нет, -1, если аттачи в таблице, №, если аттач один.’,
have_cross tinyint(1) unsigned DEFAULT NULL COMMENT ‘0/1’,
order int(11) NOT NULL,
is_deleted tinyint(1) unsigned NOT NULL,
is_hidden enum(‘0’,‘1’) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
is_moderatorial tinyint(1) unsigned NOT NULL,
is_incorrect int(11) DEFAULT NULL,
is_moderated int(11) DEFAULT NULL,
is_spam int(11) DEFAULT NULL,
last_moderator_id int(11) NOT NULL,
title varchar(255) DEFAULT NULL,
score int(11) DEFAULT NULL,
markup_class_name varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
field4 int(11) DEFAULT NULL,
field_int int(11) DEFAULT NULL,
field_str varchar(255) DEFAULT NULL,
year smallint(5) unsigned NOT NULL,
original_topic_id int(10) unsigned DEFAULT NULL,
infonesy_uuid varchar(255) DEFAULT NULL,
infonesy_data text,
PRIMARY KEY (id),
KEY posts_topic_id_idx (topic_id),
KEY posts_multi_idx (poster_id,topic_id),
KEY poster (poster),
KEY poster_id (poster_id),
KEY posted (posted),
KEY original_id (original_id),
KEY poster_ip (poster_ip),
KEY answer_to (answer_to_post_id),
KEY index_for_joins (topic_id,id),
KEY for_posted_sort_wo_filesort (topic_id,posted),
KEY order (order),
KEY page (page),
KEY topic_id (topic_id,page),
KEY topic_id_2 (topic_id,page,id),
KEY poster_id_2 (poster_id,posted),
KEY have_answers (have_answers),
KEY is_deleted (is_deleted),
KEY anwer_to_user_id (answer_to_user_id),
KEY answer_uid__posted (answer_to_user_id,posted),
KEY joined_to_topic_id (last_moderator_id),
KEY is_spam (is_spam),
KEY answers_count (answers_count),
KEY is_moderatorial (is_moderatorial),
KEY edited_idx (edited),
KEY by_tid_ordered (topic_id,is_deleted,order,posted),
KEY poster_id__is_deleted__posted (poster_id,is_deleted,posted),
KEY original_topic_id (original_topic_id),
KEY year (year),
CONSTRAINT posts_ibfk_1 FOREIGN KEY (poster_id) REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4207770 DEFAULT CHARSET=utf8mb4

«Медленная»:

show create table posts_cache\G
*************************** 1. row ***************************
Table: posts_cache
Create Table: CREATE TABLE posts_cache (
id int(10) unsigned NOT NULL COMMENT ‘post_id’,
body text COMMENT ‘post body, html’,
body_ts timestamp NULL DEFAULT NULL COMMENT ‘body timestamp’,
is_body_temporary tinyint(1) unsigned DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT posts_cache_ibfk_1 FOREIGN KEY (id) REFERENCES posts (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

и сами запросы.

select count([em]) from posts;
select count([/em]) from posts_cache;

:)

Загадочный ЛОР (и выше он много сожрал):

Превед " --±- " медвед
KRoN73 ★★★★★
() автор топика
Ответ на: комментарий от BaBL

Ой, блин, это еще и MariaDB...

Я же пишу — проблема очень давняя. На MySQL несколько лет назад было также. Там не было никаких революционных изменений.

Если у вас innodb, почему Percona не взять?

Так те же уши будут.

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

Так те же уши будут.

Не факт, они на innodb собаку давно жрут.

Я вижу в «медленной» констрейнт на «быструю», а в быстрой только на users. Учитывая что мы на колде тестируем - не может ли быть что он перед count перестраивает весь этот индекс (опять же, там каскад), грузит его в память и т.д., а уже потом быстро вам начинает все отдавать на горячем? Юзеров у вас вряд ли 4 ляма.

CONSTRAINT posts_cache_ibfk_1 FOREIGN KEY (id) REFERENCES posts (id) ON DELETE CASCADE ON UPDATE CASCADE



count([em])
- это не ошибка? count(*) считает все строки, count(id) - те. в который id is not null, count(distinct id) - уникальные id. Они выполняются по разному и с разной скоростью.

Я почему спрашиваю, если вы в count пихаете какое-то поле, то в верхней таблице у вас куча индексов по ним, а в нижней - нету.

BaBL ★★★★★
()
Последнее исправление: BaBL (всего исправлений: 1)
Ответ на: комментарий от BaBL

это не ошибка? count(X) считает все строки, count(id) - те. в который id is not null

Одинаково по скорости с count(id) (проверял), всё равно id NOT NULL.

(поменял звёздочку на X, чтобы ЛОР-парсер не жрал)

не может ли быть что он перед count перестраивает весь этот индекс

Сложно представить, зачем оно так реализовано в таком случае. Хотя, да, это единственная заметная разница.

…

Хм. Есть у меня ещё похожая таблица: 

select count(X) from posts_calculated_fields;
.----------.
| count(X) |
.----------.
|  3808257 |
.----------.
1 row in set (2.47 sec)


show create table posts_calculated_fields\G
Table: posts_calculated_fields
Create Table: CREATE TABLE posts_calculated_fields (
post_id int(10) unsigned NOT NULL,
answers_total int(10) unsigned DEFAULT NULL,
answers_other_topics int(10) unsigned DEFAULT NULL,
best10_ts timestamp NULL DEFAULT NULL,
root_post_id int(10) unsigned DEFAULT NULL,
PRIMARY KEY (post_id),
KEY answers_total (answers_total),
KEY answers_other_topics (answers_other_topics),
KEY best10_ts (best10_ts),
KEY root_post_id (root_post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Тут нет foreign index’а. И тоже работает шустро. Так что, похоже, дело в нём, действительно.

KRoN73 ★★★★★
() автор топика
Ответ на: комментарий от KRoN73 
Превед " --+-- " медвед

Мой :facepalm: Это был Lordown — пользовательский скрипт для преобразования Markdown → LORCODE

Он, оказывается, работает не с модификацией при просмотре, а с модификацией перед отсылкой :D

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

В MyISAM есть внутренний счетчик, в innodb нету, поэтому здесь count медленней

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

что-то сильно сомневаюсь что в foreign index дело.
И вообще планы выполнения запроса странные. В posts же есть первичный id, но используется is_deleted.
Попробуй вместо count(*) использовать count(id). Також можно указать принудительно использовать index USE INDEX(id). Также все измерения проводить с SQL_NO_CACHE, как в мариадб не знаю. Также попробуй рестартнуть сервер базы и выполнить сначала запросы на медленной табле, а потом на быстрой.

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

И вообще планы выполнения запроса странные. В posts же есть первичный id, но используется is_deleted.

Видимо, дело в том, что id имеет столько же значений элементов, сколько записей и длина поля индекса 4 байта, а в is_deleted — только два значения и длина поля 1 байт. Вероятно (я без понятия, как в MySQL устроены индексы) считать по второму случаю быстрее.

Попробуй вместо count(*) использовать count(id)

Никакой разницы. Кстати, план показывает тоже использование ключа is_deleted.

Також можно указать принудительно использовать index USE INDEX(id)

Вот, действительно, дело не в foreign index, а, как и предполагал сперва (но потом забылось), видимо, в размере индекса. USE INDEX(PRIMARY) в posts отрабатывает очень долго (ещё не завершён, но уже понятно, что длится минуты будет) и план показывает PRIMARY и 4 байта длину.

Также все измерения проводить с SQL_NO_CACHE

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

Также попробуй рестартнуть сервер базы

Там процесс перезапуска сервера длится десятки минут :)

и выполнить сначала запросы на медленной табле, а потом на быстрой.

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

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

хм, раз такое дело, что влияет количество байт в индексе, может не хватает места для индексов
Какие параметры в конфигах?

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_log_buffer_size
innodb_file_per_table

kiotoze ★★★★
()
Ответ на: комментарий от kiotoze 
| innodb_buffer_pool_size                   | 8589934592             |
| innodb_additional_mem_pool_size           | 33554432               |
| innodb_log_buffer_size                    | 8388608                |
| innodb_file_per_table                     | ON                     |
KRoN73 ★★★★★
() автор топика

А если и там, и там добавить group by id ?

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

Не знаю какая именно нагрузка и насколько огромная база, но если взять только для тех 3-х таблиц что я видел, то настройки хорошие.

Здесь дело даже не в том, что выборка COUNT медленная на innodb (и нужно извращаться чтобы мгновенно http://stackoverflow.com/questions/19267507/how-to-optimize-count-performance...), а в такой разнице в результатах между разными таблицами и что табла только с одним первичным ключом медленней. Вот это очень странно

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

Вот это очень странно

Вот потому я тему эту и поднял :) Если бы всюду по 10 минут длилось, я бы это списал на некритичные особенности InnoDB и забил бы...

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

" InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool. "

Получается, хочешь быстрый count() - сделай однобайтовый ключ.

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

Получается, хочешь быстрый count() - сделай однобайтовый ключ

Да, хитро оно. Вот другая таблица из Почему так по-разному работает COUNT(*) в MySQL? (комментарий)

select count(*) from posts_calculated_fields;
+----------+
| count(*) |
+----------+
|  3808511 |
+----------+
1 row in set (25.38 sec)

MariaDB [AB_FORUMS]> explain select count(*) from posts_calculated_fields;
+------+-------------+-------------------------+-------+---------------+---------------+---------+------+---------+-------------+
| id   | select_type | table                   | type  | possible_keys | key           | key_len | ref  | rows    | Extra       |
+------+-------------+-------------------------+-------+---------------+---------------+---------+------+---------+-------------+
|    1 | SIMPLE      | posts_calculated_fields | index | NULL          | answers_total | 5       | NULL | 3664599 | Using index |
+------+-------------+-------------------------+-------+---------------+---------------+---------+------+---------+-------------+


Однобайтового ключа нет, используется аж пятибайтный. Время работы много хуже «быстрой» таблицы с однобайтным ключом, но много лучше «медленной» с primary. Ключ answers_total содержит число ответов на сообщения и поэтому имеет ограниченный набор значений. Видимо, поэтому и выходит быстро. Но медленнее, чем однобайтовый ключ всего с двумя значениями :)

KRoN73 ★★★★★
() автор топика

В общем, добавил для теста однобайтовый индекс (благо поле было давно готово — is_body_temporary) — и всё стало считаться мгновенно.

Что интересно, время на добавление индекса ушло совсем немного, не засекал, но что-то порядка минуты. Т.е. добавление узкого индекса на порядок быстрее происходит, чем подсчёт COUNT(*) по широкому индексу :D

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

В общем, добавил для теста однобайтовый индекс (благо поле было давно готово — is_body_temporary) — и всё стало считаться мгновенно.

Однобайтовый ? В смысле, у тебя там не больше 256 записей ? :) Или индекс НЕ уникальный ?

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

Я же написал — индекс, а не уникальный индекс :) При чём ему достаточно быть вообще пустым (скажем, одни «0» в поле).

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