Pythian Group

PYTHIAN NEWS

Pythian is happy to announce today the achievement of three new Oracle PartnerNetwork Specializations bringing the total to seven under our Platinum level membership of the Oracle PartnerNetwork (OPN) program.

Now added to the list are Oracle Exadata, Oracle Data Warehousing, and Oracle GoldenGate.

Complementing the added specializations is Pythian’s launch of new Oracle Exadata-focused products and services including an Oracle Database 11g Release 2 migration calculator, Oracle Exadata Smart Flash Cache Monitoring Tool, and an Oracle Exadata Managed Services program that extends the company’s Oracle Exadata-readiness services.

Pythian’s most recent Oracle Exadata clients include a major financial services company in New York and an e-commerce company specializing in data aggregation for the travel industry. Pythian’s Oracle GoldenGate expertise is supporting Western Union currently.

• Read the PR about our newest Specializations
• Read about our first series of Specializations
• Hear us our Specialized experts in person at OOW11

To find out how we can put our specialized Oracle expertise to work for you, contact us, send us an email or call 1-866-798-4426 ext. 2.

Are you considering Oracle Exadata as a data warehousing or consolidation solution for your organization? Before you go any further, be sure to join Alex Gorbachev, Oracle ACE Director & Pythian CTO at noon tomorrow (EST) as he outlines where Oracle Exadata is the best fit.

• Register Now & More Webinar Details
• Read about LinkShare’s success with Oracle Exadata
• More Oracle Exadata resources
• Hear the project details of Pythian’s successful deployment of Oracle Exadata at LinkShare Corporation in New York, delivering 10x results
• Find out about more upcoming webinars

After enjoying the excellent hospitality of our host here in Pisa (6 courses) we were ready for our second day at YAPCEU 2010 here in sunny Pisa.

Larry’s new catch phrase “My Language is a four letter word” was the ‘Buzz word’ for today. We settled down to some very interesting talks, the highlight for me being Tim Bunce’s talk on using Devel::NYTProf to Optimize your code. Tim first gave us a quick and dirty overview of optimization which covered the basics of where to start and what to look for he followed up with real examples of Optimizer output and than wrapped up with a few before and after results on an optimization effort.

The rest of the day was dedicated in my opinion, to the future of DBs in with Nelson Ferraz giving an excellent presentation of his concepts for using Perl as to glue for a Data Warehouse application. Next on my agenda, Martin Berends reports on the present state of Perl 6 and interfaces database. There is progress here as we now have some access to the DBI for MySQL and some others. Also great news is the fact there is a good deal of development work going on.

Martin was quickly followed by Tim Bunce again who presented his proposal for the new Perl 6′s DBDI. Seems
we are going to use the JDBC specification with a little tweaking as our road map for the future. Tim also
showed of some Perl 6 black majik from Jonathan Worthington and he was able to how us DBI with DBD::Pg
running on Perl 6.

Allison Randal finished off today’s formal presentations with her “Migration Strategies” presentation. She
gave us some good insight into migration in that we, as developers, cannot force migration on the community. She also provided two examples of migrations: Apache, which took seven years but has almost full buy-in by users and Python, which was quick and dirty but has not received the same buy-in from the community.

Finally we all enjoyed the lightning talks as our wrap up. A number of neat quick ones such as ‘I speak Perl with a ‘c’ accent’, was a highlight for me.

Seems the videos and most of the slides (including my presentation on XS) may be online tomorrow. I will be sure to post them as soon as I see them.

At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.

See part 1 for the introduction and talking about power and hardware. This part will go over the 2nd “P”, partitioning. Learning about Oracle’s partitioning has gotten me more interested in how MySQL’s partitioning works, and I do hope that MySQL partitioning will develop to the level that Oracle partitioning does, because Oracle’s partitioning looks very nice (then again, that’s why it costs so much I guess).
Read the rest of this entry . . .

At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.

These are my notes from the session, which include comparisons of how Oracle works (which Maria gave) and how MySQL works (which I researched to figure out the difference, which is why this blog post took a month after the conference to write). Note that I am not an expert on data warehousing in either Oracle or MySQL, so these are more concepts to think about than hard-and-fast advice. In some places, I still have questions, and I am happy to have folks comment and contribute what they know.
Read the rest of this entry . . .

Pythian has full-featured Oracle Exadata Services complete with successful implementations and reference customers.

When Oracle first introduced Exadata at OpenWorld 2008, it was aimed squarely at the data warehouse market dominated by Teradata, Netezza, and other pure-play vendors. Version 2, introduced a year later, has expanded the scope to include general-purpose mixed and even pure transaction processing workloads. Marketing claims abound with reports of 10x and faster speed improvements.

