oplock performance problem with a small database

[David Collier-Brown]

Johann Zuschlag wrote:
> Well, it's just a database residing on a file server. So it will only use smb.
> It's no intelligent database server like an  SQL-server. But still, searching
> (with and without index) should be faster. With caching it runs really fast, but
> that is not save and would only work with one client.
...
> It doesn't matter whether there are 2 or 6 users, no speed difference. 

	My understanding is that with oplocks & caching, the
	files are being shipped to the client once, read there
	as many times as necessary, and if changed, eventually 
	shipped back to the server. This should be really fast...

	With the second user, it should become substantially slower:
	without caching, an index search means that the file content
	will be shipped each time to the client, the search done, and 
	the data discarded.  From what you say, I think that's what's 
	happening.

	Turning off oplocks should show down even single users to this
	speed.

	That speed should stay the same, up to some large number
	of users, at which point the server or the net gets saturated.
	This wil lhappen when the number of users makes the demand 
	more than 1,113 KB/sec on the samba server (on a 10 
	megabit/sec ethernet). Then it should slow down abruptly.  
	I doubt if you've hit this second slowdown.


	If you can separate update use and search use (for example, in
	a data warehouse), you could use fake oplocks, and have all
	the clients cache locally.  This assumes you update the
	database after everyone goes home, though!

	A properly tuned real database (including MS-SQL server) 
	would start out as fast as yours with caching, run at
	the same speed up to some larger number of users (lots more
	than 6), then start to degrade gracefully with each additional
	user.  You'd need to do some tuning initially, to get it fast,
	then you could ignore it until you had to add a whole bunch more
	users.   I'd recommend you plan for that in the long term.

	In the short term, you might want to see if you could have
	a bunch of users using a read-only copy of the database,
	and another, smaller group with a r/w copy, and update the
	read-only copy nightly.  
	If you were on Solaris, I could help a bit with system 
	tuning for the pure read-write case, but I doubt we could
	get it as fast as you see with caching unless the database
	client was written to be both network- and cache-aware.

--dave
-- 
David Collier-Brown,  | Always do right. This will gratify some people
185 Ellerslie Ave.,   | and astonish the rest.        -- Mark Twain
Willowdale, Ontario   | http://java.science.yorku.ca/~davecb
Work: (905) 477-0437 Home: (416) 223-8968 Email: davecb at canada.sun.com