Пользователь
0,1
рейтинг
16 сентября 2008 в 10:02

Разработка → MySQL Performance real life Tips and Tricks. Part 3-rd.

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

Также, после опубликования первых двух статей я получил пару отзывов и вопросов, связанных с проектированием БД / расстановкой индексов / составлением запросов. На многие вопросы старался отвечать. С некоторыми из них поделюсь и в этой статье.


Профайлинг запросов


Итак, начну со стандартных средств профайлинга запросов средствами MySQL
  1. EXPLAIN
  2. SHOW STATUS
  3. PROFILING

EXPLAIN

С помощью команды EXPLAIN MySQL показывает ориентировочный execution plan запроса (Описание всех возвращаемых полей командой EXPLAIN можно посмотреть по адресу — dev.mysql.com/doc/refman/5.0/en/using-explain.html ). На самом деле в этом определении ключевое слово — ориентировочный, т.к. EXPLAIN может ошибаться :-) Причиной данных ошибок есть то, что генерация execution plan является частью выполнения запроса (query execution), иногда построение execution plan происходит динамически в зависимости от данных. С помощью команды EXPLAIN MySQL пытается симулировать выполнение запроса, но не «трогает» при этом данные, соответственно не имеет доступа к этому динамическому компоненту. Такую оценку MySQL делает в основном полагаясь на статистику индексов. Которую в свою очередь следует держать всегда в актуальном состоянии, и в зависимости от интенсивности запросов к БД на добавление/изменение/удаление данных проводить с определенной переодичность по cron-у (скажем, каждую ночь) выполнять запросы — ANALYZE TABLE (перестраивает дерево индексов, не позволяя выраждаться ему в список, такое может происходить если мы, скажем, вставляем упорядоченные данные, тогда эффективность операции поиска записи составит не O(logn), а O(n). Замечу также, что не всегда следует выполнять команду ANALYZE TABLE на production сервере, т.к. на время её выполнения MySQL лочит таблицы MyISAM (read lock), InnoDB (write lock) ), OPTIMIZE TABLE (дефрагментация файла данных. Полезно при большом удалении записей из таблицы).
Также EXPLAIN дает далеко не всю информацию для оценки скорости выполнения запроса. Например, если в ходе запроса будут использоватся temporary table, то MySQL не покажет будут ли это in-memory или in-disk temporary tables. Также MySQL в эксплейне не покажет цену операций обращения к записи или выполнения функций используемых в запросе.

Также в этом абзаце хотел отметить главное — что производительность запроса зависит от количества записей, которые были проверены (исследованы) MySQL, а не от количества возвращенных запросом записей.

SHOW STATUS

MySQL издавна предоставляет мониторинг системных переменных по средствам команды — SHOW STATUS. Ранее (до версии 4.1.) данная команда показывала статус глобальных переменных, но с версии 4.1. появилось возможность показывать состояние сессионных переменных — SHOW SESSION STATUS. (Мануал по данной команде — dev.mysql.com/doc/refman/5.0/en/show-status.html)
Скажу лишь, что данная команда в отличие от EXPLAIN, который базируется на эвристических оценках, показывает непосредственно ЧТО произошло после выполнения запроса, т.е. количество записей, к которым MySQL физически обратился (притом посредством данной команды можно узнать сколько из них было получено из памяти, а сколько посредством обращения к диску) хочу также отметить, что это число представляет из себя не оценочный результат, а реальное число (фактически MySQL все время инкрементит переменную, когда обращается к каждой следующей строке). В общем скажу, что SHOW STATUS возвращает огромное кол-во статистики, всю её естественно описывать не буду. Покажу основные моменты, которые следует замечать при её(статистике) анализе.

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

FLUSH STATUS — данная каманда сбросит(обнулит) все сесионные переменные, глобальные переменные не будут затронуты.

После этого выполняем интересующий нас запрос

SELECT bla-bla-bla FROM test_table as tt WHERE tt.bla = 'test' LIMIT 10

