sorted by: recent | see :
popular
Content Tagged SQL
JoSQL - SQL for Java Objects
Kickfire makes it easy to be green (and to save some too)!
You can find the official results here on the TPC(tm) (Transaction Processing Performance Council) website:
http://www.tpc.org/tpch/results/tpch_price_perf_results.asp
http://www.tpc.org/tpch/results/tpch_price_perf_results.asp?resulttype=noncluster
While the amazingly low price of the Kickfire Database Appliance 2400 will grab you (only about twice the price of a typical 4U MySQL database server) -- the amazing performance per watt is truly incredible. The 3RU Kickfire appliance used in the 300GB volume test consumes just 3RU of rack space and a measly 650watts of power.
To give an appropriate comparison, my home gaming PC contains an 800W power supply. Compared to other high performance database systems (comparison by wattage, not necessarily performance) that use tens (even hundreds) of thousands of watts, the Kickfire appliance is orders of magnitude more environmentally friendly.
Not only does it reduce the amount of physical space required, but it also reduces the costs of environmental control (cooling). It uses less manufacturing resources, is more portable between data centers and it requires less excess inventory (like spare hard disks) to maintain, all of which result in significant cost of maintenance savings.
This 'green' aspect is one of the major factors in my decision to join Kickfire, and I'm proud to be working for a company making such huge strides toward sustainable computing practices.
Full disclosure: I am a paid employee of Kickfire, Inc. The opinions expressed in my personal blog posts are my own and may not reflect those of the company. All facts and figures may be validated via the TPC website, and this blog does not represent an official posting of TPC-H data, or any other TPC related information.
Kickfire is a trademark of Kickfire, Inc.
TPCH, QphH and $/QphH are trademarks of the TPC. For additional information on the TPCH benchmark, please visit the Transaction Processing Performance Council's Web site at http://www.tpc.org/.
Log Buffer #96: a Carnival of the Vanities for DBAs
This is the 96th edition of the weekly review of database blogs, Log Buffer.
Let’s start this one in SQL Server Land, with a question from Dennis Gobo — should SQL Server have the CREATE [OR REPLACE] PROCEDURE syntax? There are, he writes, advantages: “When scripting out a database you don?t have to generate if exists…..drop statements,” and disadvantages: “I can overwrite a proc without even knowing it.” Of course, the commenters have opinions of their own, and the piece becomes a straw poll for the desirability of that syntax as a feature.
Aaron Bertrand has one too: when was my database/table last accessed? Writes Aaron, “SQL Server does not track this information for you. SELECT triggers still do not exist. Third party tools are expensive and can incur unexpected overhead. And people continue to be reluctant or unable to constrain table access via stored procedures, which could otherwise perform simple logging.” He looks at 2008’s built-in auditing, and for those who can’t wait for that, illustrates a workaround for 2005.
Linchi Shea explores something else from 2008, Page Compression, focusing on how the number of processors affects the rebuilding a table with page compression.
Jamie Thomson, the SSIS Junkie writes that he has made a submission to Connect on the matter of absolute and relative paths in SSIS. “. . . I have always agreed that stipulating the use of absolute paths within SSIS was the right thing to do (and indeed I have championed it) however of late I have changed my mind. Support for relative paths would greatly simplify package deployment and package management . . . What do you think? Should SSIS support relative paths?” So far, it looks like a shoo-in.
Brian Knight also explains another little quirk, SSIS Case Sensitivity: “The case sensitivity can in some cases create behavior that is not expected and may give you bad results if you’re not careful. . . . One such example is with the Lookup Transform, where comparisons against the cache are case sensitive. If you do not expect this, you may have a miss in a match that is actually a hit.”
In the MySQL ’sphere this week, there is plenty of talk about the openness or otherwise of MySQL. (more…)
Create table and Insert into Examples for MySQL
Here are a series of create and insert statements for MySQL. I modified some of the entry values with "x", "!", "?" as to not offend some readers.
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Table structure for table `phpbb_banlist`
--
CREATE TABLE `phpbb_banlist` (
`ban_id` mediumint(8) unsigned NOT NULL auto_increment,
`ban_userid` mediumint(8) NOT NULL default '0',
`ban_ip` varchar(8) NOT NULL default '',
`ban_email` varchar(255) default NULL,
PRIMARY KEY (`ban_id`),
KEY `ban_ip_user_id` (`ban_ip`,`ban_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=97 ;
--
-- Dumping data for table `phpbb_banlist`
--
INSERT INTO `phpbb_banlist` VALUES(84, 0, '', '*@fromru.com');
INSERT INTO `phpbb_banlist` VALUES(5, 0, '', '*@mail.ru');
INSERT INTO `phpbb_banlist` VALUES(6, 0, '', '*@*.ru');
INSERT INTO `phpbb_banlist` VALUES(7, 0, '', '*@go2.pl');
...
CREATE TABLE `phpbb_disallow` (
`disallow_id` mediumint(8) unsigned NOT NULL auto_increment,
`disallow_username` varchar(25) NOT NULL default '',
PRIMARY KEY (`disallow_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;
INSERT INTO `phpbb_disallow` VALUES(1, '*porn*');
INSERT INTO `phpbb_disallow` VALUES(2, '*sex*');
INSERT INTO `phpbb_disallow` VALUES(3, '*xxxxx*');
INSERT INTO `phpbb_disallow` VALUES(4, '*co!!*');
INSERT INTO `phpbb_disallow` VALUES(5, '*axx*');
INSERT INTO `phpbb_disallow` VALUES(6, '*adult*');
INSERT INTO `phpbb_disallow` VALUES(7, '*viagra*');
INSERT INTO `phpbb_disallow` VALUES(8, '*pill*');
INSERT INTO `phpbb_disallow` VALUES(9, '*drug*');
INSERT INTO `phpbb_disallow` VALUES(10, '*pharm*');
...
-- Table structure for table `phpbb_words`
--
CREATE TABLE `phpbb_words` (
`word_id` mediumint(8) unsigned NOT NULL auto_increment,
`word` char(100) NOT NULL default '',
`replacement` char(100) NOT NULL default '',
PRIMARY KEY (`word_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;
--
-- Dumping data for table `phpbb_words`
--
INSERT INTO `phpbb_words` VALUES(1, 'xxxx', '***');
INSERT INTO `phpbb_words` VALUES(2, 'sh??', '****');
INSERT INTO `phpbb_words` VALUES(3, 'xxxch', '*****');
INSERT INTO `phpbb_words` VALUES(4, 'fxxke?', '******');
INSERT INTO `phpbb_words` VALUES(5, 'axx', '***');
INSERT INTO `phpbb_words` VALUES(6, '*turd*', '****');
...
High Performance MySQL Second Edition Schedule
I just got the rest of the production schedule from the publisher, plus the PDF files for quality control, for our upcoming book. (Now I have to proofreeed the whole book!) This is the first time I’ve seen the entire production schedule. The book is supposed to go to the printer in the first week of June. I don’t know what the on-the-shelf date will be, but I think very shortly after that. The publisher has promised that it’ll physically be on sale at Velocity.
I also took a peek at the PDFs. Without the appendixes, the last page of Chapter 14 (Tools for High Performance) is page 604. The appendixes bring it to 660 pages. That’s real material, not including tables of contents and indexes. So my estimate (620) was not too far off.
660 pages is not bad, considering that the contract was for 384 pages.
Another note: the marketing materials for the book emphasize that it covers MySQL 5.1. While this is true, I want to point out that we took a real-life approach: we write about what we’ve seen in the real world, and 5.1 is not as widely deployed in the real world. However, the book’s real value, as far as version-specific content goes, is its tremendous depth and breadth in MySQL 4.1 and 5.0. These have been “out there” for a long time, and among the four of us we’ve seen about every conceivable scenario with it. So you’ll get a lot of insight about current, production-ready, widely-used versions. Let the other guys speculate — we just report the facts. It’s not like there’s any shortage of things to say about 5.0, right?
mysqlSummary of beCamp 2008
Yesterday I went to beCamp 2008 along with four roomfuls of other people interested in technology (perhaps close to 100 people total). The conference was a lot of fun. Not everything went as planned, but that was as planned. This was an Open Spaces conference and I thought it worked very well. From an email Eric Pugh sent:
Basically it all boils down to:
Open Space is the Law of Two Feet: if anyone finds themselves in a place where they are neither learning nor contributing they should move to somewhere more productive. And from the law flow four principles:
- Whoever comes are the right people
- Whatever happens is the only thing that could have
- Whenever it starts is the right time
- When it’s over, it’s over
From Hadoop to Bang-Splat
I used the law of two feet a time or two. In fact, the first session I wanted to go to, which was about Hadoop and MapReduce, had no knowledgeable attendees. Someone overslept. OK, that’s the way it goes: move along.
From there I went to a session about Unix command-line productivity. Most of the sessions I saw were traditional in that they had one person standing up talking and many people sitting and listening, but not all. This one had several clever command-line gurus mentioning their favorite power tips.
I learned about bang-splat and bang-dollar. The bangs have always gotten me in Bash: I avoid them because I’ve never felt like reading the Bash man page section on them. (Am I too lazy, or not lazy enough?) So it was great to hear some people say “bang-splat and bang-dollar are great” and then explain them. That was easy for me, and now I know how they can be useful to me.
This problem-first type of tip is great for me: tell me the problem, then how to solve it, rather than telling me what the solution is and leaving me guessing what kinds of problems I can solve with it. (The Bash man page is solution-first).
In case you’re wondering, bang-splat substitutes the arguments to the last command, and bang-dollar substitutes the last argument of the last command. So, instead of this:
$ touch file1 file2 file3 $ rm file1 file2 file3
I can do this:
$ touch file1 file2 file3 $ rm !*
There were lots of other nice tips too.
MySQL Performance
I ended up doing a talk on MySQL performance basics. I had no idea what the audience was looking for, so I winged it. I did make some slides, but most of the talk isn’t on the slides. You can get the slides from Percona’s slide page. It seemed to be useful to the folks attending, who had a wide variety of experience and knowledge about MySQL.
Cloud Computing
This session began with a demo of how to create an entire application stack in a few minutes with Cohesive Flexible Technologies. Someone else then demoed a similar thing using RightScale. rPath’s Jeff Uphoff was also in the room, but we didn’t get to see a demo of that. During this session the talk turned to various topics including a little bit of the topics I wanted to hear about in the Hadoop session.
Lunch
Lunch was catered Indian food provided by the Rimm-Kaufman Group. Yum.
Large Scale Storage
This session was sort of a round-table. The two people who talked the most were Josh Malone from the National Radio Astronomy Observatory and the Library of Congress, both of whom have a lot of storage needs they are unsure how to meet. Some people from UVA’s library were there as well, but I didn’t ask what they were working on.
This reminded me a lot of a recent keynote Jacek Becla gave at another conference. He’s with the Stanford Linear Accelerator Center, who are going to be generating a lotta data pretty soon.
High Availability Linux
This one started off with more from Josh Malone, who demoed Nagios briefly and then talked about his storage and backup systems. He uses BackupPC, which sounds pretty neat and very smart. We then talked about some of the things he’s looking into doing, with audience suggestions to look into shared storage or DRBD. We also looked at UltraMonkey briefly — it looks like it’s stagnating, though. And the Linux HA project.
Google App Engine
Finally, someone showed us a calculator application they’d built on Google App Engine, including the code and talking about the data model somewhat. It looks like a neat idea, but the lock-in worries me, a sentiment that was voiced by many others in the room.
BackupPC, BarCamp, Bash, beCamp, beCamp2008, DRBD, Eric Pugh, Hadoop, Jacek Becla, Jeff Uphoff, Josh Malone, MapReduce, mysql, Nagios, Rimm Kaufman GroupNews flash: MySQL 5.1 has zero bugs
Zack Urlocker says MySQL 5.1 has zero bugs. He may have been misquoted, or quoted out of context, but there it is. I’ll quote enough of it that you can’t take it out of context twice:
Mickos also said MySQL 5.1 has upgraded its reliability and ease of use over 2005’s v5.0.
“Now we can admit it, but this version is much improved over 5.0, which we weren’t totally happy with,” Mickos confided.
He reported that more than 1,300 bugs (997 in 2007, 386 so far in 2008) have been fixed in v5.1, and that, according to standard DBT2 benchmarks, the performance of v5.1 is 10 to 15 percent better than the previous version.
“This version now has zero bugs,” Urlocker told eWEEK.
You can check for yourself at the MySQL bug statistics page.
Of course it’s not true. But what did Zack really say, I wonder?
bugs, innodb, Marten Mickos, mysql, Zach UrlockerCome to beCamp 2008
I’m going to be at beCamp 2008, the followup to the first beCamp, which I sadly missed.
beCamp is a BarCamp un-conference. Tonight was about meeting, greeting, and throwing ideas at the wall to see which ones stick. Literally. We stuck pieces of paper on the wall with our ideas — things we can either talk about or want to hear about — and then scratched our votes on them to see which are popular.
I live and breathe MySQL for a decent part of the day, so I hesitated, but then stuck “MySQL Performance” on the wall. It got quite a few votes, so I assume will be giving a talk on MySQL performance basics at some point during the conference. (The exact schedule is probably being determined right now, in my absence, but I’m so tired right now that I’ll just take my chances on it not being at 8:00 AM tomorrow.) [edit: I just checked the website and there won’t be anything before 9:00, and the schedule is determined tomorrow. I did say I’m tired, right?]
See you there!
PS: if you want to meet some of my colleagues from my former employer, the Rimm-Kaufman Group, they’ll be there too, wearing the “We’re Hiring” t-shirts. They’re hiring, by the way.
BarCamp, beCamp, beCamp2008, mysql, Rimm Kaufman GroupLog Buffer #95: a Carnival of the Vanities for DBAs
The 95th edition of Log Buffer, the weekly review of database blogs, has been published by Mark Schoonover on his Mark’s IT Blog.
We can look forward to LB#98 Jeff Smith’s Jeff’s SQL Server Blog on May 23rd. There’s always plenty of room for more editors, so don’t waste another minute — send an email to me, the Log Buffer coordinator, and get started!
Without further ado, here is Mark Schoonover’s Log Buffer #95.
Pre-Order High Performance MySQL Second Edition
If you’re waiting for High Performance MySQL Second Edition to hit the shelf, you’re not the only one. I am too! I can’t wait to actually hold it in my hands.
But you don’t have to wait idly. No, not at all! You can pre-order it and then you’ll get it as soon as possible. Plus your pre-order will help them figure out how much demand there is, so it doesn’t sell out and make you wait for your own copy.
No TagsImproved Cacti monitoring templates for MySQL
Download MySQL Cacti templates
As promised, I’ve created some improved software for monitoring MySQL via Cacti. I began using the de facto MySQL Cacti templates a while ago, but found some things I needed to improve about them. As time passed, I rewrote everything from scratch. The resulting templates are much improved.
You can grab the templates by browsing the source repository on the project’s homepage.
In no particular order, here are some things I improved:
- Standard polling interval and graph size by default.
- Full captions on every graph; you don’t have to guess at how big the values are. Each graph has current, max, and average values printed at the bottom for every value on it.
- Much more data is captured. I’ve graphed almost everything I could think of.
- The graphs are grouped better. Most graphs have only related values. There are some exceptions, but not many.
- The templates don’t hijack your existing installation. They don’t depend on or alter anything in your default Cacti installation.
- The script that gathers the data is totally rewritten from scratch, and much improved. For example, the math works on 32-bit systems. It has caching built-in so each poll cycle results in just one request to the server, instead of one request per graph. (This is a weakness of Cacti I’m trying to work around). It also has debugging aids and other good coding stuff.
- By default, it assumes you have the same username and password across every server you’re monitoring, so you don’t have to fill in a username and password for every single graph you create.
- One data template == one graph template. This helps work around another Cacti limitation.
- Lots more. Honestly I can’t really remember everything I’ve done. I’m sure you’ll help me remember by asking me how to get X feature working the way you want, and I’ll go “oh, yeah, that’s another thing I improved…”
Cacti templates are very laborious to create if they’re complex at all; it takes a long time and is very error-prone. Instead of doing it through Cacti’s web interface and exporting a huge XML file, I eliminated the redundancies and created a small, easy-to-maintain file from which I generate the XML template with a Perl script. This gives the added benefit of letting me (or you) generate templates with different parameters such as polling interval or graph size. The README file has the full details. However, I’ve pre-generated a set of templates that matches Cacti’s defaults, so you can probably just use that.
This has taken a lot of time. In particular, I spent a lot of time working on it at my former employer, The Rimm-Kaufman Group (kudos to them for letting me open-source the work) and I just spent most of my weekend writing the scripts to convert from the compact format to XML templates, so it’s possible to maintain these beasts. Plus I had to develop the compact format, too. This took a lot of time because I had to understand the Cacti data model, which is pretty complex.
Please enter issue reports for bugs, feature requests, etc at the Google project homepage, not in the comments of this blog post. I do not look through comments on my blog when I’m trying to remember what I should be working on for a software project.
If these templates help you and you feel like visiting my Amazon.com wishlist and sending something my way, I’d appreciate it!
PS: You may also be interested in Alexey Kovyrin’s list of templates for monitoring servers.
Alexey Kovyrin, Cacti, Cacti templates, graphing, monitoring, mysql, Rimm Kaufman GroupLog Buffer #94: a Carnival of the Vanities for DBAs
Log Buffer, the weekly review of database blogs, welcomes back for his record-breaking record-tying (Sheeri, are you reading?) third edition Ronald Bradford of Opinions, Expertise, Passion.
Why does Ronald write Log Buffer? Perhaps it’s because he knows that LB is and established and widely read feature, and hence likely to bring his own blog some new readers and improve its ranking. Or maybe he enjoys the fun and challenge of comprehending and presenting the entire DBA blog scene, not just the part that deals with his own favoured technologies. (Or maybe he just likes me? Ronald?)
Since Log Buffer is open to anyone, I encourage you also to join in. If you’d like to edit and publish an edition yourself, take a look at LB’s homepage, read the few guidelines, and then get in touch with me, the Log Buffer coordinator.
You can also contribute by emailing your favourite blog items to the editor.
And now, here’s Ronald Bradford’s Log Buffer #94.
Baron Schwartz on a podcast at MySQL Conference and Expo 2008
I did an interview with Barton George from Sun while I was at the conference last week. Barton has now posted the interview. If you’re quick, you can listen to it before I do.
Topics: everything and anything, including Maatkit and PostgreSQL.
Baron Schwartz, Barton George, maatkit, mysqluc08, mysqluc2008, Podcast, SunSQL lexing madness
Continuing on my quest to write a replacement for the MySQL lexer and parser (Hi David! :)), I come across many interesting and slightly disgusting details about SQL in general and MySQL's notion about it specifically.
So here I am reading our fine documentation and our totally "obvious" lexer and parser implementations trying out cornercases to figure out what really is valid and what is not. Finding the occasional bug, of course... I won't link to it from here, but let me assure you, it's a nice one.
Anyway, the latest thing I came across blew my mind:
Consider this simple statement
mysql> CREATE TABLE foo (a INT);
Obvious what it does, isn't it? Totally valid. No problem. Now for fun, let's try to figure out what happens when we use a reserved word:
mysql> CREATE TABLE table (a INT);
Boom:
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'table (a INT)' at line 1
Ok, fine. I expected that. Extra credit for this one:
mysql> CREATE TABLE test.table (a INT);
Query OK, 0 rows affected (0.04 sec)
Wait a moment...! That ain't right!
Except it is. Puzzled I dug around the parser's YACC grammar and couldn't find any indication that a reserved word is allowed in that place at all. In fact, it expects an identifier:
table_ident:
ident { $$=new Table_ident($1); }
| ident '.' ident { $$=new Table_ident(YYTHD, $1,$3,0);}
| '.' ident { $$=new Table_ident($2);} /* For Delphi */
;
The rule ident is made up of several other rules and tokens, none of which include the lexer symbol TABLE_SYM. (And please don't get me started on the /* For Delphi */ comment, please...)
Still puzzled, I took the risk to look into the lexer. Normally I would stay away from that handwritten beast with all its special cases, but alas, it must contain the answer. Must.
Indeed, poking around, there it is:
case MY_LEX_IDENT_SEP: // Found ident and now '.'
yylval->lex_str.str= (char*) lip->get_ptr();
yylval->lex_str.length= 1;
c= lip->yyGet(); // should be '.'
lip->next_state= MY_LEX_IDENT_START;// Next is an ident (not a keyword)
Sweet, isn't it? Made my day.
Effectively, this means that you can use any old keyword in place of tablename when using the database.tablename notation. With the added fun that you now have created a table that you cannot select from without quoting or using the database.tablename notation:
mysql> SELECT a FROM table;
ERROR 1064 (42000): [...]
mysql> SELECT a FROM `table`;
Empty set (0.00 sec)
mysql> SELECT a FROM test.table;
Empty set (0.00 sec)
Another datapoint that allowing reserved words as identifiers is just plain wrong. Plain wrong, I tell you. All it does is to make the life hard on the parser authors. And it doesn't add anything for the user, except allowing constructs to shoot yourself in the foot with. I mean I don't go around in C-land and write stuff like
if (if > 0) {
...
}
Do I?
To sum it up, here are the three ways to create a table named table:
mysql> CREATE TABLE `table` (a INT);
mysql> CREATE TABLE test.table (a INT);
mysql> CREATE TABLE .table (a INT);
Lovely.