5. Shard-Query adds parallelism to queries

5. Shard-Query adds parallelism to queries
|출처| http://www.mysqlperformanceblog.com/2010/11/15/shard-query-adds-parallelism-to-queries/

Preamble: On performance, workload and scalability:

서문: 수행, 작업량 그리고 확장성에 대해서

MySQL has always been focused on OLTP workloads. In fact, both Percona Server and MySQL 5.5.7rc have numerous performance improvements which benefit workloads that have high concurrency.
=> MySQL은 항상 OLTP 작업량(workload)에 대해 초점을 맞춘다. 사실 Percona Server 와 MySQL 5.5.7rc 둘 다 많은 성능향상을 가지고 있고 그것은 높은 동시 동작성을 가진 작업량(workloads)들에 이익을 준다.

Typical OLTP workloads feature numerous clients (perhaps hundreds or thousands) each reading and writing small chunks of data. The recent improvements to MySQL make it scale better for this workload when more resources (such as additional CPUs) are added. By scaling better I mean that it is able to take advantage of all available resources in order to handle the workload.
=> 전형적인 OLTP 작업들(workloads)은 많은 클라이언트이(아마 수백,수천개일 것이다) 각각 작은 청크단위(chunks of data)의 데이터들을 읽고 쓰는 특징을 가지고 있다. 그런데 최근 MySQL의 개선사항들은 많은 자원들이 추가되었을 때(CPU 추가와 같은..) 이와 같은 작업량에 대한 규모(크기)를 더 여유 있게 만들어 주었다. 필자의 “규모의 확장”이란 뜻은 작업량(workload)을 처리하기 위해 모든 사용 가능한 자원들을 이용할 수 있다는 뜻이다.

One reason that it works best for this workload is that a single query executes in a single thread.
MySQL never takes advantage of more than a single CPU when aggregating data and fetching rows from the buffer pool, with respect to a single query, but it can run many queries at once.
=> 이러한 작업량(workload)에 최고로 적용될 수 있는 이유 중 하나는 단일 thread에서 단일 쿼리가 작동하기 때문이다. MySQL은 단일 쿼리에 대하여 버퍼풀에서 데이터를 종합하거나 행들을 fetch할 때 절대 하나 이상의 CPU를 사용하지 않는다 하지만 한번에 많은 쿼리들을 동작시킬 수 있다.

There are workloads other than OLTP and the recent optimizations to MySQL still leave a lot of low hanging fruit where these are concerned. This is particularly true for OLAP workloads.

ð  OLTP를 제외 한 작업량들(workloads)과 최근의 MySQL의 최적화는 여전히

OLAP는 특히 더 그렇다.

이 곳은 해석이 난해하네요^^:; 아시는 분들은 댓글 부탁드려요~~)

While I’m not going to diverge into a discussion of how OLAP varies from OLTP, it suffices to say that a typical OLAP workload features a low number of concurrent queries which each examine large amounts of data.
=> 어떻게 OLTP에서 OLAP를 분리 시킬 건가에 대한 토론으로 분기시킬 생각은 없지만 전형적인 OLAP 작업량(workload )은 동시동작 쿼리의 수가 적지만 각 쿼리당 많은량의 데이터를 조사하는 특징이 있다는 것은 충분히 논할 만 하다.

Since a single query is single threaded in MySQL, the new optimizations don’t really help with this workload.
=> MySQL에서 단일 쿼리는 단일 쓰레드이기 때문에 새로운 최적화는 작업량에 실직적으로 도움이 되지 못한다.

 

여기까지의 정리...

: 수행,작업량 그리고 확장성에 대해서

 

MySQL 5.5.7rc 둘 다 많은 성능향상을 가지고 있고, CPU추가와 같은 자원추가에 대한 이득이 MySQL의 특성인 single query in single thread 면에 대해서는 쿼리 수행 시 cpu를 하나 이상 사용하지 않기 때문에 별 이득이 없을 진 모르나, OLAP 같은 하나의 쿼리 수행에 많은 데이터를 요구 하는 sql일 경우 충분한 자원이용을 위한 이득으로 작용할 수 있다.

 

