1

My boss said database is bad when query works in update and insert commands. What can I do for tuning?

normally queries respond in select commands, It respond little results but page updated about 4-5 seconds;

# Query_time: 0.000232  Lock_time: 0.000062  Rows_sent: 27  Rows_examined: 27
# Query_time: 0.000173  Lock_time: 0.000056  Rows_sent: 27  Rows_examined: 27
# Query_time: 0.000387  Lock_time: 0.000069  Rows_sent: 79  Rows_examined: 106
# Query_time: 0.002147  Lock_time: 0.000116  Rows_sent: 9  Rows_examined: 27
# Query_time: 0.000765  Lock_time: 0.000100  Rows_sent: 18  Rows_examined: 54
# Query_time: 0.014601  Lock_time: 0.000061  Rows_sent: 1131  Rows_examined: 2262
# Query_time: 0.000207  Lock_time: 0.000069  Rows_sent: 48  Rows_examined: 48
# Query_time: 0.000972  Lock_time: 0.000071  Rows_sent: 32  Rows_examined: 64
# Query_time: 0.000419  Lock_time: 0.000103  Rows_sent: 0  Rows_examined: 0
# Query_time: 0.000202  Lock_time: 0.000064  Rows_sent: 4  Rows_examined: 4
# Query_time: 0.000755  Lock_time: 0.000113  Rows_sent: 8  Rows_examined: 24

Insert and Update commands, Boss said when he update table, database are slow. it has nearlly 5 millions rows;

# User@Host: koha[koha] @  [x.160.x.x]
# Thread_id: 323047  Schema: kohadata  QC_hit: No
# Query_time: 194.853241  Lock_time: 0.000091  Rows_sent: 0  Rows_examined: 5900006
SET timestamp=1448804624;
UPDATE zebraqueue SET done = 1
                             WHERE server = 'test'
                             AND done = 0;
# User@Host: koha[koha] @  [x.160.x.x]
# Thread_id: 393768  Schema: kohadata  QC_hit: No
# Query_time: 117.138732  Lock_time: 0.000299  Rows_sent: 0  Rows_examined: 0
SET timestamp=1448946829;
INSERT INTO sessions (id, a_session) VALUES('123sadas12dsadas', '--- \n_SESSION_ATIME: \'12311111\'\n_SESSION_CTIME: \'1448946718\'\n_SESSION_ID: 1231sadsadas\n_SESSION_REMOTE_ADDR: x.160.x.x\nip: x.160.x.x\nlasttime: \'1448946718\'\nsessiontype: anon\n') ON DUPLICATE KEY UPDATE a_session = '--- \n_SESSION_ATIME: \'1448946718\'\n_SESSION_CTIME: \'1448946718\'\n_SESSION_ID: 123sadasaaasadasa\n_SESSION_REMOTE_ADDR: x.160.x.x\nip: x.160.x.x\nlasttime: \'1448946718\'\nsessiontype: anon\n';
# User@Host: koha[koha] @  [x.160.x.x]
# Thread_id: 393762  Schema: kohadata  QC_hit: No
# Query_time: 0.000681  Lock_time: 0.000086  Rows_sent: 3  Rows_examined: 9
# Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0
SET timestamp=1448946829;
SELECT vs.shelfnumber, vs.shelfname FROM virtualshelves vs WHERE vs.category=2 ORDER BY vs.lastmodified DESC LIMIT 10;

my.cnf parameters;

max_connections         = 1000
max_connect_errors      = 1000000
connect_timeout         = 20
wait_timeout            = 600
max_allowed_packet      = 1G
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 32M
max_heap_table_size     = 32M
query_cache_limit               = 256K
query_cache_size                = 1M
innodb_log_file_size    = 2G
innodb_buffer_pool_size = 75G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size  = 256M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT
innodb_thread_concurrency = 8
innodb_read_io_threads = 32
innodb_write_io_threads = 16
innodb_purge_threads    = 4
innodb_buffer_pool_instances = 3
innodb_table_locks = 0
innodb_locks_unsafe_for_binlog = 1
log-queries-not-using-indexes = 1
  • What indexes do you have on the table? – Philᵀᴹ Dec 1 '15 at 12:08
  • for sessions we have primary key (id), for zebraqueue primarykey(id) and key lookup (server,biblio_auth_number,operation,done). All indexes are BTREE – Ahmet Melih Başbuğ Dec 1 '15 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.