Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to improve the scalability of MySQL, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. Adjust query operation

Optimizing the query allows you to get the most results with the least effort. The query function will be brought into full play to meet the business requirements, and will not be overwhelmed by excessive traffic and heavy load. This is why we often see customers encounter more and more troubles, as their website visits more and more, the scalability challenge becomes more and more serious, this is the problem. It is not necessary to optimize queries on pages that are not commonly used in the corner of the site, and those pages do not receive real-world traffic. It is a common practice to make some adjustments to the network application according to the reflection, and the effect is very good.

Query optimization requires slow query logging enabled and constant observation. Use mk-query-digest, a powerful tool in the Maatkit suite, to analyze logs and make sure that the log_queries_not_using_indexes tag is set. Once you find that a query takes up a lot of resources, optimize it. Using the EXPLAIN interpretation mechanism, using profiler, observe the index usage, create the missing index, and understand how it is added and sorted.

two。 Copy using Master-Master

Master-Master 's active-passive replication mode, or circular replication, brings not only high availability, but also a high degree of scalability. This is because you can immediately assign a read-only slave disk to your application. Many web applications are segmented according to the rule of 80amp 20, 80% of the activity is used for reading or SELECT, and the rest is allocated to INSERT and UPDATE. It is feasible to configure your application or restructure to send the traffic needed for reading to the slave disk, and this type of horizontal scalability can be further extended to attach more read-only slaves if necessary.

How to quickly improve the scalability of MySQL

3. Using Stora

This sounds basic and straightforward, but it is often overlooked, and you should at least make sure that these are set:

Innodb_buffer_pool_sizekey_buffer_size (MyISAM Index Cache) query_cache_size- needs to be careful with thread_cache&table_cacheinnodb_log_file_size&innodb_log_buffer_sizesort_buffer_size,join_buffer_size,read_buffer_size,read_rnd_buffer_sizetmp_table_size&max_heap_table_size when using large SMP

4. RAID read by disk

Don't know what's under your database? please find out. Are you using RAID5? this is a huge hindrance to performance. RAID5 inserts and updates are slow, and if you lose a hard drive, there is little RAID5 can do to rebuild. RAID5 is really too slow, so what should be used instead of it? use RAID10 for mirroring and segmentation, which can make full use of all the hard drives in your server or chassis. Even if your memory can hold the entire database, you still need to do a lot of reading to the hard disk. Why? because, for example, sorting operations need to rearrange rows and rows, groups and joins, and so on, as well as adding transaction logs, and so on, these are all disk Igo O operations.

5. Adjust Key parameters

In addition, there are additional parameters that can be used to improve performance:

Innodb_flush_log_at_trx_commit=2

It can greatly improve the speed of insert and update, but is a little lazy when clearing the innodb log buffer. You can do more research on it, but in most cases it is highly recommended.

Innodb_file_per_table

Innodb development is like Oracle, and the storage aspect uses the tablespace pattern. Obviously what kernel developers are doing is not perfect, because using the default settings of a separate tablespace will cause performance bottlenecks. This parameter setting helps innodb create tablespace and data files for each table, just as MyISAM does.

After reading the above, do you have any further understanding of how to improve the scalability of MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.