[imp] ct_sql vs. ct_shm

[George Sexton]

Did you try doing some performance tuning on Postgres?

The URL below is on tuning PostgreSQL:

http://postgresql.bteg.net/docs/faq-english.html#3.7

You might also try upgrading to 7.1.3 to see if that enhances the
performance. I know there were a large number of performance improvements.
There was also an article in Linux Journal a couple of issues back on tuning
PostgreSQL.

In my system (2.2.6) I see a large number of entries in the active sessions
table (47). I know that I don't have 47 concurrent users. There is some
garbage collection code that appears to run about once every 100 session
starts that purges all sessions older than one day. You might check your
active sessions table to see how many entries there are in it and write a
query to delete old ones if you have them.

One problem I have seen in JDBC with PostgreSQL is that if an update fails,
the record is locked until a rollback command is issued. The effect on the
database is that queries against that table end up being blocked. This makes
it look like the database has hung. I looked at the docs for PHP and was
surprised to see that it doesn't even handle transactions. Still, if it is
randomly failing (rather than after a specific amount of use) this might be
worth investigating.

The rest is for the developers of IMP. This is not a flame, just an
observation from a database type person, but the database design could be
improved for performance.

Here's where I think it could:

>CREATE TABLE "active_sessions" (
>	"sid" character varying(32) NOT NULL,
>	"name" character varying(32) NOT NULL,
>	"val" text,
>	"changed" character varying(14) NOT NULL,
>	PRIMARY KEY ("name", "sid")
>);

The two keys should be integer datatypes. This would dramatically shrink the
size of the indexes and boost performance. The name field in mine is all
"HordeSession" for all 43 entries. The SID field should probably be
something like a 32 bit integer.

A simple fix would be re-ordering the primary key. In my case, there are no
unique values for the "Name" portion of the key. OTOH, the SID is fairly
unique. This means that the "Name" portion would only have to be examined
for one entry in the index, and not many.

A side issue of large character primary keys, is that in databases that have
a transaction log (which includes PGSQL 7.1), the entire primary key is
logged, along with the changed fields. This means that if you update
"changed" 10 times, the transaction log size would be 740 bytes
((32+32+10)*10)versus perhaps 260 bytes.

>CREATE TABLE "imp_pref" (
>	"username" character varying(255) NOT NULL,
>	"sig" text,
>	"fullname" character varying(70),
>	"replyto" character varying(70),
>	"lang" character varying(30),
>	PRIMARY KEY ("username")
>);

There should be an identity or Sequence field as the primary key for this
table. A UNIQUE constraint should then be placed on the UserName field to
ensure uniqueness. This would help the next table. The one requirement,
would be to create a prefs entry for every user who successfully logs in,
regardless of whether they ever set their preferences.



>CREATE TABLE "imp_addr" (
>	"username" character varying(255) NOT NULL,
>	"address" character varying(255) NOT NULL,
>	"nickname" character varying(255),
>	"fullname" character varying(255),
>	PRIMARY KEY ("username", "address")
>);

This table should have the identity field as a reference from the imp_pref
table. The user name field should be dropped. Right now, you have what is
known as an update anomaly. If the user name field changes in imp_pref, it
requires code to synchronize the names or the names in the address book are
lost. In addition to the update anomaly, using an int foreign key would
reduce the index size and speed up operations.

Finally, imp_addr should have foreign key constraint referencing imp_pref
with a cascading delete.

-----Original Message-----
From: moorewr@eckerd.edu [mailto:moorewr@eckerd.edu]
Sent: 21 August, 2001 1:39 PM
To: imp@lists.horde.org
Subject: [imp] ct_sql vs. ct_shm



We are running IMP on a dedicated Dell linux box - a PIII/866
with 512mb. It has Horde/IMP 1.2.4/2,4 (because, as I noted
previously, 2.5 has a large compose/prefs bug under this
config), postgres 7.03, RH 7.1, and php-4.0.4pl1-9. We get
30-50,000 hits on the server each day.

We have found that even with vacuuming, etc, it seems unable to
keep up with requests if we use postgresql for session
managment. Eventually the load on the system will soar up beyond
30 and the system cracks, even though the CPU is idle.

As a workaround (for our angry college president) we switched to
SHM session management. This is lightning fast, HOWEVER, after n
# of hours it eats up all the available shared mem. I've set the
size of the shm segment higher and higher - from 64000 to
99900000. Eventually the server stops returning requests and I
have to restart httpd.

We've tweaked various parameters - php/imp session max time,
KeepAlive, etc, without much variation in these problems.

Anyone have any inspiration for me?

Thank you..

+------------------------------------------------------------
Walter R. Moore --  System Administrator, Eckerd College
moorewr@eckerd.edu --  http://www.eckerd.edu/~moorewr

"Nothing in the UFO phenomena, the fundamentalist complaint
goes, requires belief in the one, true God, while much in it
contradicts th God portrayed in the Bible and Christian
tradition. _The New Age: A Christian Critique_ by Ralph Rath
discusses UFOs -- typically for such literature, with extreme
credulity. It serves their purpose to accept UFOs as real and
revile them as instruments of Satan and the Antichrist, rather
than to use the blade of scientific skepticism. That tool, once
honed, might accomplish more than just a limited heresiotomy."
	- Carl Sagan _The Demon-Haunted World_

-------------------------------------------------
This mail sent through IMP: webmail.eckerd.edu

--
IMP mailing list: http://horde.org/imp/
Archive: http://marc.theaimsgroup.com/?l=imp&r=1&w=2
Frequently Asked Questions: http://horde.org/faq/
To unsubscribe, mail: imp-unsubscribe@lists.horde.org