I have a table that is usually really small (currently 316 rows) but
goes through spasams of updates in a small time window. Therefore I
have a vacuum full run every hour on this table.
Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.
So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?
My postgres version:
PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2
20030222 (Red Hat Linux 3.2.2-5)
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html 9  2156 
Joseph Shraibman <jk*@selectacas t.net> writes: Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.
So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?
Look in pg_locks and pg_stat_activit y.
I think it is highly unlikely that there was a deadlock inside the
database. Far more likely that both jobs were waiting on some
idle-in-transaction client whose transaction was holding a lock
on the table.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Why then when I did a kill -INT on the vacuuming backends did everything
unfreeze?
Tom Lane wrote: Joseph Shraibman <jk*@selectacas t.net> writes:
Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.
So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?
Look in pg_locks and pg_stat_activit y.
I think it is highly unlikely that there was a deadlock inside the
database. Far more likely that both jobs were waiting on some
idle-in-transaction client whose transaction was holding a lock
on the table.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Joseph Shraibman <jk*@selectacas t.net> writes: Why then when I did a kill -INT on the vacuuming backends did everything
unfreeze?
You could have had other stuff backed up behind the VACUUM FULL lock
requests.
It's not impossible that you had a deadlock *outside* the database,
that is some wait loop that is partially within and partially outside
the DB. But if you want me to believe there's a bug in our deadlock
detector, you're going to have to offer some actual evidence...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
That is what I wanted to know, how to get the evidence for next time.
Tom Lane wrote: Joseph Shraibman <jk*@selectacas t.net> writes:
Why then when I did a kill -INT on the vacuuming backends did everything
unfreeze?
You could have had other stuff backed up behind the VACUUM FULL lock
requests.
It's not impossible that you had a deadlock *outside* the database,
that is some wait loop that is partially within and partially outside
the DB. But if you want me to believe there's a bug in our deadlock
detector, you're going to have to offer some actual evidence...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Joseph Shraibman wrote: That is what I wanted to know, how to get the evidence for next time.
select * from pg_locks
Regards
Gaetano Mendola
I have figured out the problem. When I do a BEGIN; and then a SELECT an
AccessShareLock is obtained on the table, and then not released until the
transaction is over. Then the vacuum comes in and tries to acquire an
exclusive lock, and in the process blocks any readers who are trying to
SELECT on the same table. My app reads from both db connections in the
same thread, thus the deadlock occurs.
So why isn't the AccessShareLock dropped as soon as the SELECT is over?
On Tue, 12 Oct 2004, Tom Lane wrote: Joseph Shraibman <jk*@selectacas t.net> writes: Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.
So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?
Look in pg_locks and pg_stat_activit y.
I think it is highly unlikely that there was a deadlock inside the
database. Far more likely that both jobs were waiting on some
idle-in-transaction client whose transaction was holding a lock
on the table.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
I have figured out the problem. When I do a BEGIN; and then a SELECT an
AccessShareLock is obtained on the table, and then not released until the
transaction is over. Then the vacuum comes in and tries to acquire an
exclusive lock, and in the process blocks any readers who are trying to
SELECT on the same table. My app reads from both db connections in the
same thread, thus the deadlock occurs.
So why isn't the AccessShareLock dropped as soon as the SELECT is over?
On Tue, 12 Oct 2004, Tom Lane wrote: Joseph Shraibman <jk*@selectacas t.net> writes: Last night one of these vacuum fulls deadlocked with a query on this
table. Both were stuck doing nothing until I did a kill -INT on the
backends doing the vacuum.
So my questions:
1) What can I do to avoid this?
2) What do I do next time this happens to get more debugging info out of
the situation?
Look in pg_locks and pg_stat_activit y.
I think it is highly unlikely that there was a deadlock inside the
database. Far more likely that both jobs were waiting on some
idle-in-transaction client whose transaction was holding a lock
on the table.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org) jk*@selectacast .net writes: So why isn't the AccessShareLock dropped as soon as the SELECT is over?
In general, locks are held till transaction commit. See any basic
database text for the reasons why this is a good idea.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html jk*@selectacast .net writes: So why isn't the AccessShareLock dropped as soon as the SELECT is over?
In general, locks are held till transaction commit. See any basic
database text for the reasons why this is a good idea.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Sean Shanny |
last post by:
To all,
The facts:
PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI
drives in hardware RAID 0 configuration. Database size with indexes is
currently 122GB. DB size before we completed the vacuum full was 150GB.
We have recently done a major update to a table, f_pageviews, in our
data warehouse. The f_pageviews table contains about 118 million rows.
|
by: Alex |
last post by:
Hi,
just a few questions on the Vaccum
I run a vacuum analyze on the database every night as part of a
maintenance job.
During the day I have a job that loads 30-70,000 records into two tables
(each 30-70k).
This job runs 2-3 times a day; the first time mainly inserts, the 2nd,
3rd time mostly updates.
|
by: Jim Seymour |
last post by:
Hi,
Environment:
PostgreSQL 7.4.2
Locally built with GCC 3.3.1
Solaris 8 (Sparc)
I have a relatively simple database created with...
|
by: Rajesh Kumar Mallah |
last post by:
Greeting,
Will it be an useful feature to be able to
vacumm / analyze all tables in a given schema.
eg
VACUUM schema.* ;
at least for me it will be a good feature.
|
by: Ed L. |
last post by:
If I see VACUUM ANALYZE VERBOSE output like this...
INFO: --Relation public.foo--
INFO: Index idx_foo_bar: Pages 219213; Tuples 28007: Deleted 9434.
CPU 17.05s/4.58u sec elapsed 3227.62 sec.
....am I correct in reading this to say that it took more than 53 minutes
(3227 secs) to get 17 seconds of CPU time? Is this an indicator of
possible I/O contention? What else would account for this if my CPUs are
clearly not very busy?
| | |
by: spied |
last post by:
look please http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=253295
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
|
by: Ilia Chipitsine |
last post by:
Dear Sirs
I'm about to write plpgsql function which will "vacuum full" all existing
databases. Below is an example how to get list of databases.
What should I write instead of "raise notice" ?
CREATE OR REPLACE FUNCTION vacuum_all() RETURNS integer
AS '
|
by: Aleksey Serba |
last post by:
Hello!
I have 24/7 production server under high load.
I need to perform vacuum full on several tables to recover disk
space / memory usage frequently ( the server must be online during
vacuum time )
The one trick that i see is to try to vacuum duplicate of
production database ( or just some tables ).
But there are some pitfalls:
http://www.postgresql.org/docs/7.4/interactive/backup-file.html
|
by: cwho.work |
last post by:
Hi!
We are using apache ibatis with our MySQL 5.0 database (using innodb
tables), in our web application running on Tomcat 5. Recently we
started getting a number of errors relating to
java.sql.SQLException: Deadlock found when trying to get lock; Try
restarting transaction message from server: "Lock wait timeout
exceeded; try restarting transaction";
We get such errors generally on inserts or updates while applying a
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
