Feed aggregator

I explored two interesting topics today while learning more about Postgres.

PostgreSQL’s partial page write protection is configured by the following setting, which defaults to “on”:

full_page_writes (boolean)

When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint… Storing the full page image guarantees that the page can be correctly restored, but at a price in increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)

Trying to reduce the cost of full-page writes by increasing the checkpoint interval highlights a compromise. If you decrease the interval, then you’ll be writing full pages to the WAL quite often. This should in theory lead to surges in the number of bytes written to the WAL, immediately following each checkpoint. As pages are revisited over time for further changes, the number of bytes written should taper off gradually until the next checkpoint. Hopefully someone who knows more can confirm this. Does anyone graph the number of bytes written to their WAL? That would be a nice illustration to see how dramatic this surging is.

Decreasing the checkpoint interval seems a bit scary, and is bound to have its own costs, for all the usual reasons. A massive checkpoint once in a while should be really expensive, and would lead to a bad worst-case time for recovery. Does the new bgwriter implementation in 8.3 fix any of this? In theory it could, but I don’t know enough yet to say. I have heard conflicting opinions on this point. I have a lot more to read about it before I form my own opinion.

Storing full pages might not really be that expensive. It could bloat the WAL, but is the cost (in terms of time) really that high? InnoDB (in MySQL) protects against partial page writes with a double-write strategy: a region in the tablespace is called the doublewrite buffer. Page writes are first sent to the doublewrite buffer, then to their actual location in the data file. I don’t remember where, but I’ve seen benchmarks showing that this doesn’t hurt performance, even though it seems counter-intuitive. Modern disks can do a lot of sequential writes, and the way InnoDB writes its data makes a lot of things sequential. I doubt that putting full pages into the PostgreSQL WAL is forced to cost a lot, unless there is an implementation-specific aspect that makes it expensive.

The TODO has some items on the WAL, which look interesting — “Eliminate need to write full pages to WAL before page modification” and a couple more items. I need to understand PostgreSQL’s recovery process better before I know what these really mean.

I was able to verify that the WAL entries have a checksum. It is a CRC32. This is in xlog.c.

However, as far as I can understand, the answer for detecting data corruption in normal data pages is “Postgres doesn’t do that.” I was told on the IRC channel that normal data pages don’t have checksums. I am not sure how to verify that, but if it’s true it seems like a weakness. I’ve seen hardware-induced corruption on InnoDB data many times, and it could sometimes only be detected by page checksums.

What happens when a page is corrupt? It probably depends on where the corruption is. If a few bytes of the user’s data is changed, then I assume you could just get different data out of the database than you inserted into it. But if non-user data is corrupted then do you get bizarre behavior, or do you get a crash or error? I need to learn more about PostgreSQL’s data file layout to understand this. Imagining (I haven’t verified this) that a page has a pointer to the next page, what happens if that pointer is flipped to refer to some other page, say, a page from a different table? If TABLE1 and TABLE2 have identical structures but different data, could SELECT * FROM TABLE1 suddenly start showing rows from TABLE2 partway through the results? Again I need to learn more about this.

Related posts:

1. The Ma.gnolia data might not be permanently lost I keep rea
2. What data types does your innovative storage engine NOT support? I’ve
3. PostgreSQL Conference East 2009, Day Three As I said

Related posts brought to you by Yet Another Related Posts Plugin.

We have been using tpcc-mysql benchmark for long time, and there many results published in our blog, but that's just single workload. That's why we are looking into different benchmarks, and one
of them is TPCE. Yasufumi made some efforts to make TPCE working with MySQL, and we are making it available for public consideration.

You can download it from our Lauchpad Percona-tools project, it's
bzr branch lp:~percona-dev/perconatools/tpcemysql

Important DISCLAIMER:
Using this package you should agree with TPC-E License Agreement,
which in human words is:

• You can't name results as "TPC Benchmark Results"
• You can't compare results with results published on http://www.tpc.org/ and you can't pretend the results are compatible with published by TPC.

And we are not going to do anything from that, your primary goals is XtraDB/InnoDB performance research and/or compare with available Storage Engines for MySQL.

The workload in tpce is quite different from tpcc. Tpcc is write intensive, while tpce
is read oriented.
To give more details, there is stats for 10 seconds:

