Hi.
(My pgbouncer is finally working and has results in at least a 3-fold
site speed increase! YAY! Thanks to everyone who helped.)
Now, a new small problem.
In my PHP code I have a condition that checks for the existence of a
record, and if not found, it INSERTs a new one.
Here's the first SQL to check existence:
# SELECT ip FROM links WHERE ip = 1585119341 AND url_md5 =
'cd4866fa5fca31dfdb07c29d8d80731c' LIMIT 1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..26.20 rows=1 width=8) (actual time=0.054..0.054
rows=1 loops=1)
-> Index Scan using idx_links_ip_url on links (cost=0.00..26.20
rows=1 width=8) (actual time=0.053..0.053 rows=1 loops=1)
Index Cond: ((ip = 1585119341) AND (url_md5 =
'cd4866fa5fca31dfdb07c29d8d80731c'::text))
Total runtime: 0.078 ms
(4 rows)
About 5% of the times (in situations of high traffic), this is not
returning a value in my PHP code. Because it's not found, the code
tries to INSERT a new record and there's a duplicate key error, which
is in the logs. The traffic to the site is much higher than the number
of these entries in my log, which means it's only happening
sometimes--my guess is for 5% of all queries, which is still quite
significant (about 60,000 a day).
I began logging these "missed" SELECT queries, and when I manually go
into the postgresql terminal and execute those queries, the record is
indeed found. No problem.
So my question: is this related to some timeout or something with
pgbouncer, where I suppose the connection is held for a split-second
longer than it would, and therefore the query doesn't return anything?
Probably an inane guess. Just wondering aloud.
Welcome any thoughts on how to debug this. Btw, the logging is
happening in the postgresql usual log file, the pgbouncer log just has
hordes of one-liners stating how many requests per minute...
Thanks!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
