sorted by: recent | see : popular

Content Tagged with group + blog

Brian Aker gives the “zinger” lightning talk about the newly announced “Drizzle”. This short (under 8 minutes) video captures Aker’s highlights of why he started the Drizzle project and how Drizzle is different from MySQL — both in what has been removed from MySQL and what features Drizzle can accomodate.

Play the video directly in your browser at http://technocation.org/node/576/play or download the 116 Mb file at http://technocation.org/node/576/download.

Greetings from Wisconsin! Welcome to the 106th edition of the Log Buffer. Mr. Edwards is on a brief holiday and kindly asked me to fill in for him. So join me as we take a tour of some of this week’s database blogging activity.

I’d like to start by sharing the story of MySQL engineer Andrii Nikitin’s young son, Ivan. The short story is that Ivan is in need of a bone marrow transplant and that would also require travelling outside of their native Ukraine for the procedure. The family is asking for donations to cover the cost of the operation and trip, so please consider donating via the previous link.

Now, moving on to the database topics, we begin with my own area of (relative) expertise, Oracle. The big news this week is the July 2008 CPU, or Critical Patch Update, which Oracle releases quarterly. The Integrity blog has a nice summary of what’s included in this latest patchset. I’m sure plenty of you will be heading to Oracle OpenWorld 2008 as well, and OTN’s Justin Kestelyn announced a community preview page with some sneak peeks of what to expect (spoiler: beer!).

In a quick round-up of Oracle 11g topics, Amit Bansal at AskDBA.org has a nice guide for installing Oracle 11g on Solaris 10. Meanwhile, Oracle performance guru (and fellow Wisconsin native) Greg Rahn makes a great case for using incremental global statistics on partitioned tables. I see that Tim Hall has taken a break from reviewing summer blockbusters to write up a nice article on AWR baseline enhancements in Oracle 11gR1, and Alex Nuijten at AMIS has a nice presentation on using Oracle 11g’s virtual columns to clean up your data model.
(more…)

By “Good SQL Querying”, I am not referring to “how to make your queries more perfomant.” I am about to go on a mini-rant about how to make readable and self-documenting SQL queries.

One practice that will get me instantly going on a rant is using a comma join. There is NO reason to do the following:

-- uses the sakila sample database
SELECT first_name, last_name, address
FROM customer,address;

What kind of join did the original author intend? A CROSS JOIN? Or did they really want an INNER JOIN and forget the WHERE clause?

The answer: you do not know for sure; you can only guess. Had the query been

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

you would know that the author intended an INNER JOIN; had the query been

SELECT first_name,last_name,address
FROM customer CROSS JOIN address;

you would know that the author intended a CROSS JOIN. I advocate using INNER JOIN instead of JOIN because like the comma, JOIN is ambiguous.

For full disclosure, the MySQL EXPLAIN plan is exact same for the above queries. The difference is purely semantic, but I think it a very important difference, because it makes the query author’s intentions clear. There are issues with the comma join not having the same precedence as INNER JOIN, but that is for more complex queries. To wit:

SELECT first_name,last_name,address
FROM customer, address INNER JOIN city;

will actually do address INNER JOIN city first, then join with customer. This was changed to be more like the SQL standard, so it’s only in MySQL 5.0.12 and higher that it acts this way.

My feeling is that a comma join should NEVER be used in MySQL. There is no such thing as a “comma join”, really what you mean is an “inner join” or a “left/right join” or “cross join” (or “natural join”).

I know that naysayers will say that

SELECT first_name,last_name,address
FROM customer INNER JOIN address;

actually does a CROSS JOIN (Cartesian product), so it is folly to rely only on the name of the join. And I agree. However, if you name your joins appropriately for what you want and intend them to be, it is much easier to catch mistakes.

And while we are on the topic of good SQL query techniques…..I would like to rant a bit about join criteria. (more…)