1. | Com_select                            | 46272       |
2. | Com_update                            | 5214        |
3. | Com_delete                            | 385         |
4. | Com_insert                            | 3468        |
5. | Com_commit                            | 5404        |

The result is quite chatty,

1. |    |     [MEE]    | [DM] |                         [CE]                          |
2. sec. |    TR,    MF |   DM |   BV,    CP,    MW,    SD,    TL,    TO,    TS,    TU | MEEThreads, ReqQueue
3.       (1st line: count, 2nd line: 90%ile response [msec.])
4.  260 |   402,    39,     0,   195,   532,   749,   588,   342,   415,   816,    88 | 30, 0
5.           20,    60,     0,    30,    20,    20,    20,    50,    20,   310,    60
6.  
7.  270 |   395,    40,     0,   201,   608,   842,   608,   358,   449,   833,    89 | 30, 0
8.           30,    40,     0,    30,    20,    20,    20,    50,    20,   300,    50

but it allows you to see count of 11 different transactions per 10 secs and 90% response time.

and final result

1. [TradeResult(TR) transaction]
2. Succeed: 150243
3. Lated:   0
4. Retried: 3
5. Failed:  0
6.  
7. 41.7342 TpsE

where you can see count of successful TR (TradeResult) transactions, and
the summary result in TpsE (transactions per seconds).

Expect our results soon!

Entry posted by Vadim | No comment

Add to: | | | |

Matt Asay writes today in Oracle loses some MySQL mojo about Ken Jacobs leaving Oracle. For me, that’s a major bummer. Ken has been a long-time visitor of the MySQL Conference and that’s where I first met him: a friendly and knowledgeable person, on database technology in general but also about MySQL. When Innobase Oy got bought by Oracle, InnoDB got placed under Ken’s leadership and did pretty well there. We’d occasionally exchange emails, and I’ve always found him to be responsive and helpful.

I think it was kinda presumed by people that the technical part of MySQL at Oracle would also reside with Ken. Obviously now, that’s not going to be the case. What that means exactly, I don’t know as I am not familiar with the other person (Edward Screven). We’ve got to know Ken over the years, so it would’ve been nice to keep going with him. Ohwell.

Now we’ll see what Edward does with it all, and how he will interact with the MySQL community. And I wonder what new adventures Ken might be off to, if any?

Many times I’ve heard people advise on “best practices” for a MySQL database. This often includes routine maintenance, such as “you should run OPTIMIZE TABLE on all of your InnoDB tables once a week to defragment them for better performance.”

But this advice is unsubstantiated and could even be detrimental. Here are some of the obvious problems that I can think of:

• The optimized table compacts the primary key (clustered index) to its default 15/16ths fill factor per page. But other indexes will be built in pseudo-random order and are likely to end up just as fragmented afterwards as before. Which indexes are more important for performance? Maybe the primary key is just a dummy value that’s not even used, and the secondary indexes are the ones that would benefit from compacting.
• Suppose the primary key is the important one, and SELECT queries will perform more quickly if it’s defragmented. Why does it get fragmented? Because of changes to the table. Now these changes could suddenly slow down dramatically as they are forced to split pages at a much higher rate due to the more compact data layout.

Why do people make a blanket “you should defragment” statement without supporting it with hard facts? It sounds like something you’d hear from a naive Windows user who buys a $99 piece of software to make his PC “boot faster” or “fix his registry” or something. Maybe it ain’t broke and should not be fixed.

I believe we hear advice like this because there isn’t easy-to-get data that can tell us the truth. To make decisions about defragmenting tables responsibly, we need either performance data on that table (hard to get in most cases), or failing that, information about cost and frequency of page splits in general (not available from InnoDB at present). It would help to have these metrics, and I think it might not be very hard to add page-split instrumentation to InnoDB.

Related posts:

1. Analyze and optimize memcached usage with Maatkit Ryan poste
2. Extended covering indexes As you can
3. The difference between a unique index and primary key in MySQL There

Related posts brought to you by Yet Another Related Posts Plugin.

The next presentation by Piotr Biel from Percona was on Multi-Master Manager for MySQL.

The introduction included a discussion of the popular MySQL HA solutions including:

• MySQL Master-slave replication with failover
• MMM managed bi-directional replication
• Heartbeat/SAN
• Heartbeat/DRBD
• NDB Cluster

A key problem that was clarified in the talk is the discussion of Multi-Master and this IS NOT master-master. You only write to a single node. With MySQL is this critical because MySQL replication does not manage collision detection.

The MMM Cluster Elements are:

• monitoring node
• database nodes

And the Application Components are:

• mon
• agent
• angel

MMM works with 3 layers.

• Network Layer – uses a virtual IP address, related to servers, not a physical machine
• Database Layer
• Application Layer

MMM uses two roles for management with your application.

• exclusive – also known as the writer
• balanced – also known as the reader

There are 3 different statuses are used to indicate node state

• proper operation
• maintenance
• fatal errors

The mmm_control is the tool used to manage the cluster including:

• move roles
• enable/disable individual nodes
• view cluster status
• configure failover

The Implementation challenges require the use of the following MySQL settings to minimize problems.

• auto_increment_offset/auto_increment_increment
• log_slave_updates
• read_only

FOSDEM 2010 MySQL Developer Room Schedule
FOSDEM 2010 Website
Brussels, Belgium
February 7, 2010

The slides for my presentation at FOSDEM 2010 are now available online at slideshare. In this presentation I describe a successful client implementation with the result of 10x performance improvements. My presentation covers monitoring, reviewing and analyzing SQL, the art of indexes, improving SQL, storage engines and caching.

The end result was a page load improvement from 700+ms load time to a a consistent 60ms.

Following the opening keynote “Dolphins, now and beyond”, Marc Delisle presented on “State of phpMyAdmin”.

phpMyAdmin is an DBA administration tool for MySQL available today in 57 different languages. This is found today in many distributions, LAMP stack products and also in cpanel. The product is found at http://phpmyadmin.net.

There are current two versions, the legacy 2.x version to support older php 3.x & 4.x, The current version 3.x is for PHP 5.2 or greater.

The current UI includes some new features including.

• calendar input for date fields
• meta data for mime types e.g images, which is great for showing the output as an image, otherwise blob data
• Relational designer with the able to show and create foreign keys

The New features in 3.3 (currently in beta) include:

• Replication support including configuring master/slave, start/stop slave.
• Synchronization model showing structure and data differences between two servers and ability to sync.
• New export to php array, xslx, mediawiki, new importing features including progress bar.
• Changes tracking for changes on per instance or per table. Providing change report and export options.

FOSDEM 2010 MySQL Developer Room Schedule
FOSDEM 2010 Website
Brussels, Belgium
February 7, 2010

I had the honor of opening the day at the MySQL developer room at FOSDEM 2010 where I had a chance to talk about the MySQL product and community, now and what’s happening moving forward.

For those that missed the talk, my slides are available online at Slideshare however slides never due justice to some of the jokes including:

• What do you consider? the Blue Pill, or the Red Pill
• Why think two dimensionally, how about the Green Pill
• Emerging Breeds with performance enhancing modifications

InfiniDB has a sensible Enterprise/Community release process, which seems similar to what I suggested for MySQL. Its simplicity also stands in stark contrast to MySQL’s new release policy, which is hard to understand and has been confusing people.

Related posts:

1. Thank you for the MySQL 5.4 Community Release MySQL 5.4
2. How to find per-process I/O statistics on Linux Newer Linu
3. MySQL Enterprise/Community split could be renewed under Oracle One of MyS

Related posts brought to you by Yet Another Related Posts Plugin.

DVD anti-features are rather well documented. The purpose of “region coding” was to make sure that everybody who ever visited a foreign country and picked up some DVDs while there would get home to find out that they wouldn’t work.

Luckily, those of us who pay good money for DVDs have free software solutions to let us used our payed for product and not force us to download “pirated” copies just so we can view what we payed for.

The region coding in DVDs was designed with the idea that DVD players would always be expensive. You could “change” which region your DVD player was in a set number of times before you could no longer change it.

DVD players can now be bought for $30 (or less). This is what you could pay for a DVD movie. So with economies of scale driving prices down, even if CSS wasn’t completely broken, you can brute force the region coding by just buying 6 DVD players ($180) – less than many of us payed for our first, second or third DVD player.

The same thing will happen with BluRay. You can now get BluRay players for a couple of hundred dollars. One for each of the regions (A, B and C) will cost you less than original BluRay players cost.

So the antifeature of limiting who can watch a DVD/BluRay release is easily broken as player costs come down.

I was really excited when I had my Gearman talk accepted to Linux Conf AU 2010 because I had never been out that far in the Pacific (only Hawaii). Of course it wasn’t in Australia this year, and instead in Wellington, New Zealand. My wife came too, and we also made a vacation out of the down times we had around the conference. It turned out Brian couldn’t make it this year so Monty, Stewart, and I gave the Drizzle talk. It was great to see some familiar faces, including Mark Atwood, Giuseppe Maxia, Josh Berkus, and Selena Deckelmann. Josh actually ended up being on the same flight out, so we got to catch up while going through New Zealand customs at 5am after a 13 hour flight. :)

New Zealand is an amazing place. We flew in and out of Auckland and took the train to Wellington. The train ride mostly consisted of grazing sheep once out of the metro areas, did you know there are more sheep than people in NZ? Beyond the sheep, there were great views along the way, especially in the middle near the larger mountains and volcanoes. We stopped for a day to hike the Tongariro Alpine Crossing. It was sunny when we started, but it it was raining with 40mph winds at the top, so we didn’t get to see as much as we hoped. There were still beautiful views on each side though.

The conference was very well run, thanks to anyone who had a hand in it! The speakers dinner was at this great museum nearby on the waterfront and included live Maori singing and dancing. The vegan options were tasty, and I got to meet a few interesting folks there (like the folks from Dreamwidth, a LiveJournal-like blogging service). Some notable sessions during the conference were “The World’s Worst Inventions” by Paul Fenwick, “Anti-features” Keynote by Benjamin Mako Hill, “The Hydras GCC Static Analysis Plugins” by Taras Glek, and “Simplicity Through Optimization” by Paul McKenney. There were many other great sessions, and some I wish I could have attended.

I’m certainly going to try to go again next year, which if you didn’t hear will be in Brisbane!

Mako has often talked very well about anti-features. The “features” in software that nobody wants and often cost money to do the easier task of not including the feature. Examples include the non-skip parts of DVDs and BluRay Discs (see here for more).

I’d like to coin a new term… anti-anti-features. These are antifeatures (i.e. a feature you didn’t want in the first place) doesn’t actually function properly itself.

The other day, I sat down with a friend to watch a movie. We had hired out a BluRay of a recently released movie, popped it in the player and attempted to hit “Pause”. Why pause? Well… movies often can auto-play and we wanted to fetch a beer, snack and otherwise prepare for the great movie watching experience.

It turns out you cannot pause the copyright notice. So if you’re trying to be good and understand your obligations under the license in which you have received this disc, you cannot actually finish reading them!

Try it – put in a DVD or BluRay and try to read the copyright notice. I bet you that for a large number of discs you cannot do so in the time allowed.

This just goes to show how utterly useless these “no skip” zones are. You will see hundreds of exactly the same notice (one for each disc you view) many, many times (each time you view it) – one would think that after the first, second, third or even 10th time you’d understand it.

Amazingly, under DVD playback software that lets you skip the “no skip” zones (e.g. every DVD player on Linux) it also allows you to pause on the copyright notice and read it.

No, You Can’t Do That With H.264 is an excellent write up of how H.264 (“MPEG-4″) is fraught with problems that you just would not have if using Free (as in Freedom) formats such as Ogg Vorbis and Theora.

It is amazing that Final Cut “Pro” cannot actually be used to create H.264 content for commercial (i.e. “Pro”) use!

It’s the same for MPEG-2.

Oh, and if you use it to decode video that was encoded by somebody without the proper license… well, then you’re also screwed. How the heck you’re meant to work that one out I have no idea.

Bernhard blogged over at http://ocklin.blogspot.com/2010/02/mysql-cluster-711-is-there.html that MySQL Cluster 7.1.1 Beta has been released. The big feature (from my point of view) is the SQL interface on top of NDB$INFO. This means there is now full infrastructure from the NDB data nodes right out to SQL in the MySQL Server for adding monitoring to any bit of the internals of the data nodes.

