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.