MySQL Swap Insanity: Solved

Yes, it has been a while since my last post.  Still in the midst of MySQL ecosystem, and enjoying it tremendously.  I thought I would make this post as it relates to a topic that has some varied results in the online world, and I would like to kick start this blog again.  We'll see how that goes... :)

MySQL has been able to harness the potential of more powerful (CPU) and larger (RAM, disk space) systems in recent years like never before. With this comes the possibility that something can go awry in a big way, and that just happened with us when we upgraded from MySQL 5.1 to MySQL 5.6. 

Our original 5.1 system was on CentOS, and aside from two funky issues (one being MySQL process getting shot in the head by cfengine), our system was stable and performant. With continuous data initiatives/pressures (more data, improved performance and increased availability), we upgraded our system to MySQL 5.6 on Ubuntu 14.04, managed by puppet.

The first day or two were smooth; transition of data was a big deal, but other than that, it was almost a non-event.

Until day 3. Swap started to increase.

Not only that, swap increased in a big way. From 1% to over 80% in a matter of days. Yes, it started to plateaued, and in some cases, it was at the ~70% mark, and in other cases, at the ~99% mark. And yes, we had a couple of MySQL instances restart due to the dreaded signal 11 message (ie crash). At that point, we ended up restarting several MySQL instances daily so that I would not get woken up in the middle of the night with an issue. And this way I would not have to repair dozens or hundreds of tables.

Restarting MySQL in this manner was clearly not acceptable, as it reminded me of my old SQL server days when we restarted those instances daily, a common Windows "fix" at that time in history. 

So what could be the culprit in dealing with this swap issue in MySQL? 

There were a number of possibilities that we considered.  The first four possibilites were MySQL related, the remaining ones were system related.

1. key_buffer_size - reduce size 
2. max_connections - reduce value
3. performance_schema - turn it off 
4. table_open_cache - reduce value
5. swappiness - reduce value
6. numactl --interleave=all 
7. sysctl -q -w vm.drop_caches=3 

We (including my trusty DBA associate, Jonathan Park) tried a few things on this list, including #1, #2, #3, #6, and #7. 

We even tested on a box with Innodb engine set off. 

We still noticed that swap increased under our load test.  

In fact, we were very successful at increasing swap rapidly over a short time. We could start with swap at 1% and it would increase to about 80% in about 15 minutes. And I was merely running 8 threads to make that happen. 

What Worked?

Swappiness.

We noticed that swappiness was set to 60 by default (we have Ubuntu 14.04), and we reduced it to 10.  We ran our tests and swap did increase a little bit, but not as badly as before.  So we reduced it to 1 (not zero) and swap behavior stayed constant.

I am interested to find out what the MySQL community has done in addition to the prominent post at Jeremy Cole: MySQL Swap Insanity and the NUMA Architecture and would like to see what may have worked for you!

And if you try setting swappiness to 1, does it work for you?

The Conflicted Data Analyst

Inspired by a post from Juice Analytics.

We are a conflicted people. We love our TV and movie violence but worry that it ruins our children’s minds. We want to reduce healthcare costs, but don’t want to restrict the free market.

Conflicts like these leave little room for a satisfactory answer. Basic principles are in conflict and deeply-rooted desires run up against painful consequences. We don’t want to choose, and the middle ground feels like failure.

The people who practice data analysis and visualization have their own set of conflicts. Does this sound familiar?

“I want a feature-rich analytics tool, but…
My powerful analytics tool is too complex to use.”

“I want a dashboard that summarizes my business or functional area, but…
I don’t want to feel limited in what information I get to see.”

“I want a predictive model, but…
I don’t trust black box models when a result doesn’t align with my understanding.”

“I only want to focus on actionable data, but…
hey, look at that data point…that’s kind of interesting.”

“I want this report immediately, but…
now that I’ve got it, I don’t really have time to look at it.”

“I want to access data without a technical resource, but…
I don’t want to learn SQL because that’s for technical people.”

“I want to run scenario analyses on my data to make better decisions, but… I don’t believe the analysis can account for the complexity of my business.”

“I love geographic maps and network diagrams, but…
it is hard to see patterns or find insights from these types of visualizations.”

In a sense, each of these conflicts represents an opportunity to innovate to solve a persistent problem within our data analysis solutions. Find a way to satisfy the initial desire without suffering the assumed consequence, and you’ve got a chance to delight users of data.

How to Find Out if an Entire String Is Numeric

Problem: Find out if an entire string is numeric.  (Just like the is_numeric function in php.)

Problem, Part II: Most online resources show how you can find out if part of your string is numeric.

Solution:

SELECT str FROM tbl
  WHERE str REGEXP('(^[0-9]+$)');

Give it a whirl!


For those who would like to view some test results, here goes:

mysql> SELECT str FROM tbl;
+-----+
| str |
+-----+
| 10  |
| 10A |
| 10E |
| 78  |
| E78 |
+-----+
5 rows in set (0.00 sec)

mysql> SELECT str FROM tbl WHERE str REGEXP('(^[0-9]+$)');
+-----+
| str |
+-----+
| 10  |
| 78  |
+-----+
2 rows in set (0.00 sec)

Now for the other way around:

mysql> SELECT str FROM tbl WHERE str NOT REGEXP('(^[0-9]+$)');
+-----+
| str |
+-----+
| 10A |
| 10E |
| E78 |
+-----+
3 rows in set (0.00 sec)

How to Parse the Most Selected Tables From MySQL General Query Log in One Line

Problem: Pin down redundant queries by finding out which tables are being selected from the most.

I like general query log parsing tools, such as mk-query-digest or mysqlsla.

However, there are times when I am on a server as a dba with limited access and can only read files in /tmp.  And other times I just need to get something done quickly.

So what did I do to find out the most selected tables from a database?

1. Make sure /tmp has enough space to do what I'm about to do
2. Turn on the general query log with the file at /tmp/mysql_general.log.  This is usually /var/log/mysqllog/mysql_general or similar, but I need access to this log without waking up the sysadmin.
3. Turn off the general query log before /tmp fills up (for some people this might be just minutes)
4. Parse using this nifty set of commands:

grep -i "SELECT " /tmp/mysql_general.log | grep -io "SELECT .*" | sed 's|\(FROM [^ ]*\) .*|\1|' | sort | uniq -c | sort -nr | head -100

That's all!

As my mother would say, "Try it... You'll like it."

My Ignite Talk at MySQL Conference

My Ignite Talk (5 minute talk, 20 slides, 15 seconds per slide) at the MySQL Conference about Data Visualization was picked among three for "the Best of Ignite".  I would say I barely scraped into third spot, and it was inspiring giving a talk after Baron Schwartz gave his at the keynote on the last day of the conference.

The other two talks were "Scale Fail" by Josh Berkus (hilarious, must see) and "Causes of Downtime" by Baron Schwartz.  Enjoy!

Need to Replicate to a Database with a Different Name?

This is the first time this has happened to me, so I got excited about it until I noticed that this command has been with us from nearly the very beginning (ie MySQL 4).

The Situation
Master Server contains db1
Slave Server contains db1 and db2
Slave Server needs db1 from the Master to replicate to db2

The Solution
On my.cnf on the slave server:

--replicate-rewrite-db=from_name->to_name

Thus, this situation would dictate the following:

--replicate-rewrite-db=db1->db2

Easy!

The Wrinkle
Part 1
What if the Master Server contained db1 and db2; and
the Slave needs only db1 from the Master to replicate to db2 on the Slave?

Part 2
We don't want to replicate both db1 and db2 to this slave, although we want to replicate both databases to another slave - this means that the filter for databases should reside on my.cnf on the slave...

Assuming that we are using replicate-rewrite-db=db1->db2:
Using replicate-do-db on the my.cnf file on the slave, which database would you use with that command?

db1 or db2?  It only takes a moment in time to find out... :)

How to Log User Connections in MySQL

There is nothing that MySQL 5.1 explicitly performs to log user connections, but there is a combination of MySQL commands that can log user connections without having to turn on the general query log. I repeat: you do not have to turn the general query log on to make this work!

Would you like to find out what that is?

With the help of Baron Schwartz's post on http://www.xaprb.com/blog/2006/07/23/how-to-track-what-owns-a-mysql-connection/ I was able to find out as well. However, I wanted to simplify things a bit, because all I need is to track MySQL connections within MySQL for now. I'll probably increase the scope in the future, but for now, let's keep it simple.

