Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Unanswered: Poor InnoDB Performance

    I converted 3 of about 30 MyISAM tables to InnoDB. Since then I have been constantly reaching my limit of 100 connections because selects on the InnoDB tables are slow.

    If I cut off traffic and execute a SELECT, it takes less than 0.01 seconds. If a execute it again with traffic it takes 18+ seconds. Other queries on other tables are acting the same.

    I have a Dual 2ghz Xeon dedicated server with (2) 73GB 10k RPM SCSI hard drives. It has 1GB of RAM. (Only 1 hard drive is used and and CPU usage is about 50-60%)

    The server is not dedicated to databases, it also has apache (with PHP) running on it.

    My current InnoDB settings are:
    | innodb_additional_mem_pool_size | 1048576
    | innodb_buffer_pool_size | 262144000
    | innodb_data_file_path | ibdata1:10M:autoextend
    | innodb_data_home_dir |
    | innodb_file_io_threads | 4
    | innodb_force_recovery | 0
    | innodb_thread_concurrency | 4
    | innodb_flush_log_at_trx_commit | 1
    | innodb_fast_shutdown | ON
    | innodb_flush_method |
    | innodb_lock_wait_timeout | 50
    | innodb_log_arch_dir | ./
    | innodb_log_archive | OFF
    | innodb_log_buffer_size | 20971520
    | innodb_log_file_size | 5242880
    | innodb_log_files_in_group | 2
    | innodb_log_group_home_dir | ./
    | innodb_mirrored_log_groups | 1
    | innodb_max_dirty_pages_pct | 90



    Does anyone have an idea what could be going wrong?


    Thanks.

  2. #2
    Join Date
    Jul 2003
    Posts
    4
    Here are some details: (I don't see an edit post button)

    20 connections per second.
    200 queries per second (shared between InnoDB and MyISAM tables)

    InnoDB Table #1 is 84MB.
    InnoDB Table #2 is 166MB.
    InnoDB Table #3 is 151MB.

    MySQL says: 'InnoDB free: 6144 kB' for each innodb table.

    The InnoDB Monitor gives this:
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request
    I/O thread 1 state: waiting for i/o request
    I/O thread 2 state: waiting for i/o request
    I/O thread 3 state: waiting for i/o request
    Pending normal aio reads: 0, aio writes: 0,
    ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 1; buffer pool: 0
    26279 OS file reads, 24278 OS file writes, 10809 OS fsyncs
    0.80 reads/s, 16384 avg bytes/read, 3.40 writes/s, 3.40 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf for space 0: size 1, free list len 380, seg size 382,
    125 inserts, 125 merged recs, 57 merges
    Hash table size 1155127, used cells 529328, node heap has 695 buffer(s)
    922.82 hash searches/s, 338.13 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 0 479156959
    Log flushed up to 0 479156959
    Last checkpoint at 0 479148210
    1 pending log writes, 0 pending chkp writes
    10188 log i/o's done, 3.40 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 316173844; in additional pool allocated 1038720
    Buffer pool size 16000
    Free buffers 0
    Database pages 15305
    Modified db pages 54
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 31305, created 9, written 14289
    0.80 reads/s, 0.00 creates/s, 0.00 writes/s
    Buffer pool hit rate 1000 / 1000
    --------------
    ROW OPERATIONS
    --------------
    4 queries inside InnoDB, 48 queries in queue
    Main thread process no 21521, state: flushing log
    Number of rows inserted 209, updated 15138, deleted 0, read 1547848
    0.20 inserts/s, 4.20 updates/s, 0.00 deletes/s, 958.41 reads/s



    In the Transaction Section of the InnoDB monitor I am seeing things like: 'Trx read view will not see trx with id >= 0 2505453, sees < 0 2505360' a lot.

  3. #3
    Join Date
    Jun 2003
    Posts
    9
    query time will be highly dependent on the type of queries

    queries on primary keys r faster than myISAM
    queries for other indexes might be slower - need to test them to find out

    --hitech

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •