Forum OpenACS Q&A: Beginners Question on Processes/ Database Pools / Threads

1: Beginners Question on Processes/ Database Pools / Threads

Posted by Hanjo Pepoulis on 06/06/02 10:57 AM

Could you please help me understand this:
(I'm just starting with the toolkit and AOLserver)

When I start AOLserver, it starts several processes. Are these the "Threads" I can define in the config file? (maxthreads/minthreads)
How can I somehow compute the number of threads I have to define for a given number of concurrent requests?

What means "connsperthread"? For what use would I have to update this to 1, 2, 3 ...?

Should the number of connections for the Database Pools be the same as the "maxthreads" parameter?

Does the "keep-alive" thread mean that some threads are waiting for requests from a certain client and do not handle other requests for that time?

Can I (in an adp file) grab just one handle per pool? Or am I able to take as many handles from a Pool until the maximum defined?

I read the database api source code, but I do not really understand the way transactions are done: The release unused handle statements are _after_ the "abort transaction" statements. I can't explain to me why the database is rolling smth. back when statements are done with different handles from different pools. Or does this not matter because the statements are issued from the same database user? Sorry for that questions, I don't know database architecture very well.

Thank you very much for any help!

Hanjo.

2: Response to Beginners Question on Processes/ Database Pools / Threads (response to 1)

Posted by Todd Gillespie on 06/06/02 04:33 PM

When I start AOLserver, it starts several processes. Are these the "Threads" I can define in the config file? (maxthreads/minthreads) How can I somehow compute the number of threads I have to define for a given number of concurrent requests?

Are you running Linux? Linux muddies the process/thread distinction; in this case each thread has its own pid (process id), so most tools (ps, for instance) get confused. As for thread computation, I really don't know. Lots of people have posted lots of tuning recommendations, and they're all different. I'll just politically plead ignorance.

What means "connsperthread"? For what use would I have to update this to 1, 2, 3 ...?

I was unaware that was still supported. connsperthread is the number of connections served by a thread before the thread is recycled. IIRC, a setting connsperthread to zero means there is no limit. Hopefully someone can back me up on this point.

Should the number of connections for the Database Pools be the same as the "maxthreads" parameter?

No, it normally should be much lower. All the threads share those pools, you see, and a thread doesn't always need a DB handle. So the db pool connections value should be some fraction of the maxthreads parameter, the exact value of which depends on your ratio of static file to DB-backed file requests.

Does the "keep-alive" thread mean that some threads are waiting for requests from a certain client and do not handle other requests for that time?

I think so. The HTTP/1.1 meaning is clear, but I'm not 100% certain about implementation. It may well be, OTOH, that threads can detach from connections and come back to a keep-alive request when appropriate. markd?

Can I (in an adp file) grab just one handle per pool? Or am I able to take as many handles from a Pool until the maximum defined?

One handle per pool per thread. That will look like a design error at the beginning, then it will look like a win when a thread tries to hog the pools, then later it will look like an error again when your scripts become more complicated and you can't tell where a pool is taken. Eventually you'll give up and start using the OACS4 db_select, db_dml, db_et-cetera functions.

I read the database api source code, but I do not really understand the way transactions are done: The release unused handle statements are _after_ the "abort transaction" statements. I can't explain to me why the database is rolling smth. back when statements are done with different handles from different pools.

Could you rephrase this? I don't understand what the problem is. You send 'abort transaction' to a handle, then release it. Seems proper to me.

3: Response to Beginners Question on Processes/ Database Pools / Threads (response to 1)

Posted by Hanjo Pepoulis on 06/06/02 05:50 PM

I read the database api source code, but I do not really understand the way transactions are done: The release unused handle statements are _after_ the "abort transaction" statements. I can't explain to me why the database is rolling smth. back when statements are done with different handles from different pools.

Could you rephrase this? I don't understand what the problem is. You send 'abort transaction' to a handle, then release it. Seems proper to me.

Why do I need a new handle for every statement?
The code looks like that. Couldn't I simply use one handle for alle statements including "begin transaction" ... (statements) ... "end transaction" ? This would make sense to me: If I release the handle or say "end transaction" the db knows what to do: rolling back.

But it seems like this to me (or am I wrong?):

1. Take a handle and use it to say "begin transaction" (from pool1)
2. Take a new handle and use it to do updates etc. (from pool2)
3. Take again a new handle and use it to delete etc. (from pool3)
4. Use handle from (1) and say "abort transaction"
5. Release unused handles...

Why can't I release the handles from (2) and (3)?

There are only three pools defined. What if I need another dml statement in the transaction?

Sorry that I'm so confused...

Maybe it does not matter what handle from what pool one uses because the database driver knows that it comes from the same AOLserver process (or thread)???

4: Response to Beginners Question on Processes/ Database Pools / Threads (response to 1)

Posted by Todd Gillespie on 06/06/02 07:09 PM

You're so confused, you're confusing me. Let's start from the top.

A handle is a database session. One session, one handle. All transaction modifiers affect only their controlling session. So when you say 'begin transaction' in one handle, it doesn't put any other handles into transaction mode. Go ahead, open up several psql or sql*plus terminals and try it out.

1. Take a handle and use it to say "begin transaction" (from pool1)
2. Take a new handle and use it to do updates etc. (from pool2)
3. Take again a new handle and use it to delete etc. (from pool3)
4. Use handle from (1) and say "abort transaction"
5. Release unused handles...

In this case, handle #2 isn't running those updates within a transaction, nor is handle #3. And action #4 only aborts what you've done in the transaction on handle #1, ie, nothing. #5 doesn't even make sense, because if the handle is unused then why is it allocated to the thread?

The correct way to do this is:

take a handle: set handle1 [ns_db gethandle main]
open a transaction: ns_db dml $handle1 "begin transaction"
use the handle to do some updates: ns_db dml $handle1 "update insults set mother = ' was a hamster' where user_region = 'French'"
do some deletes: ns_db dml $handle1 "delete from knights where saying = 'Nee!'"
abort transaction: ns_db dml $handle1 "abort transaction"
have a cookie

Why can't I release the handles from (2) and (3)?

ns_db releasehandle $handle_2
ns_db releasehandle $handle_3

Problem?

There are only three pools defined. What if I need another dml statement in the transaction?

Same as above: by spreading your statements out over time with a single handle, rather than over multiple handles all at once. There are actually very few times when you need multiple statements active at once; such as when you have to create new statements on the fly and drive them with data taken from another statement. But that's relatively uncommon.

5: Response to Beginners Question on Processes/ Database Pools / Threads (response to 1)

Posted by Hanjo Pepoulis on 06/10/02 09:14 AM

Thanks Todd,

your answers helped a lot!

The last question I have is: When I take handles and do NOT
release them, e.g. because of wrong program code, what happens
to them? Do they get released automatically from the server? the driver?

Regards,
Hanjo.

6: Response to Beginners Question on Processes/ Database Pools / Threads (response to 1)

Posted by Todd Gillespie on 06/10/02 04:49 PM

When I take handles and do NOT release them, e.g. because of wrong program code, what happens to them?

They get released (placed back in the pool) when the page where you allocated them goes out of scope (stops executing). So you won't run out of handles and then crash or something; the server won't lose handles that easily. But if you have a lot of such pages running, then they'll be competing for handles and you'll get a lot of stalling on your server. Better to use the OACS4 db_* functions, which grab a handle internally and release it as soon as they finish processing a query.

7: Response to Beginners Question on Processes/ Database Pools / Threads (response to 1)

Posted by Hanjo Pepoulis on 06/10/02 07:03 PM

Better to use the OACS4 db_* functions, which grab a handle internally and release it as soon as they finish processing a query.

No, only in db_transaction db_release_unused_handles seems to be called automatically.

You have to manually call it if you want to release handles earlier. (As the example at the end of the API implies)