Ingredients

init-connect

NOW()

CURRENT_USER()

CONNECTION_ID()

See it coming together?

Alrighty, here's the recipe...

Assumptions: The name of the database we will use to store this information will be called admin.

1. Create the table to store the connection information in. You don't have to include the unique key if you don't want to.

CREATE TABLE admin.connections (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, connect_time DATETIME NOT NULL, user_host VARCHAR(50) NOT NULL, connection_id INT UNSIGNED NOT NULL);

2. Set the init-connect variable. This is a string to be executed for each client that connects. Details here.

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

3. Check permissions of all of your users to ensure that they can insert a record into the admin.connections table.

4. Login as a user without global privileges and a row should be inserted in the admin.connections table. Note that the init-connect system variable does not work on users with global privileges. For better or for worse, all of us DBAs know what that means.

5. Watch your connection table grow and flourish. You've just started a new pet (project).

How to Save a Lot of Time with LOAD DATA INFILE Involving Large Files

Are you tired of waiting an hour or so performing a LOAD DATA INFILE statement involving a file with millions of rows?

If so, and you have enough money to pay for 3 monthly installments of $5.99... (Belgian chocolate will substitute nicely) -- you TOO can be a hero in your office and go home early for the weekend. :)

How?

Disable keys. Load data infile. Enable keys.

ALTER TABLE table_i_am_going_to_load_data_into DISABLE KEYS;
LOAD DATA INFILE '/folder/five_million_row_file.csv' INTO TABLE table_i_am_going_to_load_data_into FIELDS TERMINATED BY ',' ENCLOSED BY '"';
ALTER TABLE table_i_am_going_to_load_data_into ENABLE KEYS;

What are the savings?

In our situation -- we have hundreds of millions of rows, 12 columns, various data types, a compound primary key (bleck), a unique index, and 2 standard indexes, and a dash of pepper -- the original LOAD DATA INFILE statement took between 50 and 75 minutes to run.

Now, this is how long it takes:
1) Disabling keys: 0.01 seconds
2) Loading data infile: 2.45 to 2.8 minutes
3) Enabling keys: 10 to 12 minutes
4) Going home early: priceless

Thanks to Ronald Bradford for the idea... trying to find the link to his post on this, but I have lost it...

Have fun! Feel free to let me know of any other nifty LOAD DATA INFILE savings...

Cheers,
David

How to SHOW PROCESSLIST Every .5 Seconds

Yes, it has been a while since I last posted, so we'll see how this year goes...

Just the other evening I came across something that I should have done much sooner. Run SHOW PROCESSLIST every second without hitting "ENTER" repetitively.

The Setup

1) Create a temporary user with a non-sensitive password on localhost with SUPER privs. Yes, this crazy, but I did say *temporary*, right?

mysql> GRANT SUPER ON *.* TO tmp_user@localhost IDENTIFIED BY 'tmp_user_passwd_2102394567';

Now In Action

2) Go back to the shell and type this:

$ watch -n1 'mysql -utmp_user -ptmp_user_passwd_2102394567 --exec="SHOW PROCESSLIST"'

Simple, eh?

This is a good way to get a quick rundown of mysql's process list in a dev or test environment, such as simple web page load tests, or even an environment where you have a long running query and you are wondering when it will be completed.

Of course, there are a lot of fantastic tools out there that will do much more, and are better, but this is a good "quick & dirty" processlist check that works as a good substitute for typing SHOW PROCESSLIST in mysql 20 times over...

DON'T FORGET to DROP this user when you're done!

Until next time,
David

My Top 10 List For Developers

An excellent question was posted in a MySQL group in LinkedIn the other day that I had to post it here.

What is your "Top 10 List for Programmers Working with MySQL"?

I started mine... Let me know your thoughts on my list, and feel free to add other items to the list. I interface with programmers on a daily basis, and our experience lends us to help them in certain ways, but I think a "Top 10" list will enable us to be vigilant in our day-to-day development interactions.

Oh yes - I have 14 items in my "Top 10 List", and I have no problem adding a few more... :)

Drum roll please... [cue David Letterman]

