Postgres 9.6 성능 이야기

도서] PostgreSQL 9.6 성능 이야기

저자 김시연,최두원 | 출판사 시연아카데미

기술적으로 탄탄한 책이다. 저자가 전직 엑셈 출신이라. 그 스타일이 묻어 난다.

Postgres 디비는 현재 OpenSource DB 의 선두 주자며 모든 디비의 할아버지 정도 되는 아주 역사가 깊은 디비다. 

이런 디비를 Source Code 레벨까지 분석을 할수 있다는 건 대단한 능력이라고 할수 있다. 그런데 다른 한편으로는 그런 지식이 필요 할 까 ? 하는 생각도 든다. 

느낌으로 이런 IT 쪽의 책은 잘 안팔리거나 팔려도 극소수가 볼것 같다. 어쨋거나 나도 이런 책 좀 써봤음 좋겠다. 

MySQL Python Connect Driver

-. MySQL Python Connect Driver Db Connect

#!env python3

import mysql.connector
from mysql.connector import errorcode

config = {
   'user': 'dbmgr',
   'password': 'audtlr2',
   'host': 'localhost',
   'database': 'spp',
   'raise_on_warnings': True,
   'use_pure': True,
}

try:
  cnx = mysql.connector.connect(**config)

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database dose not exists")
  else:
   print(err)

else:
  cnx.close()

MySQL 쿼리 튜닝 하다가 생각 한번 해봐야 하는 경우

MySQL 쿼리 튜닝 하다가 생각 한번 해봐야 하는 경우

--
--
--

SELECT DISTINCT
  placement_id
  , zone_id
FROM ox_placement_zone_assoc assoc
WHERE EXISTS(SELECT 1
             FROM ox_campaigns m INNER JOIN ox_clients c
                 ON m.clientid = c.clientid AND c.block_status = 'pass'
             WHERE
               m.status = 0 AND m.block_status = 'pass' AND m.deliverable <= 1 AND assoc.placement_id = m.campaignid)
;


+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| id   | select_type | table | type | possible_keys                        | key                                  | key_len | ref                         | rows | Extra                        |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
|    1 | PRIMARY     | c     | ALL  | PRIMARY                              | NULL                                 | NULL    | NULL                        | 6728 | Using where; Using temporary |
|    1 | PRIMARY     | m     | ref  | PRIMARY,ox_campaigns_clientid        | ox_campaigns_clientid                | 3       | OPENX_V1_ADMIN.c.clientid   |    3 | Using where                  |
|    1 | PRIMARY     | assoc | ref  | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4       | OPENX_V1_ADMIN.m.campaignid |   78 | Using index                  |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+

+------------------------------+------------+
| Status                       | Duration   |
+------------------------------+------------+
| starting                     |   0.000070 |
| checking permissions         |   0.000014 |
| checking permissions         |   0.000012 |
| checking permissions         |   0.000012 |
| Opening tables               |   0.000034 |
| After opening tables         |   0.000016 |
| System lock                  |   0.000014 |
| Table lock                   |   0.000017 |
| init                         |   0.000037 |
| optimizing                   |   0.000043 |
| statistics                   |   0.000040 |
| preparing                    |   0.000032 |
| executing                    |   0.000012 |
| Creating tmp table           |   0.000037 |
| Copying to tmp table         |   1.089634 |
| converting HEAP to Aria      |   0.121867 |
| Creating index               |   0.000091 |
| Repair by sorting            |   0.912552 |
| Saving state                 |   0.000058 |
| Creating index               |   0.000014 |
| converting HEAP to Aria      |   0.006953 |
| Copying to tmp table on disk | 145.988661 | -- 메모리의 임시 테이블을 디스크로 내리면서 걸리는 시간 이 대부분
| Sending data                 |   1.860387 |
| end                          |   0.000047 |
| removing tmp table           |   0.062840 |
| end                          |   0.000021 |
| query end                    |   0.000021 |
| closing tables               |   0.000015 |
| Unlocking tables             |   0.000031 |
| freeing items                |   0.000028 |
| updating status              |   0.000026 |
| cleaning up                  |   0.000041 |
+------------------------------+------------+

-- 현재 인스턴스의 메모리 사용 부분 
# CACHES AND LIMITS #
tmp_table_size                 = 64M
max_heap_table_size            = 64M

# 수정 
tmp_table_size                 = 512M
max_heap_table_size            = 512M



####################
개발