At DrupalSouth 2010 (Wellington) after LCA2010, Peter and I implemented a Drupal module as a practical example of how the OQGRAPH engine can be used to enable social networking trickery in any website. The friendlist_graph module (available from GitHub) extends friendlist, which implements basic functionality of friends (2-way) and fans (1-way) for Drupal users.

The friendlist_graph module transposes the friendlist data using an OQGRAPH table, allowing you to query it in new and interesting ways. By adding some extra Drupal Views, it allows you to play Six Degrees of Kevin Bacon with your Drupal users or find out how two arbitrary users are connected. It can find a path of arbitrary length near-instantly. Previously, you’d just avoid doing any such thing as it’s somewhere between impossible/limited/slow/painful in a regular relational schema.

Now think beyond: retrieve/share connections using Open Social, FOAF, Twitter/Identi.ca, logins with OpenID, and you “instantly” get a very functional social networking enabled site that does not rely on localised critical mass!

We tested with about a million users in Drupal (and approx 3.5 million random connections), which worked fine – the later demo at the DrupalSouth stuffed up because I hadn’t given the demo VM sufficient memory.

Naturally, you could do the same in Joomla! or another CMS or any site for that matter, we just happened to be at DrupalSouth so a Drupal module was the obvious choice. Take a peek at the code, it’s pretty trivial. Just make sure you run a version of MySQL that has the OQGRAPH engine, for instance 5.0.87-d10 (Sail edition!) from OurDelta.

I've seen my posts on Ontime Air traffic and Star Schema Benchmark got a lot of interest
(links:

• http://www.mysqlperformanceblog.com/2010/01/07/star-schema-bechmark-infobright-infinidb-and-luciddb/
• http://www.mysqlperformanceblog.com/2009/10/02/analyzing-air-traffic-performance-with-infobright-and-monetdb/
• http://www.mysqlperformanceblog.com/2009/10/26/air-traffic-queries-in-luciddb/
• http://www.mysqlperformanceblog.com/2009/11/02/air-traffic-queries-in-infinidb-early-alpha/

).
However benchmarks by itself did not cover all cases I would want, so I was thinking about better scenario. The biggest problem is to get real big enough dataset, and I thank to Bradley C. Kuszmaul, he pointed me on Wikipedia statistics on access to Wikipedia pages, and thank to Domas, who made stats accessible. Link to the archives: http://dammit.lt/wikistats/archive/ or the original Domas's announcement .

Although the table does not have very much different information, I think it is good enough to represent cases you can face in Web application ( log processing, page visits, clickstream, etc).

I made some efforts to normalize data to have model in classic star schema and prepared queries that could be run on proposed dataset (John Sichi, lead of LucidDB helped me to draft some queries).
You can see details on our Percona Wikistat benchmark Wiki.

I have next goals with proposed benchmark:

• Compare engines in OLAP queries for planning, predicting growth, analyzing access patterns to wiki pages, draw trends.
• Compare engines in statistical queries for end users, which can be executed in real-time. I.e. How many times that or another page was accessed yesterday vs today.
• Understand specific features and characteristic of each engine.
• Compare throughput on simple queries (queries and scenario to be drafted yet)
• Check ability to load data and serve queries at the same time ( availability during data load ) (queries and scenario to be drafted yet)

So in proposed schema I have four tables:
pagestat (fact table), and pages, datesinfo, projects (dimensions tables).

Dimensions tables are supposed to be static and not changed, and we can change datasize
by varying amount of months loaded into fact table (so this is scale factor).

EER diagram

( made with MySQL Workbench )

In current dataset, which you can download from Amazon snapshot (name: “percona-wikistat”, ID:snap-a5f9bacc) we have:

• Table pages: 724.550.811 rows. data size: 40476M
• Table datesinfo: 9624 rows, one entry represents 1 hour
• Table projects: 2025 rows
• Table pagestats
  Data for 2009-06: # 3.453.013.109 rows / size 68352M
  Data for 2009-07: # 3.442.375.618 rows / size 68152M

So with two months of stats we have about 172GB of data with about 7 billion rows in fact table.

Example of query ( again, full list on Benchmark Wiki)

