ประสบการณ์ การใช้งาน PostgreSQL

ช่วงนี้ต้องตรวจสอบการทำงานของ Database เพื่อทำการ รีดประสิทธิภาพให้ได้มากที่สุด คิดว่า 80GB น่าจะใหญ่แล้ว ยังมีคนทำ DB ใหญ่กว่าเราอีกแหะ

ref: http://osdir.com/ml/db.postgresql.sql/2002-04/msg00232.html

This is a collection of many performance tips that we've gathered together at Affymetrix, and I thought it would be useful to post them to the PostgreSQL news group. 

 The single most helpful trick has been the "Partial index trick" at the bottom and the use of temp tables.  Most of these tricks came from either this news group, or from my colleagues in the bioinformatics department, so I'd like to thank and acknowledge both groups.

 I'd like to thank Tom Lane, who clearly has been working very hard on the Optimizer, and all the other people who have worked on Postgres.  Your efforts have been invaluable to us.  Keep up the good work!

 We are currently working on a Transcriptome project, which is a follow-on to the human genome project, in which we systematically look across all parts of the genome to see what is expressed in the form of RNA.  It is publicly funded by the National Cancer Institute and the data is made publicly available at: http://www.netaffx.com/transcriptome/

  We currently have about 100GB of data and will soon grow to a multi-terabyte system.  We have tables of up to 1 billion rows and have been able to get ~1 million row queries to run in about 5 min.  We've been very pleased with postgres.  After a couple of major outages in our server room, it came back up flawlessly each time.  So it has been an invaluable asset for this project.  We run 7.2 on Red Hat on a 2-processor machine with SAN, and we have a 128-node linux cluster which will make analysis runs against the database.

  Our main request is continued enhancement of the optimizer for these heavy types of queries.  Improved use of indexes, ability to control execution plans explicitly, ability to use indexes for data retrieval without touching the table in certain cases, and other such features would be very useful.  I'm also curious to hear about whether there is any good clustering system for making a parallel postgres installation, and if others have experience with creating such large databases.

  We've been very happy and impressed with the constant improvements to the system.  Thank You!

This page is a long detailed list of performance tips for doing heavy duty queries.

