Tuning MySQL Performance
MySQL Level I Services:
Recommended for all installations. We will collect data on the
production system, analyze it for bottlenecks and inefficiencies,
identify index duplications which slow data retrieval, and report
our findings and suggestions. Procedure:
MySQL Procedure:
- Enable the MySQL slow query log to collect data on your
system. Reports are taken on the live server, with normal traffic
loads, for 3-5 business days, or at least through all types of
business activities (low traffic, high traffic, abnormal
traffic).
- Analyze the data collected to identify poorly performing
SQL statements.
- Analyze server configuration variables and make
recommendations to properly balance operational and memory settings
for your unique application workloads.
- Key Buffer Sizing
- InnoDB Buffer Sizing
- Sorting and Index Effectiveness
- Join Effectiveness
- Query Cache Usage and Sizing
- Checking the system for Table Locking
- Temporary Table Usage
- Search for duplicate Indexes, to enable balancing speed
of data retrieval versus data maintenance.
- Tuning MySQL Performance
MySQL Level I Deliverables:
- A brief report summarizing initial findings.
- A suggested configuration file (my.cnf) which can be
installed to improve efficiency.
- A list of distinct SQL statements that are performing
poorly and should be analyzed further.
- A MySQL script to use for removal of duplicate
indexes.
MySQL Level II Services:
Recommended when performance has degraded or does not meet
expectations, or when the client has identified a problem. The
complete Level I service will first be done, than deeper analysis
and optimization will be performed. We will make recommendations on
tuning the production environment from the hardware level, tuning
schema on the internal architecture, and tuning indexing to meet
your unique application workload.
MySQL Procedures:
- Complete discussion of database environment, application
functionality, and potential performance bottlenecks with the
client to properly ascertain problem areas and focus the
service.
- Complete hardware analysis (RAID, disk layout, memory
analysis)
- Analysis of slow-query results and recommendation on
alteration of schema to provide better performance for the actual
workload seen.
- Examination of all tables to look for performance issues
in types and lengths of columns used, check for linked table
indexes, etc.
- InnoDB tuning and analysis, including optimum performance
from configuration of InnoDB memory structures and the use of
InnoDB engine to increase performance in highly transactional
tables.
MySQL Level II Deliverables:
We strive to provide everything needed for you to solve
performance issues.
- The Level I deliverables.
- Recommendations on how to tune your production
environment from the hardware level.
- Recommendations on tuning your schema in terms of its
internal architecture and your workload to make the most effective
use of your indexing.
MySQL Level III Services:
Once Level I and Level II analysis and evaluations are complete,
clients may elect to have us implement the recommendations.
Alternatively, a client may elect to assign specific tasks to be
performed on an hourly basis.
MySQL Database Services:
- Extracting Application Processes and re-writing
application code into efficient stored procedures running with
MySQL
- Developing Backup and Recovery strategies; determining
SLA requirements for failure response
- Devising and tuning Replication strategies
- Setting up Slave nodes for failover scenarios
- Implementing read-only replicated copies of the
production systems to ease load on transactional systems by
isolating Reporting or Batch processes from normal production
loads
- Balancing server usage across development, quality
assurance, and production environments
- Implementing appropriate Change Control Processes
procedures and formalizing a code release process to promote
application stability, efficiency, and prevent design errors from
reaching production environments.
- Formalizing documentation, allowing development staff to
function from a common Entity Relationship Diagram
(ERD)
- Provides Tuning MySQL Performance in database and inspect
stored procedures
