{"id":58486,"date":"2023-12-05T17:05:45","date_gmt":"2023-12-05T11:35:45","guid":{"rendered":"https:\/\/www.tothenew.com\/blog\/?p=58486"},"modified":"2024-01-02T17:36:58","modified_gmt":"2024-01-02T12:06:58","slug":"mysql-perfomance-tuning","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/mysql-perfomance-tuning\/","title":{"rendered":"MySQL Perfomance Tuning"},"content":{"rendered":"

<\/h3>\n

Introduction<\/strong><\/h2>\n

MySQL is one of the most popular open-supply relational databases. As your application\u2019s statistics grow, MySQL\u2019s performance may be impacted by different factors, including database design, server configuration, and query performance. In this blog, we can talk about the various components of MySQL’s overall performance tuning and explore common situations to help you to optimize your MySQL database for the most suitable speed and performance.<\/p>\n

Assume you\u2019ve got a large e-commerce database. When someone attempts to fetch consumer-integrated or built-income-integrated, then he\/she can face performance issues if the database queries are not optimized.<\/p>\n

Debugging<\/h2>\n

a. Utilize Built-in Tools: <\/strong>Use some of MySQL\u2019s built-in tools, like the Slow Query Log or Performance Schema, to identify queries that take longer to execute.<\/p>\n

b. Monitor Query Metrics:<\/strong> Monitor the query execution time, query count, and other relevant metrics to pinpoint the problematic queries.<\/p>\n

Inspection of Query Execution<\/h2>\n

a<\/strong>. Use EXPLAIN<\/strong> to advantage perception into how the MYSQL optimizer executes select statements. It helps you to recognize how MYSQL procedures your queries, inclusive of index usage and capacity overall performance bottlenecks such as complete table scans, inefficient index utilization, or complex joins<\/p>\n

b<\/strong>. Example:<\/strong><\/p>\n

EXPLAIN SELECT * FROM users WHERE email = \u2018user@example.com<\/a>\u2019;<\/p>\n

Result<\/strong><\/p>\n

<\/figure>\n

id:<\/strong> A unique identifier for each selected operation within the query.<\/p>\n

select_type: <\/strong>The type of select operation. In this case, SIMPLE indicates a simple SELECT query.<\/p>\n

partitions:<\/strong> The partitions being accessed (if applicable).<\/p>\n

type:<\/strong> The join type or access method used. Here, the ref indicates a range scan using an index.<\/p>\n

possible_keys:<\/strong> The indexes that could potentially be used (email in this case).<\/p>\n

key: <\/strong>The index MySQL chooses to use (email in this case).<\/p>\n

ref:<\/strong> The columns used for joining or filtering (const indicates a constant value).<\/p>\n

rows:<\/strong> Estimated the number of rows that MySQL needs to examine.<\/p>\n

filtered:<\/strong> The percentage of rows that are filtered out by the condition.<\/p>\n

Extra:<\/strong> Additional information about the execution plan (Using index indicates that the query uses the index to satisfy the condition).<\/p>\n

The above query is not optimized, so it takes around 30 sec to fetch records<\/p>\n

Query Optimization<\/h2>\n

1. Indexing Strategy<\/span><\/strong><\/h3>\n

a.<\/strong> Poorly optimized query<\/p>\n

SELECT * FROM users WHERE email =\u2019user@example.com\u2019;<\/pre>\n
If the query is run without indexing, the database engine scans the entire \u201cusers\u201d table to find the user with the specified email address. This can be very slow, especially in large tables.<\/p>\n
b.<\/strong> Optimisation<\/p>\n
CREATE INDEX idx_email ON users(email);\r\n\r\nSELECT * FROM users WHERE email = \u2018user@example.com\u2019;<\/pre>\n
2. Proper Use of JOINs<\/span><\/strong><\/h3>\n
a.<\/strong> Poorly optimized query<\/p>\n
SELECT * FROM customers, orders WHERE customers.id = orders.customer_id;<\/pre>\n
b.<\/strong> Optimized query using INNER JOIN<\/p>\n
SELECT orders.*, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;<\/pre>\n
In the first example, the query combines data from orders and customer tables without explicitly specifying how the tables are related. In the second example, an explicit join helps define the relationship between the two tables based on the mentioned columns. Consequently, it helps retrieve and present data as a unified result set.<\/p>\n
3. Avoid select with *<\/span><\/strong><\/h3>\n
a.<\/strong> Poorly optimized query<\/p>\n
SELECT * FROM products WHERE id = 3;<\/pre>\n
b.<\/strong> Optimized query selecting specific columns<\/p>\n
SELECT product_id, product_name, price FROM products WHERE category_id = 3;<\/pre>\n
Queries specifying needed columns are quite efficient than selecting \u2018*\u2019 queries.<\/p>\n
4. IN vs JOINs<\/span><\/strong><\/h3>\n
Avoid using the IN operator. The IN operator filters rows based on a list of values for a column. It is used with a subquery or list of literals.<\/p>\n
a<\/strong>. Poorly optimized subquery<\/p>\n
SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 5);<\/pre>\n
b.<\/strong> Optimized query using JOIN<\/p>\n
SELECT customers.* FROM customers\r\n\r\nINNER JOIN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 5) AS loyal_customers\r\n\r\nON customers.id = loyal_customers.customer_id;\r\n\r\nJOIN clause is used to explicitly define the relationship between the table and the subquery making the query more efficient.<\/pre>\n
5. Union vs. OR<\/span><\/strong><\/h3>\n
Avoid using OR operator when multiple columns have indexing in the same table. Suppose there\u2019s an index on the category and a separate index on the city column in the user table.<\/p>\n
a.<\/strong> Poorly optimized query with OR<\/p>\n
SELECT * FROM users WHERE category = \u2018A\u2019 OR city= \u2018B\u2019;<\/pre>\n
MySQL generally uses only one index per table in a query; which index should it use? If it uses the index on category, it would still have to do a table scan to find rows where the city is \u2018B.\u2019 If it uses the index on the city, it would have to do a table scan for rows where the category is \u2018A\u2019.<\/p>\n
b.<\/strong> Optimized query using UNION<\/p>\n
SELECT * FROM users WHERE category = \u2018A\u2019\r\n\r\nUNION SELECT * FROM users WHERE city= \u2018B\u2019;<\/pre>\n
The UNION operator is used to combine the result units of two or extra pick-out queries that can use the index for its search into a single result set. The columns in the result units need to have the same record kinds and be in equal order. The UNION operator gets rid of replica rows from the mixed result set.<\/p>\n
6. Use EXISTS for Existence Check<\/span><\/strong><\/h3>\n
a.<\/strong> Poorly optimized query<\/p>\n
SELECT COUNT(*) FROM subscribers WHERE email = \u2018subscriber@example.com\u2019;<\/pre>\n
b.<\/strong> Optimized query using EXISTS<\/p>\n
SELECT EXISTS(SELECT 1 FROM subscribers WHERE email = \u2018subscriber@example.com\u2019);<\/pre>\n
The EXISTS operator stops evaluating as soon as it finds a matching row, whereas the COUNT function needs to scan the entire result set to determine<\/p>\n
7. Use Limit<\/span><\/strong><\/h3>\n
a.<\/strong> Poorly optimized query<\/p>\n
SELECT * FROM orders ORDER BY order_number DESC;<\/pre>\n
When you execute a query without the LIMIT clause, the query retrieves all rows from the specified table. This can result in a large result set, especially if the table contains a significant number of rows.<\/p>\n
b.<\/strong> Optimized query with a reasonable limit<\/p>\n
SELECT * FROM orders ORDER BY order_number DESC LIMIT 10;<\/pre>\n
Including the LIMIT clause in your query Improves performance, as you\u2019re only retrieving a limited number of rows.<\/p>\n
8. Memory Allocation<\/span><\/h2>\n
If all the above optimizations are done, and you still face some performance issues, then you can check and increase the memory size for the properties below.<\/p>\n
9. InnoDB Buffer Pool<\/span><\/strong><\/h3>\n
Increase InnoDB buffer pool size into configuration file- my.cnf or my.ini as per database requirements. The InnoDB Buffer Pool caches data and index pages from InnoDB<\/em> tables in memory. It improves read performance by reducing the need to fetch data from disk for frequently accessed pages.<\/p>\n
innodb_buffer_pool_size = 4G<\/pre>\n
10. Key Buffer Size<\/span><\/strong><\/h3>\n
Configure Key Buffer Size as per database requirement into configuration file- my.cnf or my.ini. The Key Buffer Size, also known as the key cache, is used to cache index blocks for MyISAM<\/em> tables. It\u2019s used to improve read performance for queries that rely on indexes.<\/p>\n
key_buffer_size = 256M<\/pre>\n
Choose Appropriate Storage Engines<\/h2>\n
MySQL helps multiple storage engines (e.g. InnoDB, MyISAM). Pick the proper one primarily based on your use case.<\/p>\n
a. InnoDB<\/strong>: Suitable for most applications due to its ACID compliance, transaction support, and row-level locking.<\/p>\n
b. MyISAM<\/strong>: This may be suitable for read-heavy, non-transactional workloads.<\/p>\n
Table Optimization<\/h2>\n
Use the ANALYZE TABLE and OPTIMIZE TABLE statements to maintain optimal table performance by updating statistics and reclaiming fragmented space.<\/p>\n
Analyze and optimize a table.<\/p>\n
ANALYZE TABLE user;<\/pre>\n
When you execute this statement, MySQL will analyze the distribution of data within the users table, update index statistics, and store this information for query optimization. This process helps the query optimizer estimate the most efficient way to execute queries against the table.<\/p>\n
unoptimize result<\/em><\/figcaption><\/figure>\n
OPTIMIZE TABLE user;<\/pre>\n
The Optimization process will create a temporary copy of the original table. The function replaces the original table with the optimized table once the operation is done, renaming it to the original.<\/p>\n
<\/figure>\n
For more details, refer to this\u200a\u2014\u200aMySQL 8.0 Reference Manual\u00a0:: 13.7.3.4 OPTIMIZE TABLE Statement<\/a><\/p>\n
1. Choose appropriate information kinds<\/span><\/strong><\/h3>\n
a.<\/strong> Use the Smallest statistics type possible<\/strong><\/p>\n
Pick out the smallest data type to accommodate your information without sacrificing accuracy. For instance, if you\u2019re storing a range between 0 and 255, using the TINYINT<\/strong> records type (1 byte) is greater green than using an INT<\/strong> (4 bytes).<\/p>\n
b.<\/strong> Avoid To Use Generic Types<\/strong><\/p>\n
Avoid using frequent information types like VARCHAR<\/strong>(255) for all string information. Choose data types that constitute the nature of your statistics, such as VARCHAR, TEXT, or ENUM<\/strong>.<\/p>\n
c.<\/strong> Use Integer sorts for complete Numbers<\/strong><\/p>\n
For entire numbers, use integer statistics types- TINYINT, SMALLINT, INT, BIGINT<\/strong><\/p>\n
d.<\/strong> Use Floating-factor types for Decimal Numbers<\/strong><\/p>\n
For decimal or floating-point numbers, use the appropriate floating-point records kinds like FLOAT or DOUBLE<\/strong>.<\/p>\n
e.<\/strong> Use Date and Time types for Temporal records<\/strong><\/p>\n
Use date and time statistics types for storing temporal information like DATE, TIME, DATETIME, TIMESTAMP<\/strong><\/p>\n
f.<\/strong> Use ENUM or SET for fixed cost Lists<\/strong><\/p>\n
If you have a set of feasible values for a column, do not forget to use ENUM or SET<\/strong> data types. This ensures information integrity and saves area compared to storing strings.<\/p>\n
g.<\/strong> Use BLOB kinds for big Binary statistics<\/strong><\/p>\n
Use BLOB (Binary large object) record types like BINARY, VARBINARY, BLOB, or LONGBLOB for storing massive binary statistics like pics, audio, or files.<\/p>\n
h.<\/strong> Use JSON information type for JSON information<\/strong><\/p>\n
If you\u2019re storing JSON statistics, use the JSON<\/strong> facts type in MySQL 5.7. This allows for efficient garage and manipulation of JSON files.<\/p>\n
i.<\/strong> Avoid To Store Computed Values<\/strong><\/p>\n
Avoid storing computed or derived values that can be calculated using square queries. Alternatively, calculate these values while needed.<\/p>\n
2. Use Primary Keys<\/span><\/strong><\/h3>\n
The primary keys uniquely identify each record (row) in a table. They are essential in maintaining data integrity, enforcing uniqueness, and enabling efficient data retrieval.<\/p>\n
ALTER TABLE your_table ADD PRIMARY KEY (id);<\/pre>\n
Storage Considerations<\/h2>\n
a. Use SSDs (Solid-State Drives): <\/strong>for quick data access, low latency, fast Random Access and parallelism.<\/p>\n
b. Disk Partitioning:<\/strong> Disk partitioning is a database optimization technique that involves dividing a large table into smaller, more manageable pieces called partitions. Each partition is stored separately on the disk and can be managed individually.<\/p>\n
MySQL supports several types of partitioning:<\/p>\n
a.<\/strong> Range Partitioning: <\/strong>Divides the data into partitions based on a specified range of values from a column. It is useful when you have data that can be logically divided into distinct ranges, like time periods<\/p>\n
b.<\/strong> List Partitioning:<\/strong> Divides the data into partitions based on specified lists of values from a column. This is suitable for cases where data can be categorized into discrete sets.<\/p>\n
c.<\/strong> Hash Partitioning:<\/strong> Distributes data among partitions based on a hash function applied to a column\u2019s value. This can provide more uniform distribution, but partition keys aren\u2019t based on the data\u2019s nature.<\/p>\n
d.<\/strong> Key Partitioning:<\/strong> Similar to hash partitioning, but the partitioning function uses column values directly as keys. It is useful for scenarios where you want to partition data based on specific column value<\/p>\n
e.<\/strong> Data Compression:<\/strong> Enable MySQL\u2019s built-in table compression (InnoDB) to reduce storage requirements and improve I\/O performance. However, be mindful of the trade-off with CPU usage.<\/p>\n
3. Regular Maintenance<\/span><\/strong><\/h3>\n
Perform routine maintenance tasks such as optimizing tables, repairing corrupted tables, and monitoring disk space usage to prevent issues that can impact performance etc.<\/p>\n
Query Cache<\/h2>\n
Enable the query cache for caching SELECT query results. However, be cautious with this in highly dynamic databases. The query cache works well for static or infrequently changing data. The cache might become less effective in highly dynamic databases where data changes frequently due to the constant need for cache invalidation and refreshing.<\/p>\n
Example<\/strong><\/p>\n
Update the below fields into the configuration file- my.cnf or my.ini<\/p>\n
query_cache_type = 1\r\n\r\nquery_cache_size = 256M<\/pre>\n
Connection Management<\/h2>\n
Adjust table and thread settings based on the configuration file- my.cnf or my.ini on your workload.<\/p>\n
Update below fields-<\/p>\n
table_open_cache = 2000<\/p>\n
max_connections = 1000<\/p>\n
Scaling Strategies<\/h2>\n
a. Horizontal Scaling (Sharding):<\/strong> Horizontal scaling involves distributing data across multiple servers or nodes. It\u2019s an effective strategy for read-intensive workloads.<\/p>\n
b. Replication:<\/strong> MySQL replication involves maintaining multiple copies of your database. It\u2019s beneficial for both read scalability and high availability.<\/p>\n
DB Partitioning:<\/strong><\/p>\n
Partition large tables to improve manageability and performance.<\/p>\n
\u2014 Partitioning a table by a range<\/p>\n
CREATE TABLE users (\r\nuser_id INT PRIMARY KEY,\r\nusername VARCHAR(50),\r\nemail VARCHAR(255)\r\n)\r\nPARTITION BY HASH(user_id)\r\nPARTITIONS 8;<\/pre>\n
Caching:<\/strong> Enforce caching mechanisms to lessen the weight to your database.<\/p>\n
Content Delivery Network (CDN):<\/strong> Offload static assets like images, videos, and scripts to a CDN to reduce the load on your database and improve content delivery speed.<\/p>\n
Application-Level Optimization:<\/strong> Optimize your application code to minimize the number of queries and improve efficiency.<\/p>\n
Load Balancing:<\/strong> Implement load balancers to distribute traffic across multiple database servers or nodes evenly<\/p>\n
Sharding Proxy: <\/strong>Use a sharding proxy tool to automate data distribution across shards and manage communication between your application and databases.<\/p>\n
Data Archiving<\/h2>\n
Archive old or infrequently accessed data to separate tables or storage.<\/p>\n
Move old data to an archive table-<\/p>\n
INSERT INTO archived_order SELECT * FROM sales WHERE order_date < \u20182020\u201301\u201301\u2019;\r\n\r\nDELETE FROM sales WHERE order_date < \u20182020\u201301\u201301\u2019;<\/pre>\n
Some Profiling and Monitoring Tools<\/h2>\n
a. MySQL Workbench:<\/strong> MySQL Workbench offers a graphical interface for database design, SQL development, and administration. It includes tools for performance tuning, query profiling, and visually explaining plans.<\/p>\n
b. New Relic:<\/strong> New Relic offers application performance monitoring (APM) solutions that can monitor and provide insights into the performance of your MySQL database as part of your overall application stack.<\/p>\n
c. Datadog:<\/strong> Datadog is a monitoring and analytics platform that can help you monitor, visualize, and analyze MySQL performance metrics.<\/p>\n
Conclusion<\/h2>\n
MySQL performance tuning is a continuous process that involves optimizing queries, indexing, memory usage, storage strategies, and more. Understanding your application\u2019s specific needs and the underlying MySQL features is essential for achieving optimal performance. Regular monitoring, profiling, and adjustments based on changing requirements will ensure your MySQL database operates efficiently. For More details please refer\u200a\u2014\u200aMySQL 8.0 Reference Manual\u00a0:: 8 Optimization<\/a>.<\/p>\n
<\/div>","protected":false},"excerpt":{"rendered":"
Introduction MySQL is one of the most popular open-supply relational databases. As your application\u2019s statistics grow, MySQL\u2019s performance may be impacted by different factors, including database design, server configuration, and query performance. In this blog, we can talk about the various components of MySQL’s overall performance tuning and explore common situations to help you to […]<\/p>\n","protected":false},"author":1641,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":9},"categories":[1],"tags":[76],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/58486"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/1641"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=58486"}],"version-history":[{"count":8,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/58486\/revisions"}],"predecessor-version":[{"id":59647,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/58486\/revisions\/59647"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=58486"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=58486"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=58486"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}