mysql> SHOW STATUS LIKE 'Key_read%';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| Key_read_requests | 96882 |
| Key_reads | 36200 |
+--------------------+----------+

Что может дать подобная информация? И какие решения принимать, основываясь на этой информации?

Сейчас попробуем проанализировать полученные значения и принять решение по оптимизации, основываясь на данной статистике.
  • Key_read_requests — Количество запросов на чтение индексных блоков из кеша.
  • Key_reads — Количество физического чтения индексных блоков с диска. Если число Key_reads большое, тогда, вероятно, ваше key_buffer_size достаточно мало и требует увеличения. Отношение непопаданий в кэш можно посчитать как Key_reads/Key_read_requests.

В даннои млучае мы видим, что более трети индексных блоков считываются с диска, а не достаются из кеша => соответственно адекватной мерой по оптимизации будет увеличение данного параметра (key_buffer_size).
Но следует также принимать во внимание следующее: key_buffer_size — один из самых главных конфигурационных параметров, особенно если вы используете MyISAM таблицы. Тогда величина данного параметра должна составлять приблизительно 25-30% от объема свободной оперативноя памяти. Но следует также обращать внимание, чтобы этот параметр не был неоправданно большим (например, если сумарный объем файлов индексов .MYI составляет 1GB, а key_buffer_size=4GB, в таком случае вы только зря тратите память). Также следует обратить внимание на то, что максимальное значение данного параметра 4GB (The maximum allowable setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform. The value of this variable indicates the amount of memory requested. Internally, the server allocates as much memory as possible up to this amount, but the actual allocation might be less. dev.mysql.com/doc/refman/5.0/en/server-system-variables.html). Но даже если вы не пользуетесь MyISAM таблицами, значение key_buffer_size, стоит все равно поставить в 32-64MB, т.к. он будет использоваться индексами для временных таблиц (MyISAM)

Итак, какую еще полезную статистику можно получить по более сложным запросам, например, по такому:

FLUSH STATUS;

SELECT
  SQL_NO_CACHE fe.username, COUNT(*) as `count`
FROM
  `tx_images` as ti
INNER JOIN
  `fe_users` as fe
  ON ti.cruser_id = fe.uid
GROUP BY
  fe.uid
ORDER BY
  `count` desc;

* This source code was highlighted with Source Code Highlighter.


| Variable_name | Value |
+------------------------+-------+
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
+------------------------+-------+
  • Select_scan — The number of joins that did a full scan of the first table.

Последнее поле показывает, что MySQL делал FULL TABLE SCAN, на самом деле эту инфоррмацию подтверждает EXPLAIN, поле Extra, которого содержит Using temporary; Using filesort.
Если у нас в запросе фигурирует несколько таблиц, которые склеиваются во время запроса, то и другие значения в этом списке могут быть не нулевыми.

mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 598 |
| Sort_scan | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)

Опять же привиду описание данных полей из мануала.
  • Sort_rows — The number of sorted rows.
  • Sort_scan — The number of sorts that were done by scanning the table.


Также полезно знать создавались ли временные(temporary) таблицы, которые использовались во время выполнения запроса на диске или в памяти.

mysql> SHOW SESSION STATUS LIKE 'Created%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 3 |
+-------------------------+-------+
3 rows in set (0.00 sec)

Здесь MySQL показывает, что использовались инмемори tables, только почему-то показывает, что было их создано 3 :-) В действительности для данного запроса создается всего одна.

Здесь сделаю небольшое лирическое отступление.
После публикации первых двух статей я получил пару писем с вопросами и предложениями. Так вот, как я понял, достаточно распространенной ошибкой являются огрехи в плане архитектурных решений, которые тянут за собой очень низкую производительность в запросах на выборку.
Например, если у вас имеется таблица, содержащая какие-то статьи/новости и т.д. и имеет приблизительно следующую структуру.