1. DO know your tools, and do your testing on your own LAMP environment at your desk;
2. DO document;
3. DO ensure your queries use indexes and use them properly (there *is* a difference);
4. DO understand the EXPLAIN statement - its benefits and shortcomings alike;
5. DON'T use comma joins (you must explicitly help MySQL with execution plan on joins);
6. DO normalize your tables for transaction processing; (I like 3rd normal, but it depends on the app);
7. DO denormalize your tables for analytical processing; (the key is how much you denormalize!)
8. DO NOT create indexes on columns with few unique values (like gender);
9. DON'T open large recordsets (in a dev environment, this can be easily overlooked);
10. DO always test with large datasets (that's why we have Dev, Test, and Prod servers!);
11. DO always set numeric columns to NOT NULL unless NULL is absolutely required;
12. DON'T use cursors (or better yet, skip stored procedures altogether and use an API based on php, perl, C, or python);
13. DO understand the benefits and limitations of database abstraction and when to use it;
14. DON'T oversize your varchar's, char's, and don't use TEXT type when other types will do.
    

KickFire is Back

After receiving an email about talking with Robert David, Director of Sales at KickFire, I checked out recent news on KickFire, as there had been little written about this company aside from the big splash they made at the MySQL Users Conference back in April 2008.

And, lo and behold, there was a piece of news, posted on October 14 - Kickfire Enters Into MySQL Enterprise Agreement With Sun Microsystems. It did not seem to garner any attention from the MySQL community yet, and the only source that picked up on this was "The Financial". Interestingly, the tagline of "The Financial" is "Open Source Information", which does not represent the same meaning to those of us working with open source tools. I don't know who is doing the PR for KickFire, but there might have been a skipped number on the speed dial somewhere.

This obviously was not as exciting as a headline like "Kickfire Machine Now in Production, Already Saving Dozens of Companies Thousands of Dollars". Or "Extended Data Warehouse Testing Beyond TPC-H headquarters Proves the Robustness of the First MySQL Data Appliance". Perhaps a review from Percona, Pythian, The 451 Group, or others listed along the panel of PlanetMySQL.org could add some fury to the data warehouse fire that is burning in the news lately.

Anyways, tomorrow I will find out what the latest news on this is; after all, since April 2008, it does seem like a race of turtles between MySQL 5.1, the KickFire Appliance, and the introduction of an affordable electric car with a decent range. I understand that quality of the product exceeds the quantity of time, so I am willing to deal with that. But for the last 2+ years, I have learned not to hold my breath over that.

Fixing InnoDB IMPORT TABLESPACE Error: ERROR 1030 (HY000): Got error -1 from storage engine

Setup
We have one InnoDB file per table on our database, which was set with the following option: innodb_file_per_table.

This allows me to portably transport Innodb files on a system level with minimal hassle, and is much faster than mysqldump, as these tables are several GB each.

Problem
When transporting an .idb file from one server to another, I ran into the following error:

ERROR 1030 (HY000): Got error -1 from storage engine

How did I get to this point? I did the following:
1) created a dummy table in the database;
2) discarded its tablespace;
3) moved the .ibd file into the database folder on the system;
4) attached the tablespace back to the table

This is how these actions were accomplished:

mysql> CREATE TABLE new_innodb_table (value1 INT UNSIGNED NOT NULL, value2 CHAR(5) NOT NULL);

mysql> ALTER TABLE new_innodb_table DISCARD TABLESPACE;

[sys_prompt]# mv new_innodb_table.ibd /var/lib/mysql/db_name/

mysql> ALTER TABLE new_innodb_table ATTACH TABLESPACE;
ERROR 1030 (HY000): Got error -1 from storage engine

The next thing you automatically do when you see an error in your mysql client is check your error log immediately. If this log is not enabled, please stop reading this post and go here.

Here is what the log recorded:

081009 14:21:02 InnoDB: Error: tablespace id in file './db_name/new_innodb_table.ibd' is 26, but in the InnoDB
InnoDB: data dictionary it is 28.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
InnoDB: for how to resolve the issue.
081009 18:21:02 InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `stbHealth/sub_stb_health_associate`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE

From the error log we see that the error concerned mismatched tablespace ids.