• Indexes 1. Indexes are critical. Create exactly the combined (multi-field) indexes that are being joined in a particular join. The order of fields in the index and in the join must match exactly.
• Indexes 2. Multi-Field Indexes. Having indexes on individual columns as well as combinations of 2,3,and 4 columns can help. Sometimes is uses the 3 version, and sometimes it uses one 2 and one singlet index. This can be helpful, especially when seq scan is turned off and you are using limit.
• Indexes 3. Remember that multiple-field indexes must have the fields in the correct order as they are accessed in the query. An index can only be used to the extent allowed by the keys. An index over (A B C) can be used to find (A B), but not (B C).
• Vacuum. Always vacuum analyze the table(s) after creating indices (or loading/deleting data).
• Limit and Order by. May have to use order by and/or limit to use the indexes. May need to use order by with limit. Sometimes order by increases speed by causing use of an index. Sometimes it decreases speed because a Sort step is required. A where condition that is sufficiently restrictive may also cause an index to be used.
• Join Order. Order of fields, joins, and order by fields has a big impact.
• Casting 1. May have to explicitly cast things. For instance where x=3 must become (where x=cast(3 as smallint)). This can make a huge difference.
• Casting 2. Simply adding abs(destype)=(cast 111 as smallint) to my query and turning seq scans off seems to change the query execution plan. Writing this as (destype=111 or destype=-111) makes the cost over 7 times higher!!
• Seq Scans 1. Can you disable seq scans? Yes, you can type "set enable_seqscan=no;" at the psql prompt and disable it. Do not be surprised if this does not work though. You can also disable merges, joins, nested loops, and sorts. Try this and attempt to enable the correct combination that you want it to use.
• Seq Scans 2. In general you would like it to use an index, but don't be afraid to try the seq scans if cost is say < 150,000 and see if it it finishes in a few minutes. For large joins with no where clause, Postgres always uses seq scans. Try to add a where clause, even a non-restrictive one, and use an index. However, remember that postgres must go get the table data too, so this can be more costly. Postgres cannot read data solely from an index (some commercial databases can).
• Seq Scans 3. Sometimes it is true that seq scans are faster. It tries to use the analyzed statistics to decide which is better. But don't always trust it, try it both ways. This is why analyzing your table will produce different execution plans at after analysis -- The analysis step will update the stats of the table. The change in estimated costs might cause a different plan to be chosen.
• Explain Output. Reading the Explain output can be confusing. In general, the numbers are a range. If you are trying to just get some rows back, you'd like the left most number to be 0. This means that the right-most number will probably not happen, because you will not really have to search the entire table. The right-most number is an upper bound. The numbers sum as you go up. What you don't want is a large number for both the min and max. Sometimes a cost of about 100,000 takes about 3 minutes. Sometimes this is not accurate. Sometimes I was able to to see a lower seq scan cost, but when I disable seq scans and used indexes, the actual performance was faster.  In general the cost is in milliseconds.  Use Explain Analyze which will run through they query and produce actual times.
• SQL tricks. Remember the standard SQL tricks which I will not cover here (get a good thick SQL book). For example using Like, etc. can be slow. Remember that if there is no data in your table for a given where clause, it must scan the entire result just to tell you "no results found" so know your data in advance.
• Nested loops are probably the most expensive operation.
• Having several merges and sorts can be way better than having a single nestloop in your query.
• Explicit Joins. For more than 2 joined tables, consider using explicit joins (see:http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html)
• News Groups. Try the postgres news groups: http://www.us.postgresql.org/users-lounge/index.html
• Hardware/Configuration changes. I won't go into a lot of detail here as this page is more about the query optimizer, but you can look at how much your CPU and memory is being taxed, and try running postmaster with various flags to increase speed and memory. However, if your query plan is not coming out right this will have little impact.
• Identities. You can try typing "and a.id=a.id" and this will actually help encourage the query planner to use an index. In one example, select with x=x and y=y order by x worked best (order by y too made it worse!).
• Temp tables. You may want to explicitly control the query by breaking it into several steps, with intermediate tables being created along the way. You can make these true temp tables, which will go away when you log out, or you may want to keep them around. You might want to create a procedure or script that automates/hides this process.
• Views. Views sometimes say that they are adding a step to the query planner, but it does not seem to impact query speed. But if you add more clauses to the view this may change the query plan in a bad way, which is confusing to the user.
• Stored Procedures. Try writing a stored procedure to more explicitly control the query execution. If you do this break out SQL into many small cursors instead of 1 large cursor, otherwise you will run up against the same problems.
• External programs. As above, breaking out a query into a series of small, explicit nested loops in a C, Perl, or other client program, may actually improve performance (especially if you want a subset of results/tables).
• Monitor Query Progress. Alan Williams provided a good trick to monitor the progress of a long running query. If you add to the query a sequence (select nextval('sq_test'),...) then you can use select currval('sq_test') to see how far the query has progressed.
• Partial Indices. You can use this feature to force use of an index!!! (it is also useful as a true partial index). Assume table1 below has no rows where field1=0. By doing the actions below, it stores the clause field1<>0 in pg_index and when it sees that predicate, it always uses the partial index. In this case we are using it as a full index to trick it. Example:

         create index i on table1(field1) where field1 <> 0;

     select * from table1 where field1<>0;

Shane Brubaker

BioInformatics Engineer

Affymetric, Inc.

PostgreSQL กับการกำหนดค่าที่น่าสนใจของ WEB, OLPT, DW

Well, that doesn't help unless we either provide a .conf generation tool (something I favor) or docs somewhere which explain which are the variables to be the most concerned with instead of making users read through all 218 of them.

Attached is the postgresql.conf.simple I used in my presentaiton. It has an egregious math error in it (see if you can find it) but should give you the general idea.

--Josh

# ----------------------------------------
# Simple PostgreSQL Configuration File
# ----------------------------------------

