Multi-user Database Applications and Samba

Samba and Linux is a great combination for providing a reliable fast file server on a network.  Whether you are using Microsoft Access, FoxPro, Quickbooks or CA-Clipper / xHarbour, with any multi-user fileserver database application there are configurations done both on the server and the client systems for this to work reliably.  The main settings to look for when setting up the Samba server to host a database application are: lock spin, and oplocks.

O'Reilly & Associates has a very good section on tuning your Samba server.  For more please have a look at:
http://www.oreilly.com/catalog/samba/chapter/book/appb_02.html

Note there was a locking issue that was not fixed until a Samba version post version 2.2.3a  when the "lock spin" settings where introduced.  For more information on this please visit the samba email archive at Fix for multi-user database corruption problems just checked in.

Samba's default lock spin time/count settings worked fine for 2 workstations.

For 6 workstations change: "lock spin time =15" Anything higher OR lower (by 5's) makes the server load increase and reduces performance on the workstation.  This value is dependant on you server hardware, network and workstation performance.  This option along with the "socket options" can really impact the performance of your file server applications.

lock spin count =100 In slower machines this value does not effect performance, but is required to get the machines to complete the test without GPF'ing. There appears to be no reason not to be generous with this setting.  Recent versions of the kernel and samba combined with fast hard drives show that you could lower this value to tweak a little extra performance.   On a Dell 800 with: 4 SATA drives in software RAID 5, kernel 2.6, and Samba 3.x a value of 30 gave the best performance.

For Linux File Servers: Here is a PDC smb.conf file that we use for samba 3.x:

[global]
	socket options = TCP_NODELAY SO_SNDBUF=65536 SO_RCVBUF=65536 IPTOS_LOWDELAY
	use sendfile = no
	lock spin time = 15
	lock spin count = 200
	strict locking = no
	getwd cache = yes
	map to guest = bad user
	log level = 1
	security = user
	os level = 64
	local master = Yes
 	time server = Yes
	domain master = yes 
	preferred master = yes
	wins support = yes
	domain logons = yes
	dos filetimes = Yes
	workgroup = PutYourMSdomainnamehere
	netbios name = fileserver
	server string = Samba Server %v
	printcap name = cups
	load printers = yes
	printing = cups
	printer admin = root @adm @is
	log file = /var/log/samba/log.%m
	max log size = 50
	;hosts allow = 192.168.1.0/24 127.0.0.1
	interfaces = eth0 lo
	bind interfaces only = yes
	encrypt passwords = yes
	smb passwd file = /etc/samba/smbpasswd
	add user script = /usr/sbin/useradd -d /dev/null -g machines -c 'Machine Account' -s /bin/false -M %u
	wins proxy = yes
	dns proxy = no 
	logon path = 
	logon drive = U:
	logon script = %U.bat
	oplocks = no
	level2 oplocks = no
	change notify timeout = 300
	lpq cache time = 30
	winbind uid = 10000-20000
	winbind gid = 10000-20000
	winbind separator = +
	oplocks = no
	level2 oplocks = no
;	deadtime = 60
	wins proxy = yes
	lpq cache time = 30
	change notify timeout = 300
	getwd cache = yes
	dos filetimes = yes
	domain logons = yes
	obey pam restrictions = yes
	unix password sync = Yes
	pam password change = yes
	passwd program = /usr/bin/passwd %u
	passwd chat = *New*UNIX*password* %n\n *Re*ype*new*UNIX*password* %n\n \
	*passwd:*all*authentication*tokens*updated*successfully*
	add user script = /usr/sbin/useradd -s /bin/false '%u'
	delete user script = /usr/sbin/userdel '%s'
	add user to group script = /usr/bin/gpasswd -a '%u' '%g'
	delete user from group script = /usr/bin/gpasswd -d '%u' '%g'
	set primary group script = /usr/sbin/usermod -g '%g' '%u'
	add group script = /usr/sbin/groupadd %g && getent group '%g'|awk -F: '{print $3}'
	delete group script = /usr/sbin/groupdel '%g'
	add machine script = /usr/sbin/useradd -d /dev/null -g machines -c 'Machine Account' -s /bin/false -M %u
[homes]
	comment = Home Directories
	browseable = no
	writable = yes
	oplocks = yes
	level2 oplocks = yes	
;	use sendfile = yes
;	preexec = echo "%u, %G, %a, %m (%I)\" >>/tmp/.log


[netlogon]
	comment = Network Logon Service
	path = /var/lib/samba/netlogon
	writable = no
	read only = yes
	guest ok = no
	browseable = no
	share modes = no
	root preexec = /usr/bin/ntlogon -u %U -g %G -o %a -d /var/lib/samba/netlogon/
	root postexec = rm -f /var/lib/samba/netlogon/%U.bat
[printers]
	comment = All Printers
	path = /var/spool/samba
	browseable = no
	guest ok = yes
	writable = no
	printable = yes
	create mode = 0700
	print command = lpr-cups -P %p -o raw %s -r   # using client side printer drivers.
[print$]
	path = /var/lib/samba/printers
	browseable = yes
	read only = yes
	write list = @adm root @is @"Domain Admins"
	guest ok = yes
	inherit permissions = yes
	# Settings suitable for Winbind:
	; write list = @"Domain Admins" root
	; force group = +@"Domain Admins"

[pdf-generator]
	path = /var/tmp
	guest ok = No
	printable = Yes
	comment = PDF Generator (only valid users)
	print command = /usr/share/samba/scripts/print-pdf %s ~%u //%L/%u %m %I "%J" &


[tmp]
	comment = Temporary file space
	path = /tmp
	read only = no
	public = yes

[public]
	comment = Public Stuff
	path = /home/srv/public
	public = yes
	writable = yes
	printable = no
	force group = users
	;valid users = @users
	;create mask = 2750
	;directory mask = 2770
	dos filetimes = yes
	inherit permissions = yes
	create mask = 2750
	force create mode = 2750
[rsync]
	comment = Rsync Stuff
	path = /var/rsync
	public = yes
	writable = yes
	printable = no
	force group = apache
	force user = root
	;valid users = @users
	create mask = 3754
	force create mode = 3754
[www]
	use sendfile = yes
	comment = Web Site
	path = /var/www
	writeable = yes
	valid users = @adm
	force group = apache
	force user = root
	create mask = 3754
	directory mask = 3754
	force create mode = 3754
	force directory mode = 3754
	oplocks = Yes
	level2 oplocks = yes

[backup]	
	path = /home/srv/backup
	valid users = @users
	read only = yes
	oplocks = Yes
	level2 oplocks = yes
	public = yes
	printable = no
	use sendfile = yes
[logs]
	comment = Server Log Files
	path = /var/log
	read only = yes
	force group = root
	force user = root
	public = no
	valid users = @adm

[etc]
	comment = Server ETC Files
	path = /etc
	read only = yes
	force group = root
	force user = root
	public = no
	valid users = @adm

Curing the oplocks and data corruption problem

You need to make changes to the Registry for each Windows client and server.

NetSafe Checks and updates the configuration of Windows computers to prevent the infamous Windows data corruption problem.

The REDRTEST.EXE program is a diagnostic tool that can be used to test for current redirector software on network workstations and most file servers. It also tests for our recommended Windows registry settings including Windows NT/2000/XP opportunistic locking settings and can be used to implement those registry settings if they need to be changed. This program will also optionally implement recommended settings for workstations using the Novell Client 32 Redirector.

Testing/Tuning your network

Here is a simple Clipper program that will test your network and settings.  It is a very useful application to simulate very heavy database activity on your network.  This could apply to Quickbooks, FoxPro, Clipper, dBase, xBase, Paradox or any program the saves data in a file for multi concurrent user on the server.  It works in testing any file server, not just  Linux/Samba.  The program and compiled executable are available as a download: netperf.zip (~600K) .  If you run the application application from more than one workstation (recommend around 6) concurrently and if you get a GPF/error, that means your workstation and/or server is not configured properly.  GPF's could be due to incorrect "lock spin" settings or oplocks on the server and/or workstation.

Once all your workstations pass the test without any errors, you can then start to tune your network by changing any of the following settings, restarting Samba and get a new average time for your workstations to complete the test.

SO_SNDBUF=65536 SO_RCVBUF=65536
lock spin time = 15

Source code to the test program is as follows:

#include "inkey.ch"
#include "sixcdx2.ch"
#INCLUDE "set.CH"
REQUEST descend
request strtran
request pad
procedure main
	lMaster:=.f.
	setcancel(.T.)
	errorblock({|e|alertsys(e)})
//	set alternate to "atnight.log" additive
//	set alternate on
	set exclusive off
	set deleted off
	//cStation:=getenv('computername')
	cStation:=netconfig("Computer name")
	If !file("netPerf.dbf")
		lMaster:=.t.
        aDbf := {}
        AADD(aDbf, { "Station", "C", 25, 0 })
        AADD(aDbf, { "dStart", "D", 8, 0 })
        AADD(aDbf, { "tStart", "C", 8, 0 })
        AADD(aDbf, { "tEnd", "C", 8, 0 })
		AADD(aDbf, { "Elapsed", "N", 5, 0 })

        DBCREATE("netPerf2", aDbf)

        aDbf := {}
        AADD(aDbf, { "Column1", "N", 5, 0 })
        AADD(aDbf, { "Column2", "C", 1024, 0 })
        AADD(aDbf, { "Column3", "N", 13, 0 })
        AADD(aDbf, { "Station", "C", 25, 0 })

        DBCREATE("netPerf", aDbf)
		use netPerf exclusive
		index on column1 tag column1 of netperf
	else
		use netPerf exclusive new
		If !neterr()
			lMaster:=.t.
			zap
		EndIF
	endif
	dbcloseall()
	c2:=replicate('*',1024)
	c3:=1234567890123
	use netPerf2 new
	append blank
	use netPerf new
	ordsetfocus(1)
	cls
	If lMaster
		?"Start other workstations now"
		?"Press any key to start test"
		inkey(0)
		append blank
		dbcommit()
		dbunlock()
	else
		?"Waiting for start signal."
		while lastrec()=0
		end
	EndIF
	dStart:=date()
	tStart:=time()
	nStart:=seconds()
	?"Started ",cStation,dStart,tStart
	For x:=1 to 100
		@24,0 say str(x)
		append blank
		fieldput(1,x)
		dbcommit()
		fieldput(2,c2)
		dbcommit()
		fieldput(3,c3)
		dbcommit()
		fieldput(4,cstation)
		dbcommit()
		dbunlock()
		dbseek(x-1)
	Next

	replace netperf2->tEnd with time(),;
			netperf2->elapsed with seconds()-nStart,;
			netperf2->station with cStation,;
			netperf2->dStart with dStart,;
			netperf2->tStart with tStart
	??" Ended ",netperf2->elapsed
	dbcloseall()
	inkey(5)
	return
static function clear_rec()
	local fieldtype,x,fillval
	for x:=1 to fcount()
		if !empty(fieldget(x))
			fieldtype:=valtype(fieldget(x))
			do case
			case fieldtype="L"
				fillval:=.F.
			case fieldtype="D"
				fillval:=ctod("")
			case fieldtype="N"
				fillval:=0
			otherwise
				fillval:=""
			endcase
			fieldput(x,fillval)
			//dbcommit()
		endif
	NEXT
	dbcommit()
return(.T.)
static function blank_rec
	local x
	for x:=1 to fcount()
		if !empty(fieldget(x))
			return(.F.)
		endif
	next
return(.T.)

For more information..

Drouillard & Associates, Inc. has been developing database application and providing networking support for over 20 years.  Please do not hesitate to use our consulting services.