Improving Mysql Performance

Improving MySQL Performance

MySQL responding slowly

Major Hayden Version 1.0 - February 2009

Find and correct source of server load

No

Load caused by MySQL?

Yes

Server under high load?

No

Network

Enable compression, upgrade NIC/ switch, reduce result set size

Slow queries in the process list?

I/O

CPU

Type of load

Adjust buffer cache, query cache, table cache, thread cache

Reads

Mostly reads or writes?

No

Set reverse DNS record or add client to hosts file on database server

Yes

Kill slow queries, enable slow query logging and use EXPLAIN to improve queries

Yes

Verify that your application or application server is not the source of the slow responses

Yes

Consider an alternative backup method, such as LVM snapshots or replication

Yes or N/A

Yes

Adjust thread cache, remove complex calculations from queries

Reverse DNS set for client server?

No

Writes

Static application content loading slowly?

Consider using InnoDB, use noatime, get faster storage

No

Consider memcache, MySQL replication, additional hardware, reduce the number of queries and result set size, optimize table structures and joins A DBA could provide a deeper analysis.

Look for ways to reduce database load or database queries in general

No

Backups in progress?