1. SELECT project, sum(page_count) sm
2.  FROM pagestat
3.    JOIN datesinfo di ON ( di.id=date_id )
4.    JOIN projects p ON  (p.id=project_id )
5.  WHERE di.calmonth=7 AND di.calyear=2009
6.  GROUP BY project
7.  ORDER BY sm DESC
8.  LIMIT 20;

I am going to load data and run queries against available engines:

• MySQL MyISAM / InnoDB (to have reference results)
• InfoBright
• InfiniDB
• MonetDB
• LucidDB
• Greenplum

and I will report my results ( so stay with MySQLPerformanceBlog )

I'd like also to test also Paraccel, Vertica and KickFire systems, but I do not have access to.

I welcome your feedback on the benchmark, and what else you would like to see here.

Entry posted by Vadim | 15 comments

Add to: | | | |

Dear MySQL Users,

Its only been a week since last release, and already the MySQL Workbench Team has a 5.2 beta 5 ready. This Workbench Beta 5 build includes fixes for 26 bugs – 5 P1, 8 P2, 9 P3 and 4 P4.

We want to thank all the people who have been testing MySQL Workbench 5.2 alphas and betas and taking the time to file bugs and provide valuable feedback. We have fixed many bugs since last release and worked on some other details as well.
MySQL Workbench 5.2 Beta 5 provides:

1. Data Modeling
2. Query (upgrade from MySQL Query Browser)
3. Admin (upgrade from MySQL Administrator)

If you are a current user of MySQL Query Browser or MySQL Administrator, we look forward to your feedback on all the new capabilities we are delivering in a single unified MySQL Workbench

As always, you will find binaries for the various platforms on our download pages.

Please get your copy from our Download

http://dev.mysql.com/downloads/workbench/

To get started quickly, please take a look at this short tutorial.

MySQL Workbench 5.2 Beta Tutorial

http://wb.mysql.com/?p=406

Please be aware that this release is still a beta version – so please don’t use it on your production servers! Also note, MySQL Workbench files saved with version 5.2 cannot be opened with previous versions of our program.

The files for several platforms have been pushed to our main server and should be available on our mirrors.

Blog postings and general information – including build instructions for Linux – can be found on our Workbench Developer Central site.

Workbench Developer Central

http://wb.mysql.com

Workbench Documentation and details on changes between releases can be found on these pages

http://dev.mysql.com/doc/workbench/en/index.html

http://dev.mysql.com/doc/workbench/en/wb-change-history.html

If you need any additional info or help please get in touch with us.
Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.

Again, thank you for trying out the Workbench beta, we look forward to your feedback and bug reports.

- The MySQL Workbench Team

Cloudkick, a start-up which has been offering free multi-cloud management and monitoring services in preview (and which is the originator and sponsor of the open-source libcloud project), has launched its commercial offering.

Quite a bit has been written about Cloudkick in other venues, so I’ll offer a musing of a different sort: I am contemplating the degree to which cloud-agnostic monitoring service providers will evolve into general monitoring SaaS vendors. A tiny fraction of cloud IaaS users will actually be significantly multi-cloud, whereas a far vaster addressable market exists for really excellent monitoring tools, including cost-effective ways of doing third-party monitoring for the purposes of cloud SLA enforcement. Even though enterprises are likely to extend their own internal monitoring systems into their cloud environments, there will continue to be a need for third-party monitoring, and for many organizations, third-party monitoring that can also feed alerts into internal monitoring systems will be a popular choice.

Cloudkick has been interesting in the context of the debate over alleged capacity issues on Amazon EC2. Their monitoring has been showing latency issues growing in severity since Christmas or so. The public nature of this data, among other things, has pushed Amazon into making a statement that they don’t have overcapacity problems; it’s an interesting example of how making such data openly available can bring pressure to bear on service providers.

Yes, Maatkit’s name was inspired by Ma’at, which has an apostrophe. But there is NO apostrophe in the name of the popular toolkit for MySQL users.

I’m just sayin’.

Related posts:

1. Learn about Maatkit at the MySQL Conference I’m
2. Speaking about Maatkit at CPOSC I’m
3. Making Maatkit more Open Source one step at a time If you

Related posts brought to you by Yet Another Related Posts Plugin.