Thoughts on the problem of splitting clusters by a mycat+mysql level

Recently deployed and tested a MYCAT+4 percona+tokudb horizontal split cluster, the previous application is to write a class of citation data into this library, only insert operation, the previous few days running state is also better.

Since yesterday, the disk IO load has become high due to a sudden increase in traffic, and after careful analysis, it is completely problematic to find that disk read performance is much higher than the performance of disk writes. Because the insert operation is definitely written, and the write is sequential, the read operation should not be too large.

Through the mycat and MySQL aspects of the view, it is difficult to explain the way, not sure about the direction and cause of the problem. Later, I communicated with my colleagues and returned to the beginning of the system:

First confirm the CPU, and then confirm the memory, the results found that the memory cache has been full, began to use a large number of swap partitions, from this point of departure, confirm the following ideas:

650) this.width=650; "title=" 01.jpg "src=" http://s2.51cto.com/wyfs02/M00/76/97/wKioL1ZW55bjZLHOAAd9aW6XRmc944.jpg "alt=" Wkiol1zw55bjzlhoaad9aw6xrmc944.jpg "/>

Then once again to communicate with colleagues:

It should be that there is not enough memory to read the swap

Top to see the amount of memory each MySQL instance occupies

As you can see from these graphs, there is a query operation.

Insert operation because to maintain the index, there should be a ups and downs read, but whether it will be reflected in the form of a query I do not confirm

First to confirm that the memory contention is a MySQL trigger or another process

For a database server, swap is best not to use

In some extreme scenarios, swap is set to 0 to avoid swapping files on disk

This can have a big impact on performance.

You can now need to adjust the memory parameters of the small InnoDB, and then the session-related parameters are also a small tune

Let's get the memory contention down.

So according to the whole system of physical memory is 48g, the memory of each instance is up to 50%, now four instances, each instance allocates 6g memory, because TOKUDB adopts the non-directio mode "Tokudb_directio = 0", so the memory of each instance is set to 4g, More memory is used for the system cache, so that system reads use the system cache, do not use swap partitions, ensure memory hit ratios, and reduce disk IO operations.

Once this has been modified, the system's resources become:

650) this.width=650; "title=" 02.png "src=" http://s2.51cto.com/wyfs02/M01/76/97/wKioL1ZW56bjZtxnAAANS8YFztA373.png "alt=" Wkiol1zw56bjztxnaaans8yfzta373.png "/>

By Iostat, the write request is larger than the read request, and the insert operation is in a normal state:

650) this.width=650; "title=" 03.png "src=" http://s2.51cto.com/wyfs02/M00/76/97/wKioL1ZW57DQmK7iAACKoZ9Mht4470.png "alt=" Wkiol1zw57dqmk7iaackoz9mht4470.png "/>

Through the handling of this problem, I have a few insights:

1. For architectural design and performance issues, you should return to the original:

System Resource Monitoring:

Cpu

Memory

Disk

Internet

Overall

The corresponding stat command:

Vmstat

Iostat Iotop

Mpstat

Ifstat

Dstat

Deep understanding of these basic system resources, principles, review methods, to understand the performance and architecture problems, and find a breakthrough to solve the problem;

2. For horizontal splitting:

Chat History:

From the processing of this problem also feel, hardware resources, only one machine, only from the software mycat+mysql this way to split horizontally, resource consumption and contention problem is still relatively large

Horizontal split cannot be split horizontally from Mycat,mysql software, hardware system resources are sufficient, or sufficient.

Otherwise, all the business is on a physical machine, resource contention and problems, or there will be a lot of;

Especially in the face of business load pressure and change, even if the Mycat shard is reasonable, CPU, memory, disk IO These resources are insufficient, the overall performance will have a lot of problems

Optical Mycat logically shards not yet.

To make reasonable use of hardware, especially IO resources

Prevent hardware hotspots

A disk that has too many shards is useless for IO competition

Reasonable utilization of network IO disk IO memory CPU

such as Rain0 root rain10 a few pieces of disk each shard on which disk this is what the real DBA needs to plan

The DBA really is planning for these underlying resource optimizations

3. Really good architecture design, not only software design, but the entire software, hardware, business integration and coordination of the design.

This article is from the "Yumushui column" blog, be sure to keep this source http://yumushui.blog.51cto.com/6300893/1717202

Thoughts on the problem of splitting clusters by a mycat+mysql level