MySQL Forums
Forum List  »  Performance

Re: Question on slow queries
Posted by: Michele Quaini
Date: May 31, 2013 06:09AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2528
May 30, 2013 04:18AM
998
May 31, 2013 05:14AM
Re: Question on slow queries
980
May 31, 2013 06:09AM
973
June 01, 2013 02:45PM
1203
June 04, 2013 02:35AM
1289
June 05, 2013 11:27PM
815
June 06, 2013 02:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.