The following tests assume a workload consisting of a small number of concurrent queries (or only one) to demonstrate how much improvement could be made to MySQL so that is could better utilize all available resources (that is, scale better) when running small numbers of queries which examine a lot of data.
=> 뒤에 나오는 테스트는 수적으로 작은 동시성 쿼리들로 이루어진 작업량(workload)이 MySQL에서 얼마만큼의 향상성을 만들어 내는지, 그리하여 많은 양의 데이터를 조사하는(examain) 작은 수의 쿼리들을 돌릴 때 사용 가능한 모든 자원들에 대해 더 나은 활용(= 규모향상)을 증명하는 것을 보여준다.

 

 

ð  테스트 : 작은 수의 쿼리 수행에 많은 데이터를 요구하는 sql문을 실행시킴.

 

 

What is Shard-Query?

: Shard-Query가 무엇인가?
Shard-Query was initially conceived as a utility to add parallelism to horizontally partitioned data sets by running queries against each host in parallel, with the added feature of supporting aggregation.
=> Shard-Query는 처음에 수평적으로 파티션 된 데이터셋(data set)에 쿼리를 이용해 각 호스트 별 병렬로 쿼리를 동작시키는 유틸에 의해 고안 된 것이다.

Then I hit upon the idea of taking SQL constructs like IN and BETWEEN and making these queries execute in parallel on a each host. 
=> 그리고 필자는 IN이나 BETWEEN과 같은 SQL구조들을 사용하여 이 쿼리들을 각각의 호스트에서 병렬로 실행시키는 좋은 방안을 생각 해 냈다.

If you have a sharded data set, then this gives you the opportunity for additional parallelism for each query. If you have only a single server, but it has enough resources to answer queries in parallel, then it can be used to add parallelism to queries which use IN or BETWEEN clauses.
=> 만약 shard query가 적용된 dataset(sharded data set)이 당신에게 존재한다면 각 쿼리에 대해 추가적인 병행성의 기회가 주어질 것이다. 만약 당신이 단일 서버만 가지고 있지만, parallel로 쿼리의 반환에 대한 자원이 충분하다면, IN이나 BETWEEN 절들을 사용하는 쿼리에 병행성을 추가할 수 있을 것이다.

 This added parallelism can have significant performance advantages as demonstrated in this blog post.
=> 이 추가된 병행성은 이 글에서의 증명처럼 상당한 수행(performance)강점을 가질 수 있다.

 

Many database servers can add this parallelism natively, but most of those are not open source. In the future, Shard-Query can be extended to other database servers such as PostgreSQL or Firebird fairly easily.
=> 많은 DB서버들은 기본적으로 이 병행성을 추가할 수 있다. 하지만 이것들은 open소스가 아니다. 앞으로 Shard-Query는 PostgreSQL 또는 Firebird와 같은 다른 DB서버에도 꽤 쉽게 확장될 수 있을 것이다.

 

정리: Shard-Query란?

|참조| Reporting Service 2008 R2에서 소개 됨. 통계/수집 작업에 공유되어 작업효율을 높이고 일반적인 쿼리들의 전형적인 중복성을 피하는데 사용됨.

ð  http://joegilldotcom.blogspot.com/2010/07/reporting-services-shared-datasets.html

ð  http://code.google.com/p/shard-query/

 

: 원천> 데이터가 많은 or 여러 개의 테이블을 모아 데이터를 select해야 하는 작업이 필요할 때 select 되는 데이터의 양이 많거나, 많은 쿼리를 요구하는 쿼리에 대해, 미리 해당 데이터들을 모아 둔 테이블을 따로 생성 해 둔 테이블=dataset(like view) 거기서 select 하는 방식으로 데이터 반환의 효율성을 높이는데 사용되는 쿼리!!! Php class

 

이 Shard-Query에 기인하여 MySQL에서는 파티셔닝 테이블을 Shard-Query로사용.

 

 

What machine did I use?
: 무슨 서버를 사용했는가?

