Can you show us the table structure and sample queries?
On Thursday, September 2, 2010, Alexandre Vieira <nullpt@stripped> wrote:
> Hi list,
>
> I'm having some performance problems on my 5.0.45-log DB running on Solaris
> 8 (V240).
>
> We only have one table and two apps selecting, updating, inserting and
> deleting massively and randomly from this table.
>
> The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only
> one condition on an unique varchar indexed column.
>
> The table has 500k records and has been OPTIMIZED 32h ago.
>
> I've ran some sampling and:
>
> A SELECT costs between 400ms and 600ms.
> An UPDATE costs between 800ms and 1300ms.
> A DELETE costs between 900ms and 1300ms
> An INSERT costs always 900ms 2000ms.
>
> At any given time the DB is handling 60-80 operations every second. It does
> not scale any more than this because all the application connections to the
> DB are being used and waiting for the DB to move. Our application queues
> requests and it lags our clients.
>
> The perl mysqltuner only whines about "Query cache disabled" but since I get
> an ~20 updates every second I can't get any query cache hits, so I disabled
> it.
>
> If it makes any difference, we're replicating everything to another server
> that don't serve any queries.
>
> The DB has a 32 hour uptime.
>
> Any help is most welcome.
>
> You can find my.cnf, show status and show innodb status below.
>
> Kind regards
> Alex
>
> ###############################
> my.cnf:
>
> sql-mode
> ="STRICT_ALL_TABLES"
> old_passwords =1
> skip-bdb
> max_connections =100
> max_allowed_packet =1M
> table_cache
> =512
> sort_buffer_size =2M
> read_buffer_size =4M
> read_rnd_buffer_size =8M
> thread_cache_size =16
> query_cache_limit =32M
> thread_concurrency =8
> max_heap_table_size =28M
> tmp_table_size =12M
> innodb_buffer_pool_size =350M
> innodb_additional_mem_pool_size =15M
> innodb_log_buffer_size =6M
> innodb_flush_log_at_trx_commit =1
> innodb_lock_wait_timeout =50
>
> ###############################
>
> mysql> show status where Value NOT LIKE 0;
> +-----------------------------------+------------+
> | Variable_name
> | Value |
> +-----------------------------------+------------+
> | Aborted_clients | 88
> |
> | Aborted_connects |
> 37590 |
> | Binlog_cache_use |
> 2148392 |
> | Bytes_received
> | 1117 |
> | Bytes_sent
> | 8772 |
> | Com_change_db
> | 1 |
> | Com_delete
> | 4 |
> | Com_insert
> | 3 |
> | Com_select
> | 2 |
> | Com_show_databases | 1
> |
> | Com_show_fields | 3
> |
> | Com_show_status | 2
> |
> | Com_show_tables | 1
> |
> | Compression
> | OFF |
> | Connections
> | 276096 |
> | Created_tmp_files | 5
> |
> | Created_tmp_tables | 4
> |
> | Flush_commands
> | 1 |
> | Handler_commit
> | 14 |
> | Handler_prepare | 14
> |
> | Handler_read_key | 8
> |
> | Handler_read_rnd_next | 263
> |
> | Handler_write
> | 395 |
> | Innodb_buffer_pool_pages_data | 6019 |
> | Innodb_buffer_pool_pages_dirty | 1858 |
> | Innodb_buffer_pool_pages_flushed | 593993 |
> | Innodb_buffer_pool_pages_free | 15784 |
> | Innodb_buffer_pool_pages_misc | 597 |
> | Innodb_buffer_pool_pages_total | 22400 |
> | Innodb_buffer_pool_read_ahead_rnd | 1 |
> | Innodb_buffer_pool_read_requests | 42797013 |
> | Innodb_buffer_pool_reads | 3497
> |
> | Innodb_buffer_pool_write_requests | 19096507 |
> | Innodb_data_fsyncs | 4319683
> |
> | Innodb_data_pending_fsyncs | 1
> |
> | Innodb_data_read |
> 60231680 |
> | Innodb_data_reads | 3514
> |
> | Innodb_data_writes | 4496721
> |
> | Innodb_data_written | 1259458560
> |
> | Innodb_dblwr_pages_written | 593993 |
> | Innodb_dblwr_writes | 12967
> |
> | Innodb_log_write_requests | 2111208 |
> | Innodb_log_writes | 4285654
> |
> | Innodb_os_log_fsyncs | 4303114
> |
> | Innodb_os_log_pending_fsyncs | 1
> |
> | Innodb_os_log_written | 3264897024 |
> | Innodb_page_size |
> 16384 |
> | Innodb_pages_created | 2476
> |
> | Innodb_pages_read | 3543
> |
> | Innodb_pages_written | 593993
> |
> | Innodb_row_lock_time | 1339668
> |
> | Innodb_row_lock_time_avg | 379
> |
> | Innodb_row_lock_time_max | 10631
> |
> | Innodb_row_lock_waits | 3531
> |
> | Innodb_rows_deleted | 31904
> |
> | Innodb_rows_inserted | 530870
> |
> | Innodb_rows_read |
> 7885336 |
> | Innodb_rows_updated | 2100083
> |
> | Key_blocks_unused | 7159
> |
> | Key_blocks_used | 14
> |
> | Key_read_requests | 106
> |
> | Key_reads
> | 14 |
> | Last_query_cost |
> 10.499000 |
> | Max_used_connections | 66
> |
> | Ndb_config_from_host |
> |
> | Open_files
> | 54 |
> | Open_tables
> | 126 |
> | Qcache_free_blocks | 1
> |
> | Qcache_hits
> | 18 |
> | Qcache_inserts
> | 595 |
> | Qcache_not_cached | 7611
> |
> | Questions
> | 12971115 |
> | Rpl_status
> | NULL |
> | Select_scan
> | 4 |
> | Slave_running
> | OFF |
> | Ssl_cipher
> | |
> | Ssl_cipher_list |
> |
> | Ssl_session_cache_mode | NONE
> |
> | Ssl_version
> | |
> | Table_locks_immediate | 4927708
> |
> | Table_locks_waited | 67
> |
> | Threads_cached
> | 10 |
> | Threads_connected | 42
> |
> | Threads_created |
> 4133 |
> | Threads_running | 36
> |
> | Uptime
> | 120796 |
> | Uptime_since_flush_status | 120796 |
> +-----------------------------------+------------+
> 87 rows in set (0.01 sec)
>
> ###############################
>
> =====================================
> 100902 12:39:47 INNODB MONITOR OUTPUT
> =====================================
> Per second averages calculated from the last 19 seconds
> ----------
> SEMAPHORES
> ----------
> OS WAIT ARRAY INFO: reservation count 63731, signal count 62253
> Mutex spin waits 0, rounds 1686893, OS waits 41516
> RW-shared spins 23291, OS waits 11428; RW-excl spins 4076, OS waits 3446
> ------------
> TRANSACTIONS
> ------------
> Trx id counter 0 1953324058
> Purge done for trx's n:o < 0 1953323526 undo n:o < 0 0
> History list length 172
> Total number of lock structs in row lock hash table 9
> LIST OF TRANSACTIONS FOR EACH SESSION:
> ---TRANSACTION 0 0, not started, OS thread id 4121 waiting in InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276150, query id 12973147 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 0, not started, OS thread id 4119 waiting in InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276149, query id 12973129 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 0, not started, OS thread id 4067 waiting in InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276148, query id 12973102 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324017, not started, OS thread id 4098 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276144, query id 12973093 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953323942, not started, OS thread id 4140
> MySQL thread id 276135, query id 12972947 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953324045, not started, OS thread id 4074 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276133, query id 12973157 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324021, not started, OS thread id 4038
> MySQL thread id 276130, query id 12973043 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953323971, not started, OS thread id 3873 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276129, query id 12973112 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324024, not started, OS thread id 4145 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276124, query id 12973159 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324027, not started, OS thread id 4003
> MySQL thread id 276111, query id 12973050 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953323945, not started, OS thread id 3918 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276107, query id 12973115 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 1953324023, not started, OS thread id 4089 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276093, query id 12973106 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953323946, not started, OS thread id 4102
> MySQL thread id 276081, query id 12972959 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953324029, not started, OS thread id 4143 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276079, query id 12973167 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324048, not started, OS thread id 3945 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275987, query id 12973161 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324026, not started, OS thread id 4048 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275976, query id 12973110 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324040, not started, OS thread id 4142 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275975, query id 12973154 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953323944, not started, OS thread id 3979 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275946, query id 12973121 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 1953324016, not started, OS thread id 4058 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275931, query id 12973092 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324015, not started, OS thread id 4065 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275913, query id 12973090 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324033, not started, OS thread id 4111 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275907, query id 12973137 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953323993, not started, OS thread id 4078 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275889, query id 12973132 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324011, not started, OS thread id 4136 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275887, query id 12973089 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324034, not started, OS thread id 4014 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275888, query id 12973166 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953323997, not started, OS thread id 4123 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275856, query id 12973133 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324002, not started, OS thread id 3999 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275827, query id 12973151 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953323985, not started, OS thread id 4115 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275809, query id 12973105 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324036, not started, OS thread id 4133 sleeping before
> joining InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275784, query id 12973169 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324031, not started, OS thread id 4134 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275715, query id 12973136 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324000, not started, OS thread id 4124 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275665, query id 12973149 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324009, not started, OS thread id 4130 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275539, query id 12973155 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953323988, not started, OS thread id 4010 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275263, query id 12973108 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953323990, not started, OS thread id 4086 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 274948, query id 12973123 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953324032, not started, OS thread id 3934 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 274703, query id 12973163 192.168.87.6 myuser_adm statistics
> SELECT********************
> ---TRANSACTION 0 1953292243, not started, OS thread id 4046
> MySQL thread id 274066, query id 12973170 localhost root
> show innodb status
> ---TRANSACTION 0 1953324057, ACTIVE 0 sec, OS thread id 4127, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276140, query id 12973085 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324055, ACTIVE 0 sec, OS thread id 4144, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276147, query id 12973082 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324053, ACTIVE 0 sec, OS thread id 4141, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 275720, query id 12973078 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324051, ACTIVE 0 sec, OS thread id 4029, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 273472, query id 12973075 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324049, ACTIVE 1 sec, OS thread id 4109, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 274973, query id 12973084 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324046, ACTIVE 1 sec, OS thread id 4126, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276146, query id 12973081 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324043, ACTIVE 1 sec, OS thread id 4122, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 274989, query id 12973069 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324041, ACTIVE 1 sec, OS thread id 4099, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276145, query id 12973066 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324038, ACTIVE (PREPARED) 1 sec, OS thread id 4077
> preparing
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 275321, query id 12973067 192.168.87.6 myuser_adm end
> UPDATE********************
> --------
> FILE I/O
> --------
> I/O thread 0 state: waiting for i/o request (insert buffer thread)
> I/O thread 1 state: waiting for i/o request (log thread)
> I/O thread 2 state: waiting for i/o request (read thread)
> I/O thread 3 state: waiting for i/o request (write thread)
> 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
> 3514 OS file reads, 4497412 OS file writes, 4320374 OS fsyncs
> 0.00 reads/s, 0 avg bytes/read, 41.05 writes/s, 40.73 fsyncs/s
> -------------------------------------
> INSERT BUFFER AND ADAPTIVE HASH INDEX
> -------------------------------------
> Ibuf: size 1, free list len 0, seg size 2,
> 0 inserts, 0 merged recs, 0 merges
> Hash table size 1452727, used cells 496505, node heap has 597 buffer(s)
> 31.26 hash searches/s, 15.31 non-hash searches/s
> ---
> LOG
> ---
> Log sequence number 61 3783563525
> Log flushed up to 61 3783563173
> Last checkpoint at 61 3778869606
> 1 pending log writes, 0 pending chkp writes
> 4297652 log i/o's done, 40.63 log i/o's/second
> ----------------------
> BUFFER POOL AND MEMORY
> ----------------------
> Total memory allocated 419047082; in additional pool allocated 2578048
> Buffer pool size 22400
> Free buffers 15784
> Database pages 6019
> Modified db pages 1895
> Pending reads 0
> Pending writes: LRU 0, flush list 0, single page 0
> Pages read 3543, created 2476, written 594057
> 0.00 reads/s, 0.00 creates/s, 6.47 writes/s
> Buffer pool hit rate 1000 / 1000
> --------------
> ROW OPERATIONS
> --------------
> 8 queries inside InnoDB, 29 queries in queue
> 1 read views open inside InnoDB
> Main thread id 11, state: sleeping
> Number of rows inserted 530873, updated 2100423, deleted 31904, read 7886015
> 0.21 inserts/s, 19.95 updates/s, 0.00 deletes/s, 40.05 reads/s
> ----------------------------
> END OF INNODB MONITOR OUTPUT
> ============================
>
> ###############################
>
--
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped
