Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Innodb table is taking randomly long time to execute the insert query and in show processlist showing as queryend/updating and it happens for the similar queries for the same table and the queries got strucks for five to fifteen minutes and suddenly process all the queued up list.

I cannot understand what happens suddenly sometimes...

The same queries for the same version, configuratios of the mysql in different server has no problem...

Here is the innodb configurations:

+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| innodb_adaptive_flushing        | ON                               |
| innodb_adaptive_hash_index      | ON                               |
| innodb_additional_mem_pool_size | 1073741824                       |
| innodb_autoextend_increment     | 8                                |
| innodb_autoinc_lock_mode        | 1                                |
| innodb_buffer_pool_instances    | 1                                |
| innodb_buffer_pool_size         | 8589934592                       |
| innodb_change_buffering         | all                              |
| innodb_checksums                | ON                               |
| innodb_commit_concurrency       | 0                                |
| innodb_concurrency_tickets      | 500                              |
| innodb_data_file_path           | ibdata1:4G;ibdata2:4G:autoextend |
| innodb_data_home_dir            | /var/lib/mysql                   |
| 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  | 2                                |
| innodb_flush_method             |                                  |
| innodb_force_load_corrupted     | OFF                              |
| innodb_force_recovery           | 0                                |
| innodb_io_capacity              | 400                              |
| innodb_large_prefix             | OFF                              |
| innodb_lock_wait_timeout        | 50                               |
| innodb_locks_unsafe_for_binlog  | ON                               |
| innodb_log_buffer_size          | 33554432                         |
| innodb_log_file_size            | 536870912                        |
| 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      | ON                               |
| innodb_purge_batch_size         | 20                               |
| innodb_purge_threads            | 0                                |
| innodb_random_read_ahead        | OFF                              |
| innodb_read_ahead_threshold     | 56                               |
| innodb_read_io_threads          | 10                               |
| 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.30                           |
| innodb_write_io_threads         | 10                               |
+---------------------------------+----------------------------------+

Can some one suggest me... why and what exactly happens

share|improve this question
    
Is the DB running in Windows or Linux? 32-bit or 64-bit? How much RAM does the DB Server have? How many cores? Is datadir mounted on a SAN or RAID10 set? –  RolandoMySQLDBA May 24 '13 at 18:08
add comment

2 Answers 2

ASPECT #1

What catches my eye here is innodb_flush_method. You do not have it set. What does this imply? That you are letting the OS do some of InnoDB's dirty work.

If you set innodb_flush_method to O_DIRECT (which uses fsync()), then mysqld will shoulder the responsibility of flushing data and metadata changes to disk. This may make disk I/O increase slightly but it should remain constant and will provide better data consistency.

Without setting innodb_flush_method, the default value is really O_DSYNC using fdatasync(). This gives you asynchronous writes to disk. It appears faster at first, but when the OS has to do some syncing of the data from its viewpoint, it hits this massive wall of activity that is simply unavoidable.

[mysqld]
innodb_flush_method=O_DIRECT

I have a post about this from Mar 04, 2011: Clarification on MySQL innodb_flush_method variable

ASPECT #2 (Optional)

I also noticed that you have ibdata1 and ibdata2 split up in innodb_data_file_path. You also have innodb_file_per_table configured. This means no data and no indexes are in the ibdata files. In light of this, what other classes of information live in ibdata1 and ibdata2?

  • MVCC (Multiversioning Concurrency Control) Data
    • Rollback Segments
    • Undo Space
  • Table Metadata (Data Dictionary)
  • Double Write Buffer (background writing to prevent reliance on OS caching)
  • Insert Buffer (managing changes to non-unique secondary indexes)
  • See the Pictorial Representation of ibdata1

You can collapse this to one ibdata1 file. Otherwise, you could find yourself with situation like:

  • UNDO logs split across the files that are needed for the same transaction
  • UNDO logs and Rollback Segments split across the files that are needed for the same transaction
  • InnoDB Buffer Pool using different tablespace files for double write buffering and index updates.

These may not be as I/O impacting as flushing to disk but would could present fragmentation challenges to InnoDB during normal operation.

If you choose to collapse InnoDB Infrastructure to a single tablespace file, please see my StackOverflow post on how : Howto: Clean a mysql InnoDB storage engine?

ASPECT #3

Without knowing your hardware, I would just the same look over your storage.

share|improve this answer
add comment

When I have seen this in the past it was actually not the database, but rather the underlying storage. Are you on a san or local disk? I have you ran iostat or are you collecting sar data. I would start by looking there.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.