빠른 테스트를 위해 PostgreSQL 최적화

빠른 테스트를 위해 PostgreSQL 최적화

---

전형적인 Rails 애플리케이션을 위해 SQLite에서 PostgreSQL로 전환하고 있습니다.

문제는 PG로 인해 실행 사양이 느려 졌다는 것입니다.
SQLite에서는 ~ 34 초가 걸렸고 PG에서는 ~ 76 초로 2 배 이상 느립니다 .

이제 코드 수정없이 SQLite 와 동등하게 스펙의 성능 을 가져 오는 몇 가지 기술을 적용하려고합니다 (이상적으로는 연결 옵션을 설정하는 것만으로는 불가능합니다).

내 머리 꼭대기에서 분명한 몇 가지 사항은 다음과 같습니다.

• RAM 디스크 (OSX에서 RSpec을 사용하여 올바르게 설정하는 것이 좋습니다)
• 기록되지 않은 테이블 (전체 데이터베이스에 적용 할 수 있으므로 모든 스크립트를 변경하지 않아도됩니까?)

아시다시피 나는 신뢰성과 나머지 부분에 신경 쓰지 않습니다 (DB는 여기서 끔찍한 일입니다).
나는 PG를 최대한 활용하고 가능한 한 빨리 만들어야 한다 .

최선의 대답 은 이상적으로 , 그 설정, 그 트릭의 단점을 수행 하는 트릭 을 설명하는 것 입니다.

업데이트 : fsync = off + full_page_writes = off는 ~ 65 초 (~ -16 초)로 시간이 단축되었습니다. 좋은 출발이지만 34의 목표와는 거리가 멀다.

업데이트 2 : 내가 사용 RAM 디스크에 시도 하지만 성능 향상 오류 마진 이내였다. 따라서 가치가없는 것 같습니다.

업데이트 3 : * 가장 큰 병목 현상을 발견했으며 이제 내 사양이 SQLite만큼 빠릅니다.

문제는 잘린 데이터베이스 정리였습니다 . 분명히 SQLite는 너무 빠릅니다.

"수정" 하기 위해 각 테스트 전에 트랜잭션을 열고 마지막에 롤백합니다.

~ 700 개의 테스트를위한 일부 숫자.

• 잘림 : SQLite-34s, PG-76s.
• 거래 : SQLite-17s, PG-18s.

SQLite의 속도가 2 배 증가합니다. PG 속도가 4 배 증가합니다.

---

먼저 항상 최신 버전의 PostgreSQL을 사용하십시오. 성능 향상은 항상 제공되므로 이전 버전을 조정하는 경우 시간이 낭비 될 수 있습니다. 예를 들어 PostgreSQL 9.2는 속도를 크게 향상시키고TRUNCATE 인덱스 전용 스캔을 추가합니다. 사소한 릴리스조차도 항상 따라야합니다. 버전 정책을 참조하십시오 .

하지마

RAM 디스크 또는 기타 비 영구 스토리지에 테이블 스페이스를 두지 마십시오 .

테이블 스페이스가 손실되면 전체 데이터베이스가 손상되어 상당한 작업없이 사용하기 어려울 수 있습니다. UNLOGGED어쨌든 테이블을 사용 하고 캐시를 위해 많은 RAM을 사용하는 것과 비교할 때 이점이 거의 없습니다 .

램 디스크 기반 시스템을 원한다면 initdb램 디스크에 initdb새로운 PostgreSQL 인스턴스를 사용하여 램 디스크에 완전히 새로운 클러스터 가 있으므로 완전히 일회용 PostgreSQL 인스턴스가 있습니다.

PostgreSQL 서버 구성

테스트 할 때 내구성이 있지만 더 빠른 작동을 위해 서버를 구성 할 수 있습니다 .

이것은 fsync=offPostgreSQL 의 설정에 허용되는 유일한 용도 중 하나입니다 . 이 설정은 PostgreSQL에게 명령 된 쓰기 또는 다른 불쾌한 데이터 무결성 보호 및 충돌 안전 요소를 신경 쓰지 않도록 지시합니다.

