I always thought of maintaining the checklist for MySQL Performance Optimization, This actually helps me to\u00a0draw the first line of action items and developing scope at advanced stages.<\/p>\n

Optimizing at the Database LevelThe most important factor in making a database application fast is its basic design:<\/p>\n

\n
• 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.<\/li>\n
• Are the right\u00a0indexes\u00a0in place to make queries efficient?<\/li>\n
• 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\u00a0\u00a0or a nontransactional one such as\u00a0\u00a0can be very important for performance and scalability.<\/li>\n
• 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\u00a0\u00a0tables, and for read-only\u00a0\u00a0tables.<\/li>\n
• Does the application use an appropriate\u00a0locking 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\u00a0\u00a0storage 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.<\/li>\n
• Are all\u00a0memory areas used for caching\u00a0sized 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\u00a0\u00a0buffer pool, the\u00a0\u00a0key cache, and the MySQL query cache.<\/li>\n<\/ul>\n

  Optimizing at the Hardware LevelAny database application eventually hits hardware limits as the database becomes more and more busy. A DBA must evaluate whether it is possible to tune the application or reconfigure the server to avoid these\u00a0bottlenecks, or whether more hardware resources are required. System bottlenecks typically arise from these sources:<\/p>\n

  \n
  • Disk seeks. It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk.<\/li>\n
  • Disk reading and writing. When the disk is at the correct position, we need to read or write the data. With modern disks, one disk delivers at least 10\u201320MB\/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks.<\/li>\n
  • CPU cycles. When the data is in main memory, we must process it to get our result. Having large tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem.<\/li>\n
  • Memory bandwidth. When the CPU needs more data than can fit in the CPU cache, main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one to be aware of.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n
    <\/div>\n","protected":false},"excerpt":{"rendered":"

    I always thought of maintaining the checklist for MySQL Performance Optimization, This actually helps me to\u00a0draw the first line of action items and developing scope at advanced stages. Optimizing at the Database LevelThe most important […]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,3,15],"tags":[5,6,16],"yoast_head":"\n