•  

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL locking unused table

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL locking unused table

    #1
    Hi everyone,

    New to this forum. I've been appreciating Percona's work for a couple of months and we do get very impressive performance results using XtraDB on a couple of projects So good job !

    Right now, our problem is that we can't understand a particular behaviour of MySQL engine.


    Our project is making lots (>2K/sec) of inserts in a specific table.

    The problem is that, when running a pretty big SELECT query on another table, INSERTS on the other table get locked :/
    There absolutely no link to our "inserts table" in the SELECT query.

    Here is our cnf config :

    Quote:
    # MySIAM Tuning
    max_connections = 1200
    table_cache = 8192
    open_files_limit = 16384
    query_cache_type = 2
    query_cache_size = 1024M
    query_cache_limit = 8M
    key_buffer_size = 2048M
    thread_cache_size = 24
    long_query_time = 1
    max_heap_table_size = 4096M
    tmp_table_size = 4096M
    join_buffer_size = 3M

    max_connect_errors = 100000


    innodb_buffer_pool_size = 6G
    innodb_data_file_path = ibdata1:10M:autoextend

    # Tuning
    innodb_file_io_threads = 4
    innodb_thread_concurrency = 24
    #innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 256M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 90
    #innodb_flush_method=O_DIRECT
    innodb_file_per_table = 1

    # XtraDB
    innodb_io_capacity = 1000
    innodb_adaptive_checkpoint = 1
    innodb_write_io_threads = 24
    innodb_read_io_threads = 24
    Any idea?

    We also noticed very intensive disk usage (compared to MyISAM) on the "inserts" table.

    Thanks for your help !

    Gaëtan
    Tags: None

  • #2
    Kh3ops wrote on Sat, 19 March 2011 18:29
    Right now, our problem is that we can't understand a particular behaviour of MySQL engine.


    Our project is making lots (>2K/sec) of inserts in a specific table.

    The problem is that, when running a pretty big SELECT query on another table, INSERTS on the other table get locked :/
    There absolutely no link to our "inserts table" in the SELECT query.
    But both these tables are located on the same RAID right?

    Kh3ops wrote on Sat, 19 March 2011 18:29
    We also noticed very intensive disk usage (compared to MyISAM) on the "inserts" table.
    How did you measure it?

    The fact that you are going to get more writes when using InnoDB instead of MyISAM is that InnoDB needs to fulfill the Durability part of the ACID transaction requirements, which requires more writes to disk.

    And especially since you have the default setting for innodb_flush_log_at_trx_commit (which is the 1 as it says in your commented row in your config file).

    The setting 1 means that if you don't explicitly use transactions then each insert will be a separate transaction and hence it needs to flush the transaction log for each write.
    Usually this is not a problem performance wise with a RAID controller with cache, but if your SELECT saturates the RAID controller/disk setup then these extra writes would lock up the inserts since it will not continue until they have been performed.

    A common tradeoff to get better performance is to set it to:
    innodb_flush_log_at_trx_commit = 2
    that limits the writes to disk to about 1 per second, but the drawback is that you can also loose up to one second of transactions in the event of power failure/OS crash.

    Comment

    Working...
    X