If the tablespace id for my .ibd file was 26, then the tablespace would have been successfully imported. Also, if the tablespace id in the data dictionary 25, then all I would need to do is create a dummy innoDB table, thus incrementing the tablespace id in the data dictionary to 26, then I could try importing the tablespace again. Tricky, but it works (especially if that number was lower and you would make several dummy innodb tables).

Solution
However, the easier option is this: restart mysql, then do the same four steps listed near the beginning of the post. This way, the tablespace id on the data dictionary and the file matched; thus importing the tablespace succeeded.

This can give you greater confidence in dealing with some of the InnoDB "gotcha's" during the recovery process or even file transfers. It did with me!

Case Sensitive Fields

Yes, it has been too long since I last posted on this blog (3 months) - that's my apology; let's move forward.

There's too many interesting MySQL bits and pieces that I still come across my desk that I cannot help but post these fantastic learning opportunities to my blog. Obviously, you can read about all things interesting from PlanetMySql.org, but I hope that my blog will help sift people through specific issues that pertain directly to them while working with MySQL.

Oh yes... I am now located in Denver and I work for a big startup that uses MySQL for data mining. Most excellent!

Querying Case Sensitive Fields

Issue: Users to be able to query for case sensitive field values. For example, 700g is not the same as 700G.

Answer: COLLATE latin1_general_cs

Example in action:

mysql> CREATE TABLE test (non_case_sensitive char(1) NOT NULL, case_sensitive char(1) COLLATE latin1_general_cs) ENGINE=MyISAM charset=latin1;

mysql> insert into test values ('x','x');
mysql> insert into test values ('X','X');
mysql> insert into test values ('y','Y');
mysql> insert into test values ('Y','y');
mysql> select non_case_sensitive, count(*) from test group by non_case_sensitive;
+--------------------+----------+
| non_case_sensitive | count(*) |
+--------------------+----------+
| x | 2 |
| y | 2 |
+--------------------+----------+
2 rows in set (0.00 sec)

mysql> select case_sensitive, count(*) from test group by case_sensitive;
+----------------+----------+
| case_sensitive | count(*) |
+----------------+----------+
| X | 1 |
| x | 1 |
| Y | 1 |
| y | 1 |
+----------------+----------+
4 rows in set (0.00 sec)

Voila!

Performance Considerations

There does not appear to be much of a performance hit as a result of this change. On a 4.5 million record table, there was no difference. In fact many of our comparison queries (with same number of results) on the case sensitive table were a bit faster than on the case insensitive table.

Have fun! I hope to post more frequently in the future as I continue to find out interesting tidbits along the way.

Until next time,
David

Things Not Replicating Correctly? Part 2

My last post on June 10 was about when MySQL received queries from php scripts that looked like this:

mysql_select_db = ('database_I_DO_want_to_replicate', $link);
$sql = "INSERT INTO db_I_DO_NOT_want_to_replicate.repl_table (a,b,c) VALUES (1,2,3);";

-- and thus could not pass this over to the slave unless I set Replicate_Wild_Ignore_Table value IN ADDITION to Replicate_Ignore_DB as such in my configuration file:

replicate_ignore_DB = db_I_DO_NOT_want_to_replicate
replicate_wild_ignore_table = db_I_DO_NOT_want_to_replicate.%

News Flash

Three things:

1. As it happens, we do not need use replicate_ignore_DB for this to work properly;
2. Replicate_do_DB and Replicate_ignore_DB do not need to be used at all;
3. In fact, Replicate_do_DB is somewhat evil.

Firstly, replicate_wild_ignore_table is all you need to ignore databases, and it will properly replicate all kinds of php riff-raff, where the scripts do not necessarily select the proper database and they are always written in db.table style.

Secondly, replicate_wild_do_table is all you need to include databases, and it will properly replicate all kinds of php fudgerama, blah blah blah.

Thirdly, if you use replicate_do_DB in addition to replicate_wild_do_table to try to cover your bases, it will indeed NOT cover your bases. We have strict replication here, and it WILL break if the php code does this:

