performance "tests" - Mailing list pgsql-admin

Hi,

having read on this list (some time ago) that inserts could become slow
when there are foreign keys constraints, I wanted to test it. So I
created a DB with 5 tables (T1 -> T5) with, for 0<i,j<6, Tj has a
foreign key from Ti. More clearly:

create table T1(k1 integer NOT NULL PRIMARY KEY, k2 integer,k3 integer,k4 integer,k5 integer);
create table T2(k2 integer NOT NULL PRIMARY KEY, k1 integer,k3 integer,k4 integer,k5 integer, FOREIGN KEY(k1)
REFERENCEST1 ON DELETE CASCADE); 
create table T3(k3 integer NOT NULL PRIMARY KEY, k1 integer,k2 integer,k4 integer,k5 integer, FOREIGN KEY(k1)
REFERENCEST1 ON DELETE CASCADE,FOREIGN KEY(k2) REFERENCES T2 ON DELETE CASCADE); 
create table T4(k4 integer NOT NULL PRIMARY KEY, k1 integer,k2 integer,k3 integer,k5 integer, FOREIGN KEY(k1)
REFERENCEST1 ON DELETE CASCADE,FOREIGN KEY(k2) REFERENCES T2 ON DELETE CASCADE,FOREIGN KEY(k3) REFERENCES T3 ON DELETE
CASCADE);
create table T5(k5 integer NOT NULL PRIMARY KEY, k1 integer,k2 integer,k3 integer,k4 integer, FOREIGN KEY(k1)
REFERENCEST1 ON DELETE CASCADE,FOREIGN KEY(k2) REFERENCES T2 ON DELETE CASCADE,FOREIGN KEY(k3) REFERENCES T3 ON DELETE
CASCADE,FOREIGNKEY(k4) REFERENCES T4 ON DELETE CASCADE); 

I also wrote a python script to populate this DB (see below). I certainly don't
pretend this test is reflecting reality, but I wanted to ask explanations about
one of the things that happen.  I now have 300000 rows in each table, and it
fills the database quite fast, and the postmaster takes something like 25% of
the CPU. At some times, it seems to hang: it doesn't insert any rows for more
than 10 seconds. At that time, the postmaster process takes 0%. Why is that? I
would have thought that the postmaster  would use much power to insert few rows
when the DB gets filled, but it's not happening that way. When rows are
inserted, it happens more or less at the same speed as initially (when DB is
empty). When rows are inserted more slowly, it's because the postmaster
process uses less CPU.

What's strange is that everything else hangs also! Would that be due to the CPU??

Thanks for your help.

Raph.




Some info:

Debian GNU/Linux

cat /proc/cpuinfo
processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 6
model           : 6
model name      : AMD Athlon(tm) XP
stepping        : 2
cpu MHz         : 1050.052
cache size      : 256 KB



dpkg -l postgresql
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Installed/Config-files/Unpacked/Failed-config/Half-installed
|/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: uppercase=bad)
||/ Name           Version        Description
+++-==============-==============-============================================
ii  postgresql     7.1.3-7        Object-relational SQL database, descended fr


uname -r
2.4.17

cat /etc/debian_version
3.0




HEre's the script:

import random
import sys
from pyPgSQL import libpq

dbname = 'test'


random.seed()


cnx = libpq.PQconnectdb('host=localhost user=rb password=linuxxxx dbname=%s' % dbname)

INSERT FIRST 5 ENTRIES IN EACH TABLE
for i in range (1,6):    #INSERER 5 RANGEES
   for j in range(1,6):           #DANS LES 5 TABLES
       res = cnx.query('INSERT INTO T'+str(j)+' VALUES('+str(i)+','+str(i)+','+str(i)+','+str(i)+','+str(i)+')')



N=[5,5,5,5,5]    #number of rows in each table


while N[0]<1000000:
    t=random.randrange(1,6,1)    #table in which to insert the next row
    k=random.randrange(1,6,1)    #table to which we link the inserted row
    r=[random.randrange(1,N[0],1),random.randrange(1,N[1],1) ,random.randrange(1,N[2],1) ,random.randrange(1,N[3],1)
,random.randrange(1,N[4],1)] 
    r[t-1]=N[t-1]+1    #In table Tt ,the field kt is the primary key and has the value N[t]+1
    sqlquery="insert into T"+ str(t)+ " (k1,k2,k3,k4,k5) values
('"+str(r[0])+"','"+str(r[1])+"','"+str(r[2])+"','"+str(r[3])+"','"+str(r[4])+"')"
    print sqlquery
    res = cnx.query(sqlquery)
    N[t-1]=N[t-1]+1

del cnx, res