# This file provides a simple configuration with the most common options
# which most users need to modify for running PostgreSQL in production, 
# including extensive notes on how to set each of these.  If your configuration
# needs are more specific, then use the standard postgresql.conf, or add 
# additional configuration options to the bottom of this file.
#
# This file is re-read when you send a SIGHUP to the server, or on a full
# restart.  Note that on a SIGHUP simply recommenting the settings is not
# enough to reset to default value; the last explicit value you set will
# still be in effect.
#
# AvRAM:  Several of the formulas below ask for "AvRAM", which is short for
# "Available RAM".  This refers to the amount of memory which is available for
# running PostgreSQL.  On a dedicated PostgreSQL server, you can use the total
# system RAM, but on shared servers you need to estimate what portion of RAM
# is usually available for PostgreSQL.
#
# Each setting below lists one recommended starting setting, followed by
# several alternate settings which are commented out.  If multiple settings
# are uncommented, the *last* one will take effect.

# listen_addresses
# ------------------------
# listen_addresses takes a list of network interfaces the Postmaster will
# listen on.  The setting below, '*', listens on all interfaces, and is only
# appropriate for development servers and initial setup.  Otherwise, it 
# should be restrictively set to only specific addresses. Note that most
# PostgreSQL access control settings are in the pg_hba.conf file.

  listen_addresses = '*' # all interfaces 
# listen_addresses = 'localhost'  # unix sockets and loopback only
# listen_addresses = 'localhost,192.168.1.1' # local and one external interface

# max_connections
# ------------------------
# An integer setting a limit on the number of new connection processes which 
# PostgreSQL will create.  Should be set to the maximum number of connections 
# which you expect to need at peak load.  Note that each connection uses
# shared_buffer memory, as well as additional non-shared memory, so be careful
# not to run the system out of memory.  In general, if you need more than 1000
# connections, you should probably be making more use of connection pooling.
# 
# Note that by default 3 connections are reserved for autovacuum and 
# administration.

  max_connections = 200  # small server
# max_connections = 700  # web application database
# max_connections = 40   # data warehousing database

# shared_buffers
# ------------------------
# A memory quantity defining PostgreSQL's "dedicated" RAM, which is used
# for connection control, active operations, and more.  However, since
# PostgreSQL also needs free RAM for file system buffers, sorts and 
# maintenance operations, it is not advisable to set shared_buffers to a
# majority of RAM.  
#
# Note that increasing shared_buffers often requires you to increase some 
# system kernel parameters, most notably SHMMAX and SHMALL.  See 
# Operating System Environment: Managing Kernel Resources in the PostgreSQL
# documentation for more details.  Also note that shared_buffers over 2GB is 
# only supported on 64-bit systems.
#
# The setting below is a formula.  Calculate the resulting value, then
# uncomment it.  Values should be expressed in kB, MB or GB.

# shared_buffers = ( AvRAM / 4 )
# shared_buffers = 512MB   # basic 2GB web server
# shared_buffers = 8GB     # 64-bit server with 32GB RAM

# work_mem
# ------------------------
# This memory quantity sets the limit for the amount of non-shared RAM 
# available for each query operation, including sorts and hashes.  This limit
# acts as a primitive resource control, preventing the server from going
# into swap due to overallocation.  Note that this is non-shared RAM per
# *operation*, which means large complex queries can use multple times
# this amount.  Also, work_mem is allocated by powers of two, so round
# to the nearest binary step.

# The setting below is a formula.  Calculate the resulting value, then                                                                                                              
# uncomment it.  Values should be expressed in kB, MB or GB.  Maximum
# is currently 2GB.

# Most web applications should use the formula below, because their 
# queries often require no work_mem. 
# work_mem = ( AvRAM / max_connections ) ROUND DOWN to 2^x
# work_mem = 2MB  # for 2GB server with 700 connections  

# Formula for most BI/DW applications, or others running many complex
# queries:
# work_mem = ( AvRAM / ( 2 * max_connections ) ) ROUND DOWN to 2^x
# work_mem = 128MB   # DW server with 32GB RAM and 40 connections 

# maintenance_work_mem
# -------------------------
# This memory value sets the limit for the amount that autovacuum, 
# manual vacuum, bulk index build and other maintenance routines are 
# permitted to use.  Setting it to a moderately high value will increase
# the efficiency of vacuum and other operations.

# The setting below is a formula.  Calculate the resulting value, then                                                                                                                   
# uncomment it.  Values should be expressed in kB, MB or GB.  
# Maximum is currently 2GB.                                                                                                                           

