473,320 Members | 1,856 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,320 software developers and data experts.

page locking? too many btree indexes...

Can too many btree indexes cause page level locking?



I read this...



http://www.postgresql.org/docs/7.4/s...g-indexes.html



The concern is the exclusive page-level locking that occurs on inserts
to the index.



I am experiencing locking related on two tables. Each has several
indexes on it (4 or more). One table is frequently updated (20%),
occasional inserts(10%), and many reads (70%) and the other has many
inserts and reads every 20 secs.



The largest table is with many inserts currently has 2.5 million records
and has inserts of about 200 per second. The read is every 20 seconds.





Thanks,

Brian


Nov 23 '05 #1
1 3116
On Tue, Oct 26, 2004 at 11:44:42AM -0400, Brian Maguire wrote:
Can too many btree indexes cause page level locking?
Yes, too many btree indexes can, as can a single btree index.
I am experiencing locking related on two tables. Each has several
indexes on it (4 or more). One table is frequently updated (20%),
occasional inserts(10%), and many reads (70%) and the other has many
inserts and reads every 20 secs.


Most likely, your problem is not related to the indexes. Yes, there is
page-level exclusive locking on the indexes when there's insert or
delete operations going on, but they don't cause deadlocks. The likely
cause of your problem is foreign key relationships. Those are
implemented using row-level exclusive locking, and they can (and often
do) cause deadlocks.

Do you have any foreign keys defined?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Porque Kim no hacia nada, pero, eso sí,
con extraordinario éxito" ("Kim", Kipling)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2

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

Similar topics

16
by: Nid | last post by:
How do I do row-level locking on SQL Server? Thanks, Nid
5
by: Nobody | last post by:
I am trying to write a BTree class, just wondering if I missed any useful methods. This is my class definition so far (excuse the MFC portions, its a project requirement): template <class TYPE,...
1
by: David McGeorge | last post by:
Dear Gurus, A Client has the following problems/requests for their Production databases, what is your professional/practical advises to tackle and resolve these issues: 1)Number of...
3
by: Paul Janssen | last post by:
Hello! Can anyone help me out with the following situation: (a) a single query with 550 id's in the IN-clause resulting into 800+ seconds; (b) 550 queries with a single id in the IN-clause...
6
by: Sean C. | last post by:
Helpful folks, I am having a hard time figuring out how to reduce my percentage of dirty page steal activity. Below are statistics for three fairly normal days, with the bufferpool hit ratios...
6
by: Jack Orenstein | last post by:
Suppose I have a table as follows: testdb=> \d person Table "public.person" Column | Type | Modifiers ------------+-------------------------+----------- id |...
12
by: hallpa1 | last post by:
Hi All, I am designing a purge process for a db that has grown to almost 200GB. My purge process will remove about 1/3 of the 500 million rows spread over seven tables. Currently there are about...
7
by: Shak | last post by:
Hi all, I'm trying to write a thread-safe async method to send a message of the form (type)(contents). My model is as follows: private void SendMessage(int type, string message) { //lets...
14
by: Jeff | last post by:
This is the first time that I remember ever having too many indexes on a table, but it has happened. I have en employees table and store in many places, on tables, the id of the employee that...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: 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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.