|
Mårten Mickos, the CEO of Eucalyptus and former CEO of MySQL AB, will be back on stage as the closing keynoter on September 19th at MySQL Sunday, one of the community events at the start of Oracle Open World 2010. |
|
The opening keynote will be delivered by Edward Screven, Chief Corporate Architect at Oracle. MySQL Sunday has a very rich schedule, and by the registration numbers it looks like it's going to be packed. |
|
It’s that time of the year again — Oracle OpenWorld time — and it’s my pleasure to announce our regular Oracle bloggers meetup again this year. We all know that Oracle community has grown this year so we expect to see folks from all the different technologies including MySQL, Java, Sun hardware folks in addition to the core Oracle database and apps crowd.
So… all of you Oracle bloggers attending Oracle Open World 2010…
… you are invited to attend this Oracle Bloggers Meetup during OOW 2009 — a chance to meet your online buddies face-to-face in relaxed and informal atmosphere.
When: Wed, 22-Sep-2010, 5:30pm
Where: Lower Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103.
See the “Lower Dining Room” on the floor plan below and ask where is the “Bloggers Meetup” booked under my name — Alex Gorbachev. These are the keywords to find us easily.
The plan is to gather at 5:30pm on Wednesday after three (or for for those of us starting on Sunday) days of intense learning. This year, you won’t need to find where to kill few hours in between of the OOW sessions and customer appreciation event at the Treasure Island — the best place to be this year is our bloggers meetup — the place where all the “cool kids” are.
As usual, thanks to Oracle Technology Network and Pythian for sponsoring the venue and drinks. HP is planning to establish a prize again this year for something fun… yes, we will again do something fun.
Last year, we were collecting signatures on our Bloggers Meetup T-Shirts so feel free to wear them this year to show your seniority at the event. ;-) This year’s activity is a surprise but if you have something cool in mind — let me know privately {last_name} at pythian.com.
For those of you who don’t know the history… The Bloggers Meetups during the Oracle Open World were started by Mark Rittman and continued by Eddie Awad and then I picked up the flag. They have been great success so let’s keep them this way! To give you an idea, here are the photos from the OOW08 Bloggers Meetup (courtesy of Eddie Awad) and last year’s meetup blog post update from myself.
If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us make sure we have the attendance numbers right. I will maintain the list here. Make sure you provide your blog URL with your comment — it’s a Bloggers Meetup in the end! Make sure you comment here if you are attending so that we have enough room, food and (most important) drinks.
Of course, do not under any circumstances forget about to blog and tweet about this year’s bloggers meetup.
Looking forward to seeing all of you again this year!
The DOAG ("Deutsche ORACLE Anwendergruppe e.V.") is the German association of users of Oracle products. In November 2010 (16th-18th), they will hold their annual Oracle Users Conference 2010, which will take place in Nuremberg, Germany.
This event is quite an institution – it has been established more than 20 years ago already. This year, there will be more than 400 sessions by over 300 speakers. For the first time, this year's conference will also have a dedicated stream of sessions about MySQL and I'm happy to announce that I'll be there, too, giving an overview of the various MySQL Replication Technologies as well as a glimpse into a MySQL DBA's toolchest.
Here's the full list of MySQL sessions, as of today:
Tuesday 2010-11-16
Wednesday 2010-11-17
Thursday 2010-11-18
I just wanted to send out a friendly reminder: you still have time until next Monday, 16th of August, 8:00 am (UK) to submit MySQL-related talk proposals for the Conference Series Technology & E-Business Suite 2010 conference which will take place in Birmingham (Nov. 29th - Dec. 1st). The UK Oracle User Group is looking forward to set up a dedicated track with great talks about MySQL! Thank you.
Welcome to Log Buffer, a weekly review of the database industry. This week’s issue Log Buffer #198 is generously published by Sam DeFilippis, who manages Oracle Notes blogs, with latest postings on Oracle GoldenGate.
As always, if you’d like to host your own issue of Log Buffer, simply reach out to the Log Buffer coordinator.
Please enjoy Sam’s issue of Log Buffer #198.
Log Buffer #197 marks the middle of summer, and the fact that we’re a mere two weeks away from our 200th edition.
To begin this week’s reading, I’d like to highlight two Oracle blogs maintained by Sam J. DeFilippis: Oracle Notes, where he’s recently posted about troubleshooting GoldenGate and positioning a read of Extract/Replicat Trail file or Oracle redo log. Sam volunteered to host a future edition of Log Buffer (thanks, Sam!). You can too, by sending an email to the Log Buffer coordinator, we’re always looking for volunteer publishers.
Brad Hudson a points that PostgreSQL test servers have moved from Oracle to EnterpriseDB.
Marc Fielding posts Part 2 of his series A Grand Tour of Oracle Exadata. He’s also hosting a webinar on how to successfully implement Exadata, Aug. 11th.
Cary Millsap explains why thinking clearly is more important than being correct.
On PL/SQL Challenge, Steven Feuerstein and his readers try to figure out where to find a list of all the tables and debate what is a table these days.
Sheeri Cabral catches up on her blogging this week with posts datawarehousing best practices, comparing Oracle and MySQL. Part 1 covers introduction and power, and Part 2 talks about partitioning. Another, explains Pythian’s method for continuously verifying replication sync, and lastly is a farewell post, as Sheeri forks off…. Pythian wishes Sheeri all the best in her future pursuits.
Iggy Fernandez tries to decide whether to use correlated subquery or join in a query.
Always wanted to learn more about Google’s BigTable database, but find the google papers intimidating?
myNoSQL blog links to a slide-deck that makes it all clear.
Ronald Bradford outlines clear evidence that in the short to medium term Oracle will continue to promote and enhance MySQL, and invites readers to chime in to his discussion on will Oracle kill MySQL?
And, on a side note, Pythian is looking for senior systems administrators who have RedHat Linux administration experience in Australia & other international locations. Visit our candidate system to match your skills with all of our current openings or submit your resume.
For those in Canada – enjoy Civic holiday weekend.
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).
Partition – Larger tables or fact tables can benefit from partitioning because it makes data load easier and can increase join performance and use data elimination. Parallel execution can be done with partitioning due to partition pruning. The degree of parallelism should be a power of 2, because of hash-based algorithm in hash partitioning. To translate this to the MySQL world, if you are using LINEAR HASH partitioning, then you should use a degree of parallelism that is a power of 2 (I checked, and indeed. Otherwise, use a degree of parallelism that makes sense given the number of partitions you have.
One important note that during Pythian’s testing of MySQL partitioning, we found that all partitions were locked when an INSERT occurs, for the duration of the INSERT. Bulk-loading with MySQL partitioning is not as fast as it would be if MySQL allowed partition pruning for INSERTs.
So, what should be partitioned? For the first level of partitioning, the goal is to enable partitioning pruning and simplify data management. The most typical partitioning is range or interval partitioning on a date column. Interval partitioning is you say what the partition is (date, month) and partition is automatically created. MySQL does not have interval partitioning, and I have seen typical first-level partitioning be range or list based on a date or timestamp column. Note that if you use a timestamp field, the partitioning expression is optimized if you use TO_DAYS(timestamp_field) or YEAR(timestamp_field). In my experience, using anything else (such as DATE(timestamp_field)) actually makes partitioning slower than not using partitioning at all. Note that this is based on tests I did a few months ago, and your mileage may vary.
So — how do you decide partitioning strategy? Ask yourself:
The answers to the above questions will tell you about how big your interval needs to be. The best scenario is that all answers are the same, “we load every day, and people query by day.” If the answers are different weight access a higher priority than loading, because most people care more about query performance than performance of ETL.
This is true even if your intervals have different sizes — ie sales per day are much bigger in Dec but that’s OK. However, Maria recommends that the subpartition be as evenly divided as possible.
Easier to look at more partitions than to look at a partition that’s too big. But you don’t want too many partitions, max Oracle allows partitions is 1 million partitions, prior to 11g it was 64,000. “Stick closer to 64,000 than 1 million”. MySQL’s limitation is 1024 per table.
For the second level of partitioning, also called subpartitioning, the goal is to allow for multi-level pruning and improve join performance. In Oracle, the most typical subpartition is hash or list – in MySQL, you can only subpartition by hash or key.
How do you decide subpartitioning strategy?
For example, if you want to look at sales per day, per store, you would choose “per day” as the partition and “per store” as the subpartition.
If you do not have a good partition on logical elements (like grouping), then you can subpartition using hash partitioning on common joins — perhaps surrogate keys, or using join key of the largest table involved in the join.
For example, if the sales table is partitioned and another big table is product, you can hash subpartition product_id.
Because there’s overhead in partitions (loading metadata, reading metadata), make sure size of partitions and subpartitions is >20 Mb. So better to have a 30 Mb subpartition than a 15 Mb subpartition. [I have no idea if this is true in MySQL or not -- I think the general concept is true, because there is some overhead, but I have no idea about the 20 Mb figure and why that's true for Oracle, nor do I know what is true in MySQL.]
One easy calculation is double the # of CPUs, round up to nearest power of 2. If you’re executing in parallel, Oracle will use 2x CPUs. (all this advice, by the way, follows 80/20 rule, this is probably good for about 80% of the environments out there). Of course, MySQL does not do parallel execution very well, so this probably does not apply.
Oracle knows it can get partition elimination while it does a join.
If 2 tables have the same degree of parallelism (same # of buckets) and are partitioned in the same way on the join column (say, customer_id in a subpartition of sales and a partition of customer), Oracle will match the partitions when joining:
sales table joined with customer table can change into 4 small joins:
sales sub part 1 joins with customer part 1
sales sub part 2 joins with customer part 2
sales sub part 3 joins with customer part 3
sales sub part 4 joins with customer part 4
And with parallelism, the total time is now reduced to the time it takes to do one of those smaller joins.
This is also why you want to have a power of 2 for buckets – because cores/processors come in powers of 2. Partition-wise joins like this can also be done with range or list, assuming both tables in the join have the same buckets.
I have no idea if MySQL partitioning works this way, but it’s certainly a functionality that makes sense to me.
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.
One interesting point brought up:
Maria quoted someone (she said the name but I did not grab it) from Forrester saying, “3NF is typically a selfless model used by Enterprise data warehouse, which is used by the whole company. A star schema is a selfish model, used by a department, because it’s already got aggregation in it.”
I thought that was an interesting way of pointing that out — most people do not understand why 3NF is not good enough for data warehousing, and I have had a hard time explaining why a star or snowflake schema should be used. Another schema-related topic I had a hard time putting into words before this workshop was the difference between a star and a snowflake schema: compared to a star schema, in a snowflake schema, you have more than one fact table and maybe some dimensions that are not used often.
From Maria and the slides:
“Oracle says model what will suit your business best. Don’t get lost in academia. Most schemas are not 100% according to the theoretical
models. Some examples: 3NF schema with denormalized attributes to avoid costly joins, Star schema with multiple hierarchies in same fact table.”
Data warehousing has a 3-step approach —
1) data sources -> staging layer (temp loading layer)
2) staging layer (temp loading layer)-> foundation (logical, data store) layer
3) foundation (logical, data store) layer -> access and performance layer
The foundation layer is usually 3NF the access layer is usually a star or snowflake schema. As for the data sources, they can be varied, you would hope that they are in 3NF (and if they are you can skip the first 2 steps) but they are not always that way.
The 3 P’s of best practice for data warehousing (on Oracle) are power, partitioning, parallelism. The goal of the data warehousing environment is to minimize the amount of data accessed and use the most efficient joins – so it is not so index focused. This may be based on Oracle’s way of doing joins, I am not so sure if it applies to MySQL as well.
Power The weakest link in the chain (the 3 steps above) will define the throughput, so make sure your hardware configuration is balanced. Maria mentioned that as DBAs, “most of the time we don’t have control over this, but we’re still bound to the SLAs.”
This includes hardware that immediately comes to mind such as # of CPUs/cores, speed of CPU, amount of RAM, speed of disk as well as what we may not think of immediately: speed of network switches, speed of disk controllers, number and speed of host BUS adapters. Notes on host BUS adapters (HBAs): Know the # of HBA ports you have. 4 Gb HBA does 400 Mb/sec. 2 Gb HBA does 200 Mb/sec. Make sure there’s enough HBA capacity to sustain the CPU throughput (ie, make sure HBA isn’t the bottleneck). Also the speed at which it all talks. If you have a 4 Gb machine but a 2 Gb switch, you end up having 2 Gb throughput. Upgrade the network at the same time you upgrade machines.
Because we are talking about data warehousing, it is often not possible to eliminate disk I/O, so the goal is to have the fastest I/O throughput possible. Data warehouses need to be sized on I/O throughput not number of I/O’s.
I made a post earlier about how to determine I/O throughput for a system, which used information from this session. Justin Swanhart already pointed out that this is based on the fact that Oracle can do hash joins and MySQL can only do nested loop joins. I wonder, though, if there is indeed no case when using MySQL for which I/O throughput is a more useful metric than iops.
Disk arrays that are expensive are usually sized for iops, not throughput, and because they’re expensive the disk array is shared throughout the company. A DBA needs to ask ‘how many connections into the storage array do I have? How many disk controllers do I have? Where are my physical disks, and which controllers are they hanging off of?’
Typical 15k rpm disk can do about 25-35 Mb/sec (per disk) random i/o’s. Disk manufacturers will throw out numbers like 200-300 Mb/sec but that’s sequential I/O and leading edge of the drive. Make sure all your LUNs are not coming off the same set of disks, so that you’re not conflicting on disk seeks.
Continue to part 2, partitioning.