# Formula for most databases
# maintenance_work_mem = ( AvRAM / 8 ) ROUND DOWN to 2^x
# maintenance_work_mem = 256MB  #webserver with 2GB RAM
# maintenance_work_mem = 2GB  #DW server with 32GB RAM

# max_fsm_pages
# --------------------------
# An integer which sets the maximum number of data pages with free space 
# which the Postmaster will track.  Setting this too low can lead to 
# table bloat and need for VACUUM FULL.  Should be set to the maximum number
# of data pages you expect to be updated between vacuums. 
#
# Increasing this setting requires dedicated RAM and like shared_buffers
# may require to to increase system kernel parameters.  Additionally, the
# recommended setting below is based on the default autovacuum settings;
# if you change the autovacuum parameters, then you may need to adjust
# this setting to match.

# The setting below is a formula.  Calculate the resulting value, then                                                                                                                   
# uncomment it.  DBsize is your estimate of the maximum size of the database;
# if the database is already loaded, you can get his from pg_database_size().
# For large data warehouses, use the volume of data which changes between 
# batch loads as your "DBSize"

# For small databases ( less than 10GB )
# max_fsm_pages = ( ( DBsize / 8kB ) / 8 )
# max_fsm_pages = 100000  #6GB web database 

# For larger databases ( Many GB to a few TB )
# max_fsm_pages = ( ( DBsize / 8kB ) / 16 )
# max_fsm_pages = 800000  #100GB OLTP database
# max_fsm_pages = 4000000  #DW loading 0.5TB data daily

# synchronous_commit
# -------------------------
# This boolean setting controls whether or not all of your transactions
# are gauranteed to be written to disk when they commit.  If you are
# willing to lose up to 0.4 seconds of data in the event of an unexpected 
# shutdown (as many web applications are), then you can gain substantial
# performance benefits by turning off synchronous commit.  For most
# applications, however, this setting is better used on a per-session 
# basis.

  synchronous_commit = on   #most applications
# synchronous_commit = off  #if speed is more important than data

# wal_buffers
# -------------------------
# this memory setting defines how much buffer space is available for 
# the Write Ahead Log.  Set too low, it can become a bottleneck on 
# inserts and updates; there is no benefit to setting it high, however.
# As with some of the other settings above, may require increasing
# some kernel parameters.

wal_buffers = 8MB
 
# checkpoint_segments
# -------------------------
# This integer defines the maximum number of 8MB transaction log segments
# PostgreSQL will create before forcing a checkpoint.  For most
# high-volume OTLP databases and DW you will want to increase this
# setting significantly.  Alternately, just wait for checkpoint 
# warnings in the log before increasing this.
#
# Increasing this setting can make recovery in the event of unexpected 
# shutdown take longer.
#
# Maximum disk space required is (checkpoint_segments * 2 + 1) * 16MB, 
# so make sure you have that much available before setting it.

checkpoint_segments = 16    #normal small-medium database
# checkpoint_segments = 64  #high-volume OLTP database
# checkpoint_segments = 128 #heavy-ETL large database

# autovacuum
# ---------------------------
# autovacuum turns on a maintenance daemon which runs in the background, 
# periodically cleaning up your tables and indexes.  The only reason to turn
# autovacuum off is for large batch loads (ETL).

  autovacuum = on   #most databases
# autovacuum = off  #large DW

# effective_cache_size
# --------------------------
# This memory setting tells the PostgreSQL query planner how much RAM
# is estimated to be available for caching data, in both shared_buffers and
# in the filesystem cache. This setting just helps the planner make good
# cost estimates; it does not actually allocate the memory.

# The setting below is a formula.  Calculate the resulting value, then                                                                                                                   
# uncomment it.

# effective_cache_size = ( AvRAM * 0.75 )

# default_statistics_target
# --------------------------
# This integer setting determines the histogram sample size for the 
# data about table contents kept by the query planner.  The default
# is fine for most databases, but often users need to increase it 
# either because they're running data warehouses or because they have
# a lot of poorly planned queries.

default_statistics_target = 10
# default_statistics_target = 200  #have had some bad plans
# default_statistics_target = 400  #data warehouse

# constraint_exclusion
# --------------------------
# This boolean setting should be turned "on" if you plan to use table 
# partitioning.  Otherwise, it should be "off".

  constraint_exclusion = off #in general
# constraint_exclusion = on  #if you plan to use partitioning

