beer planet

• Robert Hodges from Continuent presents
• About Continuent

• leading provider of open source database availability and scaling solutions

• uni/cluster – multi-master database clustering that replicates data across multiple databases and load balances reads
• uses "database virtualization"

• protection from db and site failures
• continuous operation during upgrades

• Brewer's conjecture

• DDL support
• inconsistent reads between replicas
• deadlocks
• sequences
• non-deterministic SQL

• data replication

• where are updates processed? master/master vs master/slave
• when are updates replicated? sync vs async

• views – who is active, who is crashed, do

…

Read the rest of this article »

• Suicide

• having no backups
• depending on slaves for backup
• keeping backups on same SAN
• having a single DBA – Frank didn't like this one at all
• not keeping binlogs

• how much time?
• uncompressed backup ready to mount?
• separate network for recovery?

• first problem: backup was highly compressed (tar.gz)
• uncompressing took hours
• so keep uncompressed backups (at least last N days)
• it should be mountable, rather than transferable

• row by row recovery (get the range of ids)
• custom scripts
• may not be able to use primary key
• foreign key based retrieval faster
• lose 4 seconds for each crashed record

…

Read the rest of this article »

Updated: April 17th, 2008

I had a chance to visit the Kickfire booth after the keynotes and before the first presentation. They gave me a kicking t-shirt, followed by a presentation on the newly announced Kickfire appliance (now in beta, shipping in Fall 2008). Here are some notes I jotted down:

• von Neumann bottleneck
• SQL chip (SQC), packs the power of 10s of conventional CPUs
• Query parallelization on the chip
• On-chip memory – 64GB. No registers – no von Neumann bottleneck
• Beats the performance of a given 3 server, 32 CPU, 130TB box (1TB of actual data – space is used for distributing IO)
• SQC uses column-store, compression, intelligent indexing
• SQL Chip, PCI connection, plugs into a Linux server
  • SQL execution
  • Memory management
  • Loader

…

Read the rest of this article »

• Baron Schwartz presents
• only works for SELECTs
• nobody dares admit if they've never seen EXPLAIN
• MySQL actually executes the query
• at each JOIN, instead of executing the query, it fills the EXPLAIN result set
• everything is a JOIN (even SELECT 1)
• Columns in EXPLAIN

• id: which SELECT the row belongs to
• select_type

• simple
• subquery
• derived
• union
• union result

• join
• range
• …

• Robin Schumacher
• gives overview of MySQL products
• MySQL Enterprise
• MySQL 5.1 announced
  • table/index partitioning -> great for data warehouses, range, cache, key, list, composite, subpartitioning. Partition pruning. Response time greatly improved with proper partitioning.
  • row-based/hybrid replication -> safer and smarter
  • disk-based cluster -> supports bigger DBs
  • built-in job scheduler -> simplified task management
  • problem SQL identification -> easier troubleshooting. Dynamic query tracing is now available, no need to trace things in slow query logs.
  • faster full-text search -> 500% increase in some cases
  • 5.1.24RC available for the conference
• MySQL 6.0
  • Falcon engine – transactional engine
  • new backup (version 1.0) -> cross engine, non-blocking, to replace mysqldump
• Falcon
  • planned default transactional storage engine. Q4 GA (general availability).
  • not InnoDB replacement
  • most

…

Read the rest of this article »

• Speaker: Mikael Ronstrom, PhD, the creator of the Cluster engine
• Explains the cluster structure
• Aspects of performance
  • Response times
  • Throughput
  • Low variation of response times
• Improving performance
  • use low level API (NDB API), expensive, hard
  • use new features in MySQL Cluster Carrier Grade Edition 6.3 (currently 6.3.13), more on this later
  • proper partitioning of tables, minimize communication
  • use of hardware
• NDB API is a C++ record access API
  • supports sending parallel record operations within the same transaction or in different transactions
  • asynchronous and synchronous
  • NDB kernel is programmed entirely asynchronously
• Looking at performance
  • Fire synchronous insert transactions – 10x TCP/IP time cost
  • Five inserts in one synchronous transaction – 2x TCP/IP time cost
  • Five asynchronous insert transactions – 2x TCP/IP

…

Read the rest of this article »

Were there too many "my"'s in that title? Anyway… this week's MySQL conference is promising to be really busy and exciting. I can't wait to finally be there and experience it in all its glory. Thanks to the O'Reilly personal conference planner and scheduler and the advice of my fellow conference goers, I was able to easily (not really) pick out the speeches I am most interested in attending.

Here goes (my pass doesn't include Monday ):

Tuesday

8:30am Tuesday, 04/15/2008

State of MySQL

Keynote Ballroom E

Mårten Mickos (MySQL)

In his annual State of MySQL keynote, Marten discusses the current and future role of MySQL in the modern online world. The presentation also covers the acquisition …

Updated: April 23rd, 2008

[WORK IN PROGRESS] Here is a list of commands that I use every day with vim, in no particular order. Out of a billion possible key combinations, I found these to be irreplaceable and simple enough to remember.

…

Updated: February 24th, 2009

Why do I dread doing taxes every year? One of the main reason was having to figure out ways to import hundreds of transactions from my stock brokers: Scottrade and InteractiveBrokers. I love IB but come on, it's 2008 and they still don't provide .txf files to import into TurboTax (or TaxCut, but I use TurboTax myself). Scottrade, on the other hand, is directly importable through TurboTax but it only imports sale amounts and not purchases, so TurboTax thinks I got my stocks for free and wants to tax me on the full sale amount rather than the profits. That is, of course, very incorrect. Here are the best solutions I could find to these problems:

1. Scottrade….

Read the rest of this article »

Updated: July 8th, 2009

Today I was asked a question about defining custom extensions for vim syntax highlighting such that, for example, vim would know that example.lmx is actually of type xml and apply xml syntax highlighting to it. I know vim already automatically does it not just based on extension but by looking for certain strings inside the text, like <?xml but what if my file doesn't have such strings?

After digging around I found the solution. Add the following to ~/.vimrc (the vim configuration file):

1
2
3

syntax on
filetype on
au BufNewFile,BufRead *.lmx set filetype=xml

After applying it, my .lmx file is highlighted:

Same principle works, for instance, for mysql dumps that I have to do from time to time. …

Updated: June 16th, 2008

1

svn sw http://svn.automattic.com/wordpress/tags/2.5

and Beer Planet is on 2.5. There were no problems with the upgrade itself or any of the plugins. Great work on the new clean interface and multi-file upload, WordPress!

Info here: http://codex.wordpress.org/Version_2.5 (short) and http://wordpress.org/development/2008/03/wordpress-25-brecker/ (more detailed)…

Updated: May 23rd, 2009

In this tutorial, I'll show you how to parse JSON using Perl. As a fun example, I'll use the new SouthParkStudios.com site released earlier this week, which contains full legal episodes of South Park. I guess the TV companies are finally getting a clue about what users want.

I will parse the first season's JSON and pull out information about individual episodes (like title, description, air date, etc) from http://www.southparkstudios.com/includes/utils/proxy_feed.php?html=season_json.jhtml%3fseason=1. Feel free to replace '1' with any valid season number.

Here's a short snippet of the JSON:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

As half of the world population already knows, the MySQL conference is coming in less than 3 weeks. Since this event only happens once a year, lasts only 4 days, and costs more than a Russian mail-order bride, I'd really like to get the most out of it. Considering that the schedule is completely packed, with 8 (!!) events going on in parallel, I imagine things can get a little frantic. Additionally, I've never been to a conference of such size before and I'm not sure what to expect.

So… I'm contemplating:

• printing out the event schedule and drawing a zig-zagging "map" of exactly where I'll be jumping to next, once the previous presentation ends. I'm actually wondering

…

Read the rest of this article »

Updated: January 4th, 2009

Here are some quick links for now: http://blogs.mysql.com/kaj/2008/05/23/mysql-clusters-improved-release-model/, http://johanandersson.blogspot.com/2008/05/mysql-cluster-62-officially-released.html, http://blogs.sun.com/theaquarium/entry/improved_release_model_for_mysql.

This article contains my notes and detailed instructions on setting up a MySQL cluster. After reading it, you should have a good understanding of what a MySQL cluster is capable of, how and why it works, and how to set one of these bad boys up. Note that I'm primarily a developer, with an interest in systems administration but I think that every developer should be able to understand and set up a MySQL cluster, at least to …

Updated: April 22nd, 2008

Update: Looks like both of these have been fixed in 8.0.26.

Navicat For MySQL is a GUI for MySQL developers. I've tried a few tools before but somehow got attached to Navicat due to a few nice features that I'm not going to go into right now. Navicat suffers from a couple of annoying bugs and random crashes. I don't know if I can help fix the random ones but if I can at least file the ones I can reproduce, everyone wins. I have the latest as of today version 8.0.23.

Bug [NAL-15328]: Structure Sync Fails to notice encoding differences

…