mysql_select_db = ('database_I_DO_NOT_want_to_replicate,$link);
$sql = "INSERT INTO database_I_DO_want_to_replicate.repl_table (a,b,c) VALUES (1,2,3);";

So, my advise, once again is:

DO NOT USE replicate_ignore_db and replicate_do_db!

Instead, use replicate_wild_ignore_db and replicate_wild_do_db.

Have fun! Try it out and see for yourself how this can work for and against you! (as well as MySQL loading my.cnf twice, causing your list of tables to appear twice -- proposed to be fixed for MySQL 5.1).

Until next time,
David

An Interesting Replication Tip

We recently moved some databases to different servers, and I altered our mySQL slave configuration files to take into account some of the databases we wanted to replicate (Replicate-do-db) and others that we wanted to ignore (Replicate-ignore-db) -- each of these server cases were mutually exclusive.

All went well, until I found a peculiar error:

Error 'Table 'db101.table101' doesn't exist' on query. Default database: 'db201'. Query: 'UPDATE `db101`.`table101` SET `value101`=`value101`+1 WHERE `gender`='F' AND `site`='177' AND `date`=CURDATE()'

I did not want to replicate anything from database db101, but I did want to replicate every table from db201.

How to Proceed

These are my choices:

1. Get our team of php programmers to properly specify the database that their script is using, even if it requires 1,000 updates. And it needs to be done 10 minutes ago because our slave is stopped;
2. Provide a bug report to mySQL and wait (months or years)
   
3. Use a proxy to sort out the proper database (but there are better purposes for my lua scripts);
4. Use Replicate-Wild-Ignore-Table option in the configuration file (very fast).

The Solution

Replicate-Do-Db=db201 # This line was already here
Replicate-Wild-Ignore-Table=db101.*

In Conclusion

There exists many solutions to a problem, but we try to aim for the simplest, most elegant solution to create the smallest footprint in terms of overhead and maintenance.

To further this last point, we must try our best to keep the overhead of our systems to a minimum, otherwise things can get out of control when we least expect it, as described in this post by John Mark Walker.

Until next time,
David

How to Group Customers by Number of Purchases Quickly!

This is a great little sql statement that packs a nice punch for the number crunchers out there...

A disclaimer first: make sure you don't do this on a live OLTP table with millions of rows unless you want your customers to wait. Thus, run it on your slave :).

Onto business...

The Request

"I need to know the number of people who made one purchase, two purchases, three purchases, and so on. Right now."

The Answer

SELECT total, COUNT(1) FROM (SELECT customer_id, COUNT(1) AS total FROM purchases_table GROUP BY customer_id) derived_table GROUP BY total ORDER BY total;

And would you know it, the result was there in seconds....

Until next time, (I do hope to put up some juicy warehousing how-to's soon)
David

MySQL Conference: The Week After

Wow... I had a great time at the annual MySQL Users Conference in Santa Clara.

Highlights:

• Received my CMDBA designation. Woohoo!
• Sun announced the possibility of an open source mobile phone;
• All day mysql proxy tutorial with Jan Kneschke and Giuseppe Maxia;
• Amazon's Power of Infrastructure as a Service - great premise; glitches remain;
• Great food and a nifty side trip to Santa Cruz;
  
• MySQL Workbench - nifty tool for database development;
• Use multiple sockets to run queries in parallel to speed up web pages (from Digg.com);
• Best Keynote: Who is the Dick on My Site? by Dick Hardt
• A chant that Marty Widenus required us to repeat regarding MySQL's development future: "We don't want crippleware";
• Discovered how Fickr scaled heavy concurrent writes in real time;
• Intriguing discussion panel on database warehousing: Sun/MySQL, Kickfire, Infobright, NitroSecurity, and a data warehouse contractor;
• Chocolate covered strawberries and ice cream goodies at the exhibit hall.
  

I hope to be more of a responsible blogger and share some very insightful ideas to those of you who were not able to make it to the conference.

Until next time,
David

Data Warehousing 101: Pitfalls to Avoid

Sometimes technology gets in the way of getting things done, and us tekkies must do what we can to make our data warehouse work for the purpose it is intended. Ralph Kimball makes 10 good points about the common pitfalls to avoid when implementing a data warehouse, as listed in his book "The Data Warehouse Toolkit", published by Wiley.

• Become immersed with technology rather than the requirements and the goals of business;
• Fail to recruit an influential and accessible management visionary to sponsor the data warehouse;
• Turn this into a multi-year project instead of pursuing manageable iterative development;
• Run out of budget while creating a normalized data structure, before building a viable presentation area;
• Pay more attention to back-room ease of development over front-room ease of use and query performance;
• Make queryable data in presentation area too complex, thus causing users to refer to developers;
• Populate dimensional models on a standalone basis without regard to the data architecture that ties them together;
• Load only summarized data into presentation area's dimensional tables;
• Presume that the business, its requirements and analytics and the underlying data and technology are static;
• Neglect to acknowled that data warehouse success is tied directly to user acceptance.

Next: using MySQL and php to extract, transform and load data (ETL) into the warehouse

Until Next Time,
David

Data Warehousing 101: Requirements for a Data Warehouse

Yes, it has been a while since I added an entry in my blog. I have been working on creating a dynamic data warehouse system reliant on the traditional LAMP stack (and a very nifty graphical plug-in - please comment below if you wish to know what it is!).

Firstly, I must make these qualifications before you read further:

1. I consider Bill Inmon and Ralph Kimball the pioneers of data warehousing;
2. Data warehousing has been a relatively recent concept, since 1983 when Teradata introduced a database decision support system, and most current day concepts we understand about data warehousing have been taking shape over the past 10 years;
   
3. I am new to this as well, but am very satisfied with the results I am obtaining.

OK... What does a data warehouse look like? This is a succinct high-level view as aptly described on the data warehouse entry on wikipedia:

• Subject-oriented - data is organized so that all data elements relating to the same real-world event or object are linked together.
• Time-variant -changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time.
• Non-volatile - data in the database is never over-written or deleted - once committed, the data is static, read-only, and retained for future reporting.
• Integrated - database contains data from most or all of an organization's operational systems and this data is made consistent.

This premise draws us to what appears to be a centralized solution for a data warehouse. However, you can approach it on a smaller scale by identifying the basic bones of your database structure, thus enabling you to produce results in a shorter span of time. Note: the building blocks of data is not done according to departments - for example, sales, marketing, customer service, and so on.

Instead, you can establish what your basis for your data is through fact tables and dimension tables.

A fact table is the "primary table where the numerical performance measurements of the business are stored" (The Data Warehouse Toolkit, second edition, p.16). The most useful facts of this table are numeric and additive.

Daily Sales Fact Table

Date Key (FK)
Product Key (FK)
Store Key (FK)
Quantity Sold
Dollar Sales Amount

A dimension table, the companion to the fact table, is the user interface to the data warehouse.

Production Dimension Table

Product Key (PK)
Product Description
SKU Number (not to be used as a key in the warehouse)
Brand Description
Category Description
Department Description
...more

Bringing together facts and dimensions becomes easier across all levels of business, as users agree that the dimensional model is their business. In our examples above, store operators use the store dimension table to relate to the number of products sold daily; marketing and product development refer to the attributes of the product dimension table to make conclusions about which products to promote and develop further.

Next I will relate common pitfalls known in the early stages of data warehousing and how to overcome them.

Until Next Time,
David

Don't Ever Use NOW() - `datetime field`

***All point news bulletin***

Do not ever use NOW() - `datetime field`! Broadcast this from the mountaintops of open source to the valleys of companies who dabble in it.

Instead, use the following:

TIME_TO_SEC(TIMEDIFF(Now(),`datetime field`))

There are other ways to do the above, just as long as you do not use NOW() - `datetime field`.

Wanna see proof?

mysql> SELECT RIGHT(NOW(),8) `Now`, RIGHT(datetime_inserted,8) `Inserted`, NOW()-datetime_inserted Incorrect, TIME_TO_SEC(TIMEDIFF(NOW(),datetime_inserted)) Correct from my_table;
+----------+----------+-----------+-------+
| -- Now - | Inserted | Incorrect | Correct
+----------+----------+-----------+-------+
| 11:16:59 | 10:59:45 | 5714.0000 | 1034
| 11:16:59 | 10:59:51 | 5708.0000 | 1028
| 11:16:59 | 10:59:55 | 5704.0000 | 1024
| 11:16:59 | 11:03:17 | 1342.0000 | 822
| 11:16:59 | 11:03:19 | 1340.0000 | 820
| 11:16:59 | 11:03:20 | 1339.0000 | 819
+----------+----------+-----------+-------+

Fun, eh?

Til next time,
David