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

This article focuses on "what are the ways to improve performance with sub-table storage". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the ways to improve performance with sub-table storage?"

First of all, Tong Jiawang introduced what he thinks is optimization: first, the quickest way to do anything is to do nothing.

Tong Jiawang, Senior Database architect of ▲ Alipay

Second, do not access unnecessary data: use methods such as B*Tree/hash to locate the necessary data. Use column Store or sub-tables to store data separately. Use the Bloom filter algorithm to exclude null queries.

Third, reasonable use of hardware to improve access efficiency: the use of cache to eliminate repeated access to data. Use batch processing to reduce Seek operations on disks. Use batch processing to reduce the Round Trip of the network. Use SSD to improve disk access efficiency.

Relationship between response time and throughput

1. Performance. Measure the speed or efficiency of completing a particular task.

2. Response time. Measure how long the system can respond when it interacts with the user.

3. Throughput. Measure the amount of tasks that the system can accomplish per unit time.

▲ reaction time

Access characteristics of ▲ traditional disk

Introduction to B*Tree optimized data access

▲ B*Tree optimizes data access

B*Tree optimized data access simulation scenario

▲ B*Tree optimized data access simulation scenario

Tong Jiawang introduced how to use sub-table storage to improve performance through Alibaba's real application scenario.

1. Scene introduction:

1. Table VeryBigTable contains 30 columns

2. The number of records in the table is 50000000

3. The average number of messages per user is about 300

4. Two of the columns belong to the detailed description field, with an average length of 2k

5. The total length of other columns is 250 bytes on average.

6. There are two modes for queries on this table

7. List the main information in the table (20 items at a time, no details, 90% of the queries)

8. View the details of the record (10% of queries)

9. Save and Oracle database, default block_size (8k)

2. Requirements:

1. Optimize this service

2. Analyze the data and persuade the development department to implement this optimization.

Third, performance analysis

1. Number of records per block

8192 * 0.801 / 250 = 25.5 (main table)

8192 * 0.80 / 2000 = 3.27 (details table)

8192 / (2000 + 250) = 2.91

2. Logical IO of access (memory block access)

Query cost of List

Improved = (300 beat 25.5) * y + 4 + x = 4 + x + 11.8y = 4 (2) + 7 (3) + 11.8 * 1.5 (4) = 28.7

Before improvement = (300pm 2.91) * y + 4 + x = 4 + x + 103.y = 4 + 7 + 1031.5 = 165.5

3. Physical reads involved in access (disk block access)

Query cost of List (logical IO * (1-hit ratio))

Improved = 28.7 * (1-0.85 (5)) = 4.305

Before improvement = 165.5 * (1,0.85) = 24.825

4. Access time (ms)

Before improvement = logical IO time + physical IO time = 28.70.01 (6) + 4.305 * 7 (7) = 30.422ms

Improved = logical IO time + physical IO time = 165.5 * 0.01+ 24.825 * 7 = 175.43ms

At this point, I believe you have a deeper understanding of "what are the ways to improve performance with sub-table storage?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.