I used MySQL 5.5.7rc on a powerful Cisco UCS server with 12 real cores and 384GB of ram. The amount of ram is significantly larger than my already hefty 55GB testing data set, so this means that if MySQL could fully utilize all cores for my workload, then this test would be CPU bound.
=> 나는 12개의 real core와 384G 램으로 구성된 powerful Cisco UCS서버에 MySQL 5.5.7 RC를 사용하였다. 램의 사양은 내가 이미 가지고 있는 558G의 무거운 테스트 데이터셋 보다 더 크다. 이는 MySQL이 작업량(workload)에 대해 충분히 모든 코어들을 활용할 수 있다면 이 테스트는cpu 기반이 된다는 의미가 된다.

 

정리: 테스트 사양

서버: powerful Cisco UCS
DBMS: MySQL 5.5.7RC
Cpu : 12 개 real core
Ram: 384G
Dataset: 558G 대용량

What data did I use?
: 어떤 데이터를 사용했나?
I loaded 55GB of the same data used in this earlier post into a partitioned InnoDB table.
=> 나는 먼저 번 포스팅에 사용했던 558G의 같은 데이터를 파티션 된 InnoDB 테이블에 넣었다.

 

Partitioning example:

:파티션 예이다.

 

 

What queries did I use?

:어떤 쿼리를 사용했냐?

I used a version of the queries in that same blog post. The original queries tend to filter on the Year column.
=> Blog에 개제했던 같은 쿼리를 사용. 기존 쿼리는 년(year) 컬럼에 기반되어 분류 되어진다.

I partitioned the table into months using the FlightDate column using the improved MySQL 5.5 partitioning options which work directly on columns without the need to use TO_DAYS.
=> 하지만 나는 FlightDate컬럼을 사용해 월 별로 테이블을 파티셔닝 하고 향상된 MySQL5.5 파티셔닝 옵션을 사용하여 TO_DAYS 사용이 필요 없이 바로 작동하게 하였다.

To accommodate my partitioning schema I modified the queries to use the FlightDate column instead of the Year column. See the “full disclosure” section at the end for the complete SQL.
=> 파티셔닝 스키마를 수용시키기(적용(?)) 위해 Year컬럼 대신 FlightDate 컬럼을 사용하는 것으로 쿼리를 변경하였다.
=> 완벽한 SQL(complete SQL)의 끝부분에 “full disclosure을 참고하여라.

 

These tests were done using ‘run_query.php’, which is the example application which comes with Shard-Query.
=> 이 테스트들은 run_query.php을 사용하여 진행했었다. 이 것은 Shard-Query가 속해 있는 예제 어플리케이션이다.

As the name implies, it takes a list of queries in a file (or stdin) and a config file. It runs the SQL via ShardQuery and it prints the results.
=> 소스의 이름(run_query.php) 처럼, 이것은 file (or stdin(표준입력 in php))과 config file에서 쿼리의 리스트들을 가져와 ShardQuery를 통해 SQL을 실행시키고, 결과를 도출한다.

 

정리: 어떤 쿼리를 사용했나?

 

그림과 같이 년-월별로 파티셔닝 테이블을 구성하여 shard-query가 들어간 대표적인 소스인 run_query.php을 사용하여 테스트 진행.

 

run_query.php : 

 

