mysql genlog 分析_SQL - MySQL Server 日常維護: Log 分析(mysqlsla)

Reading slow log'slow-log'.160 total queries,21 unique.DatabasesforUnknown:example

Sorting by't'.__ 001 _______________________________________________________________________

Count:75(46%)Time(seconds):1449 total,19.32 avg,12 to 26 max

95%of Time:1348 total,18.99 avg,12 to 25 max

Lock(seconds):445 total,5.93 avg,0 to 13 max

Rows sent:247 avg,92 to 250 max

Rows examined:39787 avg,27136 to 53228 max

User:example[example]@localhost/ (76%)Database:example

Rows(EXPLAIN):24494 produced,24494 read

EXPLAIN:id:1

select_type:SIMPLE

table:SAMPLE

type:ref

possible_keys:SAMPLE

key:SAMPLE

key_len:4

ref: const,constrows:24494

Extra:Using where;Using filesort

Query1..........__ 002 _______________________________________________________________________

Count:4(2%)Time(seconds):659 total,164 avg,164 to 166 max

95%of Time:493 total,164 avg,164 to 165 max

Lock(seconds):0 total,0 avg,0 to 0 max

Rows sent:53871729 avg,53792839 to 53954842 max

Rows examined:53871729 avg,53792839 to 53954842 max

User:example[example]@localhost/ (23%)Database:example

Rows(EXPLAIN):54003520 produced,54003520 read

EXPLAIN:id:1

select_type:SIMPLE

table:SAMPLE

type:ALL

possible_keys:key:key_len:ref:rows:54003520

Extra:Query2..........__ 003 _______________________________________________________________________

Count:30(18%)Time(seconds):412 total,13.73 avg,6 to 29 max

95%of Time:355 total,12.68 avg,6 to 28 max

Lock(seconds):0 total,0 avg,0 to 0 max

Rows sent:20475 avg,3889 to 77241 max

Rows examined:20475 avg,3889 to 77241 max

User:example[example]@localhost/ (76%)Database:example

Rows(EXPLAIN):27060 produced,27060 read

EXPLAIN:id:1

select_type:SIMPLE

table:SAMPLE

type:ref

possible_keys:SAMPLE

key:SAMPLE

key_len:3

ref: constrows:27060

Extra:Query3..........__ 004 _______________________________________________________________________

Count:20(12%)Time(seconds):219 total,10.95 avg,10 to 12 max

95%of Time:207 total,10.89 avg,10 to 12 max

Lock(seconds):0 total,0 avg,0 to 0 max

Rows sent:0 avg,0 to 1 max

Rows examined:1077422 avg,1076385 to 1078557 max

User:example[example]@localhost/ (23%)Database:example

Rows(EXPLAIN):1078830 produced,3236490 read

EXPLAIN:id:1

select_type:PRIMARY

table:SAMPLE

type:index

possible_keys:SAMPLE

key:SAMPLE

key_len:8

ref:rows:1078830

Extra:Using where;Using index;Using temporary;Using filesort

id:3

select_type:DEPENDENT SUBQUERY

table:SAMPLE

type:ref

possible_keys:SAMPLE

key:SAMPLE

key_len:2

ref: constrows:1

Extra:Using where

id:2

select_type:DEPENDENT SUBQUERY

table:SAMPLE

type:unique_subquery

possible_keys:PRIMARY,forumid

key:PRIMARY

key_len:4

ref:func

rows:1

Extra:Using index;Using where

Query4..........__ 005 _______________________________________________________________________

Count:4(2%)Time(seconds):75 total,18.75 avg,16 to 22 max

95%of Time:53 total,17.67 avg,16 to 19 max

Lock(seconds):0 total,0 avg,0 to 0 max

Rows sent:1077174 avg,1076128 to 1078321 max

Rows examined:1077174 avg,1076128 to 1078321 max

User:example[example]@localhost/ (23%)Database:example

Rows(EXPLAIN):1078830 produced,1078830 read

EXPLAIN:id:1

select_type:SIMPLE

table:SAMPLE

type:ALL

possible_keys:key:key_len:ref:rows:1078830

Extra:Query5..........__ 006 _______________________________________________________________________

Count:4(2%)Time(seconds):40 total,10 avg,10 to 10 max

95%of Time:30 total,10.00 avg,10 to 10 max

Lock(seconds):0 total,0 avg,0 to 0 max

Rows sent:2207544 avg,2205402 to 2209884 max

Rows examined:2207544 avg,2205402 to 2209884 max

User:example[example]@localhost/ (23%)Database:example

Rows(EXPLAIN):2211096 produced,2211096 read

EXPLAIN:id:1

select_type:SIMPLE

table:SAMPLE

type:ALL

possible_keys:key:key_len:ref:rows:2211096

Extra:Query6..........__ 007 _______________________________________________________________________

Count:4(2%)Time(seconds):36 total,9 avg,9 to 9 max

95%of Time:27 total,9.00 avg,9 to 9 max

Lock(seconds):0 total,0 avg,0 to 0 max

Rows sent:0 avg,0 to 0 max

Rows examined:1827 avg,1826 to 1828 max

User:example[example]@localhost/ (23%)Database:example

Rows(EXPLAIN):3656396 produced,3658344 read

EXPLAIN:id:1

select_type:PRIMARY

table:SAMPLE

type:ref

possible_keys:SAMPLE

key:SAMPLE

key_len:2

ref: constrows:1948

Extra:Using where

id:2

select_type:DEPENDENT SUBQUERY

table:SAMPLE

type:ref

possible_keys:SAMPLE

key:award_id

key_len:2

ref: constrows:1877

Extra:Using where

Query7..........__ 008 _______________________________________________________________________

Count:2(1%)Time(seconds):36 total,18 avg,13 to 23 max

95%of Time:13 total,13.00 avg,13 to 13 max

Lock(seconds):6 total,3 avg,1 to 5 max

Rows sent:250 avg,250 to 250 max

Rows examined:45386 avg,40636 to 50136 max

User:example[example]@localhost/ (76%)Database:example

Rows(EXPLAIN):24494 produced,24494 read

EXPLAIN:id:1

select_type:SIMPLE

table:SAMPLE

type:ref

possible_keys:SAMPLE

key:SAMPLE

key_len:4

ref: const,constrows:24494

Extra:Using where;Using filesort

Query8..........__ 009 _______________________________________________________________________

Count:3(1%)Time(seconds):30 total,10 avg,6 to 12 max

95%of Time:18 total,9.00 avg,6 to 12 max

Lock(seconds):1 total,0.33 avg,0 to 1 max

Rows sent:0 avg,0 to 0 max

Rows examined:0 avg,0 to 0 max

User:example[example]@localhost/ (76%)Database:Unknown

Rows(EXPLAIN):EXPLAIN error:Not a SELECT statement.EXPLAIN:EXPLAIN error:Not a SELECT statement.Query9..........__ 010 _______________________________________________________________________

Count:3(1%)Time(seconds):21 total,7 avg,6 to 8 max

95%of Time:13 total,6.50 avg,6 to 7 max

Lock(seconds):0 total,0 avg,0 to 0 max

Rows sent:143522 avg,121334 to 187844 max

Rows examined:143522 avg,121334 to 187844 max

User:example[example]@localhost/ (76%)Database:example

Rows(EXPLAIN):141085 produced,141085 read

EXPLAIN:id:1

select_type:SIMPLE

table:SAMPLE

type:ref

possible_keys:SAMPLE

key:SAMPLE

key_len:3

ref: constrows:141085

Extra:Using index

Query10..........