EE' Website

Transcript

1. Performance Analysis
2. The aim of this document is to present a plan to identify and resolve the performance issues that are currently affecting the EE Web Site and Online Shop. This is a generic plan that may change significantly once applied in the environment of this application. However, it may serve as a general guideline to face the performance tuning of any MySQL database.
3. HW Resources DB Metrics / Performance DB Configuration DB Architecture DB Administration HW Performance Action Plan
4. In this phase we will identify the most important components of the hardware resources as: • Type and number of CPU's • Available memory • I/O subsystem details (Raid?) • Machine architecture • Operating System • Type of Environment Duration: 1/2 day
5. In this phase we will find KPI (key performance indicators) that occur at hardware level. We are specially interested in: • CPU’s utilization (percentage and use distribution) • Memory usage • Swap memory usage • Processes running on the server • CPU % used by each process • Disk utilization • CPU utilization of mysqld Duration: 1/2 day
6. We will now find the KPI at database level. We basically want to know the indicators mentioned below during a complete period (i.e: 1 day) • Database Size • Table space utilization (fragmentation) • Average of TPS (transactions per second) during the day • Buffers and cache utilization • Index Statistics • Slow Queries • Temporary tables
7. • Queries not using indexes • Concurrency and locking • Threads utilization • I/O synchronization • Innodb checkpoint operations • Utilization of indexes Duration: 3 days
8. • Innodb configuration • Buffering • Flushing method • Concurrency and locking • Threads utilization • I/O synchronization and checkpoint operations • Log generation and rotation Duration: 3 days Determine best configuration values for the database. All variables should be checked to detect missing or inconsistent values. The variables listed below are specially important for DB performance.
9. • Storage engines utilization • Appropriate data types for each column • Table relations (Foreign Keys) • Collations and Character Sets • Column Sizes • Normalization • Indexes utilization Duration: 3 days Analize the Database Architecture to identify possible impact on performance. We will pay special attention to:
10. • Backup procedures • Disaster recovery and contingency plans • Security procedures • Clustering and Replication Controls • Monitoring • Logs rotation and Archiving Duration: 5 days Check that critical administration tasks are performed following best practices.
11. All the previous slides are the basic steps to identify the scope of the performance tuning plan to be implemented in production. Although I can foresee the indicators to be checked, I can not anticipate the amount and nature of the tasks to be performed as they will depend on the results of the first weeks of research. In addition, any change must be tested in a UAT environment before the implementation, making the process even longer . Generically speaking, I think that this process can take 4 or 5 months of work.
12. The most important challenge of the project is to maintain the service up and running while the database is subject to downtime due to configuration and architectural changes. The impact on the service will be significantly minimized by performing the steps listed below. • Setup a secondary production environment. • Install Load Balancers to control data traffic. • Setup a failover mechanism to readdress data traffic. These changes will also be the initial setup of a future High Availability environment.
13. There is a risk of loosing data due to the continuous changes that will affect the database during this period. This is difficult to justify to users of an eCommerce site, so that to eliminate it we must setup a consistent backup policy to allow a PITR (point-in-time-recovery) at any time. The backup of the database should always be supplemented by a continuous backup of the binary logs, so that we can recover any transaction that has not been replicated by the DBMS.
14. The tools required by this project are listed below. • Hardware to setup a second production environment. • GUI administration program (Heidi, phpMyAdmin or similar). • Data modeller (Navicat or similar). • Database documentation tool (DBScribe or similar). • Database Load Balancer (HA Proxy or similar). • Set of scripts to manage failover between masters. Some of these tools are open source whilst some others are commercial.
15. The project will be divided into phases to be performed in a sequential order. The scope and duration of each phase will depend on the results of the previous one, so that, to avoid confusion and continuous changes, the following deliverables will be delivered. • A generic project plan to be expanded as the project progresses. • A detailed report at the end of each phase. • A summary of all tasks performed at the end of the project.