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:
- 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.
- 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