mysql slow insert Медленный запрос insert

Question

[anitspam]

Собственно проблема

# Time: 130809 12:07:11
# User@Host: u[u] @ localhost []
# <b>Query_time: 2.272070</b>  Lock_time: 0.000066 Rows_sent: 0  Rows_examined: 0
SET timestamp=1376010431;
INSERT INTO `archive_hour` (`device_id`, `data_date`, `param0`,`param1`,...,`paramn`) VALUES ('334', '2013-07-26 4:00:00', '0','0',...,'0');

Подскажите, как побеждают такие проблемы?
Немного исходных данных ниже. Может ещё какие-нибудь нужны для анализа?

SHOW CREATE TABLE `archive_hour`;
CREATE TABLE `archive_hour` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `data_date` datetime NOT NULL,
  `param0` int(11) DEFAULT NULL,
  `param1` int(11) DEFAULT NULL,
...
  `paramn` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_id` (`device_id`,`data_date`)
) ENGINE=InnoDB AUTO_INCREMENT=4624416 DEFAULT CHARSET=utf8;


select count(*) from archive_hour;
+----------+
| count(*) |
+----------+
|  4077212 |
+----------+

SHOW ENGINE INNODB STATUS;

+--------+------+-------------------------------------------------------------------------------------------------------
| Type | Name | Status
+--------+------+-------------------------------------------------------------------------------------------------------
| InnoDB | |
=====================================
130809 13:55:48 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
— BACKGROUND THREAD
— srv_master_thread loops: 839855 1_second, 839370 sleeps, 72537 10_second, 155130 background, 155080 flush
srv_master_thread log flush and writes: 854867
— SEMAPHORES
— OS WAIT ARRAY INFO: reservation count 94506, signal count 93282
Mutex spin waits 64294, rounds 1929090, OS waits 6537
RW-shared spins 90600, rounds 2717671, OS waits 79730
RW-excl spins 71, rounds 247170, OS waits 8215
Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 3481.27 RW-excl
— TRANSACTIONS
— Trx id counter 10AD551
Purge done for trx's n:o < 10AD535 undo n:o < 0
History list length 2424
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 81731, OS thread handle 0x2af000bca700, query id 2373827 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 10AD550, not started
MySQL thread id 81607, OS thread handle 0x2af000dfb700, query id 2373826 localhost informserver
---TRANSACTION 10AD516, not started
MySQL thread id 81606, OS thread handle 0x2af0011a2700, query id 2373448 localhost informserver
— FILE I/O
— I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0], aio writes: 0 [0, 0, 0, 0],
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
3687902 OS file reads, 1362297 OS file writes, 743154 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
— INSERT BUFFER AND ADAPTIVE HASH INDEX
— Ibuf: size 1, free list len 87, seg size 89, 7603 merges
merged operations:
insert 163196, delete mark 10483, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 7 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
— LOG
— Log sequence number 11403972949
Log flushed up to 11403972949
Last checkpoint at 11403972949
0 pending log writes, 0 pending chkp writes
424263 log i/o's done, 0.00 log i/o's/second
— BUFFER POOL AND MEMORY
— Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 663170
Buffer pool size 8191
Free buffers 1
Database pages 8183
Old database pages 3000
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3797273, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3687774, created 88342, written 840894
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8183, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
— ROW OPERATIONS
— 0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 18168, id 47210287879936, state: waiting for server activity
Number of rows inserted 8704545, updated 313076, deleted 38110, read 856592960
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
— END OF INNODB MONITOR OUTPUT
============================
|
+--------+------+-------------------------------------------------------------------------------------------------------

/etc/mysql/my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp

skip-external-locking

character-set-server=utf8
init-connect = «set names utf8»

skip-networking

key_buffer = 64M
max_allowed_packet = 64M
thread_stack = 192K
thread_cache_size = 8

myisam-recover = BACKUP

query_cache_limit = 1M
query_cache_size = 16M

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

expire_logs_days = 10
max_binlog_size = 100M

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=32M
innodb_buffer_pool_size=128M

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[isamchk]
key_buffer = 16M

mysql --version
mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.1

Сервер
OVZ-3 память 1200 MB процессор 1700 Mhz диск 10 GB

Дополнение от 12.08.13

Спасибо всем за советы.

Я поставил innodb_flush_log_at_trx_commit=0. Данные, которые я получаю от устройств, можно перезапросить, поэтому их потеря в случае форс-мажора сервера mysql не критична.

Также я поставил innodb_buffer_pool_size=256. Теперь общее значение занятой памяти по программе htop равно 614 (из 1200).

SHOW TABLE STATUS LIKE 'archive_hour';

+-------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name              | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| archive_hour      | InnoDB |      10 | Compact    | 4147021 |            111 |   463470592 |               0 |    111902720 |   5242880 |        4632149 | 2013-08-09 10:58:53 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+-------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

show variables like 'innodb_%';

+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 1                      |
| innodb_buffer_pool_size         | 268435456              |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | ON                     |
| innodb_flush_log_at_trx_commit  | 0                      |
| innodb_flush_method             | O_DIRECT               |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 33554432               |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_print_all_deadlocks      | OFF                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 0                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | ON                     |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 5.5.31                 |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+

Ещё такой вопрос.

В статьях и вопросах, которые показывает Хабр на эту тематику, предлагается сделать разбиение (партиционирование) таблицы.

Есть ли смысл для моей таблицы такое разбиение.
Данные в ней храняться с 2010 года, обращение к данным на 99.9% состоит из обращений за последний месяц-два. Если я разобью по полугодиям или кварталам — улучшится ли производительность?

Answer 1

[AxisPod]

Неужто тут нужна InnoDB? Если памяти на табличку не хватает, то будет тормозить. Опять же судя по всему это VDS, а тут диск уже точно общий, так что другие VDS на этом сервере дают хороший тормоз.

Comments

[anitspam]

Подскажите, как определить «памяти на табличку не хватает»?

[AxisPod]

Посмотрите SHOW TABLE STATUS LIKE 'archive_hour';

Покажет размер данных и размер индексов. В вашем my.cnf ключик innodb_buffer_pool_size говорит о пуле для хранения innoDB таблиц, в случае, если все активно используемые таблицы в него не влезают, то вы будете получать регулярные тормоза на самых элементарных запросах, так что оцените размер хранимых данных в бд.

Answer 2

[ivnik]

Посмотрите чему равна innodb_flush_log_at_trx_commit и скиньте сюда результат show variables like 'innodb_%'

Если innodb_flush_log_at_trx_commit = 1 то попробуйте изменить на 2 (flush журнала будет происходить раз в секунду, вместо каждой транзакции)

Answer 3

[Артём Каретников]

Коллега, есть быстрое решение — заводите еще одну таблицу, short_log, вставляете данные в неё, а фоном — отдельно от того, что нужно делать быстро — переливаете её всю в большую. Это будет пакетная вставка, она выполняется быстрей, чем вставка одной записи, потому что индекс — а мы знаем, что индекс перестраивается достаточно долго — будет перестраиваться один раз. А сейчас у вас это выполняется при вставке каждой строки. \

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

Далее. Поля int у вас допускают отрицательные значения — я бы сделал UNSIGNED, вряд ли девайс у вас может иметь отрицательный id
Далее. Дата — это по сути тот же инт, верно? Давайте его как инт и хранить — и вставлять как инт — съэкономим время на преобразование при перестройке индекса.
Нужно будет посмотреть реальную дату — выполните обратное преобразование.

Comments

[Sergey Cherepanov]

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

[Артём Каретников]

«Дата — это по сути тот же инт, верно?» — вот это что написано, о мудрый, а не глупый? Вы, может, сначала попробуете, а потом минусовать будете? Я утверждаю, что вставка записей в таблицу по 10 тысяч за раз — будет работать быстрей, чем по одной. Есть возражения? Далее, я утверждаю, что основная проблема связана с перестройкой индекса — есть возражения?

Только не в стиле: «Это всё глупость!», а именно со всей свойственной вам мудростью: «мол, дело не в индексе, потому что… А дело вот в чем, надо сделать вот это и будет щастье».

А то кроме пока ничем не обоснованной критики — предложений от вас не видно.

[Sergey Cherepanov]

>> Я утверждаю, что вставка записей в таблицу по 10 тысяч за раз
Что то я в вопросе не вижу вставки больше одной записи.

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

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

В вашем совете есть только одна хорошая мысль, вставка в таблицу в которой нет записей будет работать быстрее потому что там нет записей ) Вот только беда, как мы будем обрабатывать ошибку которая произойдет уже при переносе данных из short_log в основную таблицу? Как будем делать rollback транзакции?

[Артём Каретников]

А почему ж вставлять данные в таблицу, где нет записей — быстрей, чем в ту, где есть записи?
Не волшебство нам мешает вставлять записи в заполненную таблицу, не оно. А именно что перестройка индекса. Вот это отрицать — и есть самая настоящая глупость. И проверить легко — прибить индекс и убедиться, как же быстро всё стало вставляться.

Что до ошибок — точно так же будем обрабатывать ошибку, как сейчас — т.е. никак. У заявителя никакой транзакции нет на вставку одной записи и никакой гарантии, что оно вставилось. Его система — ему и транзакции делать самому. И никакой разницы в данном случае, на сколь долго лочить таблицу основного лога — нет.

Следите за рукою. Определяем число записей в таблице, допустим, их 12001,
1. берем последний id — 12001.
2. Выбираем все записи меньше чем этот id — естественно, первичным ключем по полю id с автоинкрементом гарантировав их последовательность.
3. Одной транзакцией вставляем это всё в главную таблицу лога.
4. Индекс перестраивается один раз — нам профит.
5. Посколь в выборке участвует малая таблица и берем не всё, а с определенной записи — ничего не мешает продолжать вставлять записи в short_log.

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

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

[Sergey Cherepanov]

Answer 4

[stavinsky]

Простите если сказал банальность, но:
1. партицирование — всегда хорошо ровно как и шардинг
2. скуль держать на vds при 4 млн записей в таблице — это по меньшей мере странно, используйте тогда хотя бы MyISAM, хотя боюсь тоже не спасет.
3. мускуль — 5.5, почему не сделали профайлинг? может быть стало бы понятно во что все упирается
4. анализ дисковой тоже не помешает, хотя бы вывод iostat -dx 5 в момент записи и без нее

Comments

[anitspam]

1. попробую сделать.
2. наверное дело в нагрузках, пока мне не требуется большой производительности. простых запросов select в mysql-slow.log нет.
3. про профайлинг почитаю. спасибо.
4. у меня дисковая система
# df -h

Файловая система      Разм  Исп  Дост  Исп% смонтирована на
/dev/simfs            9,8G  6,0G  3,9G  61% /
tmpfs                 600M     0  600M   0% /lib/init/rw
tmpfs                 600M     0  600M   0% /dev/shm

Поможет ли данная утилита в моём случае?

[stavinsky]

Ох да это еще и OpenVZ?
Покажет или нет не знаю, попробуйте, покажите что выдаст, если выдаст.

Но OpenVZ для даже маленькой бд — имхо плохо

Если у Вас основная задача — MySQL — может попробовать облачные решения? Google cloud, AWS и тд? Там можно чисто мускуль покупать. А у гугла еще и интересная штука Big Query — Там до 10к записей в день бесплатно.

[anitspam]

Я посмотрел статьи по теме iostat. Судя по всему в моём случае она не показывает нужные данные
iostat -dx 5

Linux 2.6.32-308.8.2.el5.028stab101.1 (inform)  14.08.2013      _x86_64_        (1 CPU)
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
...
Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util

Основная задача у меня как и у большинства веб-приложений nginx — php-fpm — mysql. Просто в данный момент интересно разобраться именно с MySQL.

Повторюсь — пока проблема с большим количеством медленных insert ушла.

[stavinsky]

Если ушла — хорошо.
Спасибо, теперь знаю что openvz не показывает iostat.

innodb_flush_log_at_trx_commit и buffer_pool_size — не единственные параметры которые можно менять в MySQL, советую посмотреть другие, например в моей статье, правда сейчас есть желание ее переписать, много там глупостей.

[anitspam]

в вашей статье (как и в большинстве других) речь идёт о серверах под большими нагрузками с большими характеристиками.
если вдруг будете обновлять статью, добавьте побольше относительных данных (вроде 50% от оперативной памяти, 10% от «размера» самой большой таблицы).

Если понадобятся ещё какие-нибудь данные о работе openvz — обращайтесь)

Answer 5

[Maxim Kuzovlev]

Мне кажется, что вы пытаетесь вставить строковое значение в целочисленное поле, сиквел приводит строку целому числу и на этом теряет время, попробуйте убрать кавычки у вставляемых значений, но могу ошибаться.
Пример:
INSERT INTO `archive_hour` (`device_id`, `data_date`, `param0`,`param1`,...,`paramn`) VALUES (334, '2013-07-26 4:00:00', 0,0,...,0);

Answer 6

[worker_s]

А вообще при множественной вставке, лучше использовать
INSERT INTO tablename (colname) VALUES (val1), (va...