via GIPHY Amazon releases a new database offering every other day. It sure isn’t easy to keep up. Join 35,000 others and follow Sean Hull on twitter @hullsean. Let’s say you’re hiring a devops & you want to suss out their database knowledge? Or you’re hiring a professional services firm or freelance consultant. Whatever the … Continue reading How to interview an amazon database expert →

Most analytical and BI databases have date dimension table(s). One frequently needs to generate and populate such data. I present a solution below for such data generation, written in Python. Please use different database drivers/modules to connect to your specific database server (MySQL, SQL Server, Oracle, etc.) for data population.

Notes:

1. It takes 2 parameters, start date and end date, in YYYYMMDD format, inclusive. Extensive error checking is built in, but let me know if you have comments/suggestions;

2. The script produce a Python dictionary (associated array) and print out its content;

3. The output includes dayNumber: a day’s position in a year. For example, 2011-02-01 is the 32ed day in 2011, therefore its dayNumber is 32;

4. The output includes weekNumber: a week’s position in a year. The week number in year is based on ISO standard. From documentation: the ISO year consists of 52 or 53 …

2011-06-26 update:

I am not sure if there are any changes in the latest make and gcc packages. Anyway, I noticed when run make, I encountered the message below:

make: g: Command not found
make: [qgen] Error 127 (ignored)

To fix this, find where gcc is at, then created a symbolic link g that points to gcc. All is well afterwards:
[root@ip-10-245-209-196 dbgen]# which gcc
/usr/bin/gcc
[root@ip-10-245-209-196 dbgen]# cd /usr/bin/
[root@ip-10-245-209-196 bin]# ln -s gcc g

End update
Recently I found myself doing some data loading benchmark testing with table partition. Data loading and storing for BI/DW/DSS stuff almost always involves data partitioning. SQL Server partition has a nice feature called partition switch, where you can swap data in and out of a partitioned table. …

Vadim Tkachenko published interesting benchmark results with PCI-E based SSDs here. I recently got a chance to benchmark FusionIO’s 320 GB PCI-E drive. It was really impressive. My results, done on Windows with sqlio, are consistent (not identical, of course, but in the same ballpark) with what Vadim reported in that blog post, done with sysbench on Linux.

sqlio is a popular IO throughput testing tool from Microsoft. I didn’t get to test the throughput when the SSD is close to full. The key takeaways that I learned from my testing are:

1. I can confirm that there is no difference between random and sequential IO, contrary to the traditional spindle based hard disks;

2. Read is significantly faster than write. Reads and writes with 64 threads can achieve around 1.4 GB/S and 400 MB/S …

To benchmark IO on Linux and MySQL transaction processing, SysBench is a popular choice that can do both. After poking around at the source code, it seems PostgreSQL and Oracle are also included for transaction processing testing if you have the proper header files, but I didn’t test those.

To benchmark IO on Windows and SQL Server transaction processing, Microsoft provides two tools, SQLIO and SQLIOSim. SQLIO is a misnomer in that it really doesn’t have much to do with SQL Server. It is a general purpose disk IO benchmark tool.

So today I was playing with SysBench and noticed that I can compile and build it on Windows as well. I decided I should run IO benchmark on a single machine with both tools (SQLIO and SysBench), and see if I could reconcile the results.

To make things simple, I thought I would just benchmark random read of 3G (orders of magnitude bigger than disk controller cache) files for 5 minutes (300 …

Database schema's need version control. Here you can find some guidelines help you control your changes.

Dave Edwards has offered me to write this week's Log Buffer, and I couldn't help but jump at the opportunity. I'll dive straight into it.
OracleI'll start with Oracle, the dust of the Sun acquisition has settled, so maybe it's time to return our attention to the regular issues.

Lets start with Hemant Chitale's Common Error series and his Some Common Errors - 2 - NOLOGGING as a Hint explaining what to expect from NOLOGGING. Kamran Agayev offers us an insight into Hemant's personality with his Exclusive Interview with Hemant K Chitale. My favorite quote is:

Do you refer to the documentation? And how often does …

That’s right — get your free 10-day trial! All the information I know is here:

http://bit.ly/37E9ld

But the basics are: No access to Rough Cuts or Downloads, for new subscribers only. It’s one of those “sign up and if you do not cancel after 10 days, we bill you” — and at $42.99 a month, that’s not a mistake you want to make. Must sign up by Nov. 24th.

To sign up now: https://ssl.safaribooksonline.com/tryitfree

I was asked to send this information along, so I am…Now’s your chance to skim High Performance MySQL, among other high quality books!

I am reading SQL and Relational Theory by C. J. Date. Baron Schwartz wrote a nice review for it. I am reading the online version, about half way through, so am not sure if it has an accompanying CD with source code. In any case, if you want to play with some SQL code listed in the book, here is the script to generate the tables and rows (or should I say relations at a certain point in time with tuples?)

CREATE TABLE S
   ( SNO    VARCHAR(5)   NOT NULL ,
     SNAME  VARCHAR(25)  NOT NULL ,
     STATUS INTEGER      NOT NULL ,
     CITY   VARCHAR(20)  NOT NULL ,
     UNIQUE ( SNO ) ) ;

 CREATE TABLE P
   ( PNO    VARCHAR(6)   NOT NULL ,
     PNAME  VARCHAR(25)  NOT NULL ,
     COLOR  CHAR(10)     NOT NULL ,
     WEIGHT NUMERIC(5,1) NOT NULL ,
     CITY   VARCHAR(20)  NOT NULL ,
     UNIQUE ( PNO ) ) ;

 CREATE TABLE SP
   ( SNO …

I caught up with Zach Skyles Owens, a PHP Evangelist at Microsoft. If you missed the embed, watch the video. I have some sparse notes below.

I learned some new things:

• Microsoft spends time working with the PHP community
• They are porting applications to work with an SQL Server backend
• They are ensuring that the language should “just work”, with the IIS and SQL Server stack. This is quite different from the usual AMP (Apache = server, MySQL = database, PHP = language) stack that we’re quite accustomed to.
• There is a Microsoft Web Platform, and there’s a Web Application Gallery, that brings in …