Thread: Is that a bug?

Is that a bug?

From
R D
Date:
Hi,
I have some problems with postgresql 7.0.3 running on
Slackware linux 7.1.

I have a table with about 25 milion records:

create table gsc_act(reg_id int2,id int2,ra int4, de
int4,mag int2);

i created 2 indexes:

create index gsc_act_de on gsc_act(de);
create index gsc_act_rd on gsc_act(de,ra);

so every thing was ok with queries:
1. select * from gsc_act where de>=-300000
   and de<300000 and ra<=600000 and ra>=0;
2. select * from gsc_act where de>=-300000
   and de<300000 and ra<=600000 and ra>=0
   and mag<300;

except the second one was too slow and I created third
index:

create index gsc_act_rdm on gsc_act(de,ra,mag);

I executed query 2 and it hang for 5-6 min with
permanent disk activity even Ctrl-C could not stop it.
I killed postmaster and droped the third index. Now
both queries hang the same way. Is there any way to
fix it? This are the most used queries with the
table(only the values are different).
Is this some bug in postgres?
Please help!

Rumen

__________________________________________________
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/

Re: Is that a bug?

From
Jean-Arthur Silve
Date:
Hello !

I use POSTGRES 6.4...

Everuthing was working ok for months, but since 3 days, I have sometimes
when my PHP scrits try to connect :


Warning: Unable to connect to PostgresSQL server: connectDB():
getprotobyname failed in /opt/htdocs/html/php/atos_lib.phtml on line 3

Have you any idea ??




At 00:14 21/12/00 -0800, R D wrote:
>Hi,
>I have some problems with postgresql 7.0.3 running on
>Slackware linux 7.1.
>
>I have a table with about 25 milion records:
>
>create table gsc_act(reg_id int2,id int2,ra int4, de
>int4,mag int2);
>
>i created 2 indexes:
>
>create index gsc_act_de on gsc_act(de);
>create index gsc_act_rd on gsc_act(de,ra);
>
>so every thing was ok with queries:
>1. select * from gsc_act where de>=-300000
>    and de<300000 and ra<=600000 and ra>=0;
>2. select * from gsc_act where de>=-300000
>    and de<300000 and ra<=600000 and ra>=0
>    and mag<300;
>
>except the second one was too slow and I created third
>index:
>
>create index gsc_act_rdm on gsc_act(de,ra,mag);
>
>I executed query 2 and it hang for 5-6 min with
>permanent disk activity even Ctrl-C could not stop it.
>I killed postmaster and droped the third index. Now
>both queries hang the same way. Is there any way to
>fix it? This are the most used queries with the
>table(only the values are different).
>Is this some bug in postgres?
>Please help!
>
>Rumen
>
>__________________________________________________
>Do You Yahoo!?
>Yahoo! Shopping - Thousands of Stores. Millions of Products.
>http://shopping.yahoo.com/

----------------------------------------------------------------
Le simple fait de passer par la fenetre ne suffit pas a la transformer en porte.


getprobyname problem

From
Jean-Arthur Silve
Date:
Hello !

I use POSTGRES 6.4...

Everuthing was working ok for months, but since 3 days, I have sometimes
when my PHP scrits try to connect :


Warning: Unable to connect to PostgresSQL server: connectDB():
getprotobyname failed in /opt/htdocs/html/php/atos_lib.phtml on line 3

Have you any idea ??



----------------------------------------------------------------
Le simple fait de passer par la fenetre ne suffit pas a la transformer en porte.


Re: Is that a bug?

From
R D
Date:
No, plain select also does not work.

