What resources exist for Database performance-tuning?

Mysql Sql Server Database Oracle Postgresql

Mysql Problem Overview

What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area?

The idea of this question is to collect the shed load of resources that invariably exist, so that people can have a "one stop" knowledge shop of the good, peer approved resources.

General SQL

PostgreSQL (wiki) (PGsearch)

MySQL

Oracle

MS SQL Server

Sybase SQL Anywhere

JDBC

JDBC Performance Tips

Mysql Solutions

Solution 1 - Mysql

Oracle's very own Tom Kyte has a fantastic repository on every type of performance problem imaginable on http://asktom.oracle.com. He usually takes the time to recreate specific problems and gives very detailed explanations.

Solution 2 - Mysql

This guy's answer to a not-the-same-inquiry is probably a good start.

https://stackoverflow.com/questions/368858/hidden-features-of-mysql/600830#600830

Solution 3 - Mysql

And something for PostgreSQL: "Performance Optimization" at the official wiki.

Solution 4 - Mysql

If you are using an Oracle database, this guide may also help. http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/toc.htm

Solution 5 - Mysql

For MySQL, the performance tuning 'bible' is High Performance MySQL

Solution 6 - Mysql

Quick PostgreSQL Optimization (query optimizing)

Short read, explains a lot of things well and 'works' a real example which is nice for those of us that learn better that way.

After seeing the wiki link to PostgreSQL, figured I'd edit this post with links for mysql/oracle docs, not really an optimization guides specifically but both are good resources, especially the mysql one. For optimization and any other tuning features.

Solution 7 - Mysql

SO has a good one here: How do you interpret a query’s explain plan?

Solution 8 - Mysql

A lot of good MySQL specific tips can be found at http://www.mysqlperformanceblog.com/

Solution 9 - Mysql

I would add that besides having your database theoretically tuned, you should profile your application using a profiler that tracks SQL calls.

Despite your best intentions, a few bad calls will sneak into your application and will often cause 90% of your performance-related problems.

Solution 10 - Mysql

Book: Troubleshooting Oracle Performance (Antognini Christian)

Solution 11 - Mysql

If you are looking for SQL Server specific Performance tuning references there are an absolute shed load of quality resources available online, ranging from white papers on implementing specific technologies such as partitioning, to excellent Blogs that detail step by step instruction on how to performance tune a sql server platform.

Shameless plug follows: You can start you research by reviewing the performance tuning area of my personal Blog, or for any specific SQL Server requirements/issues feel free to fire me an email.

SQL Server Resources

Solution 12 - Mysql

SQL Server Performance Decent site for MSSQL specific info.

Solution 13 - Mysql

<http://explain.depesz.com/>

helps you interpret PostgreSQL's EXPLAIN ANALYZE output.

The whole Performance Tips chapter in the PostgreSQL docs is worth reading.

Solution 14 - Mysql

How to Identify Slow Running Queries with SQL Profiler is a good tutorial on how to go about identifying slow running queries. This will allow one to focus their attention where it is most needed.

Solution 15 - Mysql

"SQL Performance Tuning" http://books.google.com/books?id=3H9CC54qYeEC&dq=sql+performance+tuning&printsec=frontcover&source=bn&hl=en&ei=1dDoSYmjMOrlnQfX-bSYBw&sa=X&oi=book_result&ct=result&resnum=4 covers most of the major DBMS -- how to write high performing cross platform SQL queries, etc.

Solution 16 - Mysql

Xaprb is a must-read blog for MySQL DBAs. The author has written a book on high-performance MySQL

For the happy few working with Sybase SQL Anywhere I can only recommend Breck Carter's blog and his SQL Anywhere Studio 9 Developer's Guide

Solution 17 - Mysql

http://www.javaperformancetuning.com/tips/jdbc.shtml
http://www.google.com/search?q=database+performance+tuning

Solution 18 - Mysql

Here is another highly-regarded book that is platform-neutral:

Dan Tow's SQL Tuning: Generating Optimal Execution Plans

Contains some specific examples for Oracle, MS SQL, and IBM DB2, but the techniques involved should apply to other platforms, too.

Solution 19 - Mysql

For SQL Server, I primarily use:

Solution 20 - Mysql

I was pretty happy when I saw this way of quickly seeing what happened with a SQL statement you are tuning under Oracle. Change the first SQL statement below to your SELECT statement and keep that hint in there.

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM DUAL;

SELECT * FROM TABLE(dbms_xplan.display_cursor( NULL, NULL, 'RUNSTATS_LAST'))
;

PLAN_TABLE_OUTPUT
-----------------------------------------------------
SQL_ID  5z36y0tq909a8, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM DUAL

Plan hash value: 272002086

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS FULL| DUAL |      1 |      1 |      1 |00:00:00.02 |       3 |      2 |
---------------------------------------------------------------------------------------------


12 rows selected.

Where:

E-Rows is estimated rows.
A-Rows is actual rows.
A-Time is actual time.
Buffers is actual buffers.

Where the estimated plan varies from the actual execution by orders of magnitude, you know you have problems.

Solution 21 - Mysql

For people working with Oracle I recommend this link.............

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

From my experiences with Oracle database development, I have found that building up a knowledge of how to use SQL, how it works and knowing what is available (supplied functions, clauses that you didn't know existed or enhanced from the last version) means I spend a lot less time having to tune sql.

Solution 22 - Mysql

I'd start out by understanding how the database works at a fundamental level. How is data stored on disk, what does creating an index do, how does query plan optimization work, how are plans cached, when to cached plans expire.

If you can commit all that to memory, most of the advice about tuning seems obvious.

Here's a great book for MSSQL

SQL Server Internals

Solution 23 - Mysql

For Oracle, Cost-Based Oracle: Fundamentals by Jonathan Lewis.

Solution 24 - Mysql

Sometimes you need to know to how to fix the problem once it is identified. This will show ways to replace a badly performing cursor with a set-based operation: http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

It was specific to SQL Server but many of the techniques might transalte to other dbs as well.

Solution 25 - Mysql

For Microsoft SQL, I'd recommend the books by Kalen Delaney (et al) called "Inside SQL Server". They offer a good insight into the internals of SQL Server, thus allowing readers to educate themselves on why particular statements might be faster than others.

Inside SQL Server 7.0
Inside SQL Server 2000
Inside Microsoft SQL Server 2005
Microsoft SQL Server 2008 Internals

There's also a book dedicated to performance tuning of SQL Server 2008 queries: SQL Server Performance Tuning Distilled

I also like the blogs by Paul Randal and Kimberly Tripp on SQLSkills.com. They are full of solid SQL advice:

Paul's blog
Kimberly's blog

Solution 26 - Mysql

For SQL Server performance tuning, Itzik Ben-Gan is a legend.

You can find his many detailed books here, all with his usual style of empirical measurement to prove his case: http://tsql.solidq.com/books/index.htm

If you're searching for the fastest solution to a t-sql problem add the word 'itzik' to your google search term.

Itzik Ben-Gan has been mentioned over 600 times here on stackoverflow, but I couldn't believe it to find not a single mention of him here on this performance tuning question.

As an additional resource, you can also find some videos of Itzik talking about performance related stuff here on youtube.

Solution 27 - Mysql

Oracle sites

2 day performance tuning guide <http://docs.oracle.com/cd/E11882_01/server.112/e10822/toc.htm>
Performance Tuning Guide <http://docs.oracle.com/cd/E36909_01/server.1111/e16638/toc.htm>

Oracle books

Oracle Core: Essential Internals for DBAs and Developers by Jonathan Lewis
Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions by Thomas Kyte
SQL Tuning by Dan Tow
Oracle Database 11g Release 2 Performance Tuning Tips & Techniques (Oracle Press) by Richard Niemiec

Content Type	Original Author	Original Content on Stackoverflow
Question	Gavin Miller	View Question on Stackoverflow
Solution 1 - Mysql	Neil Kodner	View Answer on Stackoverflow
Solution 2 - Mysql	cgp	View Answer on Stackoverflow
Solution 3 - Mysql	Milen A. Radev	View Answer on Stackoverflow
Solution 4 - Mysql	Anthony	View Answer on Stackoverflow
Solution 5 - Mysql	nathan	View Answer on Stackoverflow
Solution 6 - Mysql	Louis	View Answer on Stackoverflow
Solution 7 - Mysql	Gavin Miller	View Answer on Stackoverflow
Solution 8 - Mysql	beetstra	View Answer on Stackoverflow
Solution 9 - Mysql	Nathan Voxland	View Answer on Stackoverflow
Solution 10 - Mysql	Thomas Aregger	View Answer on Stackoverflow
Solution 11 - Mysql	John Sansom	View Answer on Stackoverflow
Solution 12 - Mysql	Christopher Klein	View Answer on Stackoverflow
Solution 13 - Mysql	Stephen Denne	View Answer on Stackoverflow
Solution 14 - Mysql	Gavin Miller	View Answer on Stackoverflow
Solution 15 - Mysql	Matt Rogish	View Answer on Stackoverflow
Solution 16 - Mysql	Vincent Buck	View Answer on Stackoverflow
Solution 17 - Mysql	dankoliver	View Answer on Stackoverflow
Solution 18 - Mysql	BradC	View Answer on Stackoverflow
Solution 19 - Mysql	Brandon	View Answer on Stackoverflow
Solution 20 - Mysql	WW.	View Answer on Stackoverflow
Solution 21 - Mysql	Ian Carpenter	View Answer on Stackoverflow
Solution 22 - Mysql	rjdevereux	View Answer on Stackoverflow
Solution 23 - Mysql	Tony Andrews	View Answer on Stackoverflow
Solution 24 - Mysql	HLGEM	View Answer on Stackoverflow
Solution 25 - Mysql	SchmitzIT	View Answer on Stackoverflow
Solution 26 - Mysql	Dave Hilditch	View Answer on Stackoverflow
Solution 27 - Mysql	Deven	View Answer on Stackoverflow