Re: slow queries on large syslog table

hi all,

thanks for your help, its comforting but also kinda scary to know
im not the only one whos having trouble!

in response to questions....

as i mentioned before, the syslog_table is currently holds about
1.7 million rows and is constantly slowly growing, the hostid_table
and ciscomdgid_table each hold about 80 rows.

the number of rows returned when i ran the query below :
    SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN (23,3))
AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000
was 19 in all cases, so i guess? the row estimations are woefully
inaccurate.

i ran a vacuum analyse a few minutes prior to trying these queries.

my postgresql is version 7.1.3, is running on freebsd 4.3 and is the
standard ports install.

with regard to resources, heres the output of top mid select :
    last pid: 77402;  load averages:  0.17,  0.08,  0.03
up 87+05:05:42  23:36:25
    48 processes:  2 running, 46 sleeping
    CPU states: 11.6% user,  0.0% nice,  5.4% system,  0.0% interrupt, 82.9%
idle
    Mem: 47M Active, 139M Inact, 52M Wired, 8784K Cache, 35M Buf, 656K Free
    Swap:

      PID USERNAME PRI NICE  SIZE    RES STATE    TIME   WCPU    CPU COMMAND
    77400 pgsql      2   0  5956K  3568K RUN      0:04 21.08% 12.94% postgres
    77399 www        2   0  7896K  6776K select   0:01  2.32%  1.46% perl
    77257 pgsql      2   0  7292K  4832K sbwait   0:04  0.05%  0.05% postgres
    65374 root      10   0  3440K  2696K nanslp  41:00  0.00%  0.00% perl
    74942 pgsql      2   0  8048K  5876K sbwait   8:46  0.00%  0.00% postgres
    75116 root       2   0  2148K  1124K poll     1:30  0.00%  0.00% syslogd
(hmm ...seems like no swap device is configured, but theres loads of
inactive pages anyway i guess)

memory :
    hw.physmem: 264351744
cpu :
    CPU: Pentium III/Pentium III Xeon/Celeron (547.18-MHz 686-class CPU)
disk :
    1 x 9gig scsi

im not using the -B option so i guess im using the3 default number/size
buffers.

hope this helps!

thanks again for all your help a i am completely clueless!

colm ennis

-----Original Message-----
From: Antonio Fiol Bonnin [mailto:fiol@w3ping.com]
Sent: 13 December 2001 18:14
To: colm ennis
Cc: PostgreSQL General Mailing list
Subject: Re: [GENERAL] slow queries on large syslog table


Are the rows estimations "real"?


colm ennis wrote:

>hi antonio,
>
>thanks for your advice.
>
>ive tried a lot of different index combinations, with extremely variable
>results,
>for instance :
>    query - SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN
>(23,3)) AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000
>    matching messages - 19
>
>with original indexes :
>    query time(s) - 225
>    explain - Limit  (cost=0.00..34559.46 rows=1000 width=24)
>  ->  Index Scan Backward using syslog_table_stimestamp_index on
>syslog_table  (cost=0.00..577149.86 rows=16700 width=24)
>
>with antonios index :
>    create index syslog_table_stimestamp_shostid_sciscomsgid_index on
>syslog_table (stimestamp, shostid, sciscomsgid);
>    query time(s) - 174
>    explain - Limit  (cost=0.00..34329.14 rows=1000 width=24)
>  ->  Index Scan Backward using syslog_table_st_sh_sc_index on syslog_table
>(cost=0.00..580639.57 rows=16914 width=24)
>
>with NO! index :
>    query time(s) - 77
>    explain - Limit  (cost=73979.79..73979.79 rows=1000 width=24)
>  ->  Sort  (cost=73979.79..73979.79 rows=16905 width=24)
>        ->  Seq Scan on syslog_table  (cost=0.00..72591.62 rows=16905
>width=24)
>
>i got similarily confusing results from other queries.
>
>it occured to me that that the index antonio suggests is going to be huge
>because
>of the per second timestamp.
>
>i thought about what you said about deciding how I would search for data.
>
>for the query above :
>    SELECT stimestamp,shostid,smsg FROM syslog_table WHERE (shostid IN (23,3))
>AND (sciscomsgid IN (41,32,70)) ORDER BY stimestamp DESC LIMIT 1000
>i would lookup shostid and sciscomsgid in a combined index, and then load
>the
>indexed rows in syslog_table.  there are about 80 different shostids and
>sciscomsgids, so i guess the max size of this index would be 1600 rows.
>
>for other queries i would use an shostid index or sciscomsgid if just one
of
>these
>fields appeared in the select, avoiding the stimestamp at all cost because
>its index
>will be huge.
>
>so to test if this was any good i created the combined index :
>    create index syslog_table_sh_sc_index on syslog_table (shostid,
>sciscomsgid);
>but using explain found it isnt being used? :
>    explain - Limit  (cost=74018.18..74018.18 rows=1000 width=24)
>  ->  Sort  (cost=74018.18..74018.18 rows=16914 width=24)
>        ->  Seq Scan on syslog_table  (cost=0.00..72629.33 rows=16914
>width=24)
>
>i still dont understand how to use indexes to increase the speed of
queries.
>
>thanks for your help so far but i still feel lost,
>
>colm ennis
>
>-----Original Message-----
>From: Antonio Fiol Bonnin [mailto:fiol@w3ping.com]
>Sent: 13 December 2001 15:29
>To: colm ennis; PostgreSQL General Mailing list
>Subject: Re: [GENERAL] slow queries on large syslog table
>
>
>>
>>
>>ive also created a web interface for selecting syslogs based on optional
>>combinations of timestamp, hostname and ciscomsg.
>>
>
>Combinations is the *magic* word.
>
>>to speed queries i created some indexes on syslog_table :
>>    create index syslog_table_stimestamp_index on syslog_table (stimestamp);
>>    create index syslog_table_shostid_index on syslog_table (shostid);
>>    create index syslog_table_sciscomsgid_index on syslog_table
(sciscomsgid);
>>    create index syslog_table_shostid_sciscomsgid_index on syslog_table
>>(shostid,sciscomsgid);
>>
>Most of them are of no use. That's what you observed...
>
>I bet you will get much better perfs with:
>
>create index syslog_table_stimestamp_shostid_sciscomsg_index on
syslog_table
>(stimestamt, shostid, sciscomsg);
>
>You can try other combinations, but the one I suggested should be of use
>in case you use all three on the query, (or even if you use only the
>first, or the first two, though not sure about this last part, in
>parentheses).
>
>
>Believe me. Creating an index on a large table is of no use, unless it
>is the right one. As a rule of thumb, include in the index as many of
>the SELECTIVE columns present in the WHERE clause as you can.
>
>I am not sure of the selectivity of your columns (never used that
>particular structure). There should be some information about that on
>some of the system tables. However, I do not know in which, or how to
>get that info.
>
>As a second rule of thumb, think how YOU would search for the data you
>need if it was written on a paper book, and especially, how you would
>like to find the book ordered. For example, if you were to look the
>address corresponding to a phone number, you would like to find the data
>ordered by phone number, and not by name. Name is not of any use to you.
>
>For selectivity, think of finding the phone numbers of all people that
>live at number 5, but of any street, and whose first name is Peter.
>
>Neither "Peter" nor "5" are REALLY useful informations to perform your
>search. And even, Peter is more useful than 5.
>
>HTH,
>
>Antonio
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>.
>