Some of MySQL Performance Optimition Tips

1. Turn on trace on slow query

http://dev.mysql.com/doc/refman/4.1/en/slow-query-log.html

E.g. in my.ini
#minimum 1, default 10 seconds
long_query_time=3
#deprecated after 5.1.12
log-slow-queries=file.log
#as of 5.1.12, use this
#slow_query_log_file=file.log
#log the queries retrieving all the rows, not necessarily meaning no index existing on the table
log-queries-not-using-indexes
log=allquery.log
#binary log for replication setup
#log-bin=basename.ext
#max_binlog_size=1000000000
2. OPTIMIZE TABLE tblName
use it if large part of table have been deleted from the table. it reclaims the unused space and defragment the data file.

3. ANALYZE TABLE tbl1,tbl2...
It analyzes and stores the key distribution for a table.

4. SHOW INDEX FROM tbl;[\G]
list the indexes defined on the table. it also reports the cardinality(an estimate of the number of unique values in the index) of the index. The higher cardinality it is, the more likely the mysql will use the index.

5. high cardinality column should be indexed as left most index key
MySQL uses multiple-column indexes in such a way that queries are fast when you specify a known quantity for the first column of the index in a WHERE clause, even if you do not specify values for the other columns.

Page Split

It is a phenomenon only on table with clustered index.

When if happens, SQL Server tries to use 50/50 policy,which means half of data remains on old page and half of data goes to newly allocated page.

Non-clustered indexes won't be impacted by page split in version later than 7.0 because they use KEY insgead of RID to locate record.

the reason it can have page split is because table with clustered index is an index and its entries are stored as their sorting orders.

Checkpoint

In RDBMS, transactions will be concreted to log files the first, then periodically, the dirty data in memory for those committed transactions are written to data files. Such an operation is usually called checkpoint.

In SQL Server, this happens approximately every minute.

To explicitly request a checkpoint, CHECKPOINT command can be issued.

Other operations, such as database  backup, will also trigger checkpoint.

To record checkpoint event in error log, trace flag 3502 can be enabled.

Checkpoint is captured in log file. It marks the start point of rolling forward operation (replay committed transactions) during database starting up. Uncimmitted transactions after the last checkpoint will be rolled back.

SELECT INTO vs INSERT SELECT

In the situation of moving data around in the database, there are two common methods usually adapted in the circle.

Method 1: CREATE and INSERT

CREATE TABLE targetTbl (
xxx
xxx
xxx
);

INSERT INTO targetTbl (xxx)
SELECT xxx
FROM source
WHERE xxx;

Method 2: SELECT INTO

SELECT XXX
INTO targetTbl
FROM source
WHERE xxx;

Which way is better? It depends on the recovery mode set on the database. For both SIMPLE and BULK-LOGGED mode, since SELECT INTO is a bulk operation, it logs minimal information, so that SELECT INTO is more efficient than INSERT method.

What about the comparison under FULL recovery mode? They are about the same.

Observe Locking Behaviour with SQL Profiler

Setup

 Assuming you have created test table according to definition in a previous post Observing Locking Behaviour With Extended Events, now launch SQL Server Profiler and define a trace that captures Lock:Acquired and Lock:Released. To avoid noises in the captured events, it's better to only capture events from the interested database and filter out the locking events upon the database.

After creating the tace, it can be exported as tracing script so that you can reuse it later.

Then the trace can be started to trace any statement you want to experiment.

Below is a screen shot of monitored locking events on "select c1,c2 from test".

Control the Tracing

When starting a server side SQL Trace with the script exported from the SQL Profiler, it's better to also know these tools to control the trace and view the captured events.

To abserve the tracing informaiton in the system,
in SQL 2000, use
    SELECT * FROM ::fn_trace_getinfo(default),
in SQL 2005 and above, use
    SELECT * FROM  fn_trace_getinfo (default)
    SELECT * FROM sys.traces
To start(1), stop(0) and close(2) a trace, use
exec sp_trace_setstatus @traceId, 2

To get data from tracing file, in SQL 2000, use ::fn_trace_gettable.
    SELECT * into xxx200 FROM ::fn_trace_gettable('c:\temp\mytrace.trc', default)

In SQL 2005 and above, use sys.fn_trace_gettable.
    SELECT * INTO temp_trc FROM fn_trace_gettable('c:\temp\mytrace.trc', default);

Observe Insert Operation

Start trace and Execute this statement:

insert into test default values

This is the events being captured.

We can see it clearly that it puts IX lock on table the first, then IX lock on the page where the record is to be inserted, then X lock on the row where it ends up, then everything goes a backward order, the X lock on the row is released, then the IX on the page is released, at last the IX on the table is released.

Further checking the page content, we can verify the 1:55:4 contains the data just being inserted.

