Sunday, April 12, 2009
I’ve just lost 2 days going completely bananas over a performance issue that I could not explain.
I’ve got this Dell R300 rack server that runs Windows Server 2008 that I dedicate to running IIS and SQL Server 2008, mostly for development purposes.
In my previous blog entry, I was trying some benchmark to compare the performance of Access and SQL Server using INT and GUID and getting some strange results.
Here are the results I was getting from inserting large amounts of data in SQL Server:
| Machine |
Operating System |
Test without Transaction |
Test with Transaction |
| MacbookPro |
Windows Server 2008 x64 |
324 ms |
22 ms |
| Desktop |
Windows XP |
172 ms |
47 ms |
| Server |
Windows Server 2008 x64 |
8635 ms!! |
27 ms |
On the server, not using transactions makes the query run more than 8 seconds, at least an order of magnitude slower than it should!
I initially thought there was something wrong with my server setup but since I couldn’t find anything, I just spend the day re-installing the OS and SQL server, applying all patches and updates so the server is basically brand new, nothing else on the box, no other services, basically all the power is left for SQL Server…
Despair
When I saw the results for the first time after spending my Easter Sunday rebuilding the machine I felt dread and despair.
The gods were being unfair, it had to be a hardware issue and it had to be related to either memory or hard disk, although I couldn’t understand really why but these were the only things that I could see have such an impact on performance.
I started to look in the hardware settings:
And then I noticed this in the Policies tab of the Disk Device Properties :
Just for the lulz of it, I ticked the box, close the properties
And then tried my query again:
| Machine |
Operating System |
Test without Transaction |
Test with Transaction |
Server |
Windows Server 2008 x64 |
254 ms!! |
27 ms |
A nearly 35 fold increase in performance!
Moral of the story
If you are getting strange and inconsistent performance results from SQL Server, make sure you check that Enable advanced performance option.
Even if you’re not getting strange results, you may not be aware of the issue, only that some operations may be much slower than they should.
Before taking your machine apart and re-installing everything on it, check your hardware settings, there may be options made available by the manufacturer or the OS that you’re not aware of…
Lesson learnt.
|
Filed under : Database,Hardware,SQL Server,sysadmin
Wednesday, March 11, 2009
I like buying technical books.
Unfortunately, here in Hong Kong, we have less choice: there are not that many technical bookstores that cater for English-language books and the selection is rather limited.
So whenever a book isn’t available here, I buy it online as a PDF.
It’s cheaper, saves postage and I can keep them handy on my desktop computer at work and my laptop.
I love Safari and being able to access such a large library of books online in such a flexible way is great, but if you’re not in the US, the experience is not always that pleasant, with the site sometimes becoming a bit too slow for comfort.
The publishers I regularly buy ebooks from are O’Reilly, when they make the PDF available, and Apress.
O’Reilly’s PDF may have your subscribtion details embedded at the bottom of each page.
It’s perfectly reasonable and doesn’t ruin the experience: I can still search, copy snippets to the clipboard and print the book if I need to keep an annotated copy.
Apress encrypt the PDF with your account email. Again, that’s fine by me, they don’t prevent me from using the book and it’s not a great annoyance to have to type the password to unlock the book.
Now comes Wrox (Wiley): they publish fine books and even have some available as ebooks.
The biggest issue I have though is that they assume that people who buy their books are all potential criminals:
- The book is as expensive in paper as it is in ebook format. That can’t be right: ebooks have zero reproduction cost while paper books have huge material costs.
- The ebook version needs to be registered with Adobe Digital Editions that locks it to a particular machine.
- You’re only allowed to download the ebook to 4 different PCs and you’ve got 2 weeks to do so.
This seems fair, but it’s not: if I change OS or PC, I’ve already burnt 2 licenses.
- You can’t copy/paste more than a page at a time, up to 10 pages every week… that’s just a bit silly.
- Can’t copy any of the artwork, diagrams, etc.
- Doesn’t say anything about what happens if Adobe drops their particular DRM software or if I need to use the book on the next laptop I’ll buy a year from now.
- Adobe Digital Edition only supports Windows and Mac and a Sony reader. So using Linux (even though Wrox plublishes books about it) or a Kindle or any other mobile device is out of the question.
So the net advantage of buying an eBook from Wrox (Wiley) is: your guess is as good as mine.
Yeah, you can buy each chapter as a PDF: great value, at US$4.99 per chapter, the book is costing you nearly US$100. You can get the fully printed version for half the cost…
Still, I’ll concede that being able to download a particular chapter can be useful.
The bottom line is: if your book gets printed, it’s going to be pirated and distributed for free in ebook format within a couple of weeks of being published.
While thinking they are protecting their copyright, Wiley is in fact punishing people who buy their books.
I’ll stick with Apress and O’Reilly I think. At least I don’t feel treated like a thief.
12FEB2010 Update: Wrox is now trying DRM-free PDF ebooks. We can only applaud this decision. While the offering is still limited at the moment, most or all of their books will eventually be available DRM-free.
Bookshelf by Charles Kalpakian
|
Filed under : Books,Business,Reviews
Wednesday, February 4, 2009
After suffering broadband trouble for the past 9 months, including interruptions that lasted a few days, I decided to get an additional line installed by a different ISP.
I could have bought one of these multi-WAN devices but decided against it for a couple of reasons: I like a challenge and I wanted to achieve a particular setup that I wasn’t sure could be answered by off-the-shelf products (for a reasonable price that is).
This long article is fairly detailed but if your setup is similar it should be enough to get you going quickly.
The basic setup
Without further ado, this is the network configuration:
Notable things
We have 2 broadband connections:
- CYBR, a standard DSL line with a fixed IP
111.99.88.77 allocated through PPPoE.
- HKBN, a standard 100Mbps line with a fixed IP
30.40.50.62.
The network is split into different zones:
- the Internet zone, connected to our Firewall through interfaces
eth0 (ppp0) and eth1.
- a Firewall zone, delimited by the firewall system itself
- a DMZ zone connected through interface
eth2 for the servers we want to make visible from the Internet.
The DMZ has its own private subnet delimited by 192.168.254.0/255.255.255.0.
- a LAN zone connected through interface
eth3 so local computers can access the Internet and be protected from it.
The DMZ has its own private subnet delimited by 192.168.0.0/255.255.255.0.
Objectives
What we want from our setup:
- our firewall protects our DMZ and LAN from unwanted access.
- our win server can host websites or other services.
- our linux server can handle receiving and sending email or other services.
- our firewall can handle incoming traffic from either ISP.
- our firewall can load-balance local outgoing traffic across both ISP.
- If one line fails, incoming traffic switches to the other line.
- If one line fails, outgoing traffic switches to the other line.
- Eventually, we want both the linux and win servers to be able to host different websites and we want the firewall to send incoming requests to the right server.
In this first article, I’ll present the setup for items 1-5.
The remaining topics will be the subject of subsequent articles of their own.
Technologies
The firewall is our primary subject. What is being discussed here is pretty much distribution-independent and should work on all flavours of Linux.
OS on the firewall system
I chose CentOS on the firewall.
Being an almost byte-for-byte identical copy of RedHat Enterprise Linux, all configuration will be identical on RedHat and its derivatives such as Fedora.
Firewall software, first try
When my firewall needs are simpler, I use the Stronger IP Firewall Ruleset from the Linux IP Masquerade HOWTO.
I started to modify the script to adapt it to my new Multi-ISP setup but things got complicated once I needed to debug routing tables.
I got it 80% of the way but tracing network connections and packet routing is complicated and time-consuming.
After a couple of days of peering into log files and wireshark capture screens, I gave up manual configuration and decided to go with something else.
Firewall software, final
The product I chose in the end is shorewall: it’s a very flexible firewall system that create the necessary iptable rules and configure most of the routing needs to properly handle complex network setup.
Shorewall is Open Source, very stable, has been out for a long time, is actively maintained and has lots of excellent documentation and examples.
Things to know
Before we get into the meat of the article, you should brush up on the following topics:
- You have some knowledge of Linux system administration, know how to configure network connections, know how to enable/disable/stop/start services, able to edit config files.
- Networking: you should know what a netmask is, what a gateway is, what a subnet is and have a passing understanding of IP classes, IP notation, what ports are for, what’s the difference between the tcp, udp, icmp protocols, what Dynamic Port Forwarding (DNAT) is, what Network Address Translation (NAT) is, what masquerading means.
- Some basic understanding of DNS and local host name resolving (using
host.conf and resolv.conf)
- Some basic knowledge of what routing is for and how it works.
- Some knowledge of how the linux kernel handles network packets (NetFilter, basics of iptables).
You don’t need to be a specialist in any of these areas but any knowledge helps.
I’m far from being well versed into Netfilter and routing, it’s not often that I have to deal directly with these topics, but brushing up on these topics helped.
Things to read
Shorewall has very extensive documentation. So much so that it can be a bit daunting, not knowing where to start.
I found the following documents helpful to get me started:
Installing shorewall
Go to the download site list [http://shorewall.net/download.htm#Sites] and download the most appropriate binary package for your distribution.
If you get RPMs for RedHat systems, you only need to install (rpm -ivh) the following packages:
shorewall-4.X.X-X.noarch.rpm
shorewall-perl-4.X.X-X.noarch.rpm
If you install from source, only download, compile and install the common, doc and perl packages.
Preparing the system
For shorewall to properly handle both our firewall and packet routing needs, we need to make sure that the other parts of the system are not interfering with it.
Internet lines
Make sure that your multiple internet lines are properly working on their own!
Disable routing
- Make sure that you don’t define a
GATEWAY in the configuration of your network interfaces (in /etc/sysconfig/network-scripts/ifcfg-XXX) .
- If you use an (A)DSL connection, also set
DEFROUTE=no if its ifcfg-XXX file as well.
- Remove the
GATEWAY from the /etc/sysconfig/network file if there is one.
- Edit your
/etc/sysctl.conf file and set net.ipv4.conf.default.rp_filter = 0.
Disable firewall
Disable the current firewall, for instance using the system-config-securitylevel helper tool.
Be careful if you’re directly connected to the Internet, you will be left without protection!
You can actually wait until shorewall is properly configured to disable the firewall.
Shorewall configuration
Shorewall uses a set of simple configuration files, all located under /etc/shorewall/.
For exact detail of each configuration files, have a look at the list of man pages.
Zones
zones are probably the simplest configuration file.
Details in the zones man page.
Here we just name the various zones we want our firewall to handle:
################################################################
#ZONE TYPE OPTIONS IN OUT
# OPTIONS OPTIONS
fw firewall
net ipv4
loc ipv4
dmz ipv4
This just reflects our setup as highlighted in the diagram above.
Note that the fw zone is often referred to as the $FW variable instead in various configuration files.
Interfaces
Here we list all the network interfaces connected to our firewall and for which zone they apply.
Details in the interfaces man page.
################################################################
#ZONE INTERFACE BROADCAST OPTIONS
net ppp0 detect
net eth1 detect
dmz eth2 detect
loc eth3 detect
Note that for our net zone, we list the 2 interfaces connected to our ISPs.
If you’re using PPPoE to connect, don;t use the interface name eth0 but use ppp0 instead.
Policy
The policy file tells shorewall which default actions should be taken when traffic is moving from one zone to another.
These default actions are taken if no other special action was specified in other configuration files.
View the policy file as a list of default actions for the firewall.
Details about this configuration file as in its man page.
################################################################
#SOURCE DEST POLICY LOG LIMIT: CONNLIMIT:
# LEVEL BURST MASK
net net DROP info
loc net ACCEPT
dmz net ACCEPT
loc dmz ACCEPT
loc $FW ACCEPT
dmz $FW ACCEPT
$FW net ACCEPT
dmz loc DROP info
net all DROP info
all all DROP info
Traffic from one zone to another needs to be explicitely ACCEPTed, REJECTed or DROPped.
For instance, loc net ACCEPT means that we allow all traffic from our local LAN to the Internet, while net all DROP means we don’t allow incoming traffic from the internet to anyone (remember this is the default action, in most cases we will override this for specific types of traffic in the rules file).
When we set the default action to DROP, we can tell shorewall to keep a trace of the details in the /var/log/messages log.
Providers
The providers file is generally only used in a multi-ISP environment.
Here we define how we want to mark packets originating from one ISP with a unique ID so we can tell the kernel to route these packets to the right interface.
Not doing this would get packets received from one interface to be routed to the default gateway instead.
The details of this configuration file are explained in the providers man page for it.
#############################################################################
#NAME NUMBER MARK DUPLICATE INTERFACE GATEWAY OPTIONS COPY
CYBR 1 0x1 main ppp0 - track,balance=1 eth2,eth3
HKBN 2 0x2 main eth1 30.40.50.61 track,balance=5 eth2,eth3
Note that the DUPLICATE columns tells shorewall that it should make a copy of the main default routing table for this particular routing table (called CYBR or HKBN depending on which ISP we refer to).
Packets are marked with number 0×1 or 0×2 so we can distinguish them during their travel through the system.
For PPPoE connections, don’t specify a GATEWAY since it’s most likely that your ISP didn’t give you one.
The most interesting part of this file are the OPTIONS: track means that we want the packets to be tracked as they travel through the system; balance tells the kernel that we want traffic coming out to be spread over both interfaces.
Additionally, we want HKBN to receive more or less 5 times more traffic than CYBR (note that this has no effect on reply packets).
The COPY columns will ensure that the routing tables created for CYBR and HKBN are copied for each internal interface, so our eth2 and eth3 interfaces know how to route packets to the right ISP.
Route Rules
For our purpsose, the route_rules file only describes how traffic should be routed through one or the other ISP we set up in /etc/shorewall/providers.
Details are in the route_rules file man page.
#####################################################################
#SOURCE DEST PROVIDER PRIORITY
ppp0 - CYBR 1000
eth1 - HKBN 1000
Here we simply say that all traffic through the CYBR table should be sent to ppp0.
The PRIORITY is an ordering number that tell shorewall to consider this routing rule before it marks the packets. Since we know the packets originated from ppp0 or eth1 we don’t really need to mark them.
Masq
The masq file will contain the masquerading rules for our private interfaces: in essence, we want traffic from the local LAN and DMZ to be hidden behind our limited number of external IPs.
See the masq manpage for all the details.
#####################################################################
#INTERFACE SOURCE ADDRESS
# Ensure that traffic originating on the firewall and redirected via
# packet marks always has the source IP address corresponding to the
# interface that it is routed out of.
# See http://shorewall.net/MultiISP.html#Example1
ppp0 30.40.50.62 111.99.88.77
eth1 111.99.88.77 30.40.50.62
ppp0 eth2 111.99.88.77
eth1 eth2 30.40.50.62
ppp0 eth3 111.99.88.77
eth1 eth3 30.40.50.62
The first part ensures that the traffic coming out of our public interfaces but originating from the other is actually rewritten as originating from the right IP for the interface.
This ensures that packets leaving eth1 for instance don’t come out with the wrong source address of the other interface.
The second part of the ensures that packets from our LAN or DMZ leaving either public interfaces are doing so with the right IP address, so traffic from my desktop going through ppp0 for instance, will have its source address as 100.90.80.70.
Rules
This is the main file where we tell shorewall our basic configuration and how we want packets to be handled in the general case.
The /etc/shorewall/rules file contains the specific instructions on where to direct traffic that will override the default actions defined in the /etc/shorewall/policy file.
#####################################################################
#ACTION SOURCE DEST PROTO
#
SECTION NEW
# Drop and log packets that come from the outside but pretend
# to have a local address
DROP:info net:192.168.0.0/24 all
DROP:info net:192.168.254.0/24 all
# Redirect incoming traffic to the correct server for WWW and email
DNAT all dmz:192.168.254.20 tcp www
DNAT all dmz:192.168.254.10 tcp 110
DNAT all dmz:192.168.254.10 tcp 143
DNAT all dmz:192.168.254.10 tcp 25
In its most basic form, what we’ve just defined here is that we want all traffic from anywhere destined for port 80 (www) to be sent to our win server.
All mail traffic, POP3 (port 110), IMAP (port 143) and SMTP (port 25) is to be redirected to our linux server in the DMZ.
There are a few more useful rules that we can include, for instance, I want to be able to access my servers through either ISPs from home (IP 123.45.67.89) and disallow everyone else from accessing it.
#####################################################################
#ACTION SOURCE DEST PROTO
#
# Allow SSH to the firewall from the outside only from home
ACCEPT net:123.45.67.89 $FW tcp ssh
# Redirect input traffic to the correct server for RDP, VNC and SSH
DNAT net:123.45.67.89 dmz:192.168.254.10:22 tcp 2222
DNAT net:123.45.67.89 dmz:192.168.254.10 tcp 5901
DNAT net:123.45.67.89 dmz:192.168.254.20 tcp 3389
When I SSH to 30.40.50.62 or 100.90.80.70, on the normal port 22, I will access the firewall.
Now if I SSH to the non-standard port 2222, I will instead access the linux server.
Ports 5901 are for remoting through VNC on the linux machine, and port 3389 will be used for Remote Desktop connections to the win server.
To make sure my machines are up and running, I like to be able to ping them:
#####################################################################
#ACTION SOURCE DEST PROTO
#
# Accept pings between zones
ACCEPT dmz loc icmp echo-request
ACCEPT loc dmz icmp echo-request
Note that ping will only work between the LAN and the DMZ and pinging my firewall from the Internet will result in the requests being silently dropped.
I usually prefer that configuration as it makes discovering the servers by random bots slightly less likely.
There are lots of other cool things we can do with forwarding but that will do for now.
shorewall.conf
The last file we’re going to look at is the main configuration file for shorewall.
See details about each option from the man page for shorewall.conf.
Most options are OK by default. The only ones that I have had to change are:
STARTUP_ENABLED=Yes
MARK_IN_FORWARD_CHAIN=Yes
FASTACCEPT=Yes
OPTIMIZE=1
The first option tells shorewall that we want it to start automatically when the system boots.
That’s not enough though, so make sure that the service will be started:
# chkconfig shorewall --levels 235 on
Installing our firewall rules
Shorewall configuration files need to be compiled without error before the firewall is actually loaded by shorewall.
The command:
# shorewall restart
will stop and recompile the current configuration.
If there are any errors, the current firewall rules will be unchanged.
There are lots of other commands that can be issued. Check the man page for a complete list.
If you use PPPoE, you will want the firewall to be restarted every time the line reconnects.
The simplest way is to create a file /etc/ppp/if-up.local with only a single line:
shorewall restart
DNS
There is one remaining issue with our firewall: if a user on the LAN attempts to access the web server by its name the request will probably fail.
Same for accessing our mail server: we can configure our desktop to connect to 192.168.254.10 to get and send emails, but on the laptop we would usually use something like pop.acme.com instead so we can read our emails from outside the office.
Similarly, trying to access www.acme.com hosted on the win server from the linux server will fail.
One solution is to route traffic through the firewall but that’s actually fairly complicated to setup properly.
The shorewall FAQ 2 discourages this and instead recommends the use of split-DNS: it’s very easy to setup and it works like a charm.
dnsmasq
Just install dnsmasq on the firewall. There are ready-made packages available for it and a simple yum install dsnmasq should suffice.
Dnsmasq provides a simple DNS forwarding and DHCP service. I had already configured dhcpd -which is already fairly simple to configure- on my firewall so I won’t need DHCP from dnsmasq but you can easily set it up if you want.
On the DNS side, dnsmasq can be told to first try to resolve hostnames by looking at the standard /etc/hosts file and then query the DNS servers defined in /etc/resolv.conf if necessary.
This simple trick means that we can:
- Keep our normal DNS service pointing to say
100.90.80.70 for www.acme.com so that people on the Internet will properly resolve their web requests to our win server.
- Add an entry in the firewall’s
hosts file to point local clients to 192.168.254.20 instead.
To achieve this, simply edit /etc/hostsand add entries matching all your services:
# Acme's services.
# One line for each DNS entries accessible from the Internet
192.168.254.20 acme.com
192.168.254.20 www.acme.com
192.168.254.10 pop.acme.com
192.168.254.10 mail.acme.com
dsnmasq configuration
Edit the /etc/dsnmasq.conf and uncomment or add the following lines:
# Never forward plain names (without a dot or domain part)
domain-needed
# Never forward addresses in the non-routed address spaces.
bogus-priv
# listen on DMZ and LAN interfaces
interface=eth2
interface=eth3
# don't want dnsmasq to provide dhcp
no-dhcp-interface=eth2
no-dhcp-interface=eth3
Then make sure that dsnmasq will start on boot:
# chkconfig dnsmasq --levels 235 on
# service dnsmasq restart
DNS resolution
There may be one last issue with DNS: in your /etc/resolv.conf you will have listed the DNS servers of one or both of your ISPs.
The problem is that some ISPs don’t allow access to their name servers from a network different than theirs.
The result is that each time any of the systems issues a DNS request it may fail and need to be sent to the next server instead, which may also fail and introduce delays in accessing named resources on the Internet.
One easy way out is to not use the ISPs DNS servers but instead only list the free OpenDNS name servers in your resolv.conf:
search acme.com
nameserver 208.67.222.222
nameserver 208.67.220.220
Then make sure that you disable DNS in your /etc/sysconfig/network-config/ifcfg-XXX configuration file for your PPPoE connection:
PEERDNS=no
Failure to do so will result in your /etc/resolv.conf file being rewritten with the DNS servers of one of your ISP every time you reconnect to them.
DHCP configuration
If you use dhcpd for local users, then you will need to make sure that its DNS server is set to the firewall’s:
# DHCP Server Configuration file.
ddns-update-style none;
ignore client-updates;
subnet 192.168.0.0 netmask 255.255.255.0 {
option routers 192.168.0.1;
option subnet-mask 255.255.255.0;
option domain-name "acme.com";
option domain-name-servers 192.168.0.1;
range 192.168.0.200 192.168.0.250;
default-lease-time 86400;
max-lease-time 132000;
}
On your local machines that use DHCP, make sure to renew your IP.
All other machines should be configured to use 192.168.0.1 as their unique DNS server and the machines in the DMZ should have their DNS set to 192.168.254.1.
Unless you reboot, don’t forget and flush the local DNS cache of each machine:
On Windows, from the command line:
C:\> ipconfig /flushdns
On Mac, from the terminal:
bash-x.xxx$ dnscacheutil -flushcache
Initial conclusions
I believe this type of firewall setup is fairly common and I hope that the -rather long- article helped you get your own setup in place.
In the -much shorter- follow-up articles, we’ll make our system as redundant as possible so our web and email services stay online even when one of the broadband connections fails.
In the meantime, don’t hesitate to leave your comments and corrections below.
History
References
|
Filed under : Linux,Security,Web,sysadmin
When you start building an Access application, it’s tempting to just think about today’s problem and not worry at all about the future.
