Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?
at 2013-01-12 02:21, Tom Lane wrote: =?gb2312?B?wO66o8H6?= hailong...@qunar.commailto:hailong...@qunar.com writes: This time I will give you the contents of the table route_raw, the download link is https://www.box.com/s/yxa4yxo6rcb3dzeaefmz or http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz . Thanks for the data, but I still can't reproduce the problem :-(. Can you confirm whether loading this dump into an empty database and running your test case (15 instances of that script) fails for you? regards, tom lane Yes,I do confirm that. I consider to try to do further test in order to reproduce it again in some way you can reproduce it simply. Thanks again Best Regards!
Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?
=?gb2312?B?wO66o8H6?= hailong...@qunar.com writes: This time I will give you the contents of the table route_raw, the download link is https://www.box.com/s/yxa4yxo6rcb3dzeaefmz or http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz . Thanks for the data, but I still can't reproduce the problem :-(. Can you confirm whether loading this dump into an empty database and running your test case (15 instances of that script) fails for you? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?
Hi,dear tom lane pgsql-hackers
In your last e-mail, you want me to think anbout the only spgist-indexed
column and give you some more further information.
This time I will give you the contents of the table route_raw, the download
link is https://www.box.com/s/yxa4yxo6rcb3dzeaefmz or
http://dl.dropbox.com/u/203288/route_raw_spgist.sql.tar.gz .
OS Version:
CentOS release 5.5 (Final)
PostgreSQL Version:
postgres=# select version();
version
---
PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-50), 64-bit
(1 row)
I create a database named test_spgist,and there is only one table named
route_raw which only has a id (primary key ) column and a spgist index column
in the database.
Some details is shown as following:
test_spgist=# \dt
List of relations
Schema | Name| Type | Owner
+---+---+--
public | route_raw | table | postgres
(1 row)
test_spgist=# \d route_raw
Table public.route_raw
Column | Type | Modifiers
---+-+
sourceurl | character varying(1000) | not null default ''::character varying
id| integer | not null default
nextval('route_raw_id_seq'::regclass)
Indexes:
route_raw_pkey PRIMARY KEY, btree (id)
route_raw_sourceurl_idx spgist (sourceurl)
--Before the test, this table has 997736 records.
test_spgist=# select count(1) from route_raw ;
count
997736
(1 row)
1. edit a shell script that function is insert data to table route_raw
$ cat /tmp/insert_spgist.sh
#/bin/bash
PSQL=/opt/pg92/bin/psql
DB=test_spgist
USR=postgres
while [ 10 -ne 9 ]
do
SQL=insert into route_raw(sourceurl) values
('http://suz.bacts.com/group/group_33_128972.aspx ,
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%B0%B2%D0%C4%C0%F6%C0%A520120613%B4%BF%CD%E6xl=12623
,
http://www.ctszj.com.cn/route_group_1_147707.html,http://jx.bacts.com/group/group_33_118381.aspx,
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=GHX%C9%F1%C5%A9%CE%D420120723xl=7096
, http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625Axl=7957 ,
http://www.ctszj.com.cn/route_group_1_134782.html ,
http://fj.bacts.com/group/group_33_113288.asp ,
http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=TUCCZ120625Axl=7957,
http://www.ctszj.com.cn/route_group_1_134782.html,http://fj.bacts.com/group/group_33_113288.aspx
,http://www.sztravel.com.cn/travel/detail.aspx?xlid=19883ctype=1 ,
http://www.ctszj.com.cn/route_group_1_134695.html ')
sleep 1
$PSQL -U $USR -d $DB -c $SQL
done
2. I create 15 clients and execute this shell script in each client
$ /tmp/insert_spgist.sh /dev/null
...
...
...
]$ /tmp/insert_spgist.sh /dev/null
3. only 1 connection to update the table
$ psql -U postgres
psql (9.2.2)
Type help for help.
postgres=# \c test_spgist
You are now connected to database test_spgist as user postgres.
test_spgist=# update route_raw set sourceurl =
'http://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LAxl=13935'wherehttp://www.gzl.com.cn/Travel/TeamDetail.aspx?tuanhao=CGZL120701MDG05LAxl=13935%27where
sourceurl like '%=CGZL120701MDG0%';
4. I execute the statement select * from pg_stat_activity; and select count(1)
from route_raw; to monitor the connections
test_spgist=# select count(1) from route_raw ;
count
997799
(1 row)
..
..
test_spgist=# select count(1) from route_raw;
count
-
1004066
(1 row)
..
..
test_spgist=# select count(1) from route_raw;
count
-
1004066
(1 row)
test_spgist=# select * from pg_stat_activity ;
datid | datname | pid | usesysid | usename | application_name |
client_addr | client_hostname | client_port | backend_start |
xact_start |
query_start | state_change | waiting | state |
nb sp;
query
---+-+---+--+--+--+-+-+-+---+---+--
Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?
=?gb2312?B?wO66o8H6?= hailong...@qunar.com writes: I am very excited to say that I may have created a test case! I've been running variants of this example for most of the afternoon, and have not seen a failure :-(. So I'm afraid there is some aspect of your situation that you've not provided enough information to reproduce. Most likely, that's the initial contents of your table, which you didn't provide. I tried seeding the table with the five values you did show and then running the insertion loops, but no luck, even after many millions of insertions with various timing changes. Please see if you can put together a self-contained test case including necessary test data. (Note there's no reason to think that any of the columns other than the spgist-indexed one are interesting, if that helps you sanitize the data to the point you can let it out.) The control flow in spgdoinsert.c is flat enough that the stack trace alone isn't much help in understanding the bug, I'm afraid. We can guess that two insertions are trying to lock the same two index pages in opposite orders, but without looking at the data that doesn't put us much closer to a fix. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?
I wrote: The control flow in spgdoinsert.c is flat enough that the stack trace alone isn't much help in understanding the bug, I'm afraid. BTW, something that possibly *would* help, since you seem to be able to reproduce the bug easily, is to do that and then capture the values of the local variables in spgdoinsert() -- especially the contents of the current and parent structs --- from each of the processes that are stuck. Also interesting would be to print the SpGistCache structs. It'd go something like frame 4 info locals p *(SpGistCache *) index-rd_amcache regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?
=?utf-8?B?5p2O5rW36b6Z?= hailong...@qunar.com writes: We have lots data to insert in that table which have the spgist index, may be the spgist index have a bug on a heavy write condition? Perhaps, but you certainly haven't provided any information that would help anyone to fix the bug. Can you create a self-contained test case? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] I s this a bug of spgist index in a heavy write condition?
hi, The problem is not always appear on our system, we can't find a way to reproduce it. After rebuild the index with btree, the problem is disappear at 2012-12-14 00:16, Tom Lane wrote: =?utf-8?B?5p2O5rW36b6Z?= hailong...@qunar.com writes: We have lots data to insert in that table which have the spgist index, may be the spgist index have a bug on a heavy write condition? Perhaps, but you certainly haven't provided any information that would help anyone to fix the bug. Can you create a self-contained test case? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
