473,326 Members | 2,438 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

what could cause inserts getting queued up and db locking??

Background Info:

I have a table with a approx 2.5 million rows. The table often gets 200-300 inserts per second. We are see that the database (7.4.1 Red Hat Enterprise ED 4 way Xeon) will periodically lock up all of a sudden and force the database to queue up hundreds of queries. The database comes to complete halt until eventually it clears itself out. There is other database activitygoing on but the above activity represents 75% of the frequency of the queries. The table described above has few reads as we try to restrict reads.We have it set so it does one read every 20 seconds collecting all of thenewest data in the last 20 seconds and moves it to a reporting table.

What could cause the database to lock up and queue up all the queries?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
1 2092
Brian Maguire wrote:

What could cause the database to lock up and queue up all the
queries?


You'll want to check the lock details (pg_locks: see "Monitoring
Database Activity" in the reference manuals) and also what the system as
a whole is doing (vmstat/iostat).

I seem to recall some configuration of ext3 could cause bursts of
activity with certain write patterns, but don't recall PG being affected
by this.

There have been discussions of context-switching issues with Xeons IIRC
- don't know the details, so check the -performance/hackers archives for
details.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

125
by: Sarah Tanembaum | last post by:
Beside its an opensource and supported by community, what's the fundamental differences between PostgreSQL and those high-price commercial database (and some are bloated such as Oracle) from...
7
by: Steven D.Arnold | last post by:
How good is Postgres' performance for massive simultaneous insertions into the same heavily-indexed table? Are there any studies or benchmarks I can look at for that? I understand Postgres uses...
375
by: rkusenet | last post by:
This article is very bleak about future of DB2. How credible is the author. http://www.eweek.com/article2/0,1895,1839681,00.asp
4
by: Carlos Moreno | last post by:
Hello, I'm using PostgreSQL 7.4.3 on a RedHat 9 Linux server (a P4 HyperThreaded, using the SMP kernel, in case this makes a difference). I'm not 100% sure I understand exactly why I am...
3
by: zelnaga | last post by:
according to the mysql manual, multiple inserts can be sped up by locking the table before doing them and unlocking the table afterwards. is the same true of multiple inserts in mysql? if so,...
669
by: Xah Lee | last post by:
in March, i posted a essay “What is Expressiveness in a Computer Language”, archived at: http://xahlee.org/perl-python/what_is_expresiveness.html I was informed then that there is a academic...
3
by: mike | last post by:
Hello, I am new to XPATH and am having a hard time selecting some nodes. I want to select all the record nodes where Status = 'Unprocessed' or 'Queued' (that part I can get) but additionally I...
6
by: lawpoop | last post by:
Hello all - I have a problem with a php page. I have a setup with Apache 2.0, PHP 5 and Postgres 8.1 on Debian 4.0. My script uses simplexml to parse large xml files ( 8 files at 2-15 MB ea. )...
18
by: Charles Law | last post by:
I have a sproc that returns data from a table using a simple SELECT. There are quite a few rows returned, e.g. ~150,000. In my first application, I use a reader on the sproc and iterate through...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, youll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shllpp 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.