--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
>
> Hmm, does a plain select * from gsc_act work?
> Also, what does explain show for the queries?
>
> Stephan Szabo
> sszabo@bigpanda.com
>
> On Thu, 21 Dec 2000, R D wrote:
>
> > Hi,
> > I have some problems with postgresql 7.0.3 running
> on
> > Slackware linux 7.1.
> >
> > I have a table with about 25 milion records:
> >
> > create table gsc_act(reg_id int2,id int2,ra int4,
> de
> > int4,mag int2);
> >
> > i created 2 indexes:
> >
> > create index gsc_act_de on gsc_act(de);
> > create index gsc_act_rd on gsc_act(de,ra);
> >
> > so every thing was ok with queries:
> > 1. select * from gsc_act where de>=-300000
> >    and de<300000 and ra<=600000 and ra>=0;
> > 2. select * from gsc_act where de>=-300000
> >    and de<300000 and ra<=600000 and ra>=0
> >    and mag<300;
> >
> > except the second one was too slow and I created
> third
> > index:
> >
> > create index gsc_act_rdm on gsc_act(de,ra,mag);
> >
> > I executed query 2 and it hang for 5-6 min with
> > permanent disk activity even Ctrl-C could not stop
> it.
> > I killed postmaster and droped the third index.
> Now
> > both queries hang the same way. Is there any way
> to
> > fix it? This are the most used queries with the
> > table(only the values are different).
> > Is this some bug in postgres?
> > Please help!
>
>
> .
> me bug in postgres?
> > Please help!
>
>
> .
>


__________________________________________________
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/

Re: Is that a bug?

From
Stephan Szabo
Date:
Hmm, does a plain select * from gsc_act work?
Also, what does explain show for the queries?

Stephan Szabo
sszabo@bigpanda.com

On Thu, 21 Dec 2000, R D wrote:

> Hi,
> I have some problems with postgresql 7.0.3 running on
> Slackware linux 7.1.
>
> I have a table with about 25 milion records:
>
> create table gsc_act(reg_id int2,id int2,ra int4, de
> int4,mag int2);
>
> i created 2 indexes:
>
> create index gsc_act_de on gsc_act(de);
> create index gsc_act_rd on gsc_act(de,ra);
>
> so every thing was ok with queries:
> 1. select * from gsc_act where de>=-300000
>    and de<300000 and ra<=600000 and ra>=0;
> 2. select * from gsc_act where de>=-300000
>    and de<300000 and ra<=600000 and ra>=0
>    and mag<300;
>
> except the second one was too slow and I created third
> index:
>
> create index gsc_act_rdm on gsc_act(de,ra,mag);
>
> I executed query 2 and it hang for 5-6 min with
> permanent disk activity even Ctrl-C could not stop it.
> I killed postmaster and droped the third index. Now
> both queries hang the same way. Is there any way to
> fix it? This are the most used queries with the
> table(only the values are different).
> Is this some bug in postgres?
> Please help!



Re: Is that a bug?

From
Stephan Szabo
Date:
Does dropping and recreating the index help?
Does explain work on the query?

Stephan Szabo
sszabo@bigpanda.com

On Thu, 21 Dec 2000, R D wrote:

> No, plain select also does not work.
>
> --- Stephan Szabo <sszabo@megazone23.bigpanda.com>
> wrote:
> >
> > Hmm, does a plain select * from gsc_act work?
> > Also, what does explain show for the queries?
> >
> > Stephan Szabo
> > sszabo@bigpanda.com
> >
> > On Thu, 21 Dec 2000, R D wrote:
> >
> > > Hi,
> > > I have some problems with postgresql 7.0.3 running
> > on
> > > Slackware linux 7.1.
> > >
> > > I have a table with about 25 milion records:
> > >
> > > create table gsc_act(reg_id int2,id int2,ra int4,
> > de
> > > int4,mag int2);
> > >
> > > i created 2 indexes:
> > >
> > > create index gsc_act_de on gsc_act(de);
> > > create index gsc_act_rd on gsc_act(de,ra);
> > >
> > > so every thing was ok with queries:
> > > 1. select * from gsc_act where de>=-300000
> > >    and de<300000 and ra<=600000 and ra>=0;
> > > 2. select * from gsc_act where de>=-300000
> > >    and de<300000 and ra<=600000 and ra>=0
> > >    and mag<300;
> > >
> > > except the second one was too slow and I created
> > third
> > > index:
> > >
> > > create index gsc_act_rdm on gsc_act(de,ra,mag);
> > >
> > > I executed query 2 and it hang for 5-6 min with
> > > permanent disk activity even Ctrl-C could not stop
> > it.
> > > I killed postmaster and droped the third index.
> > Now
> > > both queries hang the same way. Is there any way
> > to
> > > fix it? This are the most used queries with the
> > > table(only the values are different).
> > > Is this some bug in postgres?
> > > Please help!
> >
> >
> > .
> > me bug in postgres?
> > > Please help!
> >
> >
> > .
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Shopping - Thousands of Stores. Millions of Products.
> http://shopping.yahoo.com/
>


