Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.

Bug 316338

Summary:	Query that is run when client is starting causes postgres.exe to run at circa 100% for circa 20 seconds
Product:	[Technology] OSEE	Reporter:	Mark D-B <mark.db>
Component:	OSEE Application Framework	Assignee:	Ryan Brooks <ryan.d.brooks>
Status:	CLOSED FIXED	QA Contact:
Severity:	major
Priority:	P3	CC:	ryan.d.brooks, ryan.schmitt
Version:	0.9.1
Target Milestone:	0.9.4
Hardware:	PC
OS:	Windows Server 2008
Whiteboard:

Description Mark D-B

2010-06-09 13:11:56 EDT

As the database has grown, the pause between the "Thread added" note in the OSGI window and the point at which OSEE is actually running has got longer and longer.

Having looked into this, this pause coincides with a query being issued from the client to the database server (bypassing the application server, so it appears). This query causes a postgres.exe to take all available bandwidth on the server's CPU for circa 20 seconds.

Is there any way the query that is issued at this point can be changed to be less intensive please - it seems it is looking at a vast number of records for it to take so long on a 2.66 GHz machine with 2G of RAM?

Comment 1 Ryan Schmitt

2010-06-10 18:18:42 EDT

Does this happen every time a client is launched? Do you know what the query is?

Also, let me know what the following query returns:

SELECT COUNT(*) FROM OSEE_TXS

Comment 2 Mark D-B

2010-06-11 05:34:53 EDT

The query is (trying not to get any typos here, as I'm reading it from the PgAdmin Statistics window which isn't copyable as text by Windows!):

SELECT al1.art_id, txs1.gamma_id, mod_type, txd1.*, art_type_id, guid, human_readable_id FROM osee_join_artifact al1, osee_artifact art1, osee_artifact_version arv1, osee_txs txs1, osee_tx_details txd1 WHERE al1.query_id = $1 AND al1 ...

Sadly, that's as much as I can get it to display

It does it every time a client is started.

SELECT COUNT(*) FROM osee.osee_txs yields 1628616

Comment 3 Mark D-B

2010-06-14 06:29:33 EDT

The query "SELECT datname,procpid,current_query FROM pg_stat_activity" (to get the postgresql query list) yields, for each client that is starting up:

SELECT al1.art_id, txs1.gamma_id, mod_type, txd1.*, art_type_id, guid, human_readable_id FROM osee_join_artifact al1, osee_artifact art1, osee_artifact_version arv1, osee_txs txs1, osee_tx_details txd1 WHERE al1.query_id = $1 AND al1.art_id = art1.art_id AND art1.art_id = arv1.art_id AND arv1.gamma_id = txs1.gamma_id AND txd1.branch_id = al1.branch_id AND txd1.transaction_id = txs1.transaction_id AND txs1.tx_current = 1

I don't know what the parameter value is though.

Comment 4 Ryan Schmitt

2010-06-14 15:39:44 EDT

That query looks like artifact loading. When was the last time you vacuumed your PostgreSQL database?

Also, 2G of RAM is not a lot for a database server, because having to page out parts of the database to a swap file has catastrophic performance implications. However, some of the performance improvements made since 0.9.1 might help.

Comment 5 Mark D-B

2010-06-15 06:57:41 EDT

PostgreSQL has auto-vacuum turned on. It is vacuuming several times a day (judging by how often I see the vacuum process when I am looking to see which machines are running which queries).

The server is only running the OS, virus protection/firewall, OSEE application server, PostgreSQL and a mail forwarder for OSEE. According to the task manager, it never gets anywhere near using all the RAM. I'll keep on eye on this to see if there is any evidence of it using swap.

Comment 6 Mark D-B

2010-06-17 09:00:14 EDT

I've realised this is actually not just one query - it is many in sequence, presumably with a different value for the parameter each time. I say this because looking at the PostgreSQL query table shows that the query start time is changing although the same query is reported.

Comment 7 Mark D-B

2010-10-01 15:16:57 EDT

This no longer appears to be the case on OSEE 0.9.4, so marked as fixed.

Comment 8 Ryan Brooks

2010-12-14 14:08:18 EST

OSEE 0.9.4 has been released.