말할 것도없이, fsync=offPg를 다른 곳에서 재생성 할 수있는 데이터의 임시 데이터베이스로 사용하지 않는 한 프로덕션 환경에서 사용하도록 설정해서는 안됩니다 . fsync를 해제하려는 경우에만 full_page_writes더 이상 효과가 없으므로 fsync를 끌 수도 있습니다 . 그 조심 fsync=off하고 full_page_writes상기 적용 클러스터 가 영향을 미치는, 그래서 수준 의 모든 PostgreSQL의 인스턴스에서 데이터베이스를.

대규모 데이터 손상 위험없이 동일한 이점을 많이 얻을 수 있으므로 프로덕션 용도로는를 사용 synchronous_commit=off하고 설정할 수 있습니다 . 비동기 커밋을 활성화하면 최근 데이터가 손실되는 작은 창이 있지만 그게 전부입니다.commit_delayfsync=off

DDL을 약간 변경하는 옵션이있는 경우 UNLOGGEDPg 9.1+의 테이블을 사용 하여 WAL 로깅을 완전히 피하고 서버가 충돌하는 경우 테이블이 삭제되는 대신 실제 속도를 높일 수 있습니다. 모든 테이블을 로그하지 않도록하는 구성 옵션이 없으므로이 옵션을 설정하는 동안 설정해야합니다 CREATE TABLE. 테스트에 유용 할뿐만 아니라 데이터베이스에 생성되거나 중요하지 않은 데이터로 가득 찬 테이블이 있거나 그렇지 않은 경우 안전해야하는 항목이있는 경우 편리합니다.

로그를 확인하고 너무 많은 체크 포인트에 대한 경고가 표시되는지 확인하십시오. 그렇다면 checkpoint_segments를 늘려야합니다 . 원활한 쓰기를 위해 checkpoint_completion_target을 조정할 수도 있습니다.

shared_buffers워크로드에 맞게 조정 하십시오. 이것은 OS에 따라 다르며 시스템에서 수행되는 다른 작업에 따라 다르며 시행 착오가 필요합니다. 기본값은 매우 보수적입니다. shared_buffersPostgreSQL 9.2 이하 에서 증가 시키면 OS의 최대 공유 메모리 제한을 늘려야 할 수도 있습니다. 9.3 이상은이를 피하기 위해 공유 메모리를 사용하는 방법을 변경했습니다.

If you're using a just a couple of connections that do lots of work, increase work_mem to give them more RAM to play with for sorts etc. Beware that too high a work_mem setting can cause out-of-memory problems because it's per-sort not per-connection so one query can have many nested sorts. You only really have to increase work_mem if you can see sorts spilling to disk in EXPLAIN or logged with the log_temp_files setting (recommended), but a higher value may also let Pg pick smarter plans.

As said by another poster here it's wise to put the xlog and the main tables/indexes on separate HDDs if possible. Separate partitions is pretty pointless, you really want separate drives. This separation has much less benefit if you're running with fsync=off and almost none if you're using UNLOGGED tables.

Finally, tune your queries. Make sure that your random_page_cost and seq_page_cost reflect your system's performance, ensure your effective_cache_size is correct, etc. Use EXPLAIN (BUFFERS, ANALYZE) to examine individual query plans, and turn the auto_explain module on to report all slow queries. You can often improve query performance dramatically just by creating an appropriate index or tweaking the cost parameters.

AFAIK there's no way to set an entire database or cluster as UNLOGGED. It'd be interesting to be able to do so. Consider asking on the PostgreSQL mailing list.

Host OS tuning

There's some tuning you can do at the operating system level, too. The main thing you might want to do is convince the operating system not to flush writes to disk aggressively, since you really don't care when/if they make it to disk.

In Linux you can control this with the virtual memory subsystem's dirty_* settings, like dirty_writeback_centisecs.

