Shulou(Shulou.com)06/02 Report--

This article is about how to compare data compression performance in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Test environment 1.1 hardware and software

A 64-bit 2.6.18-92 kernel Linux development machine, 4G memory, 4 2800Mhz Dual-Core AMD Opteron (tm) Processor 2220 CPU.

MySQL is placed on a 7200 rpm SAT hard drive without raid.

MySQL turned off query cache without any optimization in order to prevent query cache from interfering with the test results.

1.2 Table structure

2424753 records, the actual data of a fragment in the production environment

The joint indexes of (partition_by1,idx_rank) and (partition_by1,chg_idx) are established respectively, where partition_by1 is a 32-length varchar type for retrieval, and the other two fields are floating-point numbers and are mostly used for sorting.

As a child increment, autokid acts as a PRIMARY KEY and is only used as a guarantee of atomicity when data is loaded, which has no practical significance.

two。 Test purpose 2.1 Compression space comparison

The higher the compression ratio, the smaller the disk space, which directly reduces the storage cost of the data.

2.2 query performance comparison

Query performance should not degrade significantly after compression. Archive does not support indexing, so performance degradation is inevitable, so we should also have a spectrum in mind, how much the reduction is, and whether it is acceptable or not.

3. Test tool 3.1 mysqlslap

Official tools are, of course, the best choice. For an introduction to mysqlslap, please refer to the official documentation.

3.2Test query

A total of 9973 SQL entries were intercepted from the production environment to access the topranks_ v3 table, from which 7 items with a large number of visits were extracted, 50 were sent concurrently, and repeated 10 times. The command is as follows:

. / mysqlslap-- defaults-file=../etc/my.cnf-ubiquitous samples *-pumped boxes *-c50-i10-Q.. / t.sql-- debug-info

4. Test conclusion comparison disk space consumption (seconds) CPU IdleLOAD concurrent matrix (MyISAM) 4039560042.308301550ARCHIVE75630745 > 3007541PACK993021092.596302250

Based on the test data given in the table above, we simply draw the following conclusions:

For the test table, the space occupied by Archive table is about 18.7% of that before, and the space occupied by Myisampack is about 24.6%. There is little difference between the two. From the point of view of space utilization, it seems that we need to choose archive table.

Let's look at the query performance and compare it with the matrix. In terms of total time consumption and system load, the query performance of the pack table under 50 concurrency is comparable to that of the matrix table, while the archive table takes more than 5 minutes in the case of single concurrency (I can't wait any longer, kill)!

So, we seem to conclude that the ARCHIVE engine basically doesn't have to think about tables that need to be queried online.

Why is the ARCHIVE engine so slow during this test?

We know that mysql provides archive as a storage engine to reduce disk overhead, but there is also a premise that archived data does not need or is rarely queried online, and it is okay to query slowly occasionally. For the above reasons, the archive table is not allowed to build indexes other than incremented columns.

With this consensus, let's take a test SQL to analyze why there is such a big difference in query performance before and after indexing.

In our test SQL, there is this one:

SELECT c1,c2,...,cn FROM mysqlslap.rpt_topranks_v3WHERE... AND partition_by1 = '50008090'ORDER BY added_quantity3 DESCLIMIT 500

As we said earlier, the table tested is indexed on the partition_by1 field, so we initially determine that the partition_by1 index should be used in this query on the matrix table and the myisampack table; EXPLAIN:

Mysql > EXPLAIN-> SELECT... FROM mysqlslap.rpt_topranks_v3-> WHERE... AND partition_by1 = '50008090'-> ORDER BY added_quantity3 DESC-> LIMIT 500\ row * * id: 1 select_type: SIMPLE TABLE: rpt_topranks_v3 type: refpossible_keys: idx_toprank_pid Idx_toprank_chg KEY: idx_toprank_pid key_len: 99 ref: const rows: 2477 Extra: USING WHERE USING filesort1 row IN SET (0.00 sec)

As we expected, this query uses an index built on the partition_by1 field, matching a target number of 2477 rows, followed by a sort on the added_quantity3 field. Because added_quantity3 does not have an index, filesort is used.

Let's take a look at the EXPLAIN result of this SQL on the archive table:

Mysql > EXPLAIN-> SELECT... FROM mysqlslap.rpt_topranks_v3_archive-> WHERE... AND partition_by1 = '50008090'-> ORDER BY added_quantity3 DESC-> LIMIT 500\ row * * id: 1 select_type: SIMPLE TABLE: rpt_topranks_v3_archive type: ALLpossible_keys: NULL KEY: NULL key_len: NULL ref: NULL rows: 2424753 Extra: USING WHERE USING filesort1 row IN SET (0.00 sec)

EXPLAIN said, "I don't have an index available, so I can only scan the table for 2424753 rows and then filesort." If you want to pursue performance, it is obviously a grievance to MySQL.

Thank you for reading! This is the end of the article on "how to compare the performance of data compression in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.