+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
| id   | select_type | table | type | possible_keys                        | key                                  | key_len | ref                         | rows | Extra                        |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+
|    1 | PRIMARY     | c     | ALL  | PRIMARY                              | NULL                                 | NULL    | NULL                        | 4668 | Using where; Using temporary |
|    1 | PRIMARY     | m     | ref  | PRIMARY,ox_campaigns_clientid        | ox_campaigns_clientid                | 3       | OPENX_V1_ADMIN.c.clientid   |    3 | Using where                  |
|    1 | PRIMARY     | assoc | ref  | ox_placement_zone_assoc_placement_id | ox_placement_zone_assoc_placement_id | 4       | OPENX_V1_ADMIN.m.campaignid |   76 | Using index                  |
+------+-------------+-------+------+--------------------------------------+--------------------------------------+---------+-----------------------------+------+------------------------------+


+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| id   | select_type | table | type   | possible_keys                                     | key                                  | key_len | ref                         | rows | Extra                                               |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
|    1 | PRIMARY     | m     | ref    | PRIMARY,ox_campaigns_clientid,ox_campaigns_status | ox_campaigns_status                  | 6       | const,const                 | 2728 | Using index condition; Using where; Using temporary |
|    1 | PRIMARY     | c     | eq_ref | PRIMARY                                           | PRIMARY                              | 3       | OPENX_V1_ADMIN.m.clientid   |    1 | Using where                                         |
|    1 | PRIMARY     | assoc | ref    | ox_placement_zone_assoc_placement_id              | ox_placement_zone_assoc_placement_id | 4       | OPENX_V1_ADMIN.m.campaignid |   76 | Using index                                         |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
    

##################


delivery2@localhost:OPENX_V1_ADMIN 12:21:04>select status, block_status, count(*)
    -> from ox_campaigns
    -> group by 1, 2
    -> 
    -> ;
+--------+--------------+----------+
| status | block_status | count(*) |
+--------+--------------+----------+
|      0 |              |      166 |
|      0 | pass         |     5400 | -- 정의역 
|      0 | block        |    12060 |
|      2 |              |        1 |
|      2 | pass         |       36 |
|      2 | block        |       20 |
|      3 |              |      112 |
|      3 | pass         |    20078 |
|      3 | block        |    18450 |
|      4 | pass         |        6 |
+--------+--------------+----------+

-- ox_campaigns index 구성

+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                       | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ox_campaigns |          0 | PRIMARY                        |            1 | campaignid        | A         |       50058 |     NULL | NULL   |      | BTREE      |         |               |
| ox_campaigns |          1 | ox_campaigns_clientid          |            1 | clientid          | A         |       16686 |     NULL | NULL   |      | BTREE      |         |               |
| ox_campaigns |          1 | ox_campaigns_campaign_group_id |            1 | campaign_group_id | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


-- 인덱스 생성
-- 그러나 status 값이 변동이 빈번 할 수 있음
-- (2차)

alter table ox_campaigns
    add key if not exists ox_campaigns_status (status, block_status) using BTREE ;

--
-- 개발 플랜
--
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
| id   | select_type | table | type   | possible_keys                                     | key                                  | key_len | ref                         | rows | Extra                                               |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+
|    1 | PRIMARY     | m     | ref    | PRIMARY,ox_campaigns_clientid,ox_campaigns_status | ox_campaigns_status                  | 6       | const,const                 | 2728 | Using index condition; Using where; Using temporary |
|    1 | PRIMARY     | c     | eq_ref | PRIMARY                                           | PRIMARY                              | 3       | OPENX_V1_ADMIN.m.clientid   |    1 | Using where                                         |
|    1 | PRIMARY     | assoc | ref    | ox_placement_zone_assoc_placement_id              | ox_placement_zone_assoc_placement_id | 4       | OPENX_V1_ADMIN.m.campaignid |   76 | Using index                                         |
+------+-------------+-------+--------+---------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-----------------------------------------------------+


--
-- 최종안
--

set global tmp_table_size = 512 * 1024 * 1024;
set global max_heap_table_size = 512 * 1024 * 1024;


Copying to tmp table on disk 사라짐

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000079 |
| checking permissions | 0.000015 |
| checking permissions | 0.000014 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000036 |
| After opening tables | 0.000016 |
| System lock          | 0.000016 |
| Table lock           | 0.000018 |
| init                 | 0.000040 |
| optimizing           | 0.000045 |
| statistics           | 0.000043 |
| preparing            | 0.000036 |
| executing            | 0.000015 |
| Creating tmp table   | 0.000038 |
| Copying to tmp table | 5.617840 |
| Sending data         | 1.745527 |
| end                  | 0.000019 |
| removing tmp table   | 0.024801 |
| end                  | 0.000015 |
| query end            | 0.000015 |
| closing tables       | 0.000013 |
| Unlocking tables     | 0.000018 |
| freeing items        | 0.000020 |
| updating status      | 0.000020 |
| cleaning up          | 0.000019 |
+----------------------+----------+