DBCC traceon(3604)
go
dbcc page (19,1,55,3) 
Slot 4 Offset 0xba Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 15

Memory Dump @0x0000000012D5A0BA

0000000000000000:   10000c00 05000000 40218cb5 030006††††........@!Œµ... 

Slot 4 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 5                              

Slot 4 Column 2 Offset 0x0 Length 0 Length (physical) 0

c1 = [NULL]                         

Slot 4 Column 3 Offset 0x0 Length 0 Length (physical) 0

c2 = [NULL]                         

Observe Delete Operation

Start tracing and execute this statement.
DELETE FROM test where id=5

Since there's no index created for this table yet, a table scan happens. Below is the captured events.

We can see an IX lock is put on table the first, then it puts IU lock on the page it is accessing, then it puts and releases U locks while searching through each slot in the page, until it finds the slot 4 that contains the row to be deleted. It then changes the IU lock on the page to IX lock, and puts an X lock on the targeted row. It then removes the row. After that, it continues its searching until the end of the table. After the expected operation is done, it goes backward to release the lock gained. It released the X lock on slot 4, then IX lock on page, IX lock on table.

Delete with Clustered Index

Define a clustered primary key on the table:
Alter table test add constraint PK_test_id primary key clustered (id)
After this, the table will be moved to different pages.

Start tracing and issue this statement:

DELETE FROM test where id=3

Oops, it has much more events happening than the last test. An example that table scan is more efficient than the index seek on a small table. I won't post the result here because the tool has been provided.

Major differences are
1. U lock no longer being observed, S lock is used to search the data
2. RID look-up is replaced with Key look-up
3. Index metadata retrieving is extra operation
4. Index operation is extra operation

Exported Tracing Script

Below is a trace script generated by the SQL Profiler for SQL Server 2005 and above.


/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 12/05/2012  12:24:57 AM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 24, 32, @on
exec sp_trace_setevent @TraceID, 24, 56, @on
exec sp_trace_setevent @TraceID, 24, 1, @on
exec sp_trace_setevent @TraceID, 24, 57, @on
exec sp_trace_setevent @TraceID, 24, 3, @on
exec sp_trace_setevent @TraceID, 24, 51, @on
exec sp_trace_setevent @TraceID, 24, 4, @on
exec sp_trace_setevent @TraceID, 24, 12, @on
exec sp_trace_setevent @TraceID, 24, 22, @on
exec sp_trace_setevent @TraceID, 60, 32, @on
exec sp_trace_setevent @TraceID, 60, 56, @on
exec sp_trace_setevent @TraceID, 60, 1, @on
exec sp_trace_setevent @TraceID, 60, 57, @on
exec sp_trace_setevent @TraceID, 60, 3, @on
exec sp_trace_setevent @TraceID, 60, 51, @on
exec sp_trace_setevent @TraceID, 60, 4, @on
exec sp_trace_setevent @TraceID, 60, 12, @on
exec sp_trace_setevent @TraceID, 60, 22, @on
exec sp_trace_setevent @TraceID, 23, 32, @on
exec sp_trace_setevent @TraceID, 23, 56, @on
exec sp_trace_setevent @TraceID, 23, 1, @on
exec sp_trace_setevent @TraceID, 23, 57, @on
exec sp_trace_setevent @TraceID, 23, 22, @on
exec sp_trace_setevent @TraceID, 23, 3, @on
exec sp_trace_setevent @TraceID, 23, 51, @on
exec sp_trace_setevent @TraceID, 23, 4, @on
exec sp_trace_setevent @TraceID, 23, 12, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
--Set the database id here
set @intfilter = 19
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter
--filter out database level event
set @intfilter = 2
exec sp_trace_setfilter @TraceID, 57, 0, 1, @intfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

--stop the tracing
exec sp_trace_setstatus @TraceID, 0
--close the tracing
exec sp_trace_setstatus @TraceID, 2

--load trace information into table for further analysis
--SELECT * INTO temp_trc FROM fn_trace_gettable('c:\temp\mytrace.trc', default);

Over Clause vs Group By Clause

It used to determine the partitioning and ordering of a row set before the associated window function is applied. The window function includes Ranking functions, Aggregation functions, Analytic functions etc.

In SQL Server 2012, You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results. Before that, only Ranking function can work with Order By.

(Order By in OVER happens before the ORDER BY clause in a SELECT statement)

Aggregation functions working with GROUP BY aggregates the result set according to the columns in GROUP BY, and the result set is presented according to GROUP BY.

OVER works with a window of result set, it provides the running aggregations over the window and for each of the row in the window. It replaces what we have to do with sub-queries and it is more efficient than sub-queries.

GO [count]

This a reminder that you can run the batch for [count] times. if each column allows null, you can also specify default values to quickly prepare some test data.

insert into test default values;
go 10

Maximum Memory in SQL Server 2008