[Mysql 5.5.30] Slow simple UPDATE query - "query end" and context switching

[Lech Groblewicz]

Hello,

I have problem with slow UPDATE queries. Every couple of minutes some similar update queries take more than 2 seconds. In where clause there is only PK check, there shouldn't be any queries locking the whole table.

Sample query looks as follows:

UPDATE `users` set last_portal_login = NOW(), ip = 1464635301, browser = "chrome" where uid = 2021

Table structure:

CREATE TABLE users (
  uid int(10) unsigned NOT NULL,
  login char(50) COLLATE utf8_polish_ci NOT NULL,
  hide_me tinyint(1) unsigned NOT NULL DEFAULT '0',
  addon int(10) unsigned NOT NULL DEFAULT '0',
  protocol float unsigned NOT NULL DEFAULT '0',
  mode enum('firefox','lite','chrome','opera') COLLATE utf8_general_ci DEFAULT NULL,
  browser enum('firefox','chrome','opera','ie','safari') COLLATE utf8_general_ci DEFAULT NULL,
  last_login datetime DEFAULT NULL,
  last_portal_login datetime DEFAULT NULL,
  ip int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (uid),
  UNIQUE KEY login (login)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci$$

Table contains ~2000 rows.

It gets stuck on "query end" phase with high Context_voluntary value (200+) according to SHOW PROFILE ALL command.

Mysql version: 5.5.30 dotdeb.org

What can I do to fix this?

PS:
DBMS works on VPS with 768M RAM burstable to 1.5G
InnoDB configuration:

'innodb_adaptive_flushing', 'ON'
'innodb_adaptive_hash_index', 'ON'
'innodb_additional_mem_pool_size', '8388608'
'innodb_autoextend_increment', '8'
'innodb_autoinc_lock_mode', '1'
'innodb_buffer_pool_instances', '1'
'innodb_buffer_pool_size', '268435456'
'innodb_change_buffering', 'all'
'innodb_checksums', 'ON'
'innodb_commit_concurrency', '0'
'innodb_concurrency_tickets', '500'
'innodb_data_file_path', 'ibdata1:10M:autoextend'
'innodb_data_home_dir', ''
'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', 'OFF'
'innodb_flush_log_at_trx_commit', '2'
'innodb_flush_method', 'O_DIRECT'
'innodb_force_load_corrupted', 'OFF'
'innodb_force_recovery', '0'
'innodb_io_capacity', '200'
'innodb_large_prefix', 'OFF'
'innodb_lock_wait_timeout', '50'
'innodb_locks_unsafe_for_binlog', 'OFF'
'innodb_log_buffer_size', '8388608'
'innodb_log_file_size', '67108864'
'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', 'OFF'
'innodb_purge_batch_size', '20'
'innodb_purge_threads', '0'
'innodb_random_read_ahead', 'OFF'
'innodb_read_ahead_threshold', '56'
'innodb_read_io_threads', '4'
'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', '4'

SHOW TABLE STATUS:

+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
| Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation      | Checksum | Create_options | Comment |
+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
| InnoDB |      10 | Compact    | 2241 |            124 |      278528 |               0 |       245760 |  88080384 |           NULL | 2013-04-14 23:32:21 | NULL        | NULL       | utf8_polish_ci |     NULL |           |         |
+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+
1 row in set (0.06 sec)

Edited 3 time(s). Last edit at 04/15/2013 08:58AM by Lech Groblewicz.