Re: Is that a bug?

From
R D
Date:
--- Stephan Szabo <sszabo@megazone23.bigpanda.com>
wrote:
>
> Does dropping and recreating the index help?

10x! It worked!

Rumen

__________________________________________________
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/

Re: Is that a bug?

From
Tomaz Borstnar
Date:
At 06:59 27.12.2000, R D wrote the following message:
> >
> > Does dropping and recreating the index help?
>
>10x! It worked!

Anyone have some utility to recreate the index for any given database? This
way I don't have to study the database and do lots of manual work which
computers can do so much faster.

Tomaz


----
Tomaz Borstnar <tomaz.borstnar@over.net>
"Love is the answer to the final question you ask" - Unknown


Re: Is that a bug?

From
"Todd R. Eigenschink"
Date:
Tomaz Borstnar writes:
>At 06:59 27.12.2000, R D wrote the following message:
>> >
>> > Does dropping and recreating the index help?
>>
>>10x! It worked!
>
>Anyone have some utility to recreate the index for any given database? This
>way I don't have to study the database and do lots of manual work which
>computers can do so much faster.

This is what I think you're asking for, and then some.

It's a grotesque mess of perl and shell script that we use nightly to
vacuum everything.  Since it's usually (at least for our databases) a
lot faster to drop each index, vacuum the tables, and then recreate
the index, that's what this does.

This does all tables in all databases, and assumes that they're all
owned by the same user (www in our case).  It uses "pg_dump -s" to get
the strings used to build each index (cheating :) and then just feeds
them back when it's done.  It fits our scenario nicely, but your
mileage will probably vary wildly.

One of the reasons it's wacky is that since it's run from cron, I
don't want to see the output unless there's a problem.  So this
collects the output of the vacuum, and then just eats it unless
there's a warning or error of some kind.  (I could have done the whole
thing in perl, but the first version of it was mostly shell, and then
it grew.  Kind of like a fungus.)

Developers, feel free to include this or something like it with the
PostreSQL distribution.  Credit me or not; doesn't matter.


Todd
--
Todd R. Eigenschink             TEK Interactive Group, Inc.
todd@tekinteractive.com         http://www.tekinteractive.com/
System Administrator            (219) 459-2521


----------------------------------------------------------------------
#!/bin/sh

PATH=/usr/pgsql/bin:$PATH

if [ `whoami` != 'www' ]; then
    echo "autovac must be run by www!"
    exit 1
fi

psql -l -A -q -t| tr '|' ' ' | grep -v '^template1 ' | \
while read DATABASE PGUSERID DATAPATH
do
    perl - $DATABASE <<'EOF' \
        | psql -e $DATABASE > /tmp/db.psql.$$ 2>&1
my $database = shift;

open SCHEMA, "/usr/pgsql/bin/pg_dump -s $database |";
while (<SCHEMA>)
{
    if (/CREATE TABLE \"(\S+)\"/)
    { $table{$1} = []; }
    elsif (/CREATE\s+INDEX\s+\"\S+\" on \"(\S+)\" using/)
    { push @{$table{$1}}, $_; }
}
close SCHEMA;

foreach my $table (sort keys %table)
{
    foreach (@{$table{$table}})
    {
    /CREATE\s+INDEX\s+\"(\S+)\"\s+on/;
    print "DROP INDEX $1;\n";
    }
    print "VACUUM ANALYZE $table;\n";
    foreach (@{$table{$table}})  { print; }
}
EOF

    perl - $DATABASE /tmp/db.psql.$$ <<'PROCESS'
my $database = shift;
my ($line, $mark);
while (<>)
{
    $mark = 1 if $_ !~ /^(VACUUM|CREATE|DROP)/;
    $line .= $_;
}

#(print "No errors, but here's the output:\n"), $mark = 1 if not $mark;
if ($mark)
{
    print "================================================================\n";
    print "DATABASE: $database\n";
    print $line;
    print "================================================================\n";
}
PROCESS

done

rm -f /tmp/db.psql.$$