]]>
]]>

Log queries in memory

Syntax

Table schemas

I propose make log-queries-in-memory similar to slow query log, but decompose information about slow query log to different tables:

Table "QUERY_LOG"

Column_name Column_type Controling variables Comment
Event_id UNSIGNED LONGINT NOT NULL Unique event ID, assigned automatic for every new run

Table "SLOW_LOG"

Column_name Column_type Controling variables Comment
Event_id UNSIGNED LONGINT FOREIGH KEY(QUERY_LOG) Link to QUERY_LOG thought event_id
Time DATETIME log_slow_timestamp_every - enable or disable timestamp providing
User_host VARCHAR(X) TODO: investigate maximum lenght of User@Host
Thread_id UNSIGNED LONGINT NOT NULL
Schema VARCHAR(X) or May be better link to system tables of SCHEMA? But What about DML update of schemas?
FOREIGN KEY? TODO: Investigate what the maximum length of schema name.
Last_errno UNSIGNED INT
Killed TINYINT
Query_time FLOAT log_slow_verbosity=microtime - control precision of time (seconds only or with microseconds)
What about queries, what overflow? May be null?
LP bug 600360 612954
Lock_time See above (Query_time)
Rows_sent UNSIGNED LONGINT NOT NULL
Rows_examined UNSIGNED LONGINT NOT NULL
Rows_affected UNSIGNED LONGINT I see in code:
NOT NULL (?) ((long) thd→row_count_func > 0 ) ? (ulong) thd→row_count_func : 0
May be better null, if row not counted?
TODO: Investigate when (row_count_func == NULL)
Rows_read UNSIGNED LONGINT NOT NULL
Bytes_sent UNSIGNED LONGINT NOT NULL
Tmp_tables UNSIGNED LONGINT NOT NULL
Tmp_disk_tables UNSIGNED LONGINT NOT NULL
Tmp_table_sizes UNSIGNED LONGINT NOT NULL
Innodb_transaction_id LONGLONGINT InnoDB transaction ID
QC_Hit BOOLEAN log_slow_verbosity=query_plan Hit to query cache
Full_scan BOOLEAN log_slow_verbosity=query_plan
Full_join BOOLEAN log_slow_verbosity=query_plan
Tmp_table BOOLEAN log_slow_verbosity=query_plan
Tmp_table_on_disk BOOLEAN log_slow_verbosity=query_plan
Filesort BOOLEAN log_slow_verbosity=query_plan Multipass sort thought files
Filesort_on_disk BOOLEAN log_slow_verbosity=query_plan Multipass sort thought files on disk
Merge_passes UNSIGNED LONGINT log_slow_verbosity=query_plan Passes on merge on sort

Table "SLOW_LOG_INNODB"

Column_name Column_type Controling variables Comment
Event_id UNSIGNED LONGINT FOREIGH KEY(QUERY_LOG) Link to QUERY_LOG thought event_id
IO_read_op UNSIGNED LONGINT log_slow_verbosity=innodb InnoDB I/O read operations
IO_read_bytes UNSIGNED LONGINT log_slow_verbosity=innodb InnoDB I/O read bytes
IO_read_wait UNSIGNED LONGINT log_slow_verbosity=innodb InnoDB I/O read wait
Read_queue_lock_wait FLOAT log_slow_verbosity=innodb InnoDB I/O read queue lock wait (what is mean? what the queue?)
Read_queue_wait FLOAT log_slow_verbosity=innodb InnoDB I/O read queue wait (what is mean? what the queue?)
Distinct_pages_access UNSIGNED LONGINT log_slow_verbosity=innodb InnoDB distinct pages access (what the pages?)

Ideas

Related reading

* slow_extended patch.

* circular_query_buffer specification draft.

* Blueprint on Launchpad.

 
devplan/log_queries_in_memory.txt · Last modified: 2011/07/17 21:05 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
Sales
Europe
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054

 

Share This
]]> ]]>