# log_destination & logging settings
# --------------------------
# This ENUM value determines where PostgreSQL's logs are sent.  What
# setting to use really depends on your server room setup and the 
# production status and OS of your server.
#
# Note that there are several dozen settings on what and how often
# to log; these will not be covered in detail in this quick 
# configuration file.  Instead, several common combinations are
# given.

# Syslog setup for centralized monitoring
# log_destination = 'syslog'
# syslog_facility = 'LOCAL0'  #local syslog
# syslog_facility = 'log_server_name'  #remote syslog

# Windows
# log_destination = 'eventlog'

# Private PostgreSQL Log
# log_destination = 'stderr'
# log_collector = on
# log_directory = '/path/to/log/dir'

# CSV logging for collecting performance statistics.
# Warning: this much logging will generate many log
# files and affect performance.
# log_destination = 'csvlog'
# log_collector = on
# log_directory = '/path/to/log/dir'
# log_duration = on
# log_temp_files = 256kB
# log_statement = 'all'

เดี๋ยวค่อยมาอธิบายครับ รอหน่อย

Don't speak Business to Me.

May i was in wrong place and situation!

I hate business talking because I'm an engineer. Business always speak with Finance and trick. That why i dont like it.

Please give me direct words! Because it's complicated.

But if it has to be. Please tell me first!

ท่านคึกฤทธิ์ ว่าไว้

ม.ร.ว.คึกฤทธิ์ ปราโมช
หนังสือพิมพ์สยามรัฐสัปดาหวิจารณ์
18 ตุลาคม 2502


          สัปดาห์นี้มีเรื่องความเมืองใหญ่         ไทยถูกฟ้องขับไล่ขึ้นโรงศาล

          เคยเป็นเรื่องโต้เถียงกันมานาน          ที่ยอดเขาพระวิหารรู้ทั่วกัน

          กะลาครอบมานานโบราณว่า             พอแลเห็นท้องฟ้าก็หุนหัน

          คิดว่าตนนั้นใหญ่ใครไม่ทัน              ทำกำเริบเสิบสันทุกอย่างไป

          อันคนไทยนั้นสุภาพไม่หยาบหยาม      เห็นใครหย่อนอ่อนความก็ยกให้

          ถึงล่วงเกินพลาดพลั้งยังอภัย             ด้วยเห็นใจว่ายังเยาว์เบาความคิด

          เขียนบทความด่าตะบึงถึงหัวหู           ไทยก็ยังนิ่งอยู่ไม่ถือผิด

          สั่งถอนทูตเอิกเกริกเลิกเป็นมิตร          แล้วกลับติดตามต่อขอคืนดี

          ไทยก็ยอมตามใจไม่ดึงดื้อ                เพราะไทยถือเขมรผองเหมือนน้องพี่

          คิดตกลงปลงกันได้ด้วยไมตรี            ถึงคราวนี้ใจเขมรแลเห็นกัน

          หากไทยจำล้ำเลิกบ้างอ้างขอบเขต     เมืองเขมรทั้งประเทศของใครนั่น ?

          ใครเล่าตั้งวงศ์กษัตริย์ปัจจุบัน            องค์ด้วงนั้นคือใครที่ไหนมา ?

          เป็นเพียงเจ้าไม่มีศาลซมซานวิ่ง         ได้แอบอิงอำนาจไทยจึงใหญ่กล้า

          ทัพไทยช่วยปราบศัตรูกู้พารา            สถาปนาจัดระบอบให้ครอบครอง

          ได้เดชไทยไปคุ้มกะลาหัว                 จึงตั้งตัวขึ้นมาอย่างจองหอง

          เป็นข้าขัณฑสีมาฝ่าละออง               ส่งดอกไม้เงินทองตลอดมา

          ไม่เหลียวดูโภไคไอศวรรย์                ทั้งเครื่องราชกกุธภัณฑ์เป็นหนักหนา

          ฝีมือไทยแน่นักประจักษ์ตา               เพราะทรงพระกรุณาประทานไป

          มีพระคุณจุนเจือเหลือประมาณ          ถึงลูกหลานกลับเนรคุณได้

          สมกับคำโบราณท่านว่าไว้               อย่าไว้ใจเขมรเห็นจริงเอย