Re: [Lug-bg] PgSQL table optimization.

[Илия Дюлгеров <iliad@xxxxxx>]

Marian Marinov wrote:

Здравейте колеги,

днес цял ден се мъча с една табличка на Postgre.

таблицата е следната:
cpustats=> \d stats
                                  Table "public.stats"
  Column  |            Type             |                      Modifiers
----------+-----------------------------+-----------------------------------------------------
 id       | integer                     | not null default 
nextval('stats_id_seq'::text)
 date     | timestamp without time zone |
 username | character varying(10)       | not null
 realtime | double precision            | default 0
 usertime | double precision            | default 0
 systime  | double precision            | default 0


Индексите с които съм пробвал са:

Indexes:
    "stats_id" unique, btree (id)
    "stats_24hour" btree (date) WHERE (date > '2008-06-25 13:09:33'::timestamp 
without time zone)
    "stats_24hourt" btree (date_trunc('hour'::text, date)) WHERE ((date 
  

'2008-06-25 13:09:33'::timestamp without time zone) AND (date < '2008-06-26 
    

13:10:57'::timestamp without time zone))
    "stats_date" btree (date)
    "stats_datetrunc" btree (date_trunc('hour'::text, date))
    "stats_dateuser" btree (date_trunc('hour'::text, date), username)
    "stats_realtime" btree (realtime)
    "stats_systime" btree (systime)
    "stats_usertime" btree (usertime)


А заявките са тези:
cpustats-> \d exec2
               View "public.exec2"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 date   | timestamp without time zone |
 count  | bigint                      |
View definition:
 SELECT date_trunc('hour'::text, stats.date) AS date, count(stats.date) AS 
count
   FROM stats
  WHERE stats.date::timestamp with time zone > (now() - '1 day'::interval)
  GROUP BY stats.date
  ORDER BY date;


cpustats-> \d daily_cpu_stats
          View "public.daily_cpu_stats"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 hours  | timestamp without time zone |
 rt     | double precision            |
 ut     | double precision            |
 st     | double precision            |
 count  | bigint                      |
View definition:
 SELECT date_trunc('hour'::text, stats.date) AS hours, sum(stats.realtime) AS 
rt, sum(stats.usertime) AS ut, sum(stats.systime) AS st, count(stats.systime) 
AS count
   FROM stats
  WHERE stats.date::timestamp with time zone > (now() - '1 day'::interval)
  GROUP BY date_trunc('hour'::text, stats.date)
  ORDER BY date_trunc('hour'::text, stats.date);

проблемът ми е че тези заявки ми отнемат повече от 15сек всяка и товарят 
ужастно. Независимо какви комбинации от индекси използвах.

Таблицата към момента има 1,400,000 записа и на ден ми ръсте със средно 
400,000.

Аааа да и едно ограничение, говорим си за Postgres 7.4. Аз сериозно си мисля 
да си направя експеримент с partitioning на по-нов postgres. 

Моля ако някой има някаква идея как това може да се оптимизира да се обади. 
Приемам всякакви предложения, промяна на таблицата и каквото и да е. 

Бирата е от мен.

Поздрави
  Marian Marinov
  System Architect at Siteground.com
_______________________________________________
Lug-bg mailing list
Lug-bg@xxxxxxxxxxxxxxxxxx
http://linux-bulgaria.org/mailman/listinfo/lug-bg
  

Привет признавам че не съм SQL спец а прост админ но ако искаш перформанс наистина треа да upgrandesh към  8.3 , новата версия е наистина в пъти по бърза ( миграцията става лесно казвам от опит)
машината/server-a трябва  да е на ниво (Athlon64/Opteron или Core2Duo) RAM памет да е достатъчно дисковете бързи и ако са няколко по добре.
от страна на настройки за по добра производителност има и значение конфигурацията  на postgresql.conf:

port = 5432                # (change requires restart)
max_connections = 220            # (change requires restart)
                    # (change requires restart)
                    # (change requires restart)


ssl = on                # (change requires restart)
                    # (change requires restart)
password_encryption = on



shared_buffers = 256MB        
temp_buffers = 64MB            # min 800kB
                    # (change requires restart)
work_mem = 32MB                # min 64kB
maintenance_work_mem = 128MB        # min 1MB


max_fsm_pages = 204800       


bgwriter_delay = 600ms            # 10-10000ms between rounds



fsync = off        
full_page_writes = on            # recover from partial page writes
wal_buffers = 1024kB            # min 32kB
                 

checkpoint_segments = 16    

enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on


effective_cache_size = 1024MB


geqo = on


default_statistics_target = 10    

log_destination = 'stderr'      

logging_collector = on          
log_directory = 'pg_log'    
log_truncate_on_rotation = on       
log_rotation_age = 7d            # Automatic rotation of logfiles will
                    # happen after that time.  0 to disable.
log_rotation_size = 0   
           
log_min_duration_statement = 1000    # -1 is disabled, 0 logs all statements
                 

log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on
log_line_prefix = '%h %t'          
log_lock_waits = on    


track_counts = on

autovacuum = off            # Enable autovacuum subprocess?  'on'
                 

datestyle = 'iso, mdy'
                 

lc_messages = 'en_US.UTF-8'         
                    # strings
lc_monetary = 'en_US.UTF-8'          

default_text_search_config = 'pg_catalog.english'

извадката по горе е от работещ постгре 8.3.0

regex_flavor = advanced        # advanced, extended, or basic

и задължително да се провери плана за изпъление на заявката с  http://explain-analyze.info/ и/или http://www.postgresql.org/docs/8.3/static/sql-explain.html

успех

-- 
 инж.Илия Дюлгеров  

_______________________________________________
Lug-bg mailing list
Lug-bg@xxxxxxxxxxxxxxxxxx
http://linux-bulgaria.org/mailman/listinfo/lug-bg