|참조| MySQL5.5 파티셔닝 옵션(출처: http://barnie.blog.me/46162215)

l  RANGE COLUMNS Partitioning / LIST COLUMNS partitioning

1.     DATE, DATETIME, 문자열 값들(CHAR 또는 VARCHAR 같은)을 기반으로 하는 range 또는 list 파티셔닝을 정의할 수 있다.

2.     RANGE COLUMNS 또는 LIST VOLUMNS를 이용한 파티셔닝일 때 여러 컬럼 값들을 기반으로 하는 ranges 또는 lists를 정의할 수 있다. Range 또는 list는 16컬럼까지 참조할 수 있다.



Test #1

This set of queries tests the most basic aggregation (count(*)) on a range of records. 
=> 이 쿼리의 모음들은 가장 기초적인 집합인 (count(*))를 레코드의 범위에서 테스트 하는 것이다.

This table is partitioned by month which means that MySQL can use partition pruning to reduce the amount of data which must be examined.
=>  이 테이블은 월별로 파티션 되어 있다. 그 의미는 MySQL은 실행되어야 하는 데이터의 수를 줄이기 위한 partition pruning 를 사용할 수 있다는 뜻이다.

 With this in mind, I modified Vadim’s queries to use the FlightDate column in the WHERE clause instead of Year.
=> 이를 위해 where절에 있는 Year쿼리를 대신해 FlightDate를 사용하기 위해 vadim의 쿼리를 변경하였다.

Each iteration reads an additional year of data. That is, the first query reads one year, and the last query reads 21 years of collected flight data.
=>  각각의 반복은 추가적인 년도의data를 읽게 된다. 이것이 첫 번째 쿼리가 하나의 년도를 모아진 flightData에서 읽고 마지막 쿼리가 21개의 year 데이터를 모아진 flightData에서 읽는다.

For example (the final query): 마지막쿼리

The reason that Shard-Query performs better is that it turns the OLAP query into something more like OLTP. Instead of getting one big chunk of data in one query, it runs many smaller queries each requesting significantly less data.
=> Shard-Query 수행능력이 더 나은 이유는 OLAP쿼리를 OLTP쿼리와 최대한 비슷하게 바꾸기 때문이다. 하나의 쿼리에서 하나의 큰 data chunk를 갖고오는 대신 쿼리를 잘게 잘라 각 쿼리들이 작은 데이터들로 나눠 반환하게 한다.

On the other hand, MySQL 5.5.7 does not do this on its own. This is the low hanging fruit I was talking about. Even though the data is partitioned, MySQL will examine each partition serially. In the end, this means that things get slower as the query has to examine larger volumes of data.
=> 한편으로는 MySQL 5.5.7은 이것을 스스로 해내지 않는다. 필자가 말한 “쉽게 얻는다”는 의미가 바로 이 얘기이다. 데이터가 파티셔닝 되었음에도 불구하고 MySQL은 각각의 파티션을 순차적으로 실행할 것이다. 결국 이 뜻은 더 느려진 쿼리는 더 큰 용량의 데이터를 실행(조사) 한다는 것이다.

Regarding the performance of Shard-Query, this machine has 12 real cores and 12 virtual cores, so we don’t see any advantage after increasing the number of workers past 24.
=> Shard-Query의 performance에 관련해서 이 기계는 12개의 실질적(real) core와 12개의 가상core가 있다. 그렇기 때문에 24까지 worker의 수를 늘린 이후에는 아무런 이점을 얻지 못했다.

The query becomes CPU bound at that point. If I needed more performance I could divide the data between two or more shards, or if possible, I could add more CPU cores.
=> 저 상황에서 쿼리는 cpu 기반이 된다. 만약 더한 performance를 원한다면 데이터를 두개 혹은 더 많은 shard로 나누거나, 가능하다면 cpu를 더 추가할 수도 있을 것이다.

Regardless, even with a single server Shard-Query will perform much better than regular MySQL as the volume of data grows.
=> Regardless, 심지어 단일 서버 shard-query가 보통 MySQL보다 데이터의 양이 증가 면에서 더 잘 수행될 것이다.

Remember that this workload fits entirely in the buffer pool so adding CPUs will help only until we run out of memory bandwidth.
=> 이 workload는 전적으로 buffer pool에 맞춰져 있는 것이라 cpu를 추가하는 것은 메모리 대역을 생각하지 않는 상황에서만 유용하다는 것을 명심하라.

Test #2
My second test involved the next four queries on Vadim’s list. The purpose of this test is to demonstrate that Shard-Query works with GROUP BY and other constructs.
=> 이 테스트의 목적: shard-query가 group by와 다른 구조들과 함께 동작하는 것을 증명하기 위한 것이다.

 

As you can see, each of the queries runs significantly faster than just running the SQL via MySQL.
=>  보이는 바와 같이 각 쿼리를 단순히 MySQL을 통한 SQL을 돌리는 것보다 상당히 빠르다.

The remainder of Vadim’s queries use subqueries in the FROM clause, which Shard-Query does not yet add parallelism to. I plan to add support for those queries though, and I’ll post a follow-up when I do.
=>  Vadim의 나머지 쿼리는 FROM 절 안의 서브쿼리로 이용하였고, 그 shard-query는 아직 parallelism 용으로 추가되지 않았다. 필자는 해당쿼리에 대해 추가적으로 부가할 계획이고 부가 후 post에 개제할 것이다.