Обсуждение: Is that a bug?
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/
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.
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.
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/
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!
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/ >
--- 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/
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
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.$$