CREATE TABLE `tx_articles` (
 `uid` int(11) NOT NULL auto_increment,
 `pid` int(11) NOT NULL default '0',
 `tstamp` int(11) NOT NULL default '0',
 `crdate` int(11) NOT NULL default '0',
 `cruser_id` int(11) NOT NULL default '0',
 `deleted` tinyint(4) NOT NULL default '0',
 `hidden` tinyint(4) NOT NULL default '0',
 `headline` varchar(70) NOT NULL default '',
 `bodytext` text NOT NULL,
 `type` int(11) NOT NULL default '0',
 `citycat_id` int(11) NOT NULL default '0',
 `editable` int(11) NOT NULL default '0',
 `confirm` int(11) NOT NULL default '0',
 `confirm_code` varchar(64) NOT NULL default '',
 `editorspick` int(11) NOT NULL default '0',
 `newspaper_id` int(11) NOT NULL default '0',
 PRIMARY KEY (`uid`),
 KEY `parent` (`pid`),
 KEY `citycat_id` (`citycat_id`, `tstamp`)
 KEY `newspaper_id` (`newspaper_id`)
 KEY `tstamp` (`tstamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

* This source code was highlighted with Source Code Highlighter.


Также предполагается, что львиная часть запросов на выборку будет обращена именно к этой таблице. Причем, как правило, такая таблица может содержать достаточно большое количество столбцов, причем некоторые из них, как в приведенном выше случае, имеют тип данных TEXT или BLOB (`bodytext` text NOT NULL), а также поля переменной длинны (VARCHAR). Все это усложняет работу MySQL особенно в операциях сортировки/группировки записей. Т.к. представьте себе, что каждая запись имеет в среднем длинну 1Kb и когда MySQL будет сортировать/группировать такие записи, сервер просто будет «задыхаться» ворочая каждый раз такие большие объемы данных.
И это еще не все недостатки данного подхода. На это следует обратить особое внимание — MySQL для temporary table использует HEAP engine, который работает только с полями фиксированной длинны, т.е. varchar он делает char-ом(максимальной длинны, указанной при объявлении varchar). Делается, это как вы понимаете, для быстрого поиска записей. А вот с полями TEXT и BLOB он работать не умеет, поэтому конвертит тип таблицы к MyISAM и данные оказываются на диске, а это очень медленно.
Поэтому такой архтектуры следует избегать на больших базах с большим количеством обращений и требующим быстроко времени отклика.

Что предлагается сделать? В некоторых случаях может пригодится использование covering indexes и self-join, данный пример был описан в моей первой статье (http://habrahabr.ru/blogs/mysql/38907/), поэтому я повторяться не буду… повторяться не буду… :-)

Более же правильным, с точки зрения архитектурного подхода, в данном случае является выделение полей переменной длинны, которые как правило, не учавствуют в условиях WHERE (TEXT, BLOB, в зависимости от условия задачи это может быть и VARCHAR) в отдельную таблицу. Соответственно основные условия по выборке, как правило, по индексным полям с фиксированной длинной, мы выполняем на одной таблице, также выполняем по ней LIMIT и только после этого склеиваем, довавляем дополнительные поля, в моем примере это будет «bodytext» из другой таблицы. При этом в данном случае такая склейка будет выполняться по PRIMARY KEY.

PROFILING

С версии 5.0.37 MySQL включает возможность профайлинга запросов. Данная утилита записывает статистику выполнения запросов в служебную БД information_schema

Для того, чтобы MySQL начал записывать статистику по запросу следует установить значение переменное profiling в 1

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

* This source code was highlighted with Source Code Highlighter.


После этого выполняем интересующие нас SQL запрос(ы).

Далее выполняем следующий запрос

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration  | Query                     |
+----------+------------+-----------------------------------------------+
|    0 | 0.00005100 | set profiling=1                |
|    1 | 0.80246730 | SELECT SQL_NO_CACHE fe.username, COUNT(*) as `count` FROM `tx_images` as ti INNER JOIN `fe_users` as fe ON ti.cruser_id = fe.uid GROUP BY fe.uid ORDER BY `count` desc |
+----------+------------+-----------------------------------------------+
2 rows in set (0.00 sec)
* This source code was highlighted with Source Code Highlighter.


Как я говорил ранее, данная статистика записывается в БД — information_schema, таблицу — profiling, поэтому мы можем получить статистику по выполнению запроса сделав запрос к этой таблице.

mysql> select sum(duration) from information_schema.profiling where query_id=1;
+---------------+
| sum(duration) |
+---------------+
|  0.80246730 |
+---------------+
1 row in set (0.00 sec)
* This source code was highlighted with Source Code Highlighter.


Далее можно посмотреть статистику всех стадий выполнения запроса, делается это с помощью команды

mysql> show profile for query 1;
+--------------------+------------+
| Status | Duration |
+--------------------+------------+
| (initialization) | 0.00007300 |
| Opening tables | 0.00005100 |
| System lock | 0.00000600 |
| Table lock | 0.00002000 |
| init | 0.00002200 |
| optimizing | 0.00003400 |
| statistics | 0.00010600 |
| preparing | 0.00014800 |
| executing | 0.50000700 |
| Sending data | 0.30226800 |
| end | 0.00000700 |
| query end | 0.00000500 |
| freeing items | 0.00001300 |
| closing tables | 0.00000700 |
| logging slow query | 0.00000400 |
+--------------------+------------+
15 rows in set (0.00 sec)

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

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

Slow query log


Очень полезным средством для поиска узким мест и медленных запросов к БД является логирование медленных запросов, а именно директива --log-slow-queries при старте MySQL. До версии MySQL 5.1.21 минимальное значение параметра long_query_time 1, значение по умолчанию 10. Данное значение должно быть типа интеджер. Т.е. если время выполнения запроса превышает величину данного параметра в секундах, то он заносится в лог. Также следует отметить, что если запрос попадает в slow query log, то это не говорит, что это плохой запрос, может он долго ждал пока освоболится lock. Также полезным параметром есть логирование запросов, которые не используют индексы --log-queries-not-using-indexes.
Это очень полезная информация, но если у нас большой проект и нагруженная база, то нам необходимы средства мониторинга и визуализации подобной статистики. Я лично использую MONyog

ответы редакции :-)


Терерь отвечу на пару вопросов, на которые не успел ответить:
Во время обсуждения второй статьи я в комментариях (http://habrahabr.ru/blogs/mysql/39260/#comment_941339) спорил с хабрачеловеком juks об уместности применения force index в запросах.

Рассмотрим, для примера, такую структуру таблицы

CREATE TABLE `some_table`
(
 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
 `field1` INT(10) NOT NULL,
 `field2` VARCHAR(50) NOT NULL default '',
 `field3` TIMESTAMP,
 `field4` VARCHAR(255) default '',
 PRIMARY KEY (`id`),
 KEY fields1_2 (field1, field2(8)),
 KEY fields2_3 (field1, field3) 
) ENGINE=InnoDB DEFAULT CHARSET=«UTF8»;

* This source code was highlighted with Source Code Highlighter.


После этого заполним её данным, для этого нам понадобится, к примеру, такой скрипт на PHP

<?php
set_time_limit (0);

mysql_connect(«127.0.0.1», «root»,«root_password»);
mysql_select_db(«database_name»);

function random_string() {
      $string = «ABCDEFGHIKJLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789»;
      $len = strlen($string);
      $return = '';
      $length = rand(10, 50);
      for($i=0; $i<$length; $i++) {
         $return .= $string{rand(0, $len)};
      }
      return $return;
}

$names = array(«maghamed», «maghamed1», «maghamed2», «maghamed3», «maghamed4»);

mysql_query(«ALTER TABLE `some_table` DISABLE KEYS»); //stop updating non-unique indexes

for($i=0; $i<10000000; $i++) {
 $a = $i%1000; 
 $b = rand(0, 4);
 $b = $names[$b];
 $c = random_string();
 $sql = «INSERT INTO `some_table` SET field1 = ${a}, field2 = '${b}', field4 = '${c}'»;
 mysql_query($sql) or die(«Invalid query: „. mysql_error());
}

mysql_query(“ALTER TABLE `some_table` ENABLE KEYS»);
?>
* This source code was highlighted with Source Code Highlighter.


Код в данном скрипте интуитивно понятный. Обращу внимание только на «ALTER TABLE `some_table` DISABLE KEYS» перед вставкой большого кол-ва записей, и «ALTER TABLE `some_table` ENABLE KEYS»
после вставки. Эти директивы существенно ускорят работу скрипта и вообще будут полезны в аналогичных ситуациях. «ALTER TABLE… DISABLE KEYS» — перестает обновлять не уникальные индексы (в нашем случае «fields1_2», «fields2_3») во время вставки новых записей. MySQL использует специальный алгоритм, который намного быстрей, чем обновление индексов после вставки каждой записи, поэтому отключение индексов перед большой вставкой данных должно дать значительное ускорение.

И такой запрос к данной таблице:

mysql> EXPLAIN
  -> SELECT
  ->   *
  -> FROM
  ->   `some_table`
  -> WHERE
  ->    field1 = 50 AND
  ->    field2 = 'maghamed'
  -> ORDER BY
  ->    field3 DESC
  -> LIMIT 100
  -> \G
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: some_table
     type: ref
possible_keys: fields1_2,fields2_3
     key: fields1_2
   key_len: 30
     ref: const,const
     rows: 3042
    Extra: Using where; Using filesort
1 row in set (0.05 sec)

* This source code was highlighted with Source Code Highlighter.


Как видим MySQL в данном случае предпочитает использовать индекс «fields1_2», который способствует быстрому поиску (lookup), но сортировка выполняется без помощи индекса, поле Extra — Using where; Using filesort говорит нам об этом.

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

mysql> explain
  -> SELECT
  ->   *
  -> FROM
  ->   `some_table`
  ->   FORCE INDEX (`fields2_3`)
  -> WHERE
  ->    field1 = 50 AND
  ->    field2 = 'maghamed'
  -> ORDER BY
  ->    field3 DESC
  -> LIMIT 100;
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref  | rows | Extra    |
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE   | some_table | ref | fields2_3   | fields2_3 | 4    | const | 1928 | Using where |
+----+-------------+------------+------+---------------+-----------+---------+-------+------+-------------+
1 row in set (0.03 sec)

* This source code was highlighted with Source Code Highlighter.


*Позволю заметить, что MySQL может сделать выбор в пользу индекса `fields2_3`и без FORCE INDEX это зависит от версии MySQL и актуальности статистики индексов. Поэтому так важно поддерживать актуальность индексов (ANALYZE TABLE `some_table`). Не стоит заставлять MySQL выбирать между быстрым поиском, или сортировкой по индексу в данном случае, т.к. оба варианта будут выполнятся медленней, чем вариант в котором мы имеем составной индекс по 3-м полям.

Для начала удалим старый индекс:

DROP INDEX `fields1_2` ON `some_table`
* This source code was highlighted with Source Code Highlighter.


И добавим новый

CREATE INDEX `fields1_2_3` ON `some_table` (field1, field2(8), `field3`);
* This source code was highlighted with Source Code Highlighter.





Также после первой статьи где предлагалось для быстрой группировки (GROUP BY) результатов использовать конструкцию — GROUP BY BINARY crc32(tag_text), я получил достаточно много вопрос, и сомнений в этом варианте, т.к. на выполнение ф-ии crc32 тратится дополнительное время, и многие считают, то это будет малоэффективно, некоторые предлагали вместо CRC32 использовать MD5, чтобы уменьшить кол-во возможных совпадений результатов ф-ии у разных значений.

Отвечу на все подобные сомнения таким небольшим бэнчмарком.

SET @input := 'hello world';
SELECT BENCHMARK(1000000, CRC32(@input));
* This source code was highlighted with Source Code Highlighter.

1 row in set (0.22 sec)

SET @input := 'hello world';
SELECT BENCHMARK(1000000, MD5(@input));
* This source code was highlighted with Source Code Highlighter.

1 row in set (6.02 sec)

Как видите ф-ия CRC32 выполняется очень быстро
*BENCHMARK — выполняет указанную ф-ию CRC32 или MD5 в данном случае указанное количество раз 1 000 000

На сим буду оканчивать третью статью, и так она оказалась длинней, чем планировал. На все вопросы буду стараться отвечать.
Если сообщество еще не устало от моих статей, то в следующей статье по данной тематике хотел рассказать о проблемы scale-инга больших таблиц и прочих вкустностях MySQL :-)
Игорь Миняйло @maghamed
карма
237,4
рейтинг 0,1
Пользователь

Похожие публикации

Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (40)

  • +3
    Когда я вижу такие запросы мне становится нехорошо :)

    for($i=0; $i<10000000; $i++) {

    INSERT INTO `some_table` SET field1 = ${a}, field2 = '${b}', field4 = '${c}'

    }

    Даже для показательных скриптов так писать нельзя, так как подает плохой пример, позволю себе вас поправить, надо делать так (я правда не знаю php, поэтому пример на «псевдоязыке», но суть от этого не меняется):

    INSERT INTO `some_table` (field1, field2, field3) VALUES
    for($i=0; $i<10000000; $i++) {

    ('$a', '$b', '$c')

    }[, ]

    1. Это один запрос, вместо 10 миллионов :)
    2. Здесь не нужно делать DISABLE/ENABLE KEYS, так как в процессе запроса MySQL не будет обновлять индексы
    • 0
      А здесь Вы не правы :-)

      То, что Вы пишете, это конечно правильно, что нужно стараться выполнить один запрос в место 10 000 000, и это будет на несколько порядков быстрей, но есть одно небольшое НО.

      Не помню точно, может кто-то меня поправит. Сколько там буфер для запроса у MySQL. Почему-то у меня в голове крутится 4 MB. Число не точное, могу ошибаться. Так вот, если вы попытаетесь сконкатенировать большой запрос, который по объему будет превышать это число, MySQL выдаст вам ошибку и не выполнит ничего.
      • 0
        Поправляю:

        max_allowed_packet = 16M

        в 16 мегабайт помещаются очень большие запросы.
        • 0
          по умолчанию, конечно, меньше, но с таким молчанием большой проект из дампа, где однострочные инсерты не развернуть
          • –1
            Ну коль пошла такая «пьянка», тогда и упомяну про INSERT DELAYED, с помощью конструкции INSERT DELAYED, данные записываются в память, и попадают на диск, только тогда, когда таблица, в которую они должны записываться свободна от запросов на чтение. Соответственно, когда все инсерты будут писаться за раз, также будет оптимизирована работа с диском.

            Возможны только проблемы при краше. Т.к. запросы заносятся в память, а также, то востановить их уже не удастся :-(
            • +1
              Да всё это ерунда, понятное дело, что для тестовых целей базу можно заполнять как угодно :-) Лично я в таком случае морочиться бы не стал, а заполнил бы «как-нибудь».
              • 0
                Я так и сделал :-)
        • –2
          Ок, если 16, тогда признаю, что не прав. В 16 МБ даже вставка на 10 000 000 миллионов поместиться :-) Когда писал, почему-то думал о 4х.
          • 0
            Нет, 16 это я поставил, когда первый раз на это наткнулся. По умолчанию во всех файлах приводится 1М
          • +1
            16 Мб — это 16 миллионов символов. Что-бы влезло 10 миллионов строк, нужно что-бы каждая строка была по 1,6 символов. А уж что-бы влезло «10 000 000 миллионов»…
            • –3
              Интересно, какая была оченка по математике в школе у тех, кто минусует :)
              • +1
                -5 :-)
                Я Вам исправлю этот минус, т.к. ваш комментарий уместен :-)
      • 0
        По умолчанию он вообще один мегабайт :)
        Но дело не в этом. Подобные бенчмарки не рассчитываются на то, что их будут делать на дешевом виртуальном хотинге, без доступа к конфигам сервера. Тем более, если такие запросы нужны реально, то сервер безусловно не должен работать в режиме по умолчанию. А если есть доступ к кофигам и нужны такие запросы — то поправить одну строчку max_allowed_packet = 16.32,64… — не проблема.

        Важнее другое, так вообще делать нежелательно, существует специальный пакетный режим вставки — надо юзать его. Просто другие почитают — будут писать так и в реальной жизни. Даже вариант слить все в файл а потом сделать LOAD DATA INFILE — и то лучше.

        Кстати если запросы еще и по сети идут, а не локально, например через ODBC драйвер — то разница в скорости будет просто ошеломительная между кучей INSERT-ов и INSERT… VALUES… Проверено на практике
        • 0
          Можете кратко резюмировать, как лучше всего вставить 10 000 000 записей в таблицу? Я запутался в вашем споре ;)
          • +1
            Могу, вообще %maxshopen% прав относительно того, что большие объемы данных нужно вставлять за один запрос, а не каждая запись — новый запрос к БД.
            Но если в среднем длинная записи, которую вы вставляете 100 байт (например, одно-два небольших varchar поля, пример с BLOB и TEXT я даже рассматривать не буду) и того у вас в итоге получится объем данных равный 100(байт) * 10 000 000 = 100MB. На самом деле Вам прийдется для этого изменить и параметр memory_limit в php.ini.

            Поэтому я вам искренне советую не вставлять 10 000 000 записей в таблицу :-)
            • 0
              Ну почему же, в такой вставке нет ничего страшного. Просто лучше в таком случае генерировать sql-код в файл, разбивая данные на достаточно большие insert-кусочки, чтобы каждый из них умещался в query-буфер и заливать целиком этот файл, хотя как показала практика — заливка csv с данными с помощью LOAD DATA INFILE работает в разы быстрее.

              И снова спасибо за статью, лакончинчно и доступно. :)
              • 0
                Тут еще выигрыш в том, что намного упрощается логика генерирования выходных данных, которые нужно залить в БД, плюс — на надо мучаться с размерами пакетов.
              • +1
                Ну да, правильно. Это и имелось в виду. Что нужно разбивать вставку на VALUES (...), (...), (....)
                , скажем, по 10 000 инсертов. И выполнить 1 000 таких запросов :-). Ну или, через LOAD DATA INFILE, что в данном случае, пожалуй, будет работать быстрей.

                только в этом в первом случае прийдется добавить еще проверку strlen, чтобы проверять не превысил ли наш запрос порог query-буфера. И как только он подошел к нему в плотную — запихивать в БД и составлять новый запрос заново…
                • –3
                  лучше запихивать в файл, чтобы не тормозить при генерации следующего пакета данных пока будет передаваться сделанный =)
                • +2
                  LOAD DATA INFILE быстрее не будет. Самая медленные операции — дисковые, и тут их будет две, сначала запись в файл, потом запись в базу (для простоты считаем, что кэша нет). LOAD DATA хорош для переноса в базу очень больших данных, мы его используем для 8-10 гигабайтных таблиц и другим способом это не представляется возможным сделать.

                  В случае INSERT… VALUES… дисковая запись будет одна — только в файл таблицы базы данных.

                  На самом деле, мы уже не очень понятно что обсуждаем. Ситуация генерации тестовых данных — весьма редкое явление, для тестов можно выбрать любой способ (но не для написания статьи :-) ).

                  В реальности данные в большом количестве просто так не рождаются, они откуда то берутся, т.е. где то уже хранятся. Вот от этого и надо плясать. Если это лог-файлы пригодные для LOAD DATA — надо использовать его. Если это какие то данные требующие обработки, или находящиеся в другой БД то эффективнее и быстрее делать пакетную вставку. Если нужно перенести из одной базы mysql в другую — вообще можно обойтись INSERT INTO… SELECT FROM…

                  Ну а INSERT… SET… удобен для единичных записей
                  • +1
                    Во всем с Вами согласен, особенно в этом

                    >На самом деле, мы уже не очень понятно что обсуждаем.
            • 0
              Дабавлю офтопом, точней напомню (http://habrahabr.ru/blogs/php/38754/), что когда мы делаем конкатенацию в этом случае, то очень важно помнить, что её следует выполнять вот так

              $sql .= «('$a', '$b', '$c'),»;

              а не так

              $sql = $sql.«('$a', '$b', '$c'),»;

              Опять же… одно выделение памяти… два копирование… одно освобождение… и это Мегабайты данных… даже не смотря на то, что это оперативная память… хотя если несколько таких скриптов ворочают такие объемы данных в памяти, то не факт, что они не попадут в Своп… и тогда жизнь будет вообще не сахар…
          • +2
            Советую посмотреть еще в сторону LOAD DATA INFILE:
            The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed
            .
            Резюме в документации :)
          • –3
            Вставляйте по 1000 записей, не сильно проиграете в скорости по сравнению со вставкой 10 000 000 и избавитесь от многих проблем
            • 0
              на самом деле проигрыш весьма ощутимый
              • 0
                Это проверялось как-либо или это на основе каких-то не озвученных умозаключений?
                • +1
                  это проверялось на практике при вставке 800 с чем-то там тысяч записей…

                  но даже если просто логически подойти к этому вопросу, то получается 10 000 запросов, а это 10 000 разборов запроса, 10 000 — 1 ожиданий для каждого следующего запроса пока выполнится предыдущий, не говоря уже о затратах на межпроцессное взаимодействие при передаче данных.
                  • +1
                    То есть вы пробовали отключать перестроение индекса и импортировать записи блоками по 1000 и полученный результат сравнивали с экспортом в файл на диске и дальнейшим использованием LOAD DATA INFILE? Насколько велико было различие?
                    • +1
                      импортировались записи блоками разных размеров, 1000, 5000, 10000, 25000 и 50000, выходило, что чем меньше размер пакета данных, тем больше времени затрачивается на заливку данных.

                      Пробовалось как на InnoDB так и на MyISAM. На последнем при отключенной перестройке индексов скорость была выше, норазница сохранялась.

                      Точных цифр не скажу, так как было это больше полугода назад, но переход на генерацию sql-файла и его заливку вместо прямой передачи генерируемых данных в БД уменьшил время заливки данных в БД на порядок, а переход с sql-файла на CSV и LOAD DATA INFILE ещё на 30%.

                      Причём, чем больше необходимо залить данных, тем ощутимее становится разница.
                      • 0
                        Спасибо за ответ.
                        • 0
                          На здоровье. =)
                        • 0
                          Кстати, справедливости ради следует добавить, что чем меньше данных надо за раз залить, то разница соответственно будет чувствоваться меньше.
                          И, конечно же, зависит от того, что является приемлемым временем выполнения задачи заливки.
                          • +1
                            Это-то как раз достаточно очевидно. Было бы странно если бы было иначе =)
  • 0
    Еще для аналитики полезно использовать команду

    SHOW INDEX FROM `table`

    Позволяет посмотреть cardinalility индексов в таблице, чтобы оценить будут они использоваться или нет.
  • 0
    Чтобы не порождать лишнее число информации, приведу ссылку на то, что уже есть:

  • –2
    Прочитал с удовольствием, как и предыдущие :)
  • НЛО прилетело и опубликовало эту надпись здесь
  • НЛО прилетело и опубликовало эту надпись здесь
  • 0
    а почему у меня индекс создается уже пол часа???

    CREATE INDEX `fields1_2_3` ON `some_table` (field1, field2(8), `field3`);

    ничего себе соптимизировал, боюсь и Ctrl+c нажать теперь, там живое что-нибудь останется после прерывания?

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