[GENERAL] weird behaviour
hi, i'm notice a weird behaviour while running a query on the same table, the query take a long time to be executed when this table is empty , and when this table isn't empty, it take about 1/2 second the two tables are freshly vacuumed ... here the same tables, one is empty, not the other : Table classement_jeu Attribute | Type | Modifier ---+--+-- semaine | smallint | annee | integer | id_turf | integer | points| integer | tag | smallint | Table classement_jeumat Attribute | Type | Modifier ---+--+-- semaine | smallint | annee | integer | id_turf | integer | points| integer | tag | smallint | psql -c select count(*) from classement_jeu count --- 21318 (1 row) psql select count(*) from classement_jeumat count --- 0 (1 row) now the query i want to run : SELECT id_turf FROM classement_jeumat WHERE semaine = 35 AND annee = 2001 AND id_turf IN (5247,5425, ) the IN clause contain about 600 entries run the query on table classement_jeumat (empty table) time psql laquery id_turf - (0 rows) real1m18.308s user0m0.000s sys 0m0.015s run the query on table classement_jeu (not empty table) time psql laquery id_turf - [... snip results ... ] (592 rows) real0m0.472s user0m0.009s sys 0m0.014s somebody understand what happened ? it's not really important because i will not work on an empty table ... but i'm surprised ... thanxs mat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] weird behaviour
sorry, i forgot some version number :) [mat@biniac ~]$ psql --version psql (PostgreSQL) 7.1.2 contains readline, history support Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996 Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. [mat@biniac ~]$uname -rs FreeBSD 4.3-RELEASE[mat@biniac ~]$ mat ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] memory problems with freebsd
Hi, we work with PostgreSQL 7.0.3 and FreeBSD (4.2 / 4.3). the database is in production state, and daily heavy loaded. i think our problems are similar to problems described in : http://fts.postgresql.org/db/mw/msg.html?mid=28871 recently, we have updated our kernel, according to this doc : http://www.postgresql.org/idocs/index.php?kernel-resources.html here the options we have tuned: options SHMALL=32768 options SHMMAX=(SHMALL*PAGE_SIZE) options SEMUME=40 options SHMMAXPGS=32768 options SHMSEG=256 options SEMMNI=256 options SEMMNS=512 options SEMMNU=256 options SEMMAP=256 everything looks working fine, but after 9 or 10 days in production, some problems appeared : sometimes each postmaster child stay in a strange state, according to top, 'semwait' state. running ipcclean clean all those children ! sometimes, when the number of postmaster raise up to the limit, we can't connect to the backend, we've got the message : psql: The Data Base System is in recovery mode after a pg_ctl stop, the message changes into : psql: The Data Base System is shutting down but the database never shutdown ( or are we too impatient ?). it appeared that the inactive memory raise, until we'v got 10 megs of free memory, as described by Alfred Perlstein. after a reboot, everything looks fine but after 10 days of uptime, the problem is the same. we have just updated our FreeBSD kernel to 4.3. is this update pertinent ? are the selected values for the kernel options speed up a memory leak problem ? (the problems appeared faster since our kernel optimisation) can an update of postgresql from 7.0.3 to 7.1.2 solve this problem ? the REAME.v7.1 talk about bug fixes on memory leak problem. any experience, and/or advisory will be appreciated. -- Matthieu Clavier [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
