From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from lists.gentoo.org (pigeon.gentoo.org [208.92.234.80]) by finch.gentoo.org (Postfix) with ESMTP id 70FE71381F3 for ; Wed, 31 Jul 2013 06:01:03 +0000 (UTC) Received: from pigeon.gentoo.org (localhost [127.0.0.1]) by pigeon.gentoo.org (Postfix) with SMTP id 9D2A7E09C5; Wed, 31 Jul 2013 06:00:53 +0000 (UTC) Received: from smtpq1.gn.mail.iss.as9143.net (smtpq1.gn.mail.iss.as9143.net [212.54.34.164]) by pigeon.gentoo.org (Postfix) with ESMTP id 65D9EE09B2 for ; Wed, 31 Jul 2013 06:00:52 +0000 (UTC) Received: from [212.54.34.135] (helo=smtp4.gn.mail.iss.as9143.net) by smtpq1.gn.mail.iss.as9143.net with esmtp (Exim 4.71) (envelope-from ) id 1V4PSt-0003js-9O for gentoo-user@lists.gentoo.org; Wed, 31 Jul 2013 08:00:51 +0200 Received: from 54698b76.cm-12-2c.dynamic.ziggo.nl ([84.105.139.118] helo=data.antarean.org) by smtp4.gn.mail.iss.as9143.net with esmtp (Exim 4.71) (envelope-from ) id 1V4PSs-0007hl-GL for gentoo-user@lists.gentoo.org; Wed, 31 Jul 2013 08:00:51 +0200 Received: from www.antarean.org (net.lan.antarean.org [10.20.13.13]) by data.antarean.org (Postfix) with ESMTP id DEA1D4B for ; Wed, 31 Jul 2013 08:00:46 +0200 (CEST) Received: from 83.80.30.114 (SquirrelMail authenticated user joost) by www.antarean.org with HTTP; Wed, 31 Jul 2013 08:00:46 +0200 Message-ID: <94098ddcc8ff4912850a69be70b1e392.squirrel@www.antarean.org> In-Reply-To: <20130730213403.GC29567@artifex> References: <20130729202238.GA23113@artifex> <20130730213403.GC29567@artifex> Date: Wed, 31 Jul 2013 08:00:46 +0200 Subject: Re: [gentoo-user] SQL Server Advice for Small Business From: "J. Roeleveld" To: gentoo-user@lists.gentoo.org User-Agent: SquirrelMail/1.4.22 Precedence: bulk List-Post: List-Help: List-Unsubscribe: List-Subscribe: List-Id: Gentoo Linux mail X-BeenThere: gentoo-user@lists.gentoo.org Reply-to: gentoo-user@lists.gentoo.org MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 (Normal) Importance: Normal Content-Transfer-Encoding: quoted-printable X-Ziggo-spambar: -- X-Ziggo-spamscore: -2.4 X-Ziggo-spamreport: BAYES_00=-1.9,RDNS_DYNAMIC=0.982,RP_MATCHES_RCVD=-1.452 X-Ziggo-Spam-Status: No X-Spam-Status: No X-Spam-Flag: No X-Archives-Salt: f80c29c1-0ad6-4f8a-9a60-0de2bda39472 X-Archives-Hash: c969b533c0378df93dd8413394415580 On Tue, July 30, 2013 23:34, Randy Westlund wrote: > On Tue, Jul 30, 2013 at 07:52:11AM +0200, J. Roeleveld wrote: >> >> Will the server be internet-facing? >> I would make sure you have a firewall and only open the port needed fo= r >> the front-end. >> Don't update the kernel too often, keep an eye out for security fixes >> and >> apply where necessary. >> Keep a seperate machine/VM where you build binary packages. This will >> significantly reduce the time needed to upgrade the software. >> > > No, it'll be LAN only. I'll filter out external connections. There's = no > wireless network and no adjacent businesses, so I'm not worrying too mu= ch > about security. The only thing I'll need from the outside is SSH. In that case, make sure it runs stable and take time to test new versions= . > So your recommendation is to have a VM on the server with the same > packages installed, compile things there, then move the binary package = to > the real server. I might set this up at some point, but I think I'll b= e > okay with updating things in place, so long as I do it at night. I wouldn't put the VM on the server itself, but instead on your desktop/laptop. That way you also have a development environment where you can test new features and fix the inevitable bugs. The binary packages from there can then be moved to the server when you are ready to update. I always stop applications when I update them. To minimize downtime, I always ensure I have binary packages available. >> That depends on your budget and requirements. >> For databases, RAID-10 is generally considered the best performance. >> Also >> avoid filling the disks and try to use the first half of the disk, >> rather >> then the whole. (First half is faster then 2nd half) >> RAID-10 in software (eg. Linux Software Raid in the kernel) outperform= s >> the cheaper RAID-cards easily. If you have the budget, you could inves= t >> in >> a dedicated hardware raid card (but make sure it is 100% hardware and >> doesn't use the CPU for the calculations) >> > > Okay, RAID-10 sounds good. Thanks for the tip about the first half of = the > drives. I got that from a book about Postgresql performance tuning :) The start is quite generic on how to test and optimize performance on hardware and OS level. >> Depends on how much you want in there. If just a simple share, then it >> will be simple. If you also want the MS Windows machines to authentica= te >> against it, things get a little more complicated. >> > > Should just be a simple share, I don't think I'll need any authenticati= on. I would still put in authentication. MS Windows can be set to save the password. That way, you can also set up personal homedirectories and enable tracing to see who does what. >> How mission-critical will this be? >> For my server (which has become quite critical over the years), I >> currently use a self-build server with good reliable components. >> TYAN-mainboard (with built-in iKVM), WD-RED drives, Areca hardware >> raid-card. >> >> When I started running my own server, it was on a cheap no-brand >> mainboard >> with simple desktop disks connected via IDE. (yes, ancient :) ) >> > > The server will be pretty important. If all goes according to plan, ev= ery > employee that uses a computer (~15) will be interacting with it through= out > the day. The goal is to replace paper records. Aside from the hard > drives, are there any other components that are especially important fo= r > databases? Yes, memory. Databases are happy with lots and lots of memory for caching= . Other then that, most components should work, but go for stability. Ignor= e boards that are designed for gaming/overclocking. Those are not generally designed for 24/7 usage over a period of several years. One of my mainboards is still 100% stable. Never had a crash. Only reason I stopped using it is because it only holds 4GB of memory. Tyan mainboards are, in my experience, rock-solid. Others on the list wil= l probably have their own preferences as well. You can also go for a pre-build server from the likes of DELL, HP, Supermicro,... Those generally use good quality hardware as well. And the= y often come with (optional) onsite warranty. >> You want to try to keep the database design optimized for the usage >> pattern of the client-tools. Which usually means not too much >> normalization. That helps with reporting, not when you need to do most= ly >> inserts. >> > > From what I've read so far, it sounded like everything should be > normalized as much as possible even if there's a slight performance hit > because it makes the system easier to modify and expand later. In my > prototype, I have it divided into as many tables as possible, and each > SELECT has mutiple joins. Is this a bad idea? JOINs are heavy for a database. Normalizing a database is nice, but I don't see that often on transactional systems. (Like what you are plannin= g on making) Modifying tables don't take much either, simply do an ALTER TABLE to add/expand fields. (Do NOT reduce the size, or you will LOOSE data) and i= f necessary fill the fields for existing records with default values. I would suggest not to overnormalize the database. Start by seperating th= e different pieces of information into parts like: - customers - orders - order details (a line on the order) - products - invoices - documents/files and other parts that are of interest. Then see if you can fit all that into seperate tables. >> How big will those documents be? >> Either, as already mentioned, store them as blobs, or on a (samba) sha= re >> and put metadata (filepath,name,description,...) in the database. >> > > I'm expecting job orders to have at most a few images of the job site, > blueprints, random things the customer/contractor emailed us, and a few > scanned sheets of handwritten notes. Storing them outside the database > sounds like asking for trouble. Binary blobs sounds good. It's a bit more difficult to organize, if the application itself handles the storing of the files and updating the metadata table, then that will go fine. Look at some of the DMS-systems to see how that is being handled there. >> Advice: >> 1) Backup >> 2) Backup >> 3) Did I mention backup? ;) >> >> A tip, when you decide to put the documents on a share, to ensure the >> backups are in sync, do the following: >> 1) stop access to the database >> 2) snapshot the fileshare (LVM helps here) >> 3) backup the database >> 4) allow access to the database again >> 5) backup the snapshot >> 6) remove the snapshot >> >> Total downtime with this should be less then 1 minute. A full backup >> using >> the Postgresql tools is really quick. >> Step 5 can then take as long as it takes. The environment will still b= e >> running. >> > > How often should a small database like this be backed up? Once a day? > Twice a day? I'm thinking that I should backup to another machine on t= he > network, then copy that to at least one off-side machine. That depends simply on the following: - How long a period of data can you afford to loose? I have a daily backup, but that is because I can afford to loose 1 day of data. Ensure you can always restore the backups. If you have a machine where yo= u develop new versions, use that also to restore the backups. That way you also have a backup machine handy where the employees can continue working. > Thanks for your help. You're welcome. -- Joost