The only issue with tuning writeback settings to be too slack is that a flush by some other program may cause all PostgreSQL's accumulated buffers to be flushed too, causing big stalls while everything blocks on writes. You may be able to alleviate this by running PostgreSQL on a different file system, but some flushes may be device-level or whole-host-level not filesystem-level, so you can't rely on that.

This tuning really requires playing around with the settings to see what works best for your workload.

On newer kernels, you may wish to ensure that vm.zone_reclaim_mode is set to zero, as it can cause severe performance issues with NUMA systems (most systems these days) due to interactions with how PostgreSQL manages shared_buffers.

Query and workload tuning

These are things that DO require code changes; they may not suit you. Some are things you might be able to apply.

If you're not batching work into larger transactions, start. Lots of small transactions are expensive, so you should batch stuff whenever it's possible and practical to do so. If you're using async commit this is less important, but still highly recommended.

Whenever possible use temporary tables. They don't generate WAL traffic, so they're lots faster for inserts and updates. Sometimes it's worth slurping a bunch of data into a temp table, manipulating it however you need to, then doing an INSERT INTO ... SELECT ... to copy it to the final table. Note that temporary tables are per-session; if your session ends or you lose your connection then the temp table goes away, and no other connection can see the contents of a session's temp table(s).

If you're using PostgreSQL 9.1 or newer you can use UNLOGGED tables for data you can afford to lose, like session state. These are visible across different sessions and preserved between connections. They get truncated if the server shuts down uncleanly so they can't be used for anything you can't re-create, but they're great for caches, materialized views, state tables, etc.

In general, don't DELETE FROM blah;. Use TRUNCATE TABLE blah; instead; it's a lot quicker when you're dumping all rows in a table. Truncate many tables in one TRUNCATE call if you can. There's a caveat if you're doing lots of TRUNCATES of small tables over and over again, though; see: Postgresql Truncation speed

If you don't have indexes on foreign keys, DELETEs involving the primary keys referenced by those foreign keys will be horribly slow. Make sure to create such indexes if you ever expect to DELETE from the referenced table(s). Indexes are not required for TRUNCATE.

Don't create indexes you don't need. Each index has a maintenance cost. Try to use a minimal set of indexes and let bitmap index scans combine them rather than maintaining too many huge, expensive multi-column indexes. Where indexes are required, try to populate the table first, then create indexes at the end.

Hardware

Having enough RAM to hold the entire database is a huge win if you can manage it.

If you don't have enough RAM, the faster storage you can get the better. Even a cheap SSD makes a massive difference over spinning rust. Don't trust cheap SSDs for production though, they're often not crashsafe and might eat your data.

Learning

Greg Smith's book, PostgreSQL 9.0 High Performance remains relevant despite referring to a somewhat older version. It should be a useful reference.

Join the PostgreSQL general mailing list and follow it.

Reading:

• Tuning your PostgreSQL server - PostgreSQL wiki
• Number of database connections - PostgreSQL wiki

---

Use different disk layout:

• different disk for $PGDATA
• different disk for $PGDATA/pg_xlog
• different disk for tem files (per database $PGDATA/base//pgsql_tmp) (see note about work_mem)

postgresql.conf tweaks:

• shared_memory: 30% of available RAM but not more than 6 to 8GB. It seems to be better to have less shared memory (2GB - 4GB) for write intensive workloads
• work_mem: mostly for select queries with sorts/aggregations. This is per connection setting and query can allocate that value multiple times. If data can't fit then disk is used (pgsql_tmp). Check "explain analyze" to see how much memory do you need
• fsync and synchronous_commit: Default values are safe but If you can tolerate data lost then you can turn then off
• random_page_cost: if you have SSD or fast RAID array you can lower this to 2.0 (RAID) or even lower (1.1) for SSD
• checkpoint_segments: you can go higher 32 or 64 and change checkpoint_completion_target to 0.9. Lower value allows faster after-crash recovery

참고URL : https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing