The latest from the MySQL community

It’s been ages since we’ve had an update to innotop, or at least, it’s been a while since the changes were bundled up and released officially. Barring any bug reports against the trunk code, I’m about ready to release more than a year’s worth of improvements to it:

   Changes:
   * Don't re-fetch SHOW VARIABLES every iteration; it's too slow on many hosts.
   * Add a filter to remove EVENT threads in SHOW PROCESSLIST (issue 32).
   * Add a timestamp to output in -n mode, when -t is specified (issue 37).
   * Add a new U mode, for Percona/MariaDB USER_STATISTICS enhancements (issue 39).
   * Add support for millisecond query time in Percona Server (issue 39).
   * Display a summary of queries executed in Query List mode (issue 26).

   Bugs fixed:
   * Hostname parsing wasn't standards compliant (issue …

I see this all the time:

[product] scales linearly to hundreds of servers

I haven’t seen a benchmark yet that’s truly a straight line. I would like to see one.

Related posts:

1. How to scale writes with master-master replication in MySQL
2. Archive strategies for OLTP servers, Part 1
3. Archive strategies for OLTP servers, Part 3
4. Archive strategies for OLTP servers, Part 2
…

I don’t think this was widely publicized, but the Call for Proposals for the MySQL (and beyond) conference was extended a few days. It’ll be open through November 3rd.

Related posts:

1. O’Reilly MySQL Conference CfP ends today
2. Postgres folks, consider the 2011 MySQL conference
3. The MySQL Conference will be very good this year
4. How to use extended …

Josh Owens of Webpulp.tv just posted an interview with me. Lots of good questions about MySQL, performance optimization, and Percona’s version of the server. Thanks Josh!

Related posts:

1. You have the right to see code samples in an interview
2. Baron Schwartz on a podcast at MySQL Conference and Expo 2008
3. Grab your High Performance MySQL sample content
4. …

Pagination is used very frequently in many websites, be it search results or most popular posts they are seen everywhere. But the way how it is typically implemented is naive and prone to performance degradation. In this article I attempt on explaining the performance implications of poorly designed pagination implementation. I have also analyzed how Google, Yahoo and Facebook handle pagination implementation. Then finally i present my suggestion which will greatly improve the performance related to pagination.

You have about 14 more hours to submit session proposals to the O’Reilly MySQL conference, which is soliciting sessions about all open-source databases: PostgreSQL, CouchDB, Riak, Hadoop, Firebird, you name it. The last-minute proposals are coming fast and furious, as usual, and we have a great selection to choose from, but we need more!

Related posts:

1. Postgres folks, consider the 2011 MySQL conference
2. Submit your proposals for MySQL conference 2010
3. …

TIMESTAMP and DATETIME is how dates and times, or temporal, data is represented ion MySQL. These types are combined with a bunch of operators on these types, such as ADDATE and TIME_FORMAT and such. So is this enough to effectively manage temporal data then? Nope, in my mind, far from it.

To begin with, time isn't such a simple type of data as we might look at it. Is a date just a count of the number of seconds since 00:00:00 on Jan 1 1970 or something like that? And we then convert it to a calendar and all is fine? Is time data really linear in that sense? No, it's not. Dec 24 2010 is Christmas Eve and when you get your Christmas gifts here in Sweden. Is this significant? Also, it's a Friday. That this data is a Friday can be calculated from the date or the seconds-since-jan-1-1970 counter, but that is a different thing. Looking at that counter, you cannot see that there is no way to look at that number and figure out that this day is …

I keep a file of notes, one per line, for reference. The real reason is for source material if I ever write a 3rd edition of High Performance MySQL, but hardly a day goes by that I don’t look something up. At the very top, I have a section that I want to share with you. It’s a list of URLs that are invaluable sources of information and insight.

• http://blogs.sun.com/realneel/
• http://developer.cybozu.co.jp/kazuho/
• http://www.chriscalender.com/
• http://www.facebook.com/notes.php?id=102841356695
• http://mysqlha.blogspot.com/
…

New Community version of GreenSQL Database Firewall, version 1.3 is now available.
GreenSQL 1.3 includes new features, many bug fixes and enhancements.

In this version, GreenSQL improvers the native support for PostgreSQL (http://www.postgresql.org) databases, improvers the native support for MySQL (http://www.mysql.com) databases and provides many Protocol and Network Optimizations. The Web Based GUI usability has been improved and many bugs been fixed.

GreenSQL community version 1.3.0 improvements and enhancements include:

1. Proxies dashboard: correctly displaying the proxy current status
2. Proxies automatic reloading fixes
3. Alerts include User IP Address
4. MySQL and PostgreSQL protocol fixes
5. Network optimizations
6. Alerts – redesign and graphics
7. …

I wish I could go to PGWest this year. A lot of great work has been done on PostgreSQL in the last year and a half. There’s a new release with built-in replication, and there are in-place upgrades. That solves two of what I think are its three biggest shortcomings in many large-scale database deployments. (Lack of index-only queries is the third shortcoming.)

PGWest is the first major conference about Postgres since the 9.0 release, so all the cool stuff is happening there. I encourage MySQL users to go to it as well — there is a lot to learn from PostgreSQL. Just remember to be polite and don’t start, encourage, or tolerate any sniping between MySQL and Postgres fans.

Related posts:

1. Postgres folks, consider …