I was contacted by the folks at MONyog and asked if I would review MONyog. Since using MONyog is something I have been wanting to do for a while, I jumped at the chance. Of course, “jumped” is relative; Rohit asked me at the MySQL User Conference back in April, and here it is two months later, in June. My apologies to folks for being slow.

This review is an overall review of MONyog as well as specifically reviewing the newest features released in the recent beta (Version 2.5 Beta 2). Feature requests are easily delineated with (feature request). This review is quite long, feel free to bookmark it and read it at your leisure. If you have comments please add them, even if it takes a while for you to read this entire article.

While the webyog website gives some information about what MONyog can do, it is a bit vague about what MONyog is, although there is a link to a PDF whitepaper on What is MONyog? which does answer much of these questions.

The screenshots available from the website are accurate, so I will not reproduce them here. I will note that I have not shared this feedback with the webyog team yet, so I may be upset that a feature is lacking, and the feature may be implemented but I missed it. I will post a follow-up in that case, even though they will likely comment here too.

My reference points — I have used other monitoring and graphing tools such as Nagios, Cacti, and Intermapper as well as MySQL’s Enterprise Monitor.

Overall

As an overall review — MONyog is the best out-of-the-box GUI monitoring tool for MySQL that I have seen. It “just works.” As promised, getting up and running quickly is easy, and having a centralized location for monitoring is very useful. The graphs are beautiful and the statistics that are graphed are useful time-savers.
(more…)

If you are attending Usenix 2008 at the Sheraton Hotel in downtown Boston, you can meet me and ask your burning MySQL questions at my “The Guru is In” session. On Friday, June 27th, 2008 from 2 - 3:30 pm in Constitution B, I will be helping folks out by optimizing queries and schemas, teaching general principles of working with MySQL databases, and answering (to the best of my ability) any other question they may throw at me.

The event details are at:
http://www.usenix.org/events/usenix08/tech/#fri

Hope to see you there!

Post Summary: An apology with a lesson.

When Steve Curry contacted me just after the MySQL Conference and Expo asking me if I’d be interested in a community roundtable, I was excited. Not just because Steve Curry brought me an inflatable pink dolphin after I squee‘d that I needed one, although I never forget when someone does me a favor.

However, a few weeks ago it seemed like the event was more of a PR gathering than a community roundtable. I was disappointed, and told Steve as much.

And then, one of two things happened:

1) My concerns were brought up, discussed and folks decided a roundtable involving community was a good idea;
or
2) I had come up with two different pictures of the event in my mind, based on my expectations of “community roundtable” at first and “event with businesses and PR, to include community” as the final description.

Now, last night was an excellent opportunity for me and also a lot of fun. A lot of the questions were really implicitly asking, “Is open source better? Why?” More on that in the next post, I promise.

So I wanted to say to MySQL that I was wrong.

I am sorry.

Sure, MySQL did not know what I was thinking. And certainly the event could have turned out to be one I did not enjoy.

The lesson to learn from this is that sometimes we get upset at our perception of reality, and not reality itself.

And to follow up on my cranky post where I was annoyed at the MySQL’s website’s lack of functionality at http://www.pythian.com/blogs/1016/mysql-website-a-reflection-of-values, I feel I should note that I got a call later that day from MySQL’s web designer telling me that my concerns were valid and MySQL was actively working on them. Indeed, www.mysql.com has added a “Documentation” link in the orange submenu (first is “Products” and second is “Downloads”, so I completely agree with their prioritization as well).

The other lesson: Always trade business cards with people, so they have your contact information when they want to contact you. A phone call was so much more powerful than an e-mail ever could have been.

Twitter has had many outages recently. On May 17th, 2008 http://blog.twitter.com/2007/05/devils-in-details.html was posted and says:

What went wrong? We checked in code to provide more accurate pagination, to better distribute and optimize our messaging system?basically we just kept tweaking when we should have called it a day. Details are great but getting too caught up in them is a mistake. I’ve been CEO of Twitter for two months now and this an awesome lesson learned. We’re seeing the bigger picture and Twitter is back. Please contact us if something isn’t working right (with Twitter that is).

(in other news, that post was made on May 17th and does not show up on http://blog.twitter.com, which it should, between the May 16th and May 19th posts. I found a reference in other posts and had to search the site to find that post).

A real “awesome lesson learned” is “do not tweak production without testing first.” In every job I have had I have first learned and then taught the concept of “test everything possible.” Which Twitter has not learned yet, because http://blog.twitter.com/2008/05/not-true.html, posted on Tuesday May 20th, states:

We caused a database to fail during a routine update early this afternoon.

As someone who has years of experience working with MySQL, and before that was a systems adminsitrator; as someone who was referred to as “the MySQL Queen” yesterday (by someone who wanted me to test their product, so yes, they were flattering me); I can assure you:

no matter how “routine” a change is, if you do it on production without testing it first, you are playing with fire, and 95% of the fires caused by not testing first are completely preventable.

I will repeat this, because repetition is important to learning concepts.

no matter how “routine” a change is, if you do it on production without testing it first, you are playing with fire, and 95% of the fires caused by not testing first are completely preventable.

With a proper testing environment, 19 out of 20 “whoops, didn’t expect THAT from a routine change!” issues are caught. And I can tell you that often “routine changes” cause unexpected results.

Now, I was online during an outage, and http://twitter.com/home was showing their “site isn’t working” page for at least 3 hours between 2 and 5 am EDT yesterday (Tuesday, May 20th, 2008).

So…..there is no read-only copy around that Twitter could use? Maybe I cannot tweet, but I should at least be able to read what was done before!

Of course, since last week Twitter has done the opposite — often I can see the most recent 20 or so posts, but not anything prior. Now, I understand that it is hard to get all the histories for the people I follow. But it only needs to be done once, and could then be cached — “Posts from who Sheeri follows on 5/20″. It would not be difficult, and I would be OK with the functionality changing such that “once you follow a new person, their tweets prior to when you followed them do not show up in the history.”

Alternatively, you could go the snarky way and say: http://www.techcrunch.com/2008/05/20/twitter-something-is-technically-wrong/ states:
What would be great is if Twitter just moved their blog to another platform so that it doesn?t fail when users need it most.

I am not a huge user of rails. But I will say that given the content of the public announcements, the platform is not the problem. It is the code release process that is the problem. Maybe there’s “agile development” happening, paired programming and code reviews. But there is not adequate testing.

Twitter — if you truly need scaling help, please ask for help — I know Pythian would be happy to help. However, if it really is as it seems — that basic good practice is not being followed — I would like to remind you that backups are really important too, just on the off chance that backups are not happening.

One of the most frequently needed functionality in the MySQL Proxy is the need to know which server you are on. This is not given, on purpose, by the proxy, because the proxy is supposed to be transparent. It is not supposed to matter which back-end server you are on.

However, for testing purposes we often want to know which back-end server we’re on. Thus I developed functionality for SHOW PROXY BACKEND [INDEX ADDRESS OTHER].

SHOW PROXY BACKEND INDEX — gives the index of the server you’re on (backend_ndx, ie 1)

SHOW PROXY BACKEND ADDRESS — gives the address of the server you’re on (ie, foo.bar.com:3306)

SHOW PROXY BACKEND OTHER — gives the address of all the other servers except those you’re not on, in multiline format.

Note that I was pretty lazy and the commands are case-sensitive. But I figured that since this is supposed to be used mostly in testing circumstances, it did not really matter.

The code is on the MySQL Forge Wiki at http://forge.mysql.com/tools/tool.php?id=139

Interestingly enough, this script is actually being used in production — a site has a primary and failover server, and wants to check that when the primary server is in use, there are no connections on the failover. I wrote that check as well, but as the logic is somewhat particular, I am not sure it would be useful to many. The logic is:

1. Run SHOW PROXY BACKEND INDEX.
2. if it fails, I can’t connect to the proxy, log an error, exit.
3. if it !=1, I’m on the failover server, log a warning, exit.
4. if it =1, I’m on the primary server.
5. Run SHOW PROXY BACKEND OTHER.
6. if it is empty, either I can’t connect to the proxy or there are no failovers defined, log a warning, exit
7. For each OTHER address, connect to them and find what that connection’s host looks like (sometimes it looks like foo.bar.com:4325, other times it looks like 1.2.3.4:4573). Get the value of “my host” by stripping off the port.
8. Connect to the OTHER address again, killing off connections from “my host” except for the “system user” and a few other special accounts (replication slave being one of them). Log each kill with the thread number and a warning.
9. If there are no connections to be killed, log OK.

Let me know if you’d like to see that…it’s a shell script, and it requires the mysql client and bintools like grep and cut.

At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.

For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf

* or tea, for those of us in the civilized world.

10,000 Tables Can?t Be Wrong: Designing a Highly Scalable MySQL Architecture for Write-intensive Applications by Richard Chart

Chose MySQL for performance and stability, and less important but still there, experience and support. Support is becoming increasingly more and more important.

Starting point: 1 appliance supporting 200 devices
Problem/Goal: Extensible architecture with deep host and app monitoring, over 1000 devices with 100 mgmt points each
Distributed collection over a WAN, with latency and security concerns
Current reality: several times the scale of the original goal
Commercial embedded product, so they actually pay for the embedded MySQL server

Future: The fundamentals are sound: next generation of the product moves up another order of magnitude

Data Characteristics
>90% writes
ACID not important
Resilient to loss, because gaps in data do not invalidate the rest of the data
Data elements by themselves are valuable, but much more so when relationships are added.

Chose MyISAM because: (more…)

Yesterday’s keynote panel on “Scaling MySQL — Up or Out?”

Directly download the 310MB wmv file (not if you are on the conference wireless please!), or watch it in your browser via streaming — simply click the “play” link on this page.

Keith Murphy managed to take painstaking notes with all the facts and figures. As well, Venu Anuganti presents a chart with the results as well as notes on the more detailed answers. Ronald Bradford has a brief summary of the 20 seconds of wisdom from each panelist.

Liveblogging: Extending MySQL by Brian “Krow” Aker

Brian wins the award for “most frequent great quotes during a talk”.

Before MySQL 5.1 a UDF was the only way to extend MySQL.

All you need in a UDF is: init() execute() deinit()

my_bool id3_parse_init(UDF_INIT *initid UDF_ARGS *args, char *message)

UDF_ARGS tell you about incoming args
char *message is the output that might return
args->arg_count is the # of args

WARNING: use STRICT mode in MySQL, otherwise there are tons of silent failures.

“When you work on databases you start to put everything in databases. Tip, don’t put a DVD into a database, because really long BLOBs aren’t actually supported….”

In MySQL 5.1, you can now install plugins (example is memcache_servers plugin):

mysql> INSTALL PLUGIN memcache_servers SONAME 'libmemcache_engine.so'

In the plugin architecture, there’s a callback mechanism to call a structure and then the structure is self-hosted into the environment. mysql_declare_plugin() is usually found at the bottom of the file because that’s how Brian wrote the example….

A storage engine is really about connecting to anything.

MySQL has a parser, and optimizer, and a retrieve/store part, which talks to the storage engines. The engine interface is written in C++.

Quote about the BLACKHOLE storage engine: “It’s amazing how much money can be made out of something that does NOTHING.”

What about defining features?

tables_flags()
index_flags()
handlerton flags

Table object methods:

base_ext()
create()
delete_table()
rename_table()
optimize(), repair(), etc.

As you can see, the API is pretty specific to actions that are happening at the SQL layer.

Reads
rnd_init() — scan is about to begin
rnd_next() — get the next record
(rnd = random or read next data)
index_first() — index read is about to begin
index_next() — get the next record in the index

Writes:
write_row() — “here’s new data, please store this”.
delete_row()
update_row()

on delete or update, MySQL starts reading the rows and once the predicate matches, then the delete_row() or update_row() is called. update_row() will be passed the before and after image of the row.

What about information_schema?

The information_schema API is an easy way to get more information into the information_schema tables.

(there’s a really nice example of an information_schema plugin)

A quote about his code — “That in the middle there looks like a bug….but it’s never called, so that’s OK. That’s what I get for copying and pasting my own examples.”

The plugin is responsible for all the locking.

Daemon plugins created because otherwise people would “bastardize the storage engine plugins.”

A daemon plugin has full access to the server internals, and it is a simple interface to be able to extend.

“If you’re really going to shoot yourself in the foot, you really want to go full blast.”

UDP Daemon just allows UDP packets to be put into the db (instead of TCP/IP with its darn authentication schema). Simplest interface to MySQL that exists. No connector, just UDP. The point is you can put other listeners to MySQL, such as an HTTP listener.

Daemon plugins are also a way to

code for examples
skeleton engine

Architecture of Maria: A New Storage Engine with a Transactional Design

Goals of Maria:

• ACID compliant
  
• MVCC, transactional
  
• default non-transactional and default transactional storage engine for MySQL
  
• MyISAM replacement, including temporary table use
  
• Storage engine good for data warehousing.
  
• Allow any length transactions to take place
  
• all indexes should have equal speed (clustered indexes are not in the plan)
  
• log shipping — incremental backups just by copying the logs
  
• used as a standalone library
  
• fast count(*)
  
• allow copying of Maria tables between different Maria servers
  
• Better blob handling (than MyISAM) — no memory copying, or extra memory used for blobs on INSERT/UPDATE
  
• BLOBs are allocated in big sequential blocks means that there is less fragmentation over time.
  
• BLOBs are stored so Maria can be easily extended to have access to any part of a BLOB with a single fetch in the future

Maria is actually faster than MyISAM.
Will support READ COMMITTED and REPEATABLE READ (was a specific question).

“You should be able to upgrade Maria and have it just work, without ever having to backup and restore as part of the upgrade.”

Why do we need 2 storage engines, Maria and Falcon?
More storage engines = more scenarios to be able to handle. Falcon good for lots of memory, and shorter queries, Maria is to take care of long transactions and data warehousing side, and also to be an overall “good” engine for most purposes (at least as good as InnoDB).

Project plan — MySQL 5.1 and higher. Maria strives to be bug-free.

Maria 1.0 = “crash safe”, released in Jan 2008. Cacheable row format.
Maria 1.5 = “concurrent insert/select”, Apr/May 2008. Will be merged into the MySQL 6.0 release. Non-transactional.
Supports:

• External/internal row CHECK/REPAIR/compressing
  
• Different row formats — fixed size, dynamic length, compressed, page
  
• Fast checksum of a table
  
• maria_check
  

MySQL 6.0 (includes Maria)
Maria 2.0 = “Transactional and ACID” alpha, Q4 2008
Maria 3.0 = “High concurrency + Online backup” alpha, Q1 2009; GA, Q2 2009

Advantages of Maria compared to MyISAM

• Data, index and statistics are crash safe. On crash, everything will rollback to the state of the start of the statement or last LOCK TABLES commands.
  
• Can replay everything from the log. Still need to backup the .frm files though.
  
• LOAD INDEX can skip index blocks for unwanted indexes
  
• most parts of Maria have unit tests.
  
• Supports all MyISAM formats and a new PAGE format, where data is stored in pages — this is:
  
  
  
  • Only crashsafe, transactional row format for Maria
    
  • Versioned, so you can have concurrent insert/select
    
  • Cached by page cache so will give noticeable speed improvement on systems with poor data caching
    
  
  
• MyISAM is non-transactional only, Maria can support transactional (crash-safe) and non-transactional formats
  
• Maria uses big log files by default — 1G
  
• Log control file = maria_log_control
  
• Log files = maria_log.?????
  
• If a log file is already in use by a transaction, create a new one, otherwise, reuse it. SHOW LOGS will show which logfiles exist, which ones are in use, and you can PURGE the logs.
  
• Maria uses 8K pages by default (MyISAM uses 1K pages by default. This should be faster on static size indexes but slower on variable length keys, until Maria 2.0 where there will be a directory for index pages.
  

Before Apr/May release of Maria 1.5

• Statistics and indexes versioned (data is already versioned)
  
• FULLTEXT (boolean mode) and R-TREE indexes need to be made crash-safe
  
• Parallel recovery
  

Disadvantages, not likely to be fixed

• No external locking
  
• One page size for both index and data
  
• only one page cache
  
• index number and checksums require an extra 5 bytes per page
  
• doesn’t support RAID
  
• Minimum data file size for BLOCK format is 16K, with 8K pages
  
• Storage of rows

XDB indexes — group records, and get the min and max for each group (groups are not sorted), and save that in a separate block. This means that stats like min and max are very fast. This is something that will help data warehousing queries.

Resources:

• Maria worklog (tasks to do)
  
• 5.1 Storage Engine documentation — note that Maria docs have not made it to the manual yet.
  
• http://monty-says.blogspot.com
  
• Download Maria
  
• FAQ and answers about Maria
  
• Maria Documentation on the MySQL Forge.
  

Best quote from Monty: “You don’t steal ideas in open source. You respect people.”

Jonathan Schwartz’s Keynote at the 2008 MySQL Conference can be played directly in your browser or you can download the 147MB .wmv file. (Please do not download the movie on the conference wireless system!)

I finally realized who Jonathan Schwartz reminds me of:

That’s right, comedian and magician Penn Jillette.

Schwartz started by saying, “enough of this free software stuff!” It got lots of laughs. He started to talk about Sun’s agenda, and mentioned that MySQL and Sun had similar values, as well as similar dysfunctions as well — particularly that each engineer has his/her own opinion.

But Schwartz goes on in earnest to say “The future will be defined by free….and freedom,” that “freedom is a price tag *and* a philosophy.” I agree completely.

He sees “the network is a social utility” much like heat and electricity are utilities. His talk somewhat reminded me of the Free as in Water post I made in June 2007.

He finished up by comparing the Amazon River to Sun — The Amazon River is really ecosystem of many rivers — 10,000 smaller rivers. Sun is really an ecosystem of many communities — Java, Open Office, Solaris, Open Solaris, MySQL, and so on. I was very pleased to hear that he feels that community is not only inclusive of all users, paid or otherwise, but that the ecosystem cannot exist without each part of that “smaller river” contributing to the whole. And *that* is Sun’s agenda, to continue to build that river, so if one part has problems, the entire river is not damaged.

Also, Sun has a quote that “innovation happens elsewhere,” so it is crucial to build those bridges (mixed metaphor unintended) so that Sun can support and enable the innovation. In a world where diversity is key, this is an excellent message.

Bravo, Jonathan!

Hello everyone,

Reading PlanetMySQL today, I discovered that Alex Gorbachev’s announcement that he has released the first public beta of his Oracle Grid Control plugin for MySQL was not aggregated! This is probably because Alex is primarily working on our Oracle space and so his feed isn’t on planet.

This plugin has been under development since 2006 and this is a major achievement.

Knowing that my feed is aggregated, and not willing to let this news and this amazing work go unnoticed by the MySQL community during the conference (I am at MySQLConf listening to Amazon.com’s CTO speak right now!)

In any event, if you missed them inline up there, here’s a link to Alex’s announcement with some impressive screenshots, and here’s a link to the product’s home page.

And check out the very positive comments from the first testers already on the announcement post.

Congratulations and thanks, Alex!