Robert,
the db access will be handled as any access to a database: threads will
access the tables they require, setting locks on the ones they are using
(there are several level of locks, depending on the type of access
required, which can reserve a table exclusively or not). So, the response
is yes, several processes and sessions can use the db at one time, but will
need to wait for access to tables if another thread has set a lock.
The performance hit you're seeing when running multiple sessions can have
two reasons:
1. your server is using much more resources when serving multiple sessions,
and hence becomes slower.
2. Database access uses a chaching mechanism. If you use multiple sessions,
the chance that a db page remains in cache is less than when you are using
only one sessions. So, the result is more I/O to your database, and slower
operation. You might solve this by increasing the bufpool size, or using
the self tuning mechanims for caches available from 3.7 on.
Finally, if you are going to delete a lot of big filespaces, you can
run multiple processes at once, but, keep in mind that due to the cache and
increased use of server resources, this might not be the best solution.
I've seen situations where the time required to complete multiple processes
was larger that the sum of the time required to run the processes
seperately. Unfortunately, this is no general rule.
Best regards, Wim.
Wim Feyants