In this series of articles (part 2 here and part 3 here), we’ll explore the major components of Exadata and the Oracle Database Machine and take a peek at how they’re designed with performance and scalability in mind.

Going against the industry trend of embedding database-specific logic in hardware, Exadata makes use of commodity off-the-shelf hardware components, with an underlying open source operating system stack. While arguably such a common hardware architecture makes it easier for competitors to copy functionality, it also gives Exadata a well-understood, stable, and tested platform that’s constantly evolving higher speeds and capacities.

Database nodes

The database nodes in an Oracle Database Machine will be familiar to anyone who has worked with Oracle RAC in a Linux/x86 platform. They consist of exactly the same Sun Fire x4170 1U servers sold for general-purpose computing, but come maxed out in terms of configuration: Read the rest of this entry . . .

At the March Boston MySQL User Group meeting, Jacob Nikom of MIT’s Lincoln Laboratory presented “Optimizing Concurrent Storage and Retrieval Operations for Real-Time Surveillance Applications.” In the middle of the talk, Jacob said he sometimes calls what he did in this application as “real-time data warehousing”, which was so accurate I decided to give that title to this blog post.

The slides can be downloaded in PDF format (1.3 Mb) at http://www.technocation.org/files/doc/Concurrent_database_performance_02.pdf.

This talk discussed how to do real-time retrieval operations while doing concurrent high volume insertion, including:

• How to keep up with 1.5 Mb/second per server incoming data stream
• server hardware comparison between a multi-core AMD Opteron and a multi core Intel Xeon
• MySQL/Postgres comparison
• schema design
• design of the storage/retrieval benchmark
• tuning MySQL

Read the rest of this entry . . .

The purpose of this post is to verify if a date dimension is better in regards to performance and functionality than a series of function-based indexes on a date column in the fact table.

The following is the date dimension and fact table used for the testing:

Date Dimension

create table date_dimension(
date_id number, --1..x days
sql_date date, -- trunc(date)
day_of_week varchar2(30), -- (long version)
day_of_week_short varchar2(3), -- (short version)
day_number_in_week number, -- (1..7)
day_number_in_month number, --(1..31)
day_number_in_year number, --(1..366)
workday varchar2(10), --(either workday or holiday)
holiday varchar2(50),
  --(specify text e.g. christmas, easter...)
weekday varchar2(10), -- (specify weekday or weekend)
last_day_in_month number, -- (28,29,30 or 31)
week_number_in_year number , --1..52
week_number_in_month number, --1,5
month varchar2(10) ,--(January...)
month_short varchar2(3), -- (Jan...)
month_number_in_year number, -- (1..12)
quarter_in_year number, -- (1,2,3,4)
year number, -- (4 digits)
chinesezodiac varchar2(50),
zodiac varchar2(50) ,
event varchar2(100) -- (election day,Hurricane Hugo) text field
) ;

The are rows for every day from the year 1850 to 2050 (way after I retire). There is a primary key on date_id, a unique key on sql_date, and bitmap indexes on all other columns. The table is compressed and its stats computed.

Segment sizes:

WEEK_NUMBER_IN_YEAR_IDX	.125
MONTH_IDX	.0625
MONTH_SHORT_IDX	.0625
MONTH_NUMBER_IN_YEAR_IDX	.0625
QUARTER_IN_YEAR_IDX	.0625
YEAR_IDX	.0625
EVENT_IDX	.0625
WEEK_NUMBER_IN_MONTH_IDX	.125
CHINESEZODIAC_IDX	.0625
ZODIAC_IDX	.0625
WORKDAY_IDX	.0625
HOLIDAY_IDX	.0625
WEEKDAY_IDX	.0625
LAST_DAY_IN_MONTH_IDX	.0625
DATE_DIMENSION_SQL_DATE	2
DATE_DIMENSION_PK	2
DAY_OF_WEEK_IDX	.1875
DAY_OF_WEEK_SHORT_IDX	.1875
DAY_NUMBER_IN_WEEK_IDX	.1875
DAY_NUMBER_IN_MONTH_IDX	.1875
DAY_NUMBER_IN_YEAR_IDX	.3125

Fact Table

create table fact (id number, tx_date_dm_id number, tx_date date, amount number);

There are 1 million rows in this table, with a compressed index on the date_id and tx_date (since it’s truncated to the day), as well as the following function-based indexes on the tx_date column:

Read the rest of this entry . . .