Re: Question on slow queries
Rick James Wrote:
-------------------------------------------------------
> Well, let's study the third query -- UPDATE users.
> It's averaging 364ms; it might be possible to
> improve it.
>
> Please provide the full statement, the SHOW CREATE
> TABLE, and the size of the table.
>
> COMMIT is misleading, since we can't see what the
> transaction was doing.
>
> Also provide
> SHOW VARIABLES LIKE 'innodb%';
>
> How big are all the InnoDB tables combined? Is it
> bigger than 640M? How much RAM is available?
Thank you. Here is the info:
---
FULL Statement data
# Query 3: 0.03 QPS, 0.01x concurrency, ID 0x06BA864C75EF674B at byte 379840958
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.96 [1.0], V/M = 1.33
# Query_time sparkline: | __^_ |
# Time range: 2013-05-29 19:51:16 to 2013-05-30 09:52:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1452
# Exec time 5 526s 5ms 9s 362ms 1s 695ms 163ms
# Lock time 0 46ms 23us 117us 31us 47us 8us 28us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 1.42k 1 1 1 1 0 1
# Query size 0 111.98k 75 80 78.98 76.28 0.33 76.28
# String:
# Databases melive_me
# Hosts localhost
# Users melive_me
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ###
# 10ms #################
# 100ms ################################################################
# 1s #######
# 10s+
# Tables
# SHOW TABLE STATUS FROM `melive_me` LIKE 'users'\G
# SHOW CREATE TABLE `melive_me`.`users`\G
UPDATE `users` SET `logins` = 98, `last_login` = 1369869535 WHERE `id` = 18570\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select `logins` = 98, `last_login` = 1369869535 from `users` where `id` = 18570\G
SHOW CREATE TABLE
CREATE TABLE `users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(127) NOT NULL,
`username` varchar(32) NOT NULL DEFAULT '',
`password` char(50) NOT NULL,
`status` varchar(20) NOT NULL,
`activationtoken` varchar(25) NOT NULL,
`logins` int(10) unsigned NOT NULL DEFAULT '0',
`last_login` int(10) unsigned DEFAULT NULL,
`created` int(10) unsigned NOT NULL,
`ipaddress` varchar(15) DEFAULT NULL,
`nationality` varchar(2) DEFAULT '--',
`language` varchar(25) DEFAULT NULL,
`multi_status` varchar(25) NOT NULL,
`multi_note` varchar(512) NOT NULL,
`doubloons` int(11) NOT NULL DEFAULT '0',
`gracedate` int(10) unsigned DEFAULT NULL,
`newsletter` varchar(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_username` (`username`),
UNIQUE KEY `uniq_email` (`email`),
KEY `u_ipaddress` (`ipaddress`)
) ENGINE=InnoDB AUTO_INCREMENT=18984 DEFAULT CHARSET=utf8
SHOW VARIABLES LIKE 'innodb%'
Variable_name Value
innodb_adaptive_flushing ON
innodb_adaptive_hash_index ON
innodb_additional_mem_pool_size 20971520
innodb_autoextend_increment 8
innodb_autoinc_lock_mode 1
innodb_buffer_pool_instances 1
innodb_buffer_pool_size 671088640
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 ON
innodb_flush_log_at_trx_commit 1
innodb_flush_method
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 134217728
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 2
innodb_thread_sleep_delay 10000
innodb_use_native_aio ON
innodb_use_sys_malloc ON
innodb_version 5.5.31
innodb_write_io_threads 4
Total size of INNODB tables (all db): 411 MB
RAM: I am on a virtual server, 2GB RAM.