It seems that my friends at Oninit UK have worked with i2Global to port the excellent open source CRM SugarCRM to use Informix as its repository. It will be no surprise to us that with Informix standing behind it, SugarCRM now scales much better than it did with its default repository RDBMS. Duh!
When is IBM going to get it that they have the best database without peer in Informix and finally get 100% behind it?
Wednesday, February 8, 2012
Wednesday, December 14, 2011
Solution to a problem
I received an email this morning with a link to a Linked-In post that pointed to someone's BLOG. The Blogger was proposing a solution to a problem someone had presented to him at work that he thought was rather elegant and so posted it for the consumption and edification of the Linked-In community. The problem: In a single SQL statement register an attendee for an event unless the attendee is already registered for an overlapping event. The solution was, to me, overly complex and absolutely non-portable in that it takes advantage of Oracle's conditional INSERT statement, so I said to myself: "How would I solve this better and in a fairly portable fashion?" So, presented here is my solution:
Setup:
-- Table attendee( attendee_id serial, name char(40) );
-- Table event( event_id serial, title char(80), start_time datetime year to minute, end_time datetime year to minute, location char(10) );
-- Table attendance( attendee_id int, event_id int, register_time datetime year to fraction(3), attended boolean );
Assume all tables have the appropriate RI constraints and indexes in place.
I propose that a simple INSERT TRIGGER (and yes an UPDATE trigger would also be appropriate but I leave that to the reader as an exercise) is the correct, simple, and portable solution:
CREATE PROCEDURE attendee_check() REFERENCING NEW AS neu FOR attendance;
DEFINE clash INT;
SELECT count(*)
INTO clash
FROM attendee AS a, event AS e1, attendance AS ae, event AS e2
WHERE a.attendee_id = neu.attendee_id
AND e1.event_id = neu.event_id
AND ae.attendee_id = a.attendee_id
AND e2.event_id = ae.event_id
AND e1.start_time <= e2.end_time
AND e1.end_time >= e2.start_time
AND e2.event_id != e1.event_id;
IF (clash > 0) THEN
RAISE EXCEPTION -746, 0, "Overlapping Event Rejected!";
END IF
RETURN;
END PROCEDURE;
CREATE TRIGGER attend_trig_i INSERT ON attendance
REFERENCING NEW as neu
FOR EACH ROW
( EXECUTE PROCEDURE attendee_check( ) WITH TRIGGER REFERENCES );
-- Attendees
insert into attendee( 0, 'Art' );
insert into attendee( 0, 'Fred' );
insert into attendee( 0, 'Barney' );
-- Events
insert into event values( 0, 'Event 1', '2011-12-28 09:00', '2011-12-28 10:00', 'Here');
insert into event values( 0, 'Event 2', '2011-12-28 09:00', '2011-12-28 10:00', 'There');
insert into event values( 0, 'Event 3', '2011-12-28 09:30', '2011-12-28 10:30', 'Elsewhere');
insert into event values( 0, 'Event 4', '2011-12-28 10:30', '2011-12-28 11:30','Elsewhen');
-- Test:
> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 1, current, 'f' );
1 row(s) inserted.
> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 4, current, 'f' );
1 row(s) inserted.
> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 1, current, 'f' );
268: Unique constraint (informix.attendance_pk) violated.
100: ISAM error: duplicate value for a record with unique key.
Error in line 1
Near character position 51
> insert into attendance (attendee_id,event_id, register_time, attended ) values ( 1, 2, current, 'f' );
746: Overlapping Event Rejected!
Error in line 1
Near character position 1
> insert into attendance ( attendee_id, event_id, register_time, attended ) values ( 1, 3, current, 'f' );
746: Overlapping Event Rejected!
Error in line 1
Near character position 1
>
select * from attendance;
attendee_id event_id signup_time attended
1 1 2011-12-14 10:48:11.221 f
1 4 2011-12-14 10:49:21.361 f
2 row(s) retrieved.
>
Simple, elegant, and except for the details of the syntax of the stored procedure (and in some cases the trigger) it is portable and can be implemented in most RDBMSes, unlike the original solution. The final advantage is that this solution will prevent overlapping registrations no matter who writes the application making the insert which is now a simple insert. The application developer is not required to know in advance that he has to meet this requirement (as obvious as it should be ;-).
Setup:
-- Table attendee( attendee_id serial, name char(40) );
-- Table event( event_id serial, title char(80), start_time datetime year to minute, end_time datetime year to minute, location char(10) );
-- Table attendance( attendee_id int, event_id int, register_time datetime year to fraction(3), attended boolean );
Assume all tables have the appropriate RI constraints and indexes in place.
I propose that a simple INSERT TRIGGER (and yes an UPDATE trigger would also be appropriate but I leave that to the reader as an exercise) is the correct, simple, and portable solution:
CREATE PROCEDURE attendee_check() REFERENCING NEW AS neu FOR attendance;
DEFINE clash INT;
SELECT count(*)
INTO clash
FROM attendee AS a, event AS e1, attendance AS ae, event AS e2
WHERE a.attendee_id = neu.attendee_id
AND e1.event_id = neu.event_id
AND ae.attendee_id = a.attendee_id
AND e2.event_id = ae.event_id
AND e1.start_time <= e2.end_time
AND e1.end_time >= e2.start_time
AND e2.event_id != e1.event_id;
IF (clash > 0) THEN
RAISE EXCEPTION -746, 0, "Overlapping Event Rejected!";
END IF
RETURN;
END PROCEDURE;
CREATE TRIGGER attend_trig_i INSERT ON attendance
REFERENCING NEW as neu
FOR EACH ROW
( EXECUTE PROCEDURE attendee_check( ) WITH TRIGGER REFERENCES );
-- Attendees
insert into attendee( 0, 'Art' );
insert into attendee( 0, 'Fred' );
insert into attendee( 0, 'Barney' );
-- Events
insert into event values( 0, 'Event 1', '2011-12-28 09:00', '2011-12-28 10:00', 'Here');
insert into event values( 0, 'Event 2', '2011-12-28 09:00', '2011-12-28 10:00', 'There');
insert into event values( 0, 'Event 3', '2011-12-28 09:30', '2011-12-28 10:30', 'Elsewhere');
insert into event values( 0, 'Event 4', '2011-12-28 10:30', '2011-12-28 11:30','Elsewhen');
-- Test:
> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 1, current, 'f' );
1 row(s) inserted.
> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 4, current, 'f' );
1 row(s) inserted.
> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 1, current, 'f' );
268: Unique constraint (informix.attendance_pk) violated.
100: ISAM error: duplicate value for a record with unique key.
Error in line 1
Near character position 51
> insert into attendance (attendee_id,event_id, register_time, attended ) values ( 1, 2, current, 'f' );
746: Overlapping Event Rejected!
Error in line 1
Near character position 1
> insert into attendance ( attendee_id, event_id, register_time, attended ) values ( 1, 3, current, 'f' );
746: Overlapping Event Rejected!
Error in line 1
Near character position 1
>
select * from attendance;
attendee_id event_id signup_time attended
1 1 2011-12-14 10:48:11.221 f
1 4 2011-12-14 10:49:21.361 f
2 row(s) retrieved.
>
Simple, elegant, and except for the details of the syntax of the stored procedure (and in some cases the trigger) it is portable and can be implemented in most RDBMSes, unlike the original solution. The final advantage is that this solution will prevent overlapping registrations no matter who writes the application making the insert which is now a simple insert. The application developer is not required to know in advance that he has to meet this requirement (as obvious as it should be ;-).
Tuesday, August 9, 2011
The best database for every purpose!
As a consultant I often am asked "What is the best database for everything we do?" Unfortunately, there is no easy answer for that, and I know this is not the response you all expect from me. Bear with me on this and I will explain.
The best database, and to be clear by 'database' I mean Database Management System, is not MySQL, PostGreSQL, Ingres, Oracle, Sybase, IBM DB2 branded products, IBM's Informix branded products, Voltdb, OpenSQL, Berkeley DB, Progress, Unify, MS SQL Server, or any of the literally hundreds of other RDBMS products on the market whether they are free or for-cost. No single database product can serve all of the needs of any large organization nor for most smaller organizations. That's a fact! While I think that IBM Informix Dynamic Server Ultimate Edition is the cats meow of RDBMS products, the performance, feature, and innovation leader in my book, it is not best for every purpose in every organization.
So, the question remains out there: "What is the best database for ...", what do I recommend? Because if my clients have to use a different database server product for each group of applications that fit best, or even groupings that are sometimes second best, they are still going to have to maintain and be able to manage and tune several products from several companies won't they? The list of skill sets that their DBAs will need will be very broad and I will have to hire more DBAs than I might need if I could just use one product.
I've been thinking about this for a while, and I keep coming back to the marketing presentation I made to IBM two years ago and to how appropriate that was to this question even more so than to the question of how IBM should market its database products. If my clients could get the "right" database, or close enough, for every situation from a single vendor, and if that vendor could make the skills needed to manage all of those database products they market sufficiently portable, that would reduce my clients' costs of ownership substantially making this single vendor solution even more attractive.
OK, so what product lines are out there? Oracle effectively has MySQL for very small projects and their Enterprise Class Oracle server in its various "editions". Sybase has an Enterprise class server and a mobile server. Ingres has only an Enterprise class server but not much third party software support left. Progress is a good SMB server only. Same with Unify. PostGreSQL has many nice enterprise class features but not all and many organizations won't bet the ranch on an open source product that doesn't have corporate support behind it. Microsoft has only SQL Server in its two editions. That brings us to IBM which, as I pointed out two years ago, has a plethora or database management systems appropriate for any size organization and any task:
The best database, and to be clear by 'database' I mean Database Management System, is not MySQL, PostGreSQL, Ingres, Oracle, Sybase, IBM DB2 branded products, IBM's Informix branded products, Voltdb, OpenSQL, Berkeley DB, Progress, Unify, MS SQL Server, or any of the literally hundreds of other RDBMS products on the market whether they are free or for-cost. No single database product can serve all of the needs of any large organization nor for most smaller organizations. That's a fact! While I think that IBM Informix Dynamic Server Ultimate Edition is the cats meow of RDBMS products, the performance, feature, and innovation leader in my book, it is not best for every purpose in every organization.
So, the question remains out there: "What is the best database for ...", what do I recommend? Because if my clients have to use a different database server product for each group of applications that fit best, or even groupings that are sometimes second best, they are still going to have to maintain and be able to manage and tune several products from several companies won't they? The list of skill sets that their DBAs will need will be very broad and I will have to hire more DBAs than I might need if I could just use one product.
I've been thinking about this for a while, and I keep coming back to the marketing presentation I made to IBM two years ago and to how appropriate that was to this question even more so than to the question of how IBM should market its database products. If my clients could get the "right" database, or close enough, for every situation from a single vendor, and if that vendor could make the skills needed to manage all of those database products they market sufficiently portable, that would reduce my clients' costs of ownership substantially making this single vendor solution even more attractive.
OK, so what product lines are out there? Oracle effectively has MySQL for very small projects and their Enterprise Class Oracle server in its various "editions". Sybase has an Enterprise class server and a mobile server. Ingres has only an Enterprise class server but not much third party software support left. Progress is a good SMB server only. Same with Unify. PostGreSQL has many nice enterprise class features but not all and many organizations won't bet the ranch on an open source product that doesn't have corporate support behind it. Microsoft has only SQL Server in its two editions. That brings us to IBM which, as I pointed out two years ago, has a plethora or database management systems appropriate for any size organization and any task:
- C-ISAM and IMS for application level databases.
- IBM Informix Standard Edition for small business. Clean simple OLTP performance for simple schema databases serving up to several hundred users with zero maintenance. This is the original Informix database server and it is still a viable product for the right organization and project.
- IBM Informix OnLine for small to medium business. Clean simple OLTP performance for simple schema databases up to several TB in size serving up to several thousand users with extremely low maintenance and outstanding reliability and uptime. This is the RDBMS that revolutionized the industry by finally allowing one to archive a database without having to take it offline or block transactions.
- IBM Informix Dynamic Server (Innovator, Growth/Choice, and Ultimate Editions) for:
- Medium to large business
- OLTP performance and better than five-9s reliability
- Medium to large Data Warehouse
- Large Data Mart and DSS applications
- Object Relational features
- Industry leading expandability and extendability
- Industry leading uptime and reliability with near zero unplanned outage and very low planned outage requirements
- Industry leading TCO requiring 1/2 to 1/4 the DBA support of other RDBMS products
- Databases up to 128PB serving many thousands of concurrent users
- Industry leading replication and clustering technology that scales better than the competition
- Unmatched embeddability with configurable footprint
- Heterogeneous clusters of hundreds of servers on different hardware and OS platforms without special backbones or infrastructure
- IBM DB2 Mainframe for mainframe based applications
- IBM DB2 LUW which fills most of the same niches as Informix Dynamic Server
- Strength in 3rd party application support
- Very large data warehouses.
- Distributed server
- DB2 Pure Scale highly scalable homogeneous clustering
- IBM Informix Ultimate Warehouse Edition with the Informix Warehouse Accelerator for accelerating complex data warehouse style queries by up to 300X without losing Informix's OLTP performance and features and without requiring expensive special purpose hardware.
- Netezza provides performance even beyond Informix Ultimate Warehouse for dedicated DW applications that need ultimate speed.
- I have the answer to my clients' questions and
- IBM can approach the market with this now unified product line that no one can touch
Thursday, June 30, 2011
IBM Finally Wakes Up
It looks like IBM took my test and passed! On June 8, 2011, IBM in cooperation with SmartGRIDNews.com, ran a webinar presentation to over 450 Energy Industry executives and technologists. The main thrust of the presentation was avoiding the pitfalls of implementing Smart Meter systems, however, more than 50% of the time was spent explaining how Informix is the only product that can handle the volume of data that smart data produces. (See: http://www.smartgridnews.com/artman/publish/Video-Webinars/). Kudos to IBM's Richard Wozniak (an old Informixer BTW) for a clear presentation.
Thursday, June 16, 2011
Dumb and Dumber
This is a quiz. Just like the SATs/GREs/ACTs, first we have the story, then the questions to see if you are paying attention:
Let's say I have a product that has a feature that a large segment of the market needs. Let's add to this scenario that there is no other product that can fill the particular need as well. There are competing offerings, but none will work as well, all will cost more to purchase than I will sell mine for, and all will cost the customer more to maintain and manage than using my product. Being a poor historian I remember the bit of wisdom erroneously attributed to Ralph Waldo Emerson: "If you build a better mousetrap the world will beat a path to your door." (it was actually coined by Elbert Hubbard seven years after Emerson's death as something Mr. Emerson might have said if he had thought of it). Taking that sentiment to heart I post a note in my BLOG, announce the product on my product's Facebook Wall and LinkedIn page and wait for the sales to roll in.
When that doesn't work I hire some new sales people, move others from other products, and shuffle a successful sales leader from another product line over to head up a "SWAT Team" to go talk to all of my existing customers and try to get them to start using this peachy feature so I can generate some PR success stories about them that might prompt new customers to take a look at my product and this nifty feature.
Question 1:
The most likely outcome of this scenario is:
a. I will sell thousands of new licenses for my product in just weeks.
b. I may get a few existing customers to begin using this neat feature, but it will generate no new net licenses.
c. Nothing.
Answer: [ ]
Question 2:
The best word to describe me as a business man is:
a. Entrepreneur
b. Genius
c. Market leader.
d. Idiot
Answer: [ ]
Question 3:
The response from existing customers to my efforts to get them to use the nifty feature will likely be:
a. "Hey, this is really cool and fast and saves storage too! Thanks! When can I shoot my PR video?"
b. "Dude! We don't do that kind of stuff here, so I have no need for this nifty feature. Sorry."
c. "I know all about this feature, have for years. Don't need it. Go away!"
Answer: [ ]
Let's say I have a product that has a feature that a large segment of the market needs. Let's add to this scenario that there is no other product that can fill the particular need as well. There are competing offerings, but none will work as well, all will cost more to purchase than I will sell mine for, and all will cost the customer more to maintain and manage than using my product. Being a poor historian I remember the bit of wisdom erroneously attributed to Ralph Waldo Emerson: "If you build a better mousetrap the world will beat a path to your door." (it was actually coined by Elbert Hubbard seven years after Emerson's death as something Mr. Emerson might have said if he had thought of it). Taking that sentiment to heart I post a note in my BLOG, announce the product on my product's Facebook Wall and LinkedIn page and wait for the sales to roll in.
When that doesn't work I hire some new sales people, move others from other products, and shuffle a successful sales leader from another product line over to head up a "SWAT Team" to go talk to all of my existing customers and try to get them to start using this peachy feature so I can generate some PR success stories about them that might prompt new customers to take a look at my product and this nifty feature.
Question 1:
The most likely outcome of this scenario is:
a. I will sell thousands of new licenses for my product in just weeks.
b. I may get a few existing customers to begin using this neat feature, but it will generate no new net licenses.
c. Nothing.
Answer: [ ]
Question 2:
The best word to describe me as a business man is:
a. Entrepreneur
b. Genius
c. Market leader.
d. Idiot
Answer: [ ]
Question 3:
The response from existing customers to my efforts to get them to use the nifty feature will likely be:
a. "Hey, this is really cool and fast and saves storage too! Thanks! When can I shoot my PR video?"
b. "Dude! We don't do that kind of stuff here, so I have no need for this nifty feature. Sorry."
c. "I know all about this feature, have for years. Don't need it. Go away!"
Answer: [ ]
Thursday, May 5, 2011
AUS -versus- dostats
Do you like the idea of using the task manager to take care of update statistics tasks but prefer to use dostats than AUS (Auto Update Statistics) in 11.xx? No problem, for several releases dostats_ng.ec has supported creating an AUS-like refresh function.
For a while now I have noticed that clients that are trying to use AUS to manage their statistics on larger databases are experiencing problems with the system catalogs and some tables having data distributions that are out-of-date or insufficiently detailed. Many are going back to using dostats with our help.
In this entry I am going to show you how to completely replace AUS with dostats. The steps are simple:
The third script is just for you to use to unconditionally wipe clean your existing distributions and create a complete new set using dostats. This will "prime the pump" for the aging threshold so that every table has the same initial starting point for its stats. You will want to run this script once initially during a quiet time on your system and again say monthly or quarterly just to get everything on an even keel again periodically.
{
dostats -d $dbs -Q 100 -w 10 --isolation d -m --procedure dostats_refresh_$dbs 2>&1
} | tee $logdir/
For a while now I have noticed that clients that are trying to use AUS to manage their statistics on larger databases are experiencing problems with the system catalogs and some tables having data distributions that are out-of-date or insufficiently detailed. Many are going back to using dostats with our help.
In this entry I am going to show you how to completely replace AUS with dostats. The steps are simple:
- Get utils2_ak and build and install dostats (by default it is now built from the next generation dostats_ng.ec source).
- Get utils2_ak: This one is easy. Go to the IIUG Software Repository (www.iiug.org/software) and download the package.
- Build the package: Usually easy, on Linux and any platform with GNU Make and ESQL/C configured to use GCC, just type 'make'. The file BUILDING and notes in the makefile (Makefile.nognumake) explain what to do if you have to use UNIX make and the native cc compiler on various platforms. But this is also easy, it is usually a 5 minute task to modify the makefile for your platform - don't forget to modify the makefile in myschema.d if you want to build myschema as well. If you are on AIX, you will also have to get my portable at utility "ar2" and compile and use that to extract the source for myschema from it's System V/POSIX format ar archive file. If you have trouble - email me.
- Install: There is an 'install' target in the makefile, so you just have to edit the target path into which the files will be installed and "make install".
- Disable the AUS tasks in the task manager.
- Easy Peasey. You can do this in OAT or just go into dbaccess or OAT's SQL editor and run:
UPDATE sysadmin:ph_task set tk_enable = 'f' where tk_name matches 'Auto *'; - Install new tasks.
- There are two tasks, the refresh and the evaluator. The refresh is VERY easy because dostats takes care of that for you. Configuring the evaluator is a bit trickier. Fortunately, I've scripted the whole shebang for you. The setup_dostats.ksh script below will take care of everything.
- There are three scripts below, setup_stats.ksh, evaluate_stats.ksh, and initial_stats.ksh Just install all three scripts in your path and make two edits to customize the scripts for your environment:
- In setup_stats.ksh change the start_time to the time you want the refresh task to kick off and estart_time to the time you want the next day's evaluator task to kick off. Normally you want the evaluator to run after the end of a normal day's processing cycles but before the refresh task. The refresh task should probably run before large end-of-day processes to optimize their runs. The evaluator takes about two minutes to process a 2000 table database.
- Change the location of the environment setup script you use in all three scripts.
- There is a line in each file, below the legend:
- # Environment "dot" script
- Change that next line to execute your environment file that gives access to the instance and which must also include the bin directory containing dostats and the scripts themselves in its PATH.
- All three scripts take two arguments, the name of the database you are scheduling, and the path to the directory into which you want log files for the runs to be written. The script setup_stats.ksh installs a stored procedure in the target database that runs the evaluate_stats.ksh script at the appropriate time, creates a table in sysadmin, dostats_stmts, to hold the set of commands for the refresh to run, creates the SPL procedure that executes the refresh commands and installs it as a task to be run daily at the appropriate times, and creates an initial set of commands for the first run.
- As configured the evaluator script will use whatever thresholds for aging and browsing you have already set in OAT for AUS to use. You can modify those settings in OAT or by editing the values stored in the tables in sysadmin, or you can edit the evaluate_stats.ksh script and replace --aus-thresholds with the -a -A [days] and -b -B [pct_chng] parameters.
##############################
# setup_stats.ksh - Install a procedure to generate updated data
# distributions for the named database.
#
# Written by: Art S. Kagel, Advanced DataTools Corp.
#
##############################
# setup_stats.ksh - Install a procedure to generate updated data
# distributions for the named database.
#
# Written by: Art S. Kagel, Advanced DataTools Corp.
#
##############################
dbs=$1
logdir=$2
export start_time='03:00:00'
export estart_time='00:00:00'
# Environment "dot" script
. ~informix/ADTC/aliases.art
#
# Install the initial evaluator procedure and populate the dostats_stmts table with
# an initial set of commands. Also schedules the refresh job to run at 3h
#
{
dostats -d $dbs -Q 100 -w 10 --aus-thresholds --isolation d -m --procedure dostats_refresh_$dbs --schedule --frequency d --first-time $start_time
2>&1
# Install the initial evaluator procedure and populate the dostats_stmts table with
# an initial set of commands. Also schedules the refresh job to run at 3h
#
{
dostats -d $dbs -Q 100 -w 10 --aus-thresholds --isolation d -m --procedure dostats_refresh_$dbs --schedule --frequency d --first-time $start_time
2>&1
#
# Install evaluator task to run at midnight daily
#
dbaccess -e $dbs - <<EOF
create procedure evaluate_${dbs}_stats();
define cmd char(200);
# Install evaluator task to run at midnight daily
#
dbaccess -e $dbs - <<EOF
create procedure evaluate_${dbs}_stats();
define cmd char(200);
let cmd = '/home/informix/ADTC/bin/
system cmd;
end procedure;
database sysadmin;
delete from ph_task where tk_name = 'dostats_evaluator_task';
INSERT INTO ph_task( tk_id, tk_name, tk_type, tk_group, tk_description, tk_execute,
tk_start_time, tk_stop_time, tk_frequency, tk_Sunday, tk_Monday,
tk_Tuesday, tk_Wednesday, tk_Thursday, tk_Friday, tk_Saturday ) VALUES
( 0, 'dostats_evaluator_${dbs}_task
'EXECUTE PROCEDURE $dbs:evaluate_${dbs}_
system cmd;
end procedure;
database sysadmin;
delete from ph_task where tk_name = 'dostats_evaluator_task';
INSERT INTO ph_task( tk_id, tk_name, tk_type, tk_group, tk_description, tk_execute,
tk_start_time, tk_stop_time, tk_frequency, tk_Sunday, tk_Monday,
tk_Tuesday, tk_Wednesday, tk_Thursday, tk_Friday, tk_Saturday ) VALUES
( 0, 'dostats_evaluator_${dbs}_task
'EXECUTE PROCEDURE $dbs:evaluate_${dbs}_
EOF
} | tee $logdir/setup_
} | tee $logdir/setup_
echo "Report also written to: $logdir/setup_stats.$dbs.$(date +%Y.%m.%d.%H.%M.%S).out"
==============================
#! /user/bin/ksh
##############################
# initial_stats.ksh - unconditionally generate a full set of data
# distributions for the named database.
#
# Written by: Art S. Kagel, Advanced DataTools Corp.
#
# initial_stats.ksh - unconditionally generate a full set of data
# distributions for the named database.
#
# Written by: Art S. Kagel, Advanced DataTools Corp.
#
##############################
dbs=$1
logdir=$2
# Environment "dot" script
. ~informix/ADTC/aliases.art
{
dostats -d $dbs -E -Q 100 -w 10 -n 100 --aus-thresholds --time-display --drop-distributions --isolation d -m 2>&1
} | tee $logdir/
dostats -d $dbs -E -Q 100 -w 10 -n 100 --aus-thresholds --time-display --drop-distributions --isolation d -m 2>&1
} | tee $logdir/
echo "Report also written to: $logdir/
==============================
#! /user/bin/ksh
##############################
# evaluate_stats.ksh - Install a procedure to generate updated data
# distributions for the named database.
#
# Written by: Art S. Kagel, Advanced DataTools Corp.
#
# evaluate_stats.ksh - Install a procedure to generate updated data
# distributions for the named database.
#
# Written by: Art S. Kagel, Advanced DataTools Corp.
#
##############################
dbs=$1
logdir=$2
# Environment "dot" script
. ~informix/ADTC/aliases.art
dostats -d $dbs -Q 100 -w 10 --isolation d -m --procedure dostats_refresh_$dbs 2>&1
} | tee $logdir/
Tuesday, October 26, 2010
My next new feature request
I've thought of my next feature request. Speaking with other users, especially those in high volume near 24x7 environments, I often hear the lament that after a server restart it can take many minutes and often hours before the server reaches its normal steady state with active rows in the cache and all data dictionary entries, data distributions, stored procedures, common statements, and other cached objects loaded up. During this ramp-up period performance is significantly reduced since most queries require a physical IO to read in the pages that are needed and possibly fill in other cache entries.
Based on this lament, my feature request is that in the background, after every checkpoint, the engine dumps out the list of cached data and index page addresses so that during a restart the engine can - optionally - read in the list and repopulate the cache. If this goes through the normal page processing algorithms I expect that the data dictionary and distribution caches can be filled in as well. The statements cache and stored procedure caches may have to be handled separately, though the procedure cache should be simple enough if the procnames are saved along with the page addresses at checkpoint time. The statement cache may be the costly one since the entire statement texts will have to be saved and restored to memory, but this is also doable. At least from an outsider's point of view.
I ran this by some of the Informix development team last night here at IOD and no one thought that this one was difficult to pull off. The only thing we lack is a strong user case. What do YOU think? Is this a useful feature?
Based on this lament, my feature request is that in the background, after every checkpoint, the engine dumps out the list of cached data and index page addresses so that during a restart the engine can - optionally - read in the list and repopulate the cache. If this goes through the normal page processing algorithms I expect that the data dictionary and distribution caches can be filled in as well. The statements cache and stored procedure caches may have to be handled separately, though the procedure cache should be simple enough if the procnames are saved along with the page addresses at checkpoint time. The statement cache may be the costly one since the entire statement texts will have to be saved and restored to memory, but this is also doable. At least from an outsider's point of view.
I ran this by some of the Informix development team last night here at IOD and no one thought that this one was difficult to pull off. The only thing we lack is a strong user case. What do YOU think? Is this a useful feature?
Monday, October 18, 2010
Fear the Panther
So, after much waiting and absolutely no pre-event hoopla, Panther is finally here. You will see much in the press and in the IBM official announcements about the features of Panther (Informix Dynamic Server version 11.70) that IBM thinks are important to the market place. Jerry Keesee is calling Panther "The Last of the Big Cats" hinting that IBM will be finding another theme for the next release.
I don't want to repeat all of what you will hear from IBM, but I do want to provide my own announcement of those features that I feel will be the most important in the market place over the next 12 months. This is a tremendous release as far as features are concerned. Without further ado, here's my list, categorized as IBM has done theirs:
OLTP Data Security
What do I mean by OLTP Data Security? Well, what is the greatest fear in an OLTP environment? To me it is loss of transactional data. If a user has completed a transaction and thinks it is secured in your database, you cannot afford to lose it. If that transaction was a $20 ATM withdrawal, your company is on the hook for the money. If it was a multi-million dollar electronic funds transfer, even more so.
If a user is in the midst of a transaction and your primary server crashes, do you tell the users "Sorry. System problem. Please try again later." You will lose a customer, or several hundred customers.
"OK.", you ask, "How can Panther help?" Panther has a new feature that IBM is calling "transaction survival". I call it "uninterruptible transactions". If your primary MACH11 server goes down any transactions begun by a user connected to any secondary server (HDR, SDS, or RSS) will not be lost but will be picked up by the surviving SDS secondary server which is promoted to be the new primary server and the transaction will continue unharmed. There is no application change or ONCONFIG parameter needed to enable this feature and make it work. Your existing applications don't have to be modified to reconnect to the new primary server. This is all automatic and effort-free. No other RDBMS server in the industry has this feature. Oracle does have a similar feature but it's not real and it's not automatic. You have to enable it in your application because it is actually implemented in the front-end libraries which fake survival by recording the transaction locally on the client in a flat file and replay the transaction from scratch after reconnecting to the new server. The feature is so lame that Oracle doesn't even talk about it.
To me transaction survival is a biggie! The biggest feature in Panther.
OLTP Performance
First, ADTC's testing shows that Panther is about 11% faster than 11.50xC7 without taking advantage of any of the performance enhancing features mentioned below. However, Panther has several features to further improve performance of OLTP systems.
One is the removal of the requirement for a foreign key constraint to have an index on the foreign key. The supporting index can now be optionally disabled at constraint create time (or later) and its storage will be released. The selectivity of these indexes on the foreign keys pointing to lookup tables with few rows is low and the utility of requiring the index has always been questionable. The engine does not need these indexes to enforce the constraints unless cascading deletes are enabled. You likely don't need them for searching because most systems have composite keys containing those foreign key columns that are used for searching and filtering. It is rare in an OLTP style query for the lookup tables to be selected as the primary query tables requiring an index on the dependent table to support the join. Removing this requirement will reduce the overhead of inserting and modifying data in tables with many code columns without having any impact on query performance.
Another one is the new Forest of Trees indexes. This is a new indexing method that combines many of the advantages of a Hash index with those of a traditional Informix B+tree index. For indexes on keys who's first column(s) have low selectivity but are still important to the correct processing of many OLTP queries (think geo, country, region of an index supporting a company's geographic reporting structure as an example) the b+tree indexes can become rather deep with relatively few unique elements on a level. What Forest of Trees indexes do is to let the Database Architect specify one or more of the leading columns to be used to create a hash key. Then a separate b+tree is created for each hash value containing only the remaining key parts following the hash columns. This results in several flatter b+tree indexes. You can't perform range scans on the hash columns (for that you will also need a pure b+tree index) but you can do so on the remaining columns in the index key.
Multiple index scans in the optimizer is another big win for OLTP. Often you have a complex join between three or four tables using the filtered rows from the independent tables to act as filters on the dependent table and several filters on multiple columns in the dependent table. The optimizer in earlier releases of IDS had to select the "best index" often resulting the engine having to read many rows of data and perform the final filters using the actual row data even though indexes on the filter columns are available. The old XPS optimizer could use more than one index on each table in a query. Now Panther can as well. This can reduce the number of long key compound indexes which can also improve insert, delete, and update performance. For databases that are delivered as part of a third party application where you can't control the schema, this one may be a HUGE win since the composite indexes that could make such queries reasonably efficient may never have existed in the first place.
Data Warehouse Performance
New Star Schema support in the optimizer. What does this one mean? This is another offshoot of XPS and the multi-index support above. In Panther, when you have many dimension tables, you can create a single compound index on all of the dimension table keys in the fact table. The optimizer will realize this and use the filters on the dimension tables to generate a list of valid combinations of the dimension tables' keys into a temp table and join that temp table to the fact table using the compound index to quickly locate the rows that satisfy the criteria on the dimension tables. Whoosh! At ADTC we've tested this one quite a bit and the results are impressive.
Administration
I put the new Heterogeneous Server Grid feature under this heading because I can't think of a better one. IBM is actually making quite a bit of noise about this one and rightfully so. If you have lots of old hardware around, don't throw it out. Don't sell it to a junker or to a used equipment reseller. Configure it as an IDS grid node and add to your company server farm's net computing power at near zero cost (since the boxes are already paid for and amortized). I'm waiting for confirmation about whether this means you can have an HDR or other MACH11 secondary that's build from different hardware or different OS. Is suspect not, but who knows what the future may bring. Heterogeneous grid was a big request from a number of very large Informix customers, so if Heterogeneous MACH11 members are what you need, speak up.
Zero downtime rolling upgrades. Speaks for itself. Jarrod down in New Zealand is jumping for joy over this one I'm sure. He and his one assistant have to upgrade a very large number of servers during the few weeks a year that his company's servers can afford to be offline even for a few hours. Being able to configure their server grid to be able to do this anytime to roll in a fixpack that removes a critical bug will be a god send for him and many of us out there.
The IIUG will soon be posting its new Features Survey which we will then hand to IBM to help them design the next release of Informix Dynamic Server. If you don't think that you have a voice in how IBM determines features for Informix, think again. Every one of the features mentioned here was requested by users like you/me/us either directly or through an expressed need that had to be filled. Several are features that were asked for and received high marks on the previous Features Surveys. So, when you see the Insider or email announcement of the Survey, fill it out.
In the next release I'd like to see:
What would you like to see? Comments welcome.
I don't want to repeat all of what you will hear from IBM, but I do want to provide my own announcement of those features that I feel will be the most important in the market place over the next 12 months. This is a tremendous release as far as features are concerned. Without further ado, here's my list, categorized as IBM has done theirs:
OLTP Data Security
What do I mean by OLTP Data Security? Well, what is the greatest fear in an OLTP environment? To me it is loss of transactional data. If a user has completed a transaction and thinks it is secured in your database, you cannot afford to lose it. If that transaction was a $20 ATM withdrawal, your company is on the hook for the money. If it was a multi-million dollar electronic funds transfer, even more so.
If a user is in the midst of a transaction and your primary server crashes, do you tell the users "Sorry. System problem. Please try again later." You will lose a customer, or several hundred customers.
"OK.", you ask, "How can Panther help?" Panther has a new feature that IBM is calling "transaction survival". I call it "uninterruptible transactions". If your primary MACH11 server goes down any transactions begun by a user connected to any secondary server (HDR, SDS, or RSS) will not be lost but will be picked up by the surviving SDS secondary server which is promoted to be the new primary server and the transaction will continue unharmed. There is no application change or ONCONFIG parameter needed to enable this feature and make it work. Your existing applications don't have to be modified to reconnect to the new primary server. This is all automatic and effort-free. No other RDBMS server in the industry has this feature. Oracle does have a similar feature but it's not real and it's not automatic. You have to enable it in your application because it is actually implemented in the front-end libraries which fake survival by recording the transaction locally on the client in a flat file and replay the transaction from scratch after reconnecting to the new server. The feature is so lame that Oracle doesn't even talk about it.
To me transaction survival is a biggie! The biggest feature in Panther.
OLTP Performance
First, ADTC's testing shows that Panther is about 11% faster than 11.50xC7 without taking advantage of any of the performance enhancing features mentioned below. However, Panther has several features to further improve performance of OLTP systems.
One is the removal of the requirement for a foreign key constraint to have an index on the foreign key. The supporting index can now be optionally disabled at constraint create time (or later) and its storage will be released. The selectivity of these indexes on the foreign keys pointing to lookup tables with few rows is low and the utility of requiring the index has always been questionable. The engine does not need these indexes to enforce the constraints unless cascading deletes are enabled. You likely don't need them for searching because most systems have composite keys containing those foreign key columns that are used for searching and filtering. It is rare in an OLTP style query for the lookup tables to be selected as the primary query tables requiring an index on the dependent table to support the join. Removing this requirement will reduce the overhead of inserting and modifying data in tables with many code columns without having any impact on query performance.
Another one is the new Forest of Trees indexes. This is a new indexing method that combines many of the advantages of a Hash index with those of a traditional Informix B+tree index. For indexes on keys who's first column(s) have low selectivity but are still important to the correct processing of many OLTP queries (think geo, country, region of an index supporting a company's geographic reporting structure as an example) the b+tree indexes can become rather deep with relatively few unique elements on a level. What Forest of Trees indexes do is to let the Database Architect specify one or more of the leading columns to be used to create a hash key. Then a separate b+tree is created for each hash value containing only the remaining key parts following the hash columns. This results in several flatter b+tree indexes. You can't perform range scans on the hash columns (for that you will also need a pure b+tree index) but you can do so on the remaining columns in the index key.
Multiple index scans in the optimizer is another big win for OLTP. Often you have a complex join between three or four tables using the filtered rows from the independent tables to act as filters on the dependent table and several filters on multiple columns in the dependent table. The optimizer in earlier releases of IDS had to select the "best index" often resulting the engine having to read many rows of data and perform the final filters using the actual row data even though indexes on the filter columns are available. The old XPS optimizer could use more than one index on each table in a query. Now Panther can as well. This can reduce the number of long key compound indexes which can also improve insert, delete, and update performance. For databases that are delivered as part of a third party application where you can't control the schema, this one may be a HUGE win since the composite indexes that could make such queries reasonably efficient may never have existed in the first place.
Data Warehouse Performance
New Star Schema support in the optimizer. What does this one mean? This is another offshoot of XPS and the multi-index support above. In Panther, when you have many dimension tables, you can create a single compound index on all of the dimension table keys in the fact table. The optimizer will realize this and use the filters on the dimension tables to generate a list of valid combinations of the dimension tables' keys into a temp table and join that temp table to the fact table using the compound index to quickly locate the rows that satisfy the criteria on the dimension tables. Whoosh! At ADTC we've tested this one quite a bit and the results are impressive.
Administration
I put the new Heterogeneous Server Grid feature under this heading because I can't think of a better one. IBM is actually making quite a bit of noise about this one and rightfully so. If you have lots of old hardware around, don't throw it out. Don't sell it to a junker or to a used equipment reseller. Configure it as an IDS grid node and add to your company server farm's net computing power at near zero cost (since the boxes are already paid for and amortized). I'm waiting for confirmation about whether this means you can have an HDR or other MACH11 secondary that's build from different hardware or different OS. Is suspect not, but who knows what the future may bring. Heterogeneous grid was a big request from a number of very large Informix customers, so if Heterogeneous MACH11 members are what you need, speak up.
Zero downtime rolling upgrades. Speaks for itself. Jarrod down in New Zealand is jumping for joy over this one I'm sure. He and his one assistant have to upgrade a very large number of servers during the few weeks a year that his company's servers can afford to be offline even for a few hours. Being able to configure their server grid to be able to do this anytime to roll in a fixpack that removes a critical bug will be a god send for him and many of us out there.
The IIUG will soon be posting its new Features Survey which we will then hand to IBM to help them design the next release of Informix Dynamic Server. If you don't think that you have a voice in how IBM determines features for Informix, think again. Every one of the features mentioned here was requested by users like you/me/us either directly or through an expressed need that had to be filled. Several are features that were asked for and received high marks on the previous Features Surveys. So, when you see the Insider or email announcement of the Survey, fill it out.
In the next release I'd like to see:
- Transaction survival when the server your session is connected to crashes!
- Bitmap indexes with multiple bitmaps used to generate a net bitmap of satisfying rows.
What would you like to see? Comments welcome.
Wednesday, July 28, 2010
New Journaled Filesystem Rant
There have been questions from multiple posters on the Informix Forums lately asking about Journaled File Systems (JFSes) like EXT3, EXT4, and ZFS among others. Bottom line? JFSes should NEVER be used for storing data for a database system. ANY database system, whether it is Berkley DB, Oracle, Sybase, DB2, MySQL, PostGreSQL, MS SQL Server, Informix, whatever. "But", you protest, "the journaling makes the filesystem safer. It speeds recovery. It is 'good thing'!" No. Not for databases. Flat out - no!
First, your database is already performing its own logging (read journaling for the DB neophite). That is sufficient to permit proper and secure recovery. It is also fast - if it weren't the database product would have gone the way of DBase2 and DBase3 long ago. The filesystem's journal is redundant at best and at worst will actually slow recovery (versus using RAW or COOKED - non-filesystem - space for storage) by requiring two sets of recovery operations to happen sequentially. Note that all properly designed database systems use O_SYNC or O_DIRECT mode write operations to ensure that their data is safely on disk. However, it has come to my attention that many journaling filesystems do not obey these directives when it comes to metadata changes. On these filesystems metadata is ALWAYS cached. Therefore there is neither a safety nor recovery speed gain from using JSFes for database storage.
Most JFSes use metadata only journaling. Here is some insight into that process, and why JFSes should not be used for database storage:
JFS2 and the Open Source JFS filesystem have no serious problems. EXT3 in default mode and ZFS at least are safe, but the problem with them is just the fact of the block relocations. There is the performance problem of rewriting a whole block every time the database changes a single page within the block and so negating much of the gains of caching and there is the bigger problem that the file is no longer even as contiguous as a non-journaled filesystem would have it be. Standard UNIX filesystems (EXT2 and UFS as examples) allocate blocks of contiguous space and try to leave free space that is contiguous with those allocated blocks unused when allocating space for other files so that as a file grows it remains mostly contiguous in multi-block chunks. This fragments the free space in an FS making it difficult to write very large files (like Informix chunks) that are contiguous, but if you keep the chunks on an FS that's dedicated to Informix chunks that has not been a real problem up until recently since Informix did not extend existing chunks over time prior to the recent release of Informix v 11.70. Informix 11.70 can, optionally, extend the size of an existing chunk. JFS's break that rule keeping the level of contiguous bits of a file the same as the block level. Even if a chunk were allocated as contiguous initially, over time the JFS will cause the file to become internally fragmented. A two logically contiguous blocks that were originally also physically contiguous can become spread out within the file's allocated space over time when they are rewritten. If you make the FS block size smaller to alleviate the costs of multiple block rewrites, you make the file fragmentation worse.
These problems don't affect filesystems and normal files as much as databases because the nature of the IO to files is different than IO to databases. When you write to a flat file, you write mostly sequentially, you rarely rewrite a portion of the file (unless you rewrite the entire file) and you never sync the file to disk before you close the file. That means that the cache will coalesce all writes until an entire block has been written out before the FS and OS cause a flush and sync of the cache to disk. That means that the FS has the ability to try to keep the rewritten blocks contiguous by allocating the replacement blocks contiguously. Essentially the file is relocated whole if it is rewritten.
Databases don't work that way. Informix, for example, writes every block to a COOKED device or filesystem chunk either under O_SYNC or O_DIRECT control both of which force the single write operation (and Informix only ever writes a single page or eight contiguous pages at a time) to be physically written and committed before the write() call returns. That means that the coalescing features of the FS and OS cache management are bypassed in favor of data safety. So, if the engine performs what it thinks is a sequential scan, it is actually performing a random read of the file swinging the read/write heads back and forth across the disk. If the physical structure is shared with other applications and even other machines (can you say massive SAN?) that will also be competing with those other storage clients for head positioning. In normal sequential scanning (ie RAW or COOKED device or non-JFS files) the disk, controller, filesystem, and database read ahead processing reduces the performance impact of this head contention somewhat. In a JFS that uses block relocation read ahead cannot help at all.
All of this having been said, I guess I have to change my mantra:
NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!!
Oh! Also, PLEASE: NO RAID6!!!!!!!!!!! Yuck.
First, your database is already performing its own logging (read journaling for the DB neophite). That is sufficient to permit proper and secure recovery. It is also fast - if it weren't the database product would have gone the way of DBase2 and DBase3 long ago. The filesystem's journal is redundant at best and at worst will actually slow recovery (versus using RAW or COOKED - non-filesystem - space for storage) by requiring two sets of recovery operations to happen sequentially. Note that all properly designed database systems use O_SYNC or O_DIRECT mode write operations to ensure that their data is safely on disk. However, it has come to my attention that many journaling filesystems do not obey these directives when it comes to metadata changes. On these filesystems metadata is ALWAYS cached. Therefore there is neither a safety nor recovery speed gain from using JSFes for database storage.
Most JFSes use metadata only journaling. Here is some insight into that process, and why JFSes should not be used for database storage:
- This (logical metadata only journaling) is the method used by EXT3, EXT4, JFS2, and ZFS
- All of these except AIX's JSFS2 use block relocation instead of physical block journaling (AIX's JFS2 - and the Open Source JFS filesystem derived from it - does not journal or relocate data blocks so it is safe). This means that on write a block is always written to a new location rather than overwriting the existing block on disk. A properly designed JFS will commit the new version of the disk block before updating the metadata or the logical journal (that's the problem with EXT4 - and EXT3 with write-back enabled - they write the metadata first, then the journal entry before actually committing the physical change to disk). Once the write and journal are completed the FS metadata is updated and the write is acknowledged. This means that, in a proper JFS, on a crash there are three possibilities:
- The new block version was partially or completely written but the journal entry was not written.
- The new block version and journal entry were written and committed.
- The new block version, journal, and metadata were written and committed.
In the first case, after recovery, the file remains unchanged, however the changes are lost. In the second case, after recovery, the FS makes the missed metadata entries and the file is modified during recovery and the original block version is freed for reuse. In the third case all was well before the crash and the original version of the block was released for reuse.
The problem with EXT4 (and EXT3 with write-back enabled) is that the application (meaning in this case Informix or other database system) thinks everything is hunky dory since the FS acknowledged the change as committed. However, immediately after the acknowledgment the physically modified block is still ONLY in cache and only the metadata and journal entry have been saved to disk. At this point if there is a crash, the file is actually unrecoverable! The metadata and the journal entry say the block has been moved to a new location and rewritten, but the new location has garbage in it from some previous block. This one made Linus Torvalds absolutely livid and he tore the EXT4 designers a new one over the design. You can GOOGLE his rants on the subject yourself. Last I heard you could not disable the write-back behavior of EXT4 - Linus was pushing to have that fixed, but I don't know if it ever was. I use EXT3 default mode for filesystems and EXT2 (the original non-journaled Linux FS) for database storage that I care about.JFS2 and the Open Source JFS filesystem have no serious problems. EXT3 in default mode and ZFS at least are safe, but the problem with them is just the fact of the block relocations. There is the performance problem of rewriting a whole block every time the database changes a single page within the block and so negating much of the gains of caching and there is the bigger problem that the file is no longer even as contiguous as a non-journaled filesystem would have it be. Standard UNIX filesystems (EXT2 and UFS as examples) allocate blocks of contiguous space and try to leave free space that is contiguous with those allocated blocks unused when allocating space for other files so that as a file grows it remains mostly contiguous in multi-block chunks. This fragments the free space in an FS making it difficult to write very large files (like Informix chunks) that are contiguous, but if you keep the chunks on an FS that's dedicated to Informix chunks that has not been a real problem up until recently since Informix did not extend existing chunks over time prior to the recent release of Informix v 11.70. Informix 11.70 can, optionally, extend the size of an existing chunk. JFS's break that rule keeping the level of contiguous bits of a file the same as the block level. Even if a chunk were allocated as contiguous initially, over time the JFS will cause the file to become internally fragmented. A two logically contiguous blocks that were originally also physically contiguous can become spread out within the file's allocated space over time when they are rewritten. If you make the FS block size smaller to alleviate the costs of multiple block rewrites, you make the file fragmentation worse.
These problems don't affect filesystems and normal files as much as databases because the nature of the IO to files is different than IO to databases. When you write to a flat file, you write mostly sequentially, you rarely rewrite a portion of the file (unless you rewrite the entire file) and you never sync the file to disk before you close the file. That means that the cache will coalesce all writes until an entire block has been written out before the FS and OS cause a flush and sync of the cache to disk. That means that the FS has the ability to try to keep the rewritten blocks contiguous by allocating the replacement blocks contiguously. Essentially the file is relocated whole if it is rewritten.
Databases don't work that way. Informix, for example, writes every block to a COOKED device or filesystem chunk either under O_SYNC or O_DIRECT control both of which force the single write operation (and Informix only ever writes a single page or eight contiguous pages at a time) to be physically written and committed before the write() call returns. That means that the coalescing features of the FS and OS cache management are bypassed in favor of data safety. So, if the engine performs what it thinks is a sequential scan, it is actually performing a random read of the file swinging the read/write heads back and forth across the disk. If the physical structure is shared with other applications and even other machines (can you say massive SAN?) that will also be competing with those other storage clients for head positioning. In normal sequential scanning (ie RAW or COOKED device or non-JFS files) the disk, controller, filesystem, and database read ahead processing reduces the performance impact of this head contention somewhat. In a JFS that uses block relocation read ahead cannot help at all.
All of this having been said, I guess I have to change my mantra:
NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!! NO JFS, NO RAID5!!!
Oh! Also, PLEASE: NO RAID6!!!!!!!!!!! Yuck.
Wednesday, June 16, 2010
RAID5 Rant
I used to post to the Informix NewsGroup about once a year. I haven't done so in a long time, and now I have a different forum to do that in. This BLOG. So, for all of your reading pleasure here is my analysis of why RAID5 is Unsafe at Any Speed:
RAID5 versus RAID10 (or even RAID3 or RAID4)
What is RAID5?
OK here is the deal, RAID5 uses ONLY ONE parity drive per stripe and many
RAID5 arrays are 5 (if your counts are different adjust the calculations
appropriately) drives (4 data and 1 parity though it is not a single
drive that is holding all of the parity as in RAID 3 & 4 but read on). If
you have 10 drives or say 20GB each for 200GB RAID5 will use 20% for
parity so you will have 160GB of storage. Now since RAID10, like
mirroring (RAID1), uses 1 (or more) mirror drive for each primary drive
you areusing 50% for redundancy so to get the same 160GB of storage you
will need 8 pairs or 16 - 20GB drives, which is why RAID5 is so popular.
This intro is just to put things into perspective.
RAID5 is physically a stripe set like RAID0 but with data recovery
included. RAID5 reserves one disk block out of each stripe block for
parity data. The parity block contains an error correction code which can
correct any error in the RAID5 block, in effect it is used in combination
with the remaining data blocks to recreate any single missing block, gone
missing because a drive has failed. The innovation of RAID5 over RAID3 &
RAID4 is that the parity is distributed on a round robin basis so that
there can be independent reading of different blocks from the several
drives. This is why RAID5 became more popular than RAID3 & RAID4 which
must sychronously read the same block from all drives together. So, if
Drive2 fails blocks 1,2,4,5,6 &7 are data blocks on this drive and blocks
3 and 8 are parity blocks on this drive. So that means that the parity on
Drive5 will be used to recreate the data block from Disk2 if block 1 is
requested before a new drive replaces Drive2 or during the rebuilding of
the new Drive2 replacement. Likewise the parity on Drive1 will be used to
repair block 2 and the parity on Drive3 will repair block4, etc. For
block 2 all the data is safely on the remaining drives but during the
rebuilding of Drive2's replacement a new parity block will be calculated
from the block 2 data and will be written to Drive 2.
Now when a disk block is read from the array the RAID software/firmware
calculates which RAID block contains the disk block, which drive the disk
block is on and which drive contains the parity block for that RAID block
and reads ONLY the data drive. It returns the data block . If you later
modify the data block it recalculates the parity by subtracting the old
block and adding in the new version then in two separate operations it
writes the data block followed by the new parity block. To do this it
must first read the parity block from whichever drive contains the parity
for that stripe block and reread the unmodified data for the updated block
from the original drive. This read-read-write-write is known as the RAID5
write penalty since these two writes are sequential and synchronous the
write system call cannot return until the reread and both writes complete,
for safety, so writing to RAID5 is up to 50% slower than RAID0 for an
array of the same capacity.
Now what is RAID10:
RAID10 is one of the combinations of RAID1 (mirroring) and RAID0
(striping) which are possible. There used to be confusion about what
RAID01 or RAID01 meant and different RAID vendors defined them differently.
Several years ago I proposed the following standard language which
seems to have taken hold. When N mirrored pairs are striped together
this is called RAID10 because the mirroring (RAID1) is applied before
striping (RAID0). The other option is to create two stripe sets and mirror
them one to the other, this is known as RAID01 (because the RAID0 is applied
first). In either a RAID01 or RAID10 system each and every disk block is
completely duplicated on its drive's mirror. Performance-wise both RAID01
and RAID10 are functionally equivalent. The difference comes in during
recovery where RAID01 suffers from some of the same problems I will describe
affecting RAID5 while RAID10 does not.
OK, so what?
Now if a drive in the RAID5 array dies, is removed, or is shut off data is
returned by reading the blocks from the remaining drives and calculating
the missing data using the parity, assuming the defunct drive is not the
parity block drive for that RAID block. Note that it takes 4 physical
reads to replace the missing disk block (for a 5 drive array) for four out
of every five disk blocks leading to a 64% performance degradation until the
problem is discovered and a new drive can be mapped in to begin recovery.
If a drive in the RAID10 array dies data is returned from its mirror drive
in a single read with only minor (6.25% on average) performance reduction
when two non-contiguous blocks are needed from the damaged pair and none
otherwise.
One begins to get an inkling of what is going on and why I dislike RAID5,
but, as they say on late night info-mercials, wait, there's more.
What's wrong besides a bit of performance I don't know I'm missing?
OK, so that brings us to the final question of the day which is: What is
the problem with RAID5? It does recover a failed drive right? So writes
are slower, I don't do enough writing to worry about it and the cache
helps a lot also, I've got LOTS of cache! The problem is that despite the
improved reliability of modern drives and the improved error correction
codes on most drives, and even despite the additional 8 bytes of error
correction that EMC puts on every Clariion drive disk block (if you are
lucky enough to use EMC systems), it is more than a little possible that
a drive will become flaky and begin to return garbage. This is known as
partial media failure. Now SCSI controllers reserve several hundred disk
blocks to be remapped to replace fading sectors with unused ones, but if
the drive is going these will not last very long and will run out and SCSI
does NOT report correctable errors back to the OS! Therefore you will not
know the drive is becoming unstable until it is too late and there are no
more replacement sectors and the drive begins to return garbage. [Note
that the recently popular ATA drives do not (TMK) include bad sector
remapping in their hardware so garbage is returned that much sooner.]
When a drive returns garbage, since RAID5 does not EVER check parity on
read (RAID3 & RAID4 do BTW and both perform better for databases than
RAID5 to boot) when you write the garbage sector back garbage parity will
be calculated and your RAID5 integrity is lost! Similarly if a drive
fails and one of the remaining drives is flaky the replacement will be
rebuilt with garbage also.
Need more?
During recovery, read performance for a RAID5 array is degraded by
as much as 80%. Some advanced arrays let you configure the preference
more toward recovery or toward performance. However, doing so will
increase recovery time and increase the likelihood of losing a second
drive in the array before recovery completes resulting in catastrophic
data loss. RAID10 on the other hand will only be recovering one drive out
of 4 or more pairs with performance ONLY of reads from the recovering pair
degraded making the performance hit to the array overall only about 20%!
Plus there is no parity calculation time used during recovery - it's a
straight data copy.
What about that thing about losing a second drive?
Well with RAID10 there is no danger unless the one mirror that is recovering
also fails and that's 80% or more less likely than that any other drive in a RAID5
array will fail! And since most multiple drive failures are caused by
undetected manufacturing defects you can make even this possibility
vanishingly small by making sure to mirror every drive with one from a
different manufacturer's lot number.
"Oh!", I can hear you say, "This schenario does not seem likely!"
Unfortunately it is all too likely. It happened to me and I have heard
from several other DBAs and SAs who have similar experiences. My former
employer lost 50 drives over two weeks when a batch of 200 IBM OEM drives
began to fail. IBM discovered that that single lot of drives would have
their spindle bearings freeze after so many hours of operation. Fortunately
due in part to RAID10 and in part to a herculean effort by DG techs and our
own people over 2 weeks no data was lost. HOWEVER, one RAID5 filesystem was
a total loss after a second drive failed during recover. Fortunately
everything was on tape and the restore succeeded. However, that filesystem
was down for several hours causing 1500 developers to twiddle their thumbs
for most of a day. That one internal service outage of only a few hours
cost more in lost productivity than the extra cost of using RAID10 for all
of those filesystem arrays!
Conclusion? For safety and performance favor RAID10 first, RAID3 second,
RAID4 third, and RAID5 last! The original reason for the RAID2-5 specs
was that the high cost of disks was making RAID1, mirroring, impractical.
That is no longer the case! Drives are commodity priced, even the biggest
fastest drives are cheaper in absolute dollars than drives were then and
cost per MB is a tiny fraction of what it was. Does RAID5 make ANY sense
anymore? Obviously I think not.
To put things into perspective: If a drive costs $1000US (and most are
far less expensive than that) then switching
from a 4 pair RAID10 array to a 5 drive RAID5 array will save 3 drives or
$3000US. What is the cost of overtime, wear and tear on the technicians,
DBAs, managers, and customers of even a recovery scare? What is the cost
of reduced performance and possibly reduced customer satisfaction? Finally
what is the cost of lost business if data is unrecoverable? I maintain
that the drives are FAR cheaper! Hence my mantra:
NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5!
RAID5 versus RAID10 (or even RAID3 or RAID4)
What is RAID5?
OK here is the deal, RAID5 uses ONLY ONE parity drive per stripe and many
RAID5 arrays are 5 (if your counts are different adjust the calculations
appropriately) drives (4 data and 1 parity though it is not a single
drive that is holding all of the parity as in RAID 3 & 4 but read on). If
you have 10 drives or say 20GB each for 200GB RAID5 will use 20% for
parity so you will have 160GB of storage. Now since RAID10, like
mirroring (RAID1), uses 1 (or more) mirror drive for each primary drive
you areusing 50% for redundancy so to get the same 160GB of storage you
will need 8 pairs or 16 - 20GB drives, which is why RAID5 is so popular.
This intro is just to put things into perspective.
RAID5 is physically a stripe set like RAID0 but with data recovery
included. RAID5 reserves one disk block out of each stripe block for
parity data. The parity block contains an error correction code which can
correct any error in the RAID5 block, in effect it is used in combination
with the remaining data blocks to recreate any single missing block, gone
missing because a drive has failed. The innovation of RAID5 over RAID3 &
RAID4 is that the parity is distributed on a round robin basis so that
there can be independent reading of different blocks from the several
drives. This is why RAID5 became more popular than RAID3 & RAID4 which
must sychronously read the same block from all drives together. So, if
Drive2 fails blocks 1,2,4,5,6 &7 are data blocks on this drive and blocks
3 and 8 are parity blocks on this drive. So that means that the parity on
Drive5 will be used to recreate the data block from Disk2 if block 1 is
requested before a new drive replaces Drive2 or during the rebuilding of
the new Drive2 replacement. Likewise the parity on Drive1 will be used to
repair block 2 and the parity on Drive3 will repair block4, etc. For
block 2 all the data is safely on the remaining drives but during the
rebuilding of Drive2's replacement a new parity block will be calculated
from the block 2 data and will be written to Drive 2.
Now when a disk block is read from the array the RAID software/firmware
calculates which RAID block contains the disk block, which drive the disk
block is on and which drive contains the parity block for that RAID block
and reads ONLY the data drive. It returns the data block . If you later
modify the data block it recalculates the parity by subtracting the old
block and adding in the new version then in two separate operations it
writes the data block followed by the new parity block. To do this it
must first read the parity block from whichever drive contains the parity
for that stripe block and reread the unmodified data for the updated block
from the original drive. This read-read-write-write is known as the RAID5
write penalty since these two writes are sequential and synchronous the
write system call cannot return until the reread and both writes complete,
for safety, so writing to RAID5 is up to 50% slower than RAID0 for an
array of the same capacity.
Now what is RAID10:
RAID10 is one of the combinations of RAID1 (mirroring) and RAID0
(striping) which are possible. There used to be confusion about what
RAID01 or RAID01 meant and different RAID vendors defined them differently.
Several years ago I proposed the following standard language which
seems to have taken hold. When N mirrored pairs are striped together
this is called RAID10 because the mirroring (RAID1) is applied before
striping (RAID0). The other option is to create two stripe sets and mirror
them one to the other, this is known as RAID01 (because the RAID0 is applied
first). In either a RAID01 or RAID10 system each and every disk block is
completely duplicated on its drive's mirror. Performance-wise both RAID01
and RAID10 are functionally equivalent. The difference comes in during
recovery where RAID01 suffers from some of the same problems I will describe
affecting RAID5 while RAID10 does not.
OK, so what?
Now if a drive in the RAID5 array dies, is removed, or is shut off data is
returned by reading the blocks from the remaining drives and calculating
the missing data using the parity, assuming the defunct drive is not the
parity block drive for that RAID block. Note that it takes 4 physical
reads to replace the missing disk block (for a 5 drive array) for four out
of every five disk blocks leading to a 64% performance degradation until the
problem is discovered and a new drive can be mapped in to begin recovery.
If a drive in the RAID10 array dies data is returned from its mirror drive
in a single read with only minor (6.25% on average) performance reduction
when two non-contiguous blocks are needed from the damaged pair and none
otherwise.
One begins to get an inkling of what is going on and why I dislike RAID5,
but, as they say on late night info-mercials, wait, there's more.
What's wrong besides a bit of performance I don't know I'm missing?
OK, so that brings us to the final question of the day which is: What is
the problem with RAID5? It does recover a failed drive right? So writes
are slower, I don't do enough writing to worry about it and the cache
helps a lot also, I've got LOTS of cache! The problem is that despite the
improved reliability of modern drives and the improved error correction
codes on most drives, and even despite the additional 8 bytes of error
correction that EMC puts on every Clariion drive disk block (if you are
lucky enough to use EMC systems), it is more than a little possible that
a drive will become flaky and begin to return garbage. This is known as
partial media failure. Now SCSI controllers reserve several hundred disk
blocks to be remapped to replace fading sectors with unused ones, but if
the drive is going these will not last very long and will run out and SCSI
does NOT report correctable errors back to the OS! Therefore you will not
know the drive is becoming unstable until it is too late and there are no
more replacement sectors and the drive begins to return garbage. [Note
that the recently popular ATA drives do not (TMK) include bad sector
remapping in their hardware so garbage is returned that much sooner.]
When a drive returns garbage, since RAID5 does not EVER check parity on
read (RAID3 & RAID4 do BTW and both perform better for databases than
RAID5 to boot) when you write the garbage sector back garbage parity will
be calculated and your RAID5 integrity is lost! Similarly if a drive
fails and one of the remaining drives is flaky the replacement will be
rebuilt with garbage also.
Need more?
During recovery, read performance for a RAID5 array is degraded by
as much as 80%. Some advanced arrays let you configure the preference
more toward recovery or toward performance. However, doing so will
increase recovery time and increase the likelihood of losing a second
drive in the array before recovery completes resulting in catastrophic
data loss. RAID10 on the other hand will only be recovering one drive out
of 4 or more pairs with performance ONLY of reads from the recovering pair
degraded making the performance hit to the array overall only about 20%!
Plus there is no parity calculation time used during recovery - it's a
straight data copy.
What about that thing about losing a second drive?
Well with RAID10 there is no danger unless the one mirror that is recovering
also fails and that's 80% or more less likely than that any other drive in a RAID5
array will fail! And since most multiple drive failures are caused by
undetected manufacturing defects you can make even this possibility
vanishingly small by making sure to mirror every drive with one from a
different manufacturer's lot number.
"Oh!", I can hear you say, "This schenario does not seem likely!"
Unfortunately it is all too likely. It happened to me and I have heard
from several other DBAs and SAs who have similar experiences. My former
employer lost 50 drives over two weeks when a batch of 200 IBM OEM drives
began to fail. IBM discovered that that single lot of drives would have
their spindle bearings freeze after so many hours of operation. Fortunately
due in part to RAID10 and in part to a herculean effort by DG techs and our
own people over 2 weeks no data was lost. HOWEVER, one RAID5 filesystem was
a total loss after a second drive failed during recover. Fortunately
everything was on tape and the restore succeeded. However, that filesystem
was down for several hours causing 1500 developers to twiddle their thumbs
for most of a day. That one internal service outage of only a few hours
cost more in lost productivity than the extra cost of using RAID10 for all
of those filesystem arrays!
Conclusion? For safety and performance favor RAID10 first, RAID3 second,
RAID4 third, and RAID5 last! The original reason for the RAID2-5 specs
was that the high cost of disks was making RAID1, mirroring, impractical.
That is no longer the case! Drives are commodity priced, even the biggest
fastest drives are cheaper in absolute dollars than drives were then and
cost per MB is a tiny fraction of what it was. Does RAID5 make ANY sense
anymore? Obviously I think not.
To put things into perspective: If a drive costs $1000US (and most are
far less expensive than that) then switching
from a 4 pair RAID10 array to a 5 drive RAID5 array will save 3 drives or
$3000US. What is the cost of overtime, wear and tear on the technicians,
DBAs, managers, and customers of even a recovery scare? What is the cost
of reduced performance and possibly reduced customer satisfaction? Finally
what is the cost of lost business if data is unrecoverable? I maintain
that the drives are FAR cheaper! Hence my mantra:
NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5! NO RAID5!
Subscribe to:
Posts (Atom)