content_image/785347.jpg
## 8.1 Optimization Overview 

Database performance depends on several factors at the database level, such as tables, queries, and configuration settings. These software constructs result in CPU and I/O operations at the hardware level, which you must minimize and make as efficient as possible. As you work on database performance, you start by learning the high-level rules and guidelines for the software side, and measuring performance using wall-clock time. As you become an expert, you learn more about what happens internally, and start measuring things such as CPU cycles and I/O operations. 

Typical users aim to get the best database performance out of their existing software and hardware configurations. Advanced users look for opportunities to improve the MySQL software itself, or develop their own storage engines and hardware appliances to expand the MySQL ecosystem. 

## Optimizing at the Database Level 

•Optimizing at the Database Level 

•Optimizing at the Hardware Level 

•Balancing Portability and Performance 

The most important factor in making a database application fast is its basic design: 

•Are the tables structured properly? In particular, do the columns have the right data types, and does each table have the appropriate columns for the type of work? For example, applications that perform frequent updates often have many tables with few columns, while applications that analyze large amounts of data often have few tables with many columns. 

•Are the right indexes in place to make queries efficient? 

•Are you using the appropriate storage engine for each table, and taking advantage of the strengths and features of each storage engine you use? In particular, the choice of a transactional storage engine such as InnoDB  or a nontransactional one such as MyISAM  can be very important for performance and scalability. 

<img src='content_image/785347.jpg'>

## Note 

InnoDB  is the default storage engine for new tables. In practice, the advanced InnoDB  performance features mean that InnoDB  tables often outperform the simpler MyISAM  tables, especially for a busy database. 

•Does each table use an appropriate row format? This choice also depends on the storage engine used for the table. In particular, compressed tables use less disk space and so require less disk I/O to read and write the data. Compression is available for all kinds of workloads with InnoDB  tables, and for read- only MyISAM  tables. 

•Does the application use an appropriate locking strategy? For example, by allowing shared access when possible so that database operations can run concurrently, and requesting exclusive access when appropriate so that critical operations get top priority. Again, the choice of storage engine is significant. The InnoDB  storage engine handles most locking issues without involvement from you, allowing for better concurrency in the database and reducing the amount of experimentation and tuning for your code. 

•Are all memory areas used for caching sized correctly? That is, large enough to hold frequently accessed data, but not so large that they overload physical memory and cause paging. The main memory areas to configure are the InnoDB  buffer pool and the MyISAM  key cache. 

