Moving to PostgreSQL's Object-Relational DBMS
For those planning to run an Access front end atop a converted back end, pgAdmin should do an adequate job of moving the data automatically. Calabrese also used a modified version of exSQL to define how Access and PostgreSQL would handle relationships between tables. The version he has made public at www.geocities.com/musica_6898/ postgresaccess_home.html runs a script that alters field-type conversion for several tasks, such as regulating how Access handles the money type. Bike Friday's Access front end saw PostgreSQL's numeric decimal fields as text fields. In order for Access to view the math properly, Calabrese changed the fields to a Float4—the method by which PostgreSQL describes a four-byte floating number—allowing Access to read them properly.
With more than 100 tables, Bike Friday's interface is fairly complex. Viewed from the user end, Bike Friday uses more than 80 screens for everything from entering an order, viewing a parts table, to scheduling production and tracking inventory. Therefore, Calabrese had to be sure that the system scaled for tens of users. Testing took several weeks, redesigning SQL queries as needed along the way, either by rewriting them on the Access side or, when that proved problematic, rewriting them on the back end until they ran at speed. Listings 1 and 2 illustrate the difference in typical queries and queries optimized for speed.
Generally one optimizes PostgreSQL queries using SQL commands such as Create index, vacuum, vacuum analyze, cluster and explain. However, Calabrese offers this warning: Access 97 took the liberty of changing his queries based on how it thought they would be more efficient. Calabrese headed this off by using a pass-through query that told Access not to touch the query but send it straight to back end.
In the optimization he did for Bike Friday's PostgreSQL database, Calabrese scored most of his speed gains by extracting smaller, more exact amounts of data. Instead of the database querying 100,000 product order details at once, he told it to only look at the orders using some 2,000 details instead. “Access is greedy”, Calabrese said. “It grabs all the records and goes through them every time. That's very inefficient. We have 30 people with the company now, and if each has a computer accessing the database, that's going to be problem real fast in terms of speed.”
The next stage in the changeover is debugging queries, and there are two basic routes here. The first is to activate and use the debugging tools in the ODBC driver for PostgreSQL. One can have the driver create a log so that whenever Access sends an SQL command, PostgreSQL puts it into the log, which is written to the root of the C drive. This will catch Access in the act if it tries to retrieve something like 100,000 rows or otherwise butcher a query and, for example, break it into a thousand smaller ones. Basically, it's an audit trail that makes it easier to catch haywire queries and rewrite them if something goes wrong, as it did here:
conn=86311032, query=' ' CONN ERROR: func=SQLDriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
Alternately, if Access is sending a query and the system hangs, one can change the debug level on the server side to read the queries being sent to it. Fine tuning is a matter of going through each screen and testing them to ensure they're all up to speed by simplifying queries, making them faster or combining them. This process sounds easy but isn't, considering how esoteric some SQL lore can be. But, running two or three complete alpha tests at this point is going to save grief later.
The next step before putting the whole thing into production is to beta test it. Calabrese monitored Bike Friday's back-end system while salespeople, executives and associates used it in real time. “You're not just testing whether the front end has errors, but how big you need to make the server”, Calabrese said. He wrote a query script that kept close watch on the three main bottlenecks (CPU, the disk and the network) to see what loads they were taking.
For hardware tweaking (CPU, disks and memory), Bruce Momjian's Linux Journal article “PostgreSQL Performance Tuning” (August 2001 issue and also accessible on-line at www.linuxjournal.com/lj-issues/issue88/4791.html ) provides a handy overview. Calabrese's script measured CPU stress based on how many seconds the load remained at 100%, 50% and idle. It looked at disk transfers in terms of the number of reads and writes to and from the disk, as well as the amount in kilobytes of those reads and writes. As for the network rate, Calabrese's script counted packets per second and bytes per second. Calabrese also suggested doing a ping/F on an isolated network, a flood ping that will indicate how much the server can take before it maxes. As far as memory goes, the more you have, the more data PostgreSQL will load into it and the faster the database will be.
Of course, the only way really to determine if a database is fast enough is whether or not the people using it feel it is fast enough. Fractional waits that seem insignificant on paper can be much longer in real time. Each organization will have its own tolerance level for speed and performance. The only way to be sure the database is working the way the organization wants it to is to let people use it and listen to what they say.
Finally, once you've gone through a few production tests, having listed and cleaned up all the errors in the interface, you're ready to roll out an open-source foundation for a real-world, enterprise e-business.
Chris Volpe is a technology writer based in New Hampshire. He can be reached at firstname.lastname@example.org.
|Designing Electronics with Linux||May 22, 2013|
|Dynamic DNS—an Object Lesson in Problem Solving||May 21, 2013|
|Using Salt Stack and Vagrant for Drupal Development||May 20, 2013|
|Making Linux and Android Get Along (It's Not as Hard as It Sounds)||May 16, 2013|
|Drupal Is a Framework: Why Everyone Needs to Understand This||May 15, 2013|
|Home, My Backup Data Center||May 13, 2013|
- RSS Feeds
- Dynamic DNS—an Object Lesson in Problem Solving
- Making Linux and Android Get Along (It's Not as Hard as It Sounds)
- Designing Electronics with Linux
- Using Salt Stack and Vagrant for Drupal Development
- New Products
- A Topic for Discussion - Open Source Feature-Richness?
- Drupal Is a Framework: Why Everyone Needs to Understand This
- Validate an E-Mail Address with PHP, the Right Way
- What's the tweeting protocol?
- Kernel Problem
9 hours 56 min ago
- BASH script to log IPs on public web server
14 hours 24 min ago
17 hours 59 min ago
- Reply to comment | Linux Journal
18 hours 32 min ago
- All the articles you talked
20 hours 55 min ago
- All the articles you talked
20 hours 58 min ago
- All the articles you talked
21 hours 14 sec ago
1 day 1 hour ago
- Keeping track of IP address
1 day 3 hours ago
- Roll your own dynamic dns
1 day 8 hours ago
Enter to Win an Adafruit Pi Cobbler Breakout Kit for Raspberry Pi
It's Raspberry Pi month at Linux Journal. Each week in May, Adafruit will be giving away a Pi-related prize to a lucky, randomly drawn LJ reader. Winners will be announced weekly.
Fill out the fields below to enter to win this week's prize-- a Pi Cobbler Breakout Kit for Raspberry Pi.
Congratulations to our winners so far:
- 5-8-13, Pi Starter Pack: Jack Davis
- 5-15-13, Pi Model B 512MB RAM: Patrick Dunn
- 5-21-13, Prototyping Pi Plate Kit: Philip Kirby
- Next winner announced on 5-27-13!
Free Webinar: Hadoop
How to Build an Optimal Hadoop Cluster to Store and Maintain Unlimited Amounts of Data Using Microservers
Realizing the promise of Apache® Hadoop® requires the effective deployment of compute, memory, storage and networking to achieve optimal results. With its flexibility and multitude of options, it is easy to over or under provision the server infrastructure, resulting in poor performance and high TCO. Join us for an in depth, technical discussion with industry experts from leading Hadoop and server companies who will provide insights into the key considerations for designing and deploying an optimal Hadoop cluster.
Some of key questions to be discussed are:
- What is the “typical” Hadoop cluster and what should be installed on the different machine types?
- Why should you consider the typical workload patterns when making your hardware decisions?
- Are all microservers created equal for Hadoop deployments?
- How do I plan for expansion if I require more compute, memory, storage or networking?