mysql slow insert

Question

MySQL

Actually the problem

# Time: 130809 12:07:11
# [email protected]: 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');

Tell me how to overcome such problems?
Some background information below. Is there anything else that needs to be analyzed?

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

Server
OVZ-3 memory 1200 MB processor 1700 Mhz disk 10 GB

Update as of 08/12/13

Thanks everyone for the advice.

I set innodb_flush_log_at_trx_commit=0. The data that I receive from the devices can be re-requested, so their loss in case of force majeure of the mysql server is not critical.

I also set innodb_buffer_pool_size=256. Now the total value of memory used by the htop program is 614 (out of 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                      |
+---------------------------------+------------------------+

Another such question.

In the articles and questions that Habr shows on this topic, it is proposed to split (partition) the table.

Whether there is a sense for my table such partition.
The data has been stored in it since 2010, access to data for 99.9% consists of accesses for the last month or two. If I break it down by semesters or quarters, will performance improve?

Answer 1

Is InnoDB really needed here? If there is not enough memory for the plate, it will slow down. Again, apparently this is a VDS, but here the disk is already shared, so other VDS on this server give a good brake.

Answer 2

Look at what innodb_flush_log_at_trx_commit is equal to and drop the result here show variables like 'innodb_%'
If innodb_flush_log_at_trx_commit = 1 then try changing it to 2 (log flush will occur once a second, instead of every transaction)

Answer 3

Colleague, there is a quick solution - start another table, short_log, insert data into it, and in the background - apart from what you need to do quickly - pour it all into a large one. This will be a batch insert and is faster than a single record insert because the index - and we know that the index takes a long time to rebuild - will rebuild once. And now you have this done on insertion of each row. \
You write a separate procedure, run it every 10 minutes - if there are more than, say, 10 thousand lines in short_log - fill everything from it into a large log.
Further. Your int fields allow negative values ​​- I would do UNSIGNED, it is unlikely that your device can have a negative id
Further. Date is essentially the same int, right? Let's store it as an int and store it - and insert it as an int - to save time on conversion when rebuilding the index.
You will need to look at the real date - perform the reverse conversion.

Answer 4

Forgive me if I said a banality, but:
1. partitioning is always good exactly like sharding
2. keeping a whine on vds with 4 million records in the table is at least strange, then use at least MyISAM, although I’m afraid it won’t save either.
3. muscle - 5.5, why didn't you do profiling? maybe it would become clear what everything rests on
4. disk analysis also does not hurt, at least the output of iostat -dx 5 at the time of recording and without it

Answer 5

It seems to me that you are trying to insert a string value into an integer field, the sequel converts the string to an integer and wastes time on this, try removing the quotes from the inserted values, but I could be wrong.
Example:
INSERT INTO `archive_hour` (`device_id`, `data_date`, `param0`,`param1`,...,`paramn`) VALUES (334, '2013-07-26 4:00:00', 0 ,0,...,0);

Answer 6

In general, with multiple inserts, it is better to use
INSERT INTO tablename (colname) VALUES (val1), (va...