I'm not sure I care what the version number is (5.3,5.4,5.4,6.0), and you can talk about milestone releases all you want. What really matters to me is when we'll have something beyond 5.1 marked as GA. Will it be 5.5? When should I expect that?   5.4 was announced last year at the conference with lots of performance fixes for the mysql server itself. Great. Until those changes percolate up into a stable release, they aren't worth too much to me. With all the hoopla about the release cycle, I haven't seen any results in the form of new features/fixes making it to stable any faster than 5.1 did.   What's the point of any release cycle beyond getting good solid code to stable in a timely (i.e., not 3 years) and safe (i.e., well tested) manner? Can't we have both timely and safe?

MySQL University: MySQL Galera Multi-Master Replication

This Thursday (February 11th, 14:00 UTC), Seppo Jaakola & Alex Yurchenko will talk about MySQL Galera Multi-Master Replication. Galera provides synchronous multi-master replication and uses a certification-based replication method for replicating transaction write sets in a DBMS cluster. The replication method requires close co-operation with database transaction processing and DMBS must support a specific replication API to be compatible with Galera. Codership has integrated Galera replication in the InnoDB storage engine, and the resulting MySQL/Galera cluster product has been published as a production-ready GA release in December 2009. The MySQL/Galera release 0.7 is available on the Codership and Launchpad sites. For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, and more.) All MySQL University sessions are recorded, that is, slides and voice can be viewed as a Flash movie (.flv). You can find those recordings on the respective MySQL University session pages which are listed on the MySQL University home page. MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the tentative list of upcoming sessions: February 18: Performance Schema: Instrumenting Code (Marc Alff) February 25: Securich - Security Plugin for MySQL (Darren Cassar) March 4: MySQL Column Databases (Robin Schumacher) March 11: Improving MySQL Full-Text Search (Kristofer Pettersson) By the way, did I mention that we need more speakers to fill up the 2010 schedule? If you'd like to be a speaker, have a look at this blog article!

In mathematics, a monotonic function (or monotone function) is a function which preserves the given order. [Wikipedia] To be more precise, a function f is monotonic increasing, if for every x ≤ y it holds that f(x) ≤ f(y). f is said to be strictly monotonic increasing is for every x < y it holds that f(x) < f(y). So, if we follow values in some order, we say that f is monotonic increasing if f’s value never decreases (it either increases or stays the same), and we say that f is strictly increasing if f’s value is always changes “upwards”. Monotonic functions play an important role in SQL. To discuss monotonic functions in SQL we must first determine what the order is, and then, what the function is. Well, they both change according to our point of view. Let’s look at some examples. Take a look at the following table: CREATE TABLE `log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `error_level` tinyint(4) DEFAULT NULL, `subject` varchar(32) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) In the above log table, log entries are added with id and ts getting automatically evaluated. Assuming no dirty hacks occur, we can expect that ts in monotonic by order of id. That is, as id increases, so does ts. Is is possible that we get the same ts for a few rows (it is not unique), but once it increases, it never decreases again. Why is this interesting? Because it simplifies common problems. For example, it simplifies a search for a given ts value, when no index exists on the ts column. If we were to look for a log entry from ‘2009-02-07 11:58:00′ by simple SELECT, we would have to use a full table scan. But, by knowing that ts is monotonic, we can also use binary search on id. As another example, it simplifies the task of purging all rows up to last midnight. Instead of issuing “DELETE FROM log WHERE ts < DATE(NOW())”, thus using, again, full table scan plus locking all rows (depending on storage engine), we can use other methods: We can detect the id for the first row which holds the condition using binary search, then “DELETE FROM log WHERE id < ###” Or we can slowly work our way in ascending id order, issuing something like “DELETE FROM log WHERE ts < DATE(NOW()) ORDER BY id ASC LIMIT 1000″, and stop once the ROW_COUNT() is less than 1000. We know we need not advance any further, without needing to compute anything. We thus block less, while retaining correctness of our operation. Monotonic functions in MySQL When we iterate InnoDB tables (as in full table scan), we know that rows are iterated in ascending PRIMARY KEY order [¹]. So the PRIMARY KEY dictates the order by which monotonic functions are evaluated. With MyISAM, rows are iterated according to internal storage order. It has nothing to do with PRIMARY KEYs (though depending on concurrent_insert this can be somewhat controlled). It also has nothing to do with chronological order. Newer rows may capture space held by older rows. But MyISAM allows for ALTER TABLE … ORDER BY … syntax, which allows us to do a one-time sort of the table. Assuming no writes shortly thereafter, a full table scan will iterate the rows according to specified order. Monotonic functions and indexes A column which is indexed dictates a monotonic function by index order. Wait. Isn’t that obvious? Of course: if we index a column, then the index sorts by that column, and the column is ascending by the index order which is,… itself. I call that trivial, but it does interest us: because, while mathematically there may be nothing significant here, we do care about this order when we have index scans. So, if we can force an index scan on our query, then we can anticipate the order by which rows are processed; we now have some order by which to evaluate monotonic functions. OK, maybe I made it sound more complicated than it really is. Monotonic functions work well when the order by which they are monotonic is some indexed column(s). The AUTO_INCREMENT PRIMARY KEY we saw in the log example above, it perhaps the most trivial case. While MySQL does not support function indexes, if the functions we consider is monotonic, we still benefit from adding an index on the raw column. Other examples of monotonic functions So, where else can we find them? Timestamp columns are probably the most common (this post holds true until time travel to the past is introduced). But also summaries: like a reporting table which lists down some ever-ascending value (the number of books ever sold in our store; trip mileage; hop counter; etc.). I’ve seen many cases (though difficult to illustrate in this scope) when foreign key values are in ascending order. A very brief example is a 1-1 relation between two denormalized tables, where the tables ids do not necessarily have to match, but is always ascending). And Baron’s wishlist for SQL can also benefit from monotonic functions. Conclusion When a monotonic function is present, it brings an added value to our schema and query design. It allows for less indexing; quicker operations. Look for these. I’ve only discussed increasing functions. Indeed, MySQL’s indexes are always increasing (they cannot be defined in decreasing order), but query simplifications work just as well for monotonic decreasing functions. [¹] I’ve actually seen a different behavior on temporary InnoDB tables and on compressed InnoDB Plugin tables; I’ll write on this on another occasion.

TOTD #121: JDBC resource for MySQL and Oracle sample database in GlassFish v3

This blog clearly explains how to configure the MySQL sample database (sakila) with GlassFish. Even though the instructions use a specific database but should work for other databases (such as Oracle, JavaDB, PostgreSQL, and others) as well. The second half of the blog provide specific syntax for the Oracle sample database. Download sakila sample database and unzip the archive. Install the database as described here - basically load and run "sakila-schema.sql" and "sakila-data.sql" extracted from the archive. Create a new MySQL user account using MySQL CLI Admin and assign the privileges Using "root" user (sudo mysql --user root) CREATE USER glassfish IDENTIFIED BY 'glassfish'; GRANT ALL PRIVILEGES ON *.* TO 'glassfish'@'localhost' IDENTIFIED BY 'glassfish'; FLUSH PRIVILEGES; Using "glassfish" user (sudo mysql --user glassfish) source sakila-schema.sql; source sakila-data.sql; Download Connector/J, unzip and copy "mysql-connector-java-5.x.x-bin.jar" to "glassfish/domains/domain1/lib/ext" directory. Start GlassFish server as: asadmin start-domain Create a JDBC resource Create JDBC connection pool as: asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource --restype javax.sql.DataSource --property "User=glassfish:Password=glassfish:URL=jdbc\:mysql\://localhost/sakila" jdbc/sakilaPool Test the JDBC connection pool as: asadmin ping-connection-pool jdbc/sakilaPool Create the JDBC resource as: asadmin create-jdbc-resource --connectionpoolid jdbc/sakilaPool jdbc/sakila That's it! Creating a JDBC resource for any other database requires the following updates to the steps mentioned above. Lets consider modifying these steps for the Oracle sample database. Use the client interface SQL*PLus and connect as: sqlplus "/ as sysdba" create user and grant the privileges as: CREATE USER glassfish IDENTIFIED BY glassfish DEFAULT tablespace users TEMPORARY tablespace temp; GRANT CONNECT TO glassfish IDENTIFIED BY glassfish; GRANT UNLIMITED TABLESPACE TO glassfish; GRANT CREATE TABLE TO glassfish; GRANT CREATE SEQUENCE TO glassfish; Copy the appropriate JDBC driver (ojdbc6.jar). Create the JDBC resource as: asadmin create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource --property "User=hr:Password=hr:URL=jdbc\:oracle\:thin\:@localhost\:1521\:orcl" jdbc/hr asadmin ping-connection-pool jdbc/hr asadmin create-jdbc-resource --connectionpoolid jdbc/hr jdbc/hr as explained in TOTD #108. Here are a few other related entries: RESTful representation of sakila using NetBeans and GlassFish JPA + Servlet 3.0 Application using Sakila in Eclipse JSF + JPA + EJB Application using Oracle, NetBeans, and GlassFish Technorati: totd javaee glassfish v3 jpa mysql sakila oracle

Today I reached 109k Queries per Second. I was quite impressed by it. Some background on the situation. I developed some stored procedures to process some rather large tables we had in our database. I managed to get the stored procedures to be very efficient and quick. I then wanted to test it out and tried to overload the server to see how much it could take. Normally, the server would do around 1k at best with these kinds of tasks. I have recently been able to tweak it to 20k QPS. But today, for some reason, the cache managed to get itself in the right position and produced this result. The Server: A 4+ year old Dell server, with SAS drives, 1 Quad-core CPU and 16Gbs of memory. Database: MySQL 5.0.48 - with MyISAM tables only The Tasks: Reference a 101 million row table (12+ Gbs) to fill in a column in three 8-9 million row tables (2-5Gbs). Reference a 700k row table to fill in a 7 million row table. So 4 tasks at the same time. Click on the picture to see the full screenshot Applications used: Mtop & Htop

Don't forget the COMMIT in MySQL

Yes, MySQL has transactions if you use InnoDB or NDB Cluster for example. Using these transactional storage engines, you'll have to commit (or roll back) your inserts, deletes or updates.I've seen it a few times now with people being surprised that no data is going into the tables. It's not so a silly problem in the end. If you are used to the defaults in MySQL you don't have to commit anything since it is automatically done for you.Take the Python Database Interfaces for MySQL. PEP-249 says that, by default, auto-commit should be turned off. You could turn it back on, but it's good practice to be explicit and commit in your code. Remember the Zen of Python!Here is just a small example to show it. Uses MySQL Connector/Python, but it does work also with others:import mysql.connectorcnx = mysql.connector.connect(db='test')cur = cnx.cursor()cur.execute("""CREATE TABLE innodb_t1 ( id INT UNSIGNED NOT NULL, c1 VARCHAR(128), PRIMARY KEY (id)) ENGINE=InnoDB""")ins = "INSERT INTO innodb_t1 (id,c1) VALUES (%s,%s)"cur.execute(ins, (1,'MySQL Support Team _is_ already the best',))cnx.commit()cur.close()cnx.close()

I explored two interesting topics today while learning more about Postgres. Partial page writes 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. Detecting data corruption 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:The Ma.gnolia data might not be permanently lost I keep reaWhat data types does your innovative storage engine NOT support? I’vePostgreSQL Conference East 2009, Day Three As I said Related posts brought to you by Yet Another Related Posts Plugin.

News, Jacob's leaves, Assay to Canonical

News Monday! Matt Assay to JOIN Canonical as COOThis took me a bit by surprise at first. I don't find myself often agreeing with Matt. Most of what he tends to write/argue for is what I have referred to in the past as "crippleware". Canonical in recent time has taken to opening up their platform. I've been a strong advocate for Launchpad, it is a great service. I love that they opened it up in recent time. When it comes to infrastructure software on the size of LP, I don't believe that many others will ever install it. Slash, G-Forge, and the Livejournal software are examples of infrastructure software that approach the size or outweigh the LP codebase. They have rarely been successfully deployed by others. The advantage in the Launchpad software being open source is the potential for others to audit the code. I suspect that they will receive some patches, but I doubt that the number of patches will ever out pace what the conical staff itself creates. This morning I got a number of worried pieces of email over Matt's new position at Canonical. Do I find that I am worried about Assay joining Canonical? Not really. The job of the COO is too keep the company moving on a day to day basis. With his background at Alfresco, the COO role makes sense. Canonical has a lot of strong open source advocates so I wouldn't expect change in a direction that would create issue. Canonical's Ubuntu One is their longterm play. Service based revenue work hand in hand with open source go well together (...how many online services can you name that aren't based on open source?). The COO position is one of the key positions that a company will hire for, yet, many smaller companies tend to pass over the creation of this position in lieu of having the CEO also fill this role. This is a real shame since you can often have a great CEO, who makes for a poor COO. Ken Jacobs leaves OracleWhen Innodb was first acquired by Oracle there was a lot of shock and dismay within the MySQ Ecosystem. MySQL INC's reaction to the acquisition, which then rippled to the community, created a mistrust of Oracle. Ken Jacobs really changed that reaction in the community. There has been a number of times over the years that I found myself on the same side of the fence as Ken when it came to both leadership and technical vision about MySQL. I am sure Oracle has other competent executives to fill his shoes, but Ken has been a real asset to Oracle over the years. I am sad to see him leave the ecosystem, he played a very positive role in the community. Oracle buying Innodb was never the killer move most envisioned at the time. It kick started engine development around MySQL, which was the only real innovation we saw for many years. Around the time of the acquisition all but one of the engineers who knew MySQL well, worked for MySQL. Having multiple companies working on engines re-invogorated outside development in the project. Without Oracle buying Innodb, the MySQL ecosystem would have never been forced into an innovators cycle again.

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: PLAIN TEXT CODE: | Com_select                            | 46272       | | Com_update                            | 5214        | | Com_delete                            | 385         | | Com_insert                            | 3468        | | Com_commit                            | 5404        | The result is quite chatty, PLAIN TEXT CODE: |    |     [MEE]    | [DM] |                         [CE]                          | sec. |    TR,    MF |   DM |   BV,    CP,    MW,    SD,    TL,    TO,    TS,    TU | MEEThreads, ReqQueue       (1st line: count, 2nd line: 90%ile response [msec.])  260 |   402,    39,     0,   195,   532,   749,   588,   342,   415,   816,    88 | 30, 0           20,    60,     0,    30,    20,    20,    20,    50,    20,   310,    60    270 |   395,    40,     0,   201,   608,   842,   608,   358,   449,   833,    89 | 30, 0           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 PLAIN TEXT CODE: [TradeResult(TR) transaction] Succeed: 150243 Lated:   0 Retried: 3 Failed:  0   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: | | | |

Automating MySQL access with expect and bash scripting

If you have multiple database servers with strange names, or if you have to hop over multiple machines to connect to any mysql database server, then you know what a pain it can be to administer such a setup. Thanks to some scripting, you can automate such tasks as follows: Create an expect script: /path/to/sshmysql.exp #!/usr/bin/expect -f #script by darren cassar #mysqlpreacher.com set machine [lindex $argv 0] set timeout -1 spawn ssh username@$machine match_max 100000 expect -exact “assword: ” send — “password\r” send — “sudo -k; sudo su – mysql\r” expect -exact “sudo -k; sudo su – mysql” expect -exact “assword:” send — “password\r” interact # you should change the word password in ’send — “password\r”‘ to your login password # if you have the same password for each environment you could also script logging into mysql directly from the same expect script BUT that is not recommended. Create a bash script: /path/to/login.sh #!/bin/bash #script by darren cassar #mysqlpreacher.com sm=’/path/to/sshmysql.exp’ menu() { echo ” 101 – dev.databaseserver1 ” echo ” 102 – dev.databaseserver2 ” echo ” 103 – dev.databaseserver3 ” echo ” 201 – qa.databaseserver1 ” echo ” 301 – uat.databaseserver1 ” echo ” 302 – uat.databaseserver2 ” echo ” 401 – prod.databaseserver1 ” echo ” ” } ARGUMENT=notmenu if [ -z "$1" ] then ARGUMENT=menu else choice=$1 fi if [ $ARGUMENT = "menu" ] then menu else case “$choice” in 101|dev.databaseserver1 ) $sm dev.databaseserver1;; 102|dev.databaseserver2 ) $sm dev.databaseserver2;; 103|dev.databaseserver3 ) $sm dev.databaseserver3;; 201|qa.databaseserver1 ) $sm qa.databaseserver1;; 301|uat.databaseserver1 ) $sm uat.databaseserver1;; 302|uat.databaseserver2 ) $sm uat.databaseserver2;; 401|prod.databaseserver1 ) $sm prod.databaseserver1;; * ) echo “Wrong value passed to script” menu ;; esac fi alias l=’/path/to/login.sh’ Output: [darrencassar@mymachine ~ ]$ l 101 – dev.databaseserver1 102 – dev.databaseserver2 103 – dev.databaseserver3 201 – qa.databaseserver1 301 – uat.databaseserver1 302 – uat.databaseserver2 401 – prod.databaseserver1 Output: The below command would log you into the first development database server as mysql user. [darrencassar@mymachine ~ ]$ l 101 On each machine place aliases for each instance in the .profile alias use3306=’mysql -u root -p -h 127.0.0.1 -P 3306 –prompt=”mysql \D> “‘ The above setup can be used using any client/server OS: Linux, Solaris, MAC OS or Windows(running Cygwin) NOTE: If you store the password in clear text inside the expect script, you should at least save the scripts inside an encrypted partition on your machine and make sure that folder is not shared or accessible by anyone. Another way of doing it would be to use either SSHKeys OR save the password inside a file and encrypt it using OpenSSL Enjoy!

One of the issues I have with the Free Software approach is that advocates have habit of throwing the baby out with the bathwater when discussing issues that they see as in any way negative to free software. I was reminded of this while reading Bradley M. Kuhn’s criticism of Mark Shuttleworth’s reported views on copyright assignment. Having read the original interview with Mark, and then Bradley’s response, it is pretty clear that the two have very different perspectives on copyright assignment: Mark is speaking from the perspective of a commercial business, Bradley form that of a non-profit foundation. The two entities have very different reasons for enforcing copyright assignment policies, and Bradley is right to point out that a potential contributor should approach a copyright assignment policy from a commercial entity with a great degree of caution. However, the ultimate reason for enforcing copyright assignment is about control. From a vendor’s perspective the desire for control is often to produce closed versions of the code. From the FSF’s perspective the desire for control is about keeping the code, and derivatives of it, open. However, the fact that the FSF “promises to never proprietarize its versions of the software assigned to it”, does not support Bradley’s assertion that Mark “wants to confuse us about copyright assignment so we just start signing away our software”. This claim is especially problematic given that Mark appeared (and it must be said we are reliant on the reporting of his statements to understand what he meant by them) to be attempting to reduce confusion around copyright assignments by, if possible, introducing some sort of standardization. This is a suggestion that deserves more consideration. However, Bradley is so busy protecting the FSF from being maligned by Mark that he completely ignores the point raised by Mark - that copyright assignment policies are confusing, complex, and potentially problematic. As the iTWire report demonstrates, the issue of copyright assignment is not just one that impacts contributions by individual developers (which is a common way of looking at it) but also of contributions from employees of Canonical to projects led by the likes of MySQL, Zope, Novell, Red Hat, Intel and others. As previously noted, Oracle’s acquisition of Sun, and with it MySQL, has highlighted the issue of copyright control in encouraging/restricting community development in vendor-led development projects and providing acquirers with the potential to close an open source project. Clearly, the issue is not as problematic for non-profit foundation-led projects, but the issue of copyright assignment needs more thoughtful assessment than a response that amounts to “non-profit=good, for-profit=bad”. For more considered analysis of the issue of copyright assignment see: Dave Neary: Copyright assignment and other barriers to entry CAOS Theory: On the importance of copyright assignment Daniel Chalef: OSBC, Community Engagement and Contributor Agreements Michael Meeks: Some thoughts on Copyright Assignment Tarus Balog: More on Copyright Assignment

Ken Jacobs a great advocate of the database user communities

Ken Jacobs has been a fantastic advocate of the Oracle and MySQL user communites.  I met Ken on the board of the Independent Oracle Users Group (IOUG).  While Ken was the board liasson on the IOUG board, he was always supporting the Oracle user groups and made very important contributions throughout his time on the board and afterwards.  After serving time on the board, Ken was still always

What does it take for someone, fiercely loyal to a company to suddenly leave? Ken Jakobs, Oracle employee number 18, a man that sincerely loves the company, has resigned! The only reason I can think of is an extreme snub!I must say, I am very disappointed. The prospect of Ken running MySQL was a light at the end of the tunnel for the community. Why? Because Ken is a MySQL insider! He knows the project, he knows the community.As an engine developer I have come to know Ken well over the last 4 years. He lead the InnoDB team and is largely responsible for the improvements made to the engine since the Oracle acquisition. At the yearly Engine Summit he was always professional and constructive in his suggestions, with a deep technical knowledge of the subject. His track record shows that he has always kept his word with regard to Oracle's intensions with InnoDB, and I would trust him to do the same with MySQL.Goodbye Ken. This is great loss for both the MySQL community and Oracle!

Python, oursql and MacOS X 10.6 (Snow Leopard)

This post explains how to compile oursql and install it on MacOS 10.6. oursql is a Python database interface for MySQL, an alternative to MySQL for Python (i.e. MySQLdb) and MySQL Connector/Python.First, find out which MySQL you installed. This can be either the 32-bit or the 64-bit version. To make sure, find the mysqld (e.g. in /usr/local/mysql/bin) and do the following in a Terminal window:shell> file /usr/local/mysql/bin/mysqld.../mysqld: Mach-O 64-bit executable x86_64If you see x86_64, you got 64-bit, otherwise 32-bit. If you see both, then you have a universal build. This is important for specifying the ARGSFLAG when building.Download oursql from Launchpad and unpack it into some directory. Using the information from above, you'll have to do following for 64-bit platform (or universal build) in a Terminal window: shell> ARCHFLAGS="-arch x86_64" python setup.py build shell> sudo python setup.py installFor 32-bit, you'll have to do: shell> ARCHFLAGS="-arch i386" python setup.py build shell> sudo python setup.py installFollowing error will be reported when you don't specify the correct ARCHFLAGS: ld: warning: in .../lib/libmysqlclient.dylib, file is not of required architectureTips:When building failed, it is good to remove oursql, unpack it and try again.If you don't want to compile anything, or run into more troubles, give MySQL Connector/Python a try (alpha releases). It's a pure Python implementation of the MySQL Client/Server protocol and doesn't need compiling or a MySQL installation.You can download MySQL from either www.mysql.com or dev.mysql.com.

I wanted to offer another InfiniDB load rate metric using the SSB lineorder fact table.  In this case we are using a scale factor of 10,000 which translates to 60 Billion rows.  As a point of reference, the recent Percona benchmark was at a scale factor of 1000 (6 billion rows) http://www.mysqlperformanceblog.com/2010/01/07/star-schema-bechmark-infobright-infinidb-and-luciddb/ . The load rate per hour varied only slightly across the entire run, averaging about 4Read More...

Poor Matt, poor Ken

Well, for Matt Asay, I should start by congratulating you for the new job and nice title! (Also, we learn some intelligence from Matt's blog: apparently Canonical is already close to the size of MySQL AB at the time of the Sun acquisition.) Usually we are told to "ignore the trolls" and all that. The blogosphere unfortunately seems to be full of commentators who like to have share their opinion - even while they are entirely clueless. Sometimes, like the comments on Slashdot, it is ok and considered part of the entertainment. Sometimes it is harmless, because nobody reads that blog. And sometimes, it is just unacceptable: read more

Here is one more procedure – (this time) for mass renaming of table. Adding and Removing table-name prefixes A friend of mine was renaming 100+ tables by using replace methods in notepad. I showed em a bit better editor: Editplus and then I thought of rescue rest of those who are still interested in some [...] Related posts:Stored procedure – Execute query if table or Column exists Stored procedure to Find database objects Search through all databases, tables, columns in mysql

Ken Jacobs leaves Oracle

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?

We received some nice feedback on our care and feeding of InfiniDB blog entry, and we appreciate all of you who were kind enough to respond. We did fail, however, to communicate a few other intentions we have regarding how we plan to release and label the InfiniDB software so here’s some more thoughts from us on this important matter:For new releases, we plan to follow the traditional alpha, beta, RC framework. Alpha means an upcoming release is not yet feature complete and moreRead More...

How often should you use OPTIMIZE TABLE?

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:Analyze and optimize memcached usage with Maatkit Ryan posteExtended covering indexes As you canThe difference between a unique index and primary key in MySQL There Related posts brought to you by Yet Another Related Posts Plugin.

In case if you working on Windows environment for MySQL development (sometimes I use visual studio for easy debugging); and in case if you change the parser code (sql_yacc.yy) or if you are working directly from development branch (bzr launchpad), then the build breaks to generate the yacc files (sql_yacc.h and sql_yacc.cc) with an error bison: M4: Invalid argument as shown below: 1>------ Build started: Project: sql, Configuration: Debug Win32 ------ 1>Generating sql_yacc.h, sql_yacc.cc 2>------ Build started: Project: GenServerSource, Configuration: Debug Win32 ------ 2>Generating sql_yacc.h, sql_yacc.cc 1>bison: m4: Invalid argument 1>Project : error PRJ0019: A tool returned an error code from "Generating sql_yacc.h, sql_yacc.cc" 1>sql - 1 error(s), 0 warning(s) 2>bison: m4: Invalid argument 2>Project : error PRJ0019: A tool returned an error code from "Generating sql_yacc.h, sql_yacc.cc" 2>GenServerSource - 1 error(s), 0 warning(s) But if use source zip file for any particular release, then it won’t fail as the files (sql_yacc.cc and sql_yacc.h) are pre-built and copied to the distribution zip file. But, again if you wanted to change the code or happen to save sql_yacc.yy, then it starts generating the files and build will break. It looks like lot of people are experincing the same problem to build parser code on Windows using any recent version of bison (not just MySQL code base). Both bison.exe and m4.exe are in the path and they are the latest version; but still it fails.. c:\mysql-5.1\sql>which bison C:\Gnu\GetGnuWin32\gnuwin32\bin\bison.EXE   c:\mysql-5.1\sql>which m4 C:\Gnu\GetGnuWin32\gnuwin32\bin\m4.EXE   c:\mysql-5.1\sql>bison --version bison (GNU Bison) 2.4.1   c:\mysql-5.1\sql>m4 --version m4 (GNU M4) 1.4.13 It looks like the problem is with Windows version of bison to pick m4 executable even though m4 is in the path. For example, you can directy try to generate the files from sql directory using bison as… c:\mysql-5.1\sql>bison -y -p MYSQL --defines=sql_yacc.h --output=sql_yacc.cc sql_yacc.yy bison: m4: Invalid argument The work around what I found is to copy m4.exe to sql directory directly, so that bison can pick from local working directory, then everything starts working as expected. c:\mysql-5.1\sql>bison -y -p MYSQL --defines=sql_yacc.h --output=sql_yacc.cc sql_yacc.yy bison: m4: Invalid argument   c:\mysql-5.1\sql>ls -al sql_yacc.* -rw-rw-rw- 1 venu 0 413012 2010-02-07 11:58 sql_yacc.yy   c:\mysql-5.1\sql>which m4 C:\Gnu\GetGnuWin32\gnuwin32\bin\m4.EXE   c:\mysql-5.1\sql>copy C:\Gnu\GetGnuWin32\gnuwin32\bin\m4.EXE . 1 file(s) copied.   c:\mysql-5.1\sql>which m4 c:\mysql-5.1\sql\m4.EXE   c:\mysql-5.1\sql>bison -y -p MYSQL --defines=sql_yacc.h --output=sql_yacc.cc sql_yacc.yy   c:\mysql-5.1\sql>ls -al sql_yacc.* -rw-rw-rw- 1 venu 0 1510389 2010-02-07 14:33 sql_yacc.cc -rw-rw-rw- 1 venu 0 30532 2010-02-07 14:33 sql_yacc.h -rw-rw-rw- 1 venu 0 413012 2010-02-07 11:58 sql_yacc.yy Kind of weired, but atleast there is a work around to change the parser code on Windows now; and it works great including Visual studio also starts building without any errors. But if you remove m4.exe from sql directory, then things starts to break immediately.

Oracle loses some MySQL mojo

Oracle veteran and MySQL sympathizer Ken Jacobs has resigned from the database giant, calling into question Oracle's ability to deliver on its MySQL promises.

One way to do a “Conditional Join” in MySQL is by using a “LEFT JOIN”.  Create a “LEFT JOIN” for each condition and combine the results into one column using an “IF” statement by the “SELECT” expression.  Here’s an example: Suppose you have three tables: questions: a table consisting of question ids, timestamps, and whether or [...]

How To Set Up MySQL Database Replication With SSL Encryption On Ubuntu 9.10

How To Set Up MySQL Database Replication With SSL Encryption On Ubuntu 9.10 This tutorial describes how to set up database replication in MySQL using an SSL connection for encryption (to make it impossible for hackers to sniff out passwords and data transferred between the master and slave). MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

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

FOSDEM: 'Connecting MySQL and Python', handout & wrap-up

Apparently, my talk at FOSDEM 2010 about Connecting MySQL and Python was the only one about Python? There should be more, or?I have a hand-out ready in PDF. The slides are not usable without my chatter. It contains a few examples and links. Any comments, corrections, criticism.. are welcome!The longer version of this talk will be given at the O'Reilly MySQL Conference&Expo 2010 in Santa Clara, California (USA).

Between following (from a distance) the talks at Fosdem and anticipating the ones at MySQL User Conference in April, I was reminded of 2 interesting MySQL talks that have had a deeper meaning to me than their original speakers probably intended. I thought today could be a good time to share these 2 stories that for me personally are filed in the "things I learned from MySQL AB and Sun" folder... "If you can't solve the problem, try solving some other problem" read more

10x Performance Improvements in MySQL – A Case Study

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. 10x Performance Improvements – A Case Study View more presentations from Ronald Bradford.

The last few weeks I am still being asked what is going on with Oracle and MySQL and where is MySQL with it's software releases.  So I am going to include some URLs to hopefully answer some of your questions regarding MySQL. Oracle's press release on December 14, 2009 regarding MySQL.http://www.oracle.com/us/corporate/press/042364 Summary list MySQL software releases. A more detailed list can be

State of phpMyAdmin – FOSDEM 2010

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 Dolphins Now And Beyond – FOSDEM 2010 View more presentations from Ronald Bradford.

MySQL Meetup Group - Back in the Bay!

The recently revived MySQL Meetup group had its first meeting. A big thanks to Venu Anuganti, our host and Schooner our sponsor. If anyone is interested in joining the fun please check out the Meetup.com site here. During our first meetup we went over basic MySQL topics including configuration, performance tuning, locking, along with running MySQL in the cloud. All in all it was a well done presentation and I am looking forward to the next.Thanks again for getting the Meetup going!Chris

In MySQL Workbench, there’s the standard tokens (table, stable, dtable, column) that you can use to help customize your naming conventions of your columns, keys, etc.  What you can also do is apply/pipe filters to these tokens.  The filters are upper, lower & capitalize.  You can see in the image above how they can be applied.

Monitoring for errors

I am trying to debug a server with a high rate of Aborted connects reported in SHOW STATUS.This frequently occurs when the maximum number of concurrent connections, max_connections, has been exceeded. Alas, this server does not have too many connections, I have no problem creating a connection and there are connections for all accounts so the password table is probably valid. It is time to read the source code. The aborted_connects counter is incremented when: the call to pthread_create in create_new_thread() fails. This creates a new thread to handle the new connection and failure is unlikely. the call to my_thread_init() in handle_one_connection() fails. This is unlikely. the call to THD::store_globals() in handle_one_connection() fails. This is unlikely. the call to check_connection() in handle_one_connection() fails. This is likely. At this point I assume the failure is in check_connection() but there are many reasons for that to fail: return ER_BAD_HOST_ERR when vio_peer_addr() fails return ER_OUT_OF_RESOURCES when memory allocation fails return ER_HOST_IS_BLOCKED when there were too many connection errors for this client's host. The counters for this are reset when FLUSH HOSTS is run. return ER_HOST_NOT_PRIVILEGED when the client's host is not allowed to connect return ER_HANDSHAKE_ERROR when there is a network during authentication (read or write fails, not enough data) return an error when check_user() fails Running FLUSH HOSTS does not fix the problem and I don't think memory allocation is failing. check_user() calls mysql_change_db() and that can fail for several reasons: return ER_NO_DB_ERROR because a db name was not specified return ??? on memory allocation errors return ER_WRONG_DB_NAME because the format of the db name is bad return ER_DBACCESS_DENIED_ERROR because the user is not allowed to access the db return ER_BAD_DB_ERROR because the filesystem directory for the db does not exist or cannot be accessed Finally, there are several reasons for a failure in check_user() other than failures from mysql_change_db(): return ER_HANDSHAKE_ERROR when there is not enough data in the authentication packet or network writes fail return ER_CON_COUNT_ERROR when there are too many concurrent connections (max_connections) return ER_TOO_MANY_USER_CONNECTIONS when max_user_connections has been exceeded return ER_USER_LIMIT_REACHED when there were too many connections, updates or queries in the last hour for this account return ER_NOT_SUPPORTED_AUTH_MODE when the client uses the wrong a short hash to authenticate return ER_ACCESS_DENIED_ERROR when authentication fails That is a lot of potential errors. How do I find the problem? It would be easy if I had access to all client hosts and all client software logged all errors. That is rarely true for large deployments. It would be easier if I had SHOW USER_STATISTICS which had a few counters for authentication failures per user. From that I could determine whether the problem was limited to a known account. However, even that doesn't count some of the errors including attempts to use accounts that don't exist. I want to know which errors occur most frequently. For that I need the command SHOW ERROR COUNTS. Alas, that command does not exist. Otherwise, I need to figure out how to count errors by parsing tcpdump output. Time to assign the problem to someone else.

Not quite pop quiz format, but if you enjoyed the ones I published some time ago (almost 2 years ago now… how time flies), you’ll probably be interested to know that (more…)

Knowing your PERC 6/i BBU

I’ve recently become supremely disappointed in the availability of Nagios checks for RAID cards. Too often, I see administrators rely on chance (or their hosting provider) to discover failed drives, a dying BBU, or a degrading capacity on their RAID cards. So I began work on check_raid (part of check_mysql_all) to provide a [...]

Topics for this podcast: *Matt Asay moves from Alfresco to Canonical *GPL fade fuels heated discussion *Apple’s iPad and its enterprise and open source impact *Open source in data warehousing and storage *Our perspective on Oracle’s plans for Sun open source iTunes or direct download (32:50, 9.2 MB)

MySQL Cluster Performance Tuning Best Practices – Webinar replay available

For anyone that missed the recent webinar on getting the best performance out of MySQL Cluster then the replay is now available from mysql.com. Benefits of connection pooling Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries? Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance when using MySQL Cluster. It discusses guidelines and best practices covering the following areas: General Design Concepts and Guidelines Schema Optimization BLOB/Text vs VARBINARY/VARCHAR Partition by Key Index Selection and Tuning Basic Query Tuning MySQL Cluster Parameter Tuning Guidelines Tools to accelerate application development and testing

Welcome, everyone, to the 177th edition of Log Buffer, the weekly review of database blogs. It was another week heavy with technical posts, so let’s waste no time, and get it all started with . . .  PostgreSQL David Fetter shares his recipe for adding only new rows: “Let’s say you have a table and a data set, and would like to add only those rows in your data set that aren’t already in the table. There are hard ways, but here’s an easy one.” Simon Riggs, the Database Explorer, offers his thoughts on parallel query in Postgres: “I’m disappointed we’ve not made much progress with parallel operations and partitioning in core Postgres in last few releases. Recent Greenplum results show we have much work to do in improving things.” David Christensen shares a PostgreSQL tip: using pg_dump to extract a single function. Roppert Kalmar shares a screenshot of the new Sun Oracle PostgreSQL. That doesn’t even sound right, does it? Oracle Let’s begin with Chen Shapira, since she is so excited about the NoCOUG Winter Conference, and here gives a quick rundown of what attendees can expect to see. Contrariwise (or not), Iggy Fernandez offers five reasons not to attend a NoCOUG conference in 2010. “Most Oracle professionals will benefit a lot from attending a NoCOUG conference in 2010. However, the following categories will not benefit much:  . . .  Those Oracle professionals who believe that Oracle’s goal in buying Sun is to replace Oracle Database with MySQL. This is probably a very small group . . . ” Uwe Hesse, the Oracle Instructor and his readers share a discussion on sharing READ ONLY Tablespaces between databases. Uwe says, “ . . . the question was raised, whether it is possible to use the same READ ONLY Tablespace in multiple Databases. At first glance, I thought that this should of course be possible, though the answers where somewhat discouraging. So I have done a quick test to prove it . . . ” Krishna Manoharan of Performance Engineering and Capacity Planning steps up with an item on understanding CPU time as an Oracle Wait event. Krishna pursues the question, “[What] if the stats from the system show that CPU Utilization (% Util and Run queue) are well within thresholds and show plenty of available capacity, but Oracle continues to report CPU time as a Top 5 wait event?” Charles Hooper grapples the question, Which Plan is Better? Charles writes, “A recent post appeared in the OTN forums that indirectly asked the question: which execution plan is better?  . . .  If you are attempting to conclude which plan is faster/better based on the estimates in [a] first plan and an altered plan with a hinted cardinality estimate, you might be setting yourself up for failure.” “Seems like all I ever write about these days is SQL Profiles,” writes Kerry Osborne. “I do other stuff, honest! It just seems like getting Oracle to do what you want when you can’t touch the code is the closest thing to ‘Magic’ that DBAs get to do.” Here’s Kerry’s post on single-hint SQL profiles, inspired by a discussion with Jonathan Lewis. And now here is Jonathan with a post on SQL Server.  . . .  Wait a minute–what the?! SQL Server Ahem. And now here is Jonathan Lewis with a post on SQL Server. Yes, Jonathan Lewis, famous Oracle guy. “A few days ago,” Jonathan writes, “I did a presentation on SQL Server.  . . .  The title was ‘What the Enterprise needs in an RDBMS’ . . . and the presentation was about whether or not you could find everything you needed in SQL Server 2008, where you’d have to look in the manuals, and supplementary questions you’d have to ask.” Buck Woody also has been, as it were, treading the boards: “I give series of classes and presentations on Data Design. I say ‘data’ design instead of ‘database’ design because we should consider more than just the database.  . . .  Here are the links I use in that presentation. Although this isn’t a comprehensive list of Data Design topics, I’ll visit this topic from time to time so you may want to bookmark this page in your favorites[.]” Merrill Aldrich admonishes, don’t get burned by replication of SQL Server files: “ . . . if you try to use file system replication (robocopy, xcopy, repli-whatever) to maintain a DR server from your production SQL Server, you might be in for a nasty surprise.” Buck Woody relays more nastiness in his post, Transparent Data Encryption and the Latest Data Breach: “Well, It’s happened again. Hundreds of thousands of private records were stolen from a database. This one, however, was different. No one stole any passwords, no one did any social engineering, nothing was captured in-line. No, this one was accomplished by stealing the actual hard drives themselves!” One way to circumvent this–no hard drives! But wait–before you send them all to the kilns, Aaron Bertrand says, your laptop may be ready for SSDs, but are your SQL Servers? “I am not trying to be Debbie Downer here,” he explains,” … SSDs sound great . . .  But right now, if you are looking at expanding or upgrading your I/O under SQL Server, I’d give the vendors some time to shake off these early jitters.” Here’s Thomas LaRock, DBA Survivor, with a Name That Caption Contest. The deal is: “The person who provides the best caption will win a copy of my book,” DBA Survivor, “and I will figure out a way to incorporate your caption into the main page.” MySQL On Serge’s blog appears a useful item on using UNIX_TIMESTAMP as a partitioning function for MySQL 5.1.43 Falko Timme shares How To Back Up MySQL Databases With mylvmbackup On Debian Lenny on HowtoForge. Simon Mudd has some further thoughts on mysql upgrades. From atop his MySQL Soapbox, Lachlan Mulcahy address the matter of a replicate-do-db gotcha, discovered lurking in the config file. Gotcha again. Dathan Vance Pattishall says, “INNODB has some irritating gotchas that makes disk space management hard. In 2002ish INNODB, added innodb_file_per_table to get around allot of these issues, but it does not fix everything.” The post is innodb_file_per_table, shrinking table spaces and the data dictionary. On EXPLAIN EXTENDED, Quassnoi examines some details of join on overlapping date ranges in answering the question, “Is there any way to optimize the query for overlapping ranges in MySQL if both ranges are dynamic?” Last, The InfiniDB Team Blog announces that InfiniDB 1.0 is Now Available! That’s all there’s room and time for. Let’s hear your favourite DB blogs from this week, and we’ll see each other again in the next one. Till then!

451 CAOS Links 2010.02.06

Matt Asay joins Canonical. Paula Hunter joins the CodePlex Foundation. And more. Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca “Tracking the open source news wires, so you don’t have to.” # Matt Asay joined Canonical as chief operating officer. # Paula Hunter was named executive director of the CodePlex Foundation. # Actuate recorded $6.5m in BIRT-related business for Q4; annual BIRT-related business of $18.2m up 18%. # Glyn Moody outlined The Great Oracle Experiment. # The Symbian Foundation confirmed the 100% open source Symbian platform. # Zarafa’s Collaboration Platform is to be packaged for Ubuntu and Fedora. # Jaspersoft 3.7 Community release is now available. # Oracle updated its Oracle Enterprise Pack plug-ins for Eclipse. # CBR published an interview with Novell CEO on the company’s new strategy. # Nuxeo released its open source Digital Asset Management offering Nuxeo DAM. # Oracle is discontinuing access to Project Kenai, Sun’s open source project-hosting site. # Jonathan Schwartz explained his departure from Sun: “Financial crisis/Stalled too many customers/CEO no more.” # Funambol released version 8.5 of its mobile data sync and collaboration platform. # Sauce Labs added a number of Python and Jython core committers to its team. # OSOR.eu is offering public administrations access to more than two thousand free and open source applications. # INSIDE Contactless is making its Open NFC protocol stack available using the Apache License. # Bradley M Kuhn provided his views on copyright assignment. # Black Duck Software was awarded a patent for automatically resolving software license obligations and conflicts. # Greg Kroah-Hartman published Android and the Linux kernel community. # Monty Widenius’s view on what to expect next from Oracle-MySQL. Parts one and two. # Facebook released HipHop, a source code transformer for PHP.

Even though things have been a tad bit turbulent around here in the recent weeks, our engineers did not rest and churned out an impressive number of updates and new releases of the MySQL Server and related products. Here's a quick summary of what we released this year so far (in chronological order): MySQL Server 5.1.42 MySQL Server 5.5.1 Milestone 2 aka "Betony" MySQL Workbench 5.2.14 Beta 4 MySQL Connector/J 5.1.11 MySQL Proxy 0.8.0 MySQL Workbench 5.2.15 Beta 5 MySQL Server 5.0.90 MySQL Server 5.1.43 MySQL Cluster 6.3.31 MySQL Cluster 7.0.11 MySQL Cluster 7.1.1-beta Kudos to the developers! Source and binaries can be downloaded from the usual place. Enjoy! We welcome your feedback and bug reports.

MySQL Cluster Uses

MySQL Cluster can be used as a general purpose transactional storage engine, but if you convert all your InnoDB tables to it and connect your application straight to it you may not see the performance you were hoping for.  This is because MySQL Cluster was originally designed for real-time telecommunications applications (such as RADIUS servers).  It has slowly been modified to become more general purpose and improvements are being made every day but there are still some performance considerations which go with this.  In some cases tweaking your schema and/or queries can help performance dramatically, so I shall try and outline some of things to watch for here. Indexes The fastest type of lookup you can do in Cluster is a primary key equality lookup (ie. SELECT * FROM table WHERE pkey = 2).  This is because the primary key is stored as a hash index as well as an optional ordered index.  This hash index is used to partition the data between the data nodes.  MySQL is smart enough to process the hash and go directly to the data node with the data. When running a query which uses an ordered index (or unique hash index) the query is sent to the Transaction Coordinator in one node which then asks the Local Query Handlers in one or more nodes to return rows that match this query.  The Transaction Coordinator is aware of the indexes so knows which nodes to ask to process the query.  This in general is slightly slower and can actually perform worse for ordered indexes as more data nodes are added because more Local Query Handlers need to be contacted. Finally if the query is a table scan the Transaction Coordinator must ask all the Local Query Handlers to search for the data.  This is much slower. Joins Joins in MySQL Cluster do not currently perform well.  Internally the second table must be queried to match every row returned by the first table, this can mean a lot of network traffic which can slow things down.  There is work in progress to improve this by pushing the join condition down into the data nodes, this will give a massive performance increase when using joins.  More information about this (called SPJ) can be seen in Jonas' blog. BLOBs and TEXT As I have already mentioned in this blog BLOBs (and TEXT) columns require a separate table to hold most of the BLOB data.  This can cause performance problems and locking issues so if possible VARCHAR or VARBINARY should be used instead. Transactions MySQL Cluster currently only supports READ COMMITTED transaction isolation level so if you are used to REPEATABLE READ that you typically get from engines such as InnoDB you may want to check that this will not cause problems with your application. In general we recommend transactions should be short, limited to just a few queries.  MySQL Cluster can give temporary errors for a number of reasons, these do not happen often but will mean that the entire transaction will need to be started again.  For many applications it will be very hard to replay a transaction which has been running for a whole hour.  Also committing very large transactions can lead to GCP Stop.

I am in Brussels, waiting to attend FOSDEM 2010, one of the biggest open source gatherings in Europe, taking place this weekend in Brussels.On Sunday, there is a Developers Room for MySQL and Friends, with 14 talks from open source professionals coming from Europe and North America.The novelty of this round of talks is that thy will be 20 minutes long, rather than 1 hour. This will force all presenters to be more cautious about their timing, and to concentrate their talks on the essential. Even the experienced ones, who have given the same talk several times, will have to make an effort to come to the point in less time. The idea cam from reading Scott Berkun's book, Confessions of a public speaker, where he argues successfully on the usefulness of short lectures. It's going to be interesting! The hosts of the Developers Room are Ronald Bradford and myself, but nothing of this could have happened without the excellent preparatory work done by Lenz Grimmer, who can't be here to enjoy the results of his organization, because he must stay home, waiting for his second child to come any moment. Thanks, Lenz, and good luck! In addition to the DevRoom, I will have a lightning talk on an unusual (for my public speaking record) topic: Blaming the unknown: a positive approach to technology. If you happen to be around, come see it. It's fun, I promise you, and also informative, or so I hope.

MariaDB 5.1.42 released!

Dear MariaDB users, MariaDB 5.1.42, a new branch of the MySQL database which includes all major open source storage engines, myriad bug fixes, and many community patches, has been released. We are very proud to have made our first final release, and we encourage you to test it out and use it on your systems. For an overview of what’s new in MariaDB 5.1.42, please check out the release notes. For information on installing MariaDB 5.1.42 on new servers or upgrading to MariaDB 5.1.42 from previous releases, please check out the installation guide. MariaDB is available in source and binary form for a variety of platforms and is available from the download pages. It is also our pleasure to announce that we have a partnership with Webyog to offer their tools for trial and at a discounted rate if purchased within 30 days. Find out more at: Download – SQLyog MySQL Fronted, MONyog MySQL Monitoring Tool or via the software partner downloads. We welcome and appreciate your feedback, bug reports, bug fixes, patches, and participation on our mailing list. Find out more about working with the community. Enjoy! Related posts:MySQL on Leopard OS X 10.5 PrefPane fixed! MySQL with yaSSL vulnerability MySQL Connector/PHP for MySQL 5.0.24 and PHP 5.1.5 released

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:Thank you for the MySQL 5.4 Community Release MySQL 5.4 How to find per-process I/O statistics on Linux Newer LinuMySQL Enterprise/Community split could be renewed under Oracle One of MyS Related posts brought to you by Yet Another Related Posts Plugin.

On Testing MySQL Knowledge

I can't read posts like this one without at least a little chuckle. Is the number one question you should be asking people how to start and stop MySQL on Windows? How does that really demonstrate how good someone is at their job when most people deploy on Linux[1]?The original MySQL certification for 4.1 used to ask a whole bunch of trivia exactly like this - my favourite was a question where you had to say if a particular subqueries caused a syntax error. I don't know how this tests skill, since most subqueries shouldn't be used in production (hint: they are unoptimized in MySQL).But MySQL changed its certification format: the new exams are Performanced-Based. This means to pass, you have to solve some of the problems you will be doing in real life. Hats off to Dave for leading this initiative.Technical interviews need to change just as MySQL has. They should be organized in a way that doesn't intimidate the candidate who might know what they are doing, but can't always express it words when under pressure. Silly questions and 'gut feelings' about responses tend to favour the over confident.I had a hand in designing the interview process at Percona. One of the steps candidates go through is a challenge to be completed on two running EC2 instances. I don't think it's flawless, but you tell me what is likely to be a better indication of talent:Test #1: * What does tee command do in MySQL? * What is a serial data type in MySQL? * If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table?Test #2: * Log into these two servers (xxx is the master, yyy is the slave). * Tell me if you think there is a replication problem. * Resync the slave using the lowest impact method possible if there is. * Optimize these two queries while you are at it.Test #2 isn't the actual test we use, but it's not far off.I know a lot of DBAs that probably can't answer test #1 correctly. Does this mean they are bad at their job? That is one possibility, but the more likely is that this test is useless and should only come out on Pub Trivia night.[1] On an unrelated note, the example answers for questions 3, 11, 12, 14, 15, 22 also demonstrate a misunderstanding ranging from small to just fundamentally wrong.

Answering questions asked on the site. Daniel asks: I have a table which stores track titles in a VARCHAR(200) field. The field is indexed, but searching for titles beginning with a letter Z is noticeably slower than for those beginning with A, and the closer the initial letter is to Z, the slower is the query. My understanding is that a full table scan occurs, but EXPLAIN shows that the index is used. Besides, the table is quite large but the query is still reasonably fast. Could you please explain this behavior? MySQL stores its indexes in B-Tree data structures. The Wikipedia link above explains the structure quite well so I won’t repeat it. I’ll rather draw a picture similar to the one in the article: This picture is quite self-explanatory. The records are sorted in a tree order, so if you are searching for a certain value, say, 11, you, starting from the first page, should find the link to follow. To do this, you need to find the pair of values less than and greater than 11. In this case, you should follow the link which is between 8 and 12. Then you search for the next pair, etc, until you find your value or reach the end and make sure that your value is not there. Following the links is quite simple, but how does the engine search for the values within one page? This depends on how you declared the table. MyISAM supports two algorithms for storing the index keys in a page: packed keys and unpacked keys. Unpacked keys are what you are seeing on the picture above: each page just stores the key values and the links to the pages down the tree. This is very simple. Packed keys are designed to improve performance on character data. Many words and phrases, especially those that are close to each other, start with the same sequence of characters. If you are going to store track names like: The Man Who Sold The World The Man Who Invented Himself The Man Who Has Everything , MyISAM can optimize it in terms of storage space and store them like this (The Man Who) Sold The World (×11) Invented Himself (×11) Has Everything This is called key compression: instead of repeating the key value for each record, MyISAM just stores the longest common prefix once and prepends the subsequent records that share it with its lengths. This makes the keys shorter and the index more compact. However, this affects the index search time. With an unpacked index, a binary search is applied to find the keys within each level page. With a packed index, this won’t work: you need to know the value of two keys to compare them and not every record contains full information about the key. So in case of a packed index, MySQL remembers the value of the prefix and iterates the records one by one. This is less efficient than a binary search, but due to the fact that much more records can fit on on page, this keeps the amount of page traversals to a minimum and overall efficiency increases. But the records on one page still need to be compared and searched for. And with a linear search, the keys with less values tend to require less iterations than those with greater values. Let’s look on the picture above again. The keys are searched left to right. To search for key 1, we only need two comparisons: compare to 4, get the next page, compare to 1. But to search for key 15, we need to compare to 4, 8, 12 then get to the next page and compare to 13, 14 and finally to 15. This is 6 operations compared to 2 required to fetch the first key. Now, let’s create the sample tables and see some figures: Table creation details CREATE TABLE filler ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE=Memory; CREATE TABLE t_source ( id INT NOT NULL PRIMARY KEY ) ENGINE=MyISAM; CREATE TABLE t_packed ( id INT NOT NULL PRIMARY KEY, name CHAR(6) NOT NULL, KEY ix_packed_name (name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1; CREATE TABLE t_unpacked ( id INT NOT NULL PRIMARY KEY, name CHAR(6) NOT NULL, KEY ix_unpacked_name (name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0; DELIMITER $$ CREATE PROCEDURE prc_filler(cnt INT) BEGIN DECLARE _cnt INT; SET _cnt = 1; WHILE _cnt <= cnt DO INSERT INTO filler SELECT _cnt; SET _cnt = _cnt + 1; END WHILE; END $$ DELIMITER ; START TRANSACTION; CALL prc_filler(50000); COMMIT; INSERT INTO t_source SELECT id FROM filler; INSERT INTO t_packed SELECT id, ( SELECT GROUP_CONCAT(CHAR(65 + FLOOR(RAND(20100204) * 26)) SEPARATOR '') FROM ( SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL ) q ) FROM filler; INSERT INTO t_unpacked SELECT * FROM t_packed; There are two MyISAM tables with randomly generated character sequences like this: id name 1 RTUPPH 2 RKZQJW 3 FKMEKL 4 BYOZFE 5 GSTRAF 6 YBNMSG 7 ZEZKCE 8 PMPNUJ 9 OQMMYH 10 OYAFDZ 49999 DZMKRC 50000 NHYWLR The structure of the tables is identical, except that t_packed packs keys and t_unpacked does not. Unpacked keys SELECT COUNT(*) FROM t_source s LEFT JOIN t_unpacked p ON p.name = IF(s.id > -1, _UTF8'ABCDEF', NULL) COUNT(*) 50000 1 row fetched in 0.0001s (0.7656s) SELECT COUNT(*) FROM t_source s LEFT JOIN t_unpacked p ON p.name = IF(s.id > -1, _UTF8'GHIJKL', NULL) COUNT(*) 50000 1 row fetched in 0.0001s (0.7331s) SELECT COUNT(*) FROM t_source s LEFT JOIN t_unpacked p ON p.name = IF(s.id > -1, _UTF8'NOPQRS', NULL) COUNT(*) 50000 1 row fetched in 0.0001s (0.7656s) SELECT COUNT(*) FROM t_source s LEFT JOIN t_unpacked p ON p.name = IF(s.id > -1, _UTF8'ZYXWVU', NULL) COUNT(*) 50000 1 row fetched in 0.0001s (0.7712s) The queries against the strings beginning with A, G, N and Z take the same time. All queries have been run several times to populate the cache and the execution times are consistent. The LEFT JOIN against a non-existent value was used in the query to avoid stopping on a found key and make the query traverse the index as much as possible. We also put a formal dependency on s.id here so that t_source is always leading in the join and no const optimizations are performed. Packed keys Let’s try the same queries on packed keys: SELECT COUNT(*) FROM t_source s LEFT JOIN t_packed p ON p.name = IF(s.id > -1, _UTF8'ABCDEF', NULL) COUNT(*) 50000 1 row fetched in 0.0001s (1.9531s) SELECT COUNT(*) FROM t_source s LEFT JOIN t_packed p ON p.name = IF(s.id > -1, _UTF8'GHIJKL', NULL) COUNT(*) 50000 1 row fetched in 0.0001s (2.3593s) SELECT COUNT(*) FROM t_source s LEFT JOIN t_packed p ON p.name = IF(s.id > -1, _UTF8'NOPQRS', NULL) COUNT(*) 50000 1 row fetched in 0.0002s (2.7812s) SELECT COUNT(*) FROM t_source s LEFT JOIN t_packed p ON p.name = IF(s.id > -1, _UTF8'ZYXWVU', NULL) COUNT(*) 50000 1 row fetched in 0.0002s (2.9375s) The query against the string beginning with the letter Z takes more than 50% more time than the query against a string beginning with A. Summary Here’s a little summary table: Search string Unpacked key Time, % Packed key Time, % ABCDEF 0.7656 100.00 1.9531 100.00 GHIJKL 0.7331 95.75 2.3593 120.79 NOPQRS 0.7656 100.00 2.7812 142.39 ZYXWVU 0.7712 100.73 2.9375 150.40 We see that the value being searched for does not affect time to search the index with unpacked keys but seriously affects performance of the indexes with packed keys. This increase is due to linear search used to locate the records within a single page. Note that not any pair of records in a page (both with packed and unpacked keys) have a corresponding lower-level page containing intermediate values. There can be leaves and branches on the same depth in the tree. This can lead to some artifacts: certain strings can be found (or proved absent) faster than the others. However, in average, all records have same depth. And in average, with packed indexes, the need for linear search increases the time required to find the keys with the greater values. Hope that helps. I’m always glad to answer the questions regarding database queries. Ask me a question

innodb_file_per_table, shrinking table spaces and the data dictionary

INNODB has some irritating gotchas that makes disk space management hard. In 2002ish INNODB, added innodb_file_per_table to get around allot of these issues, but it does not fix everything.If you are running innodb_file_per_table, you will notice in your database directory db.opt - database characteristics file. tablename.frm - the table structure. tablename.ibd - the actual innodb table space file Imagine that you have a table with 10 million rows and you delete say 5 million rows in multiple chunks around 400K chunks, because deletes are slow. Next, you notice that the table space file did not shrink. So what do you do? OPTIMIZE tablename, tada all the wasted space is reclaimed, but here is the PROBLEM the ibdata file grew!ibdata stores all of the UNDO LOGS thus GROWS due to the deletes and space is never reclaimed.Ok, lets try coping the tablespace file to another directory and re-import the tablespace file after wiping the data dictionary (ibdata).For instance/etc/init.d/mysql stopcd /var/lib/mysql/DBcp * /tmp/holdrm /var/lib/mysql/ib*cp /tmp/hold/* /var/lib/mysql/DB/etc/init.d/mysql start // create the ibdata fileALTER TABLE tablename IMPORT TABLESPACEERROR 1146 (42S02): Table 'DB.tablename' does not existReally, there is nothing that you can do to force innodb to purge the undo logs in the ibdata file without a full dump of the data to text and then reimport the data.So here are the steps to shrink all table spaces and the ibdata filemysqldump --all-databases (or use mk-parallel-dump)stop mysqlrm -f /var/lib/mysql/ib* /var/lib/mysql/DB/*start mysqlmysqlimport (or use mk-parallel-restore)Takes a bunch of time but there is no other recourse. If you know of another way please share :)

Installing Nginx With PHP5 And MySQL Support On Fedora 12 Nginx (pronounced "engine x") is a free, open-source, high-performance HTTP server. Nginx is known for its stability, rich feature set, simple configuration, and low resource consumption. This tutorial shows how you can install Nginx on a Fedora 12 server with PHP5 support (through FastCGI) and MySQL support.

MONyog MySQL Monitor 3.7 introduces multi-user authentication and licensing changes.

MONyog MySQL Monitor 3.7  Has Been Released. From MONyog 3.7 we have introduced 3 editions of the commercial version of MONyog. When we had first released MONyog 2 years back, it was already an almost complete tool for monitoring MySQL servers based on what the MySQL server exposes on SHOW statements.  Since then we have added features that are not basic server monitoring features in the strict sense but additional or supplementing features.  Most important the Query Analyzer was added around one year ago. Multi-user authentication is a long standing request from customers belonging to large organizations – or just having the need for monitoring lots of MySQL servers. There may be multiple server administrators. There may be development/test servers that the developers of that organization should be able to monitor, but is may also be unwanted to give them access to see details from servers having delicate information (customer data, payroll data etc.).  We have completely rewritten the MONyog authentication system.  It is now possible to create multiple user profiles and give access for a specific user to a subset of servers available only.  Additionally specific ‘admin’ functionalities (access to edit server settings, to KILL queries and to execute FLUSH STATUS) can be disabled for a user. As MONyog is no longer just a tool that organizes what the server exposes on SHOW statements – but rather a ‘bundle’ of tools – we have realized that not all tools are useful to every MONyog customer. MONyog is no longer a “one-size-fits-all” application. MONyog caters to a very diverse range to customers. In some organizations MONyog may be used by a single person only, and in other organizations by dozens of people. Some users will not use the Query Analyzer at all (because they only run standard applications where they are not in control of schema and query design anyway). And so on.  The Ultimate edition is for those users in particular that have full control over their database and their applications and who need multiuser authentication.  The Enterprise edition provides the basic monitoring functionalities as well as the Query Analyzer – but not multi-user authentication.  The Professional edition is an entry-level version with the (original) basic monitoring functionalities. Refer to the comparison sheet for full details. We thought it is now the right time to have multiple editions of MONyog. This also means that people can start with the lower edition and gradually move to a higher edition if the need arises. This will also reduce the overall complexity and total cost of ownership for several customers. We have also migrated all existing MONyog customers to MONyog Ultimate and Ultimate upgrade prices are kept moderate.  So if you are already a MONyog customer your Total Cost of Ownership remains same while you continue to enjoy the powerful tools and features of Ultimate. Release notes in traditional form can be viewed here. Feel free to leave your feedback in the comments section.

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 [...]

New OLAP Wikistat benchmark: Introduction and call for feedbacks

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 [...]

Percona is hiring. As part of our growth process, we introduced the role of the Shift Support Captain in 2009 (see the original announcement here) to provide customers with a 24x7 technical contact person. The Shift Support Team dispatches incoming emergencies, assigns new issues, handles or escalates incoming Nagios alerts from some customers, and in general makes sure [...]

Community Events February-March

February and March as busy months for Community events.  There's MySQL University, Fosdem, the Seattle MySQL Meetup & Confoo.ca. Here are the details: February 4th - I'll be presenting a talk via MySQL University on Optimizing Queries with Explain.  This talk will be about learning to read the output from MySQL's EXPLAIN command, and optimizing some [...]

A while back Friendfeed posted a blog post explaining how they changed from storing data in MySQL columns to serializing data and just storing it inside TEXT/BLOB columns. It seems that since then, the technique has gotten more popular with Ruby gems now around to do this for you automatically. So when is it a [...]

XtraDB feature: save / restore buffer pool

We recently released XtraDB-9, and while we did not highlight it in announcement, the release-making feature is ability to save and restore InnoDB buffer pool. The idea is not new and was originally developed by Jeremy Cole (sorry, I do not have the link on hands) some time ago, and now we implemented it in XtraDB. Why [...]

So you get MySQL or other applications using too much memory on the box or OS behaving funny and using more memory for cache and pushing application to the swap. This causes swapping and causes performance problems. This much is obvious. But how bad is it ? Should you count it same as normal Disk [...]

Fast storage: 8 SSD Intel X-25M 80GB benchmarks

I appreciate opportunity Jos van Dongen from Tholis Consulting gave me. He granted me access to servers with 8 attached Intel X-25M 80GB MLC cards. The cards attached to 2 Adaptec 5805 raid controllers, with 4 cards per controller. The cost of setup is 8 x 260$ (X-25M) + 2x500$ (Adaptec 5805) = ~3000$. Available space [...]

I generally thought about MySQL replication as being quite low overhead on Master, depending on number of Slaves. What kind of load extra Slave causes ? Well it just gets a copy of binary log streamed to it. All slaves typically get few last events in binary log so it is in cash. [...]

2010 Percona Training Schedule

After a nice long vacation, it's time to unveil our destinations for public classes in 2010.  We are now offering a course for Developers as well as DBAs.  The dates are: Seattle 16 February San Francisco 18-19 February Boston 22-23 February New York City 25-26 February Montréal 8-9 March Denver 15-16 March Chicago 18-19 March Washington DC 25-26 March Los Angeles 19-20 April Raleigh 22-23 [...]

Green Computing, Data Centre Efficiency, Virtualization And The Cloud

There's been a lot of talk of late about green computing, how data centres are becoming more efficient, virtualization and particularly the idea that cloud computing is some sort of panacea. Yes, that maybe so. But you rarely hear anyone mention inefficient software being taken to task: it's the elephant in the room.

read more

Serve Static Drupal Content Faster With Boost And Nginx

Serve Static Drupal Content Faster With Boost And Nginx

By Stephen Jayna, 23rd December 2009

read more

How To Speed Up Drupal and/or PHP With XCache

By Stephen Jayna, 22nd December 2009

Doubtless most of you do this already, but if you don't you probably should at least consider it: install XCache. If you serve pages from Drupal or moreover with PHP you could, as I have, increase your PHP throughput by 167% for five minutes of effort.

read more

How To Reduce table_locks_waited In MySQL/MyISAM

How To Reduce table_locks_waited In MySQL/MyISAM

By Stephen Jayna, 19th August 2009

The scourge of parallelism and scaling everywhere: locking. Or in MySQL/MyISAM — and to be more precise — table locks. Here's an overview of what to look out for and how one might go about reducing the frequency at which they occur.

read more

How To Speed Up MySQL: An Introduction To Optimizing

By Stephen Jayna, 3rd August 2009

Although there is nothing groundbreaking in this document consider it a bringing together of techniques for your first foray into optimization. We won't discuss the more esoteric methods of squeezing the very last millisecond out of MySQL. There are a myriad of parameters to tune: here's what you need to get right first.

read more

Today I answered a question where OP wanted to convert a 64-bit filetime value to a system time. After a little reading about FILETIME, I learnt that the number is a value for 100 ns passed since January 1st 1601. This gets little tricky since the normal date zero for SQL Server is January 1st 1900.

But with a little arithmetic things worked out. The most cumbersome part was the INT limit for passing parameters to DATEADD function, but that was easy to overcome too. However, I am not convinced the actual number for the nanosecond value is returned "reversed" from system API originally. If that's not the case, just skip the REVERSE function in the derived table d.

See the code below


DECLARE @Sample TABLE
        (
            f CHAR(5),
            lastmod CHAR(16),
            filedate DATETIME
        )

INSERT  @Sample
VALUES  ('File1', '0d4081fc676aac10', '2/5/2010 10:20'),
        ('File2', '0c03f1fc676aac10', '2/5/2010 10:20'),
        ('File3', '0c18164bf2f7ac10', '12/17/2009 10:43'),
        ('File4', '0bbe32fc676aac10', '2/5/2010 10:20'),
        ('File5', '0a6a82fc676aac10', '2/5/2010 10:20'),
        ('File6', '46bd5b9873db9c10', '4/14/2009 14:30'),
        ('File7', '0a88d2fc676aac10', '2/5/2010 10:20')

;WITH cteYak
AS (
    SELECT  f,
            filedate,
            CAST('' AS XML).value('xs:hexBinary(sql:column("Yak"))', 'BINARY(8)') AS Bin
    FROM    (
                SELECT  f,
                        filedate,
                        REVERSE(lastmod) AS Yak
                FROM    @Sample
            ) AS d
)
SELECT  f,
        filedate,
        DATEADD(SECOND, Sec, DATEADD(MINUTE, Peso, 0)) AS Peso
FROM    (
            SELECT  f,
                    filedate,
                    CAST((CAST(Bin AS BIGINT) - CAST(94354848000000000.0 AS BIGINT)) / 10000000 / 60 AS INT) AS Peso,
                    CAST((CAST(Bin AS BIGINT) - CAST(94354848000000000.0 AS BIGINT)) / 10000000 % 60 AS INT) AS Sec
            FROM    cteYak
        ) AS d

Ad-Hoc Rollup by date/time Interval

I often use aggregate queries to rollup data by an arbitrary date/time interval.  I'll share some techniques that I use to accomplish the task in case you find these useful, using the same table below:

Simple Rollup

Without an auxiliary table, a little DATEADD magic can do the trick.  Here's an example that summarizes web page requests by minute for the specified date/time range:

Results:

Arbitrary Intervals

The simple rollup method works well for any of the pre-defined units provided by the DATEADD function (year, quarter, month, day, hour, minute, second or week).  However, it lacks the flexibility to roll up to an arbitrary interval like 15 minutes or 30 seconds.  A little DATEADD/DATEDIFF math addresses this gap.  Below is an example of a 30-minute interval rollup using this technique:

Missing Intervals

You probably noticed that periods with no activity at all are omitted rather than reporting a zero value.  One method to include the missing intervals is with an outer join to a temporal table containing all the desired intervals.  Ideally, the temporal table would be a permanent one but I've found it impractical to maintain such a table for ad-hoc needs.  Fortunately, a utility numbers CTE is a handy way to generate the needed intervals dynamically.  The example below provides up to 65,536 interval values and can be easily extended as needed.

Peter Larsson recently scrubbed his laptop but didn’t bring over his favorites and made a plea for interesting SQL Server related blogs.  My reading list of SQL Server blogs and sites is up to 83 sites in Google Reader and I thought I’d share my list in hopes of finding more.  Here’s the link to the OMPL file that you can import into a feed reader: SQL Server Blogs OPML file.

Below is the complete list of SQL Server blogs I read regularly.  If you’re writing about SQL Server and not on that list please use the contact me form and let me know about your blog.  I’d like to get you added to the list.

Bill subscriptions in Google Reader

• [RSS] Allen Kinsel - SQL DBA
• [RSS] Andrew Fryer's Blog
• [RSS] Bill Ramos on SQL Server
• [RSS] Bob Beauchemin's Blog
• [RSS] Brent Ozar - SQL Server DBA
• [RSS] Carpe Datum
• [RSS] Chris Shaw's Weblog
• [RSS] Conor Cunningham's Blog
• [RSS] Conor vs. SQL
• [RSS] Dan's Blog
• [RSS] Data Dude
• [RSS] Databases, Structures, and the Damn Data Itself
• [RSS] DataWorks WebLog
• [RSS] Developer hearted / Relational minded
• [RSS] Direct Reports (Brian Welcker's Weblog)
• [RSS] Esoteric
• [RSS] Euan Garden's BLOG
• [RSS] Glenn's space
• [RSS] Henk's tech blog
• [RSS] In Recovery...
• [RSS] Jeremiah Peschka, SQL Server Developer
• [RSS] Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, Productivity, etc.
• [RSS] Kalen Delaney
• [RSS] Kendal Van Dyke
• [RSS] Kevin E. Kline
• [RSS] Kimberly L. Tripp: Improving *my* SQL skills through your questions!
• [RSS] Laurentiu Cristofor's blog
• [RSS] Microsoft SQL Server Development Customer Advisory Team
• [RSS] Microsoft SQL Server Release Services
• [RSS] Microsoft SQL Server Support Blog
• [RSS] Midnight DBA
• [RSS] Most Recent KBs for SQL Server 2005
• [RSS] MSDN: SQL Server
• [RSS] MSSQLTips - Latest SQL Server Tips
• [RSS] PASS - The Professional Association for SQL Server
• [RSS] Patrick Sirr's Blog
• [RSS] Pragmatic Works
• [RSS] PSS SQL Server Engineers
• [RSS] rusanu.com
• [RSS] Slava Oks's WebLog
• [RSS] SQL Fool
• [RSS] SQL Programmability & API Development Team Blog
• [RSS] SQL Server
• [RSS] SQL Server 2005: CLR Integration
• [RSS] SQL Server Blog by Jason Massie
• [RSS] SQL Server Community Blogs
• [RSS] SQL Server Engine Tips
• [RSS] SQL Server Performance
• [RSS] SQL Server Policy-Based Management
• [RSS] SQL Server Premier Field Engineer Blog
• [RSS] SQL Server Storage Engine
• [RSS] SQLAndy
• [RSS] SQLBatman.com
• [RSS] SQLblog.com - The SQL Server blog spot on the web
• [RSS] SQLCAT.com
• [RSS] SQLJunkies Blogs
• [RSS] SQLServerCentral.com
• [RSS] SQLServerCentral.com
• [RSS] SQLServerCentral.com
• [RSS] SQLServerCentral.com
• [RSS] SQLServerPedia
• [RSS] SQLServerTimes2
• [RSS] SQLTeam.com Feed
• [RSS] SQLTeam.com Weblogs
• [RSS] SQLvariations
• [RSS] SSIS Talk
• [RSS] StraightPath Solutions SQL Blog
• [RSS] The Cranky DBA
• [RSS] The Data Platform Insider
• [RSS] The Kirkapedia
• [RSS] The SQL Doctor is In (Real In)
• [RSS] The SQL UPDATE Statement
• [RSS] Tips, Tricks, and Advice from the SQL Server Query Optimization Team
• [RSS] Tips, Tricks, and Advice from the SQL Server Query Processing Team
• [RSS] (title unknown)
• [RSS] Tony Rogerson's ramblings on SQL Server
• [RSS] Transact-SQL Blog
• [RSS] VSTS DB Team Blog
• [RSS] Ward Pond's SQL Server blog
• [RSS] WebbTech Solutions
• [RSS] Write Ahead Blog
• [RSS] SQLServerCentral.com Articles

The impossible thing happened...

Yesterday I got the brilliant idea to format my laptop and reinstall Windows 7 from scratch. I've had a few problem last month, mostly due to upgrading the preinstalled Vista to Windows 7.
Said and done, I boldly finished the installation and thought everything was ok. I mean, my email is IMAP so all email are stored at my ISP, all my relevant documents are on another partition on the harddrive and so on...

But..!
Now I have noticed I forgot to save my Favorites Links.

So now I need your help. I can try to remember all my favorites (about 80 of them), or I can pledge to you to post interesting SQL-related blogs to me, so I can quickly check them and add them to my Favorites again.


Thank you

//Peter

Convert binary value to string value

With SQL Server 2008, we can easily use

DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

SELECT  CONVERT(VARCHAR(MAX), @bin, 2)


But how can we do this in SQL Server 2005? You can't use a simple CAST or CONVERT, because you get the ASCII representation of the binary values.

So, here is how you can do the conversion with SQL Server 2005 by using XML.

-- Prepare value
DECLARE @bin VARBINARY(MAX)
SET     @bin = 0x5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

-- Display the results
SELECT @bin AS OriginalValue,
        CAST('' AS XML).value('xs:hexBinary(sql:variable("@bin"))', 'VARCHAR(MAX)') AS ConvertedString

This past Saturday I enjoyed the hospitality of the Tampa SQLSaturday (#32) team.  My presentation was “Clustering for Mere Mortals”.  Unlike many clustering presentations, I had demonstration content.  This resulted in the most asked question being “Do you have instructions on how you built that?”  This is the first part of those instructions.

Here is what the cluster consists of (Virtually):

One Windows 2003 R2 (x64) Server acting as Domain Controller, DNS Server, DHCP Server, File Server and iSCSI Target Server

Two Windows 2008 R2 Servers as cluster Nodes.

All this runs on a Lenovo W500 laptop with an Intel Core 2 Duo T9600 processor, 8 GB RAM and a 320 GB Internal Hard disk running Windows 7 Enterprise (x64).  The total disk footprint is slightly under 50 GB for all virtual guests.

One of the toughest decisions was choosing a virtualization platform.  I didn’t want to run Windows Server 2008 R2 on my notebook since that took away Hibernation and Sleep modes, plus getting the drivers for WiFi, Bluetooth, etc. working is a real pain.  VMWare was out since I wanted this to run Microsoft VHDs I downloaded for training as well as my new demo cluster.  Virtual PC was out since I needed 64-bit guest capability to run Windows 2008 R2 as a guest.  I found a winning candidate in VMLite.  VMLite is a Windows implementation of Sun’s VirtualBox platform.  It supports VDI, VMDK and VHD disk formats as well as 64-bit guests.  Incidentally, it is also free.

Based on prior experience with the above virtualization products, I chose to create a VM folder off of the root of my C: drive.  Below that I created a Machines folder and a HardDrives folder.  Set I then these as default locations in VMLite.  I also copied the VMLiteGuestAdditions.iso file to this location so it was easy to find later.

Like most Sun products (cough Java cough) it is semi-hostile to Windows users.  Still, this was the option with the lowest suck factor so I started building.  VMLIte does have a few quirks you have to learn to work around.  VMLite uses a UUID to identify virtual disk files to its Virtual Media Manager.  Evidently unique file names and paths are not adequate, probably because XML isn’t involved.  So if you copy a VHD file it will not allow you to “register” it with the Virtual Media Manager without a “mother may I?” step.   Many of the VHDs downloaded from Microsoft also have identical UUIDs, even ones for different training classes.  Common base OS image is the most likely explanation.  Anyway, to fix this is actually pretty simple

Open a command prompt

Change directories to the VMLite executable folder (C:\Program Files\VMLite\VMLite Workstation on my system).  Look for the VBoxManage.exe file. 

Type:  VBoxManage InternalCommands SetUUID <path\Filename to VHD>  Note:  Do not put quotes around the path and filename.

Using this technique you can now add any VHD file to the Virtual Media Manager tool in VMLite .

Interestingly enough, VMLite will not create a VHD file when you define a new Virtual Machine.  It natively will create a VDI (Sun Virtual Device) file.  You can use the Windows 7 Disk Management tool to create a VHD file with the correct characteristics and then detach it from the Host OS for use in a VM.

There are some other unexpected behaviors of VMLite (remember I did mention user-hostile?)  but no show-stoppers.  Finding them will be left as an exercise for the student. J

Start building OS base images and generally working in VMLite to get familiar with it.  At this point, you should be able to create new VMs, import existing VHDs, and generally do basic functions and navigation within VMLite.  We still have a ways to go to build a cluster, but we have a solid foundation to start on.

Get date from ISO week number

This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx.
The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week.
Or you can use the function blogged here http://weblogs.sqlteam.com/peterl/archive/2009/12/01/How-to-get-a-date-from-Year-week-and-weekday.aspx.


CREATE FUNCTION dbo.fnISOMonday
(
    @theYear SMALLINT,
    @theWeek TINYINT
)
RETURNS DATETIME
AS
BEGIN
    RETURN  (
                SELECT  DATEADD(DAY, 7 * @theWeek - 7, CurrentYear)
                FROM    (
                            SELECT  DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear,
                                    DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear
                            FROM    (
                                        SELECT  DATEADD(YEAR, @theYear - 1900, 3) AS Jan4
                                        WHERE   @theYear BETWEEN 1900 AND 9999
                                                AND @theWeek BETWEEN 1 AND 53
                                    ) AS x
                        ) AS d
                WHERE   DATEADD(DAY, 7 * @theWeek - 7, CurrentYear) < NextYear
            )
END

Happy to announce the arrival of Benjamin Micah Caldwell.  Mother and baby are both doing well.  Big sister thinks he’s just another baby doll for her to play with.  Sleep is at a premium, but everyone is adapting well.

Celko Stumper - The Class Scheduling Problem

Joe Celko has posted a new Stumper - The Class Scheduling Problem
here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/

Here is one suggestion to solve the problem. It's linear in time so it should be very fast. It's based on the "Descending order" approach, and the "paths" columns are used to store valid room and classes.

-- Initialize and find the valid combinations
DECLARE  @HowManySeatsFree INT = 0 -- Set to zero for maximum seating, and to 1 for letting in late pupils.

DECLARE  @Source TABLE
        (
            room_nbr CHAR(2),
            class_nbr CHAR(2),
            recID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
        )

INSERT      @Source
            (
                room_nbr,
                class_nbr
            )
SELECT      r.room_nbr,
            c.class_nbr
FROM        dbo.Rooms AS r
INNER JOIN  dbo.Classes AS c ON c.class_size <= r.room_size - @HowManySeatsFree
ORDER BY    r.room_size DESC,
            c.class_size DESC

-- Iterate the possibilities and return the unique answers
;WITH cteYak(recID, room_nbr, roomPath, class_nbr, classPath, isPresent)
AS (
    SELECT  recID,
            room_nbr,
            '/' + CAST(room_nbr AS VARCHAR(MAX)) + '/' AS roomPath, -- List of taken rooms
            class_nbr,
            '/' + CAST(class_nbr AS VARCHAR(MAX)) + '/' AS classPath, -- List of taken classes
            CAST(0 AS BIGINT)
    FROM    @Source
    WHERE   recID = 1

    UNION ALL

    SELECT      recID,
                room_nbr,
                CASE isPresent -- If room never encountered before (isPresent=0), take it!
                    WHEN 0 THEN roompath + CAST(room_nbr AS VARCHAR(MAX)) + '/'
                    ELSE roompath
                END AS roompath,
                class_nbr,
                CASE isPresent -- If class never encountered before (isPresent=0), take it!
                    WHEN 0 THEN classpath + CAST(class_nbr AS VARCHAR(MAX)) + '/'
                    ELSE classpath
                END AS classpath,
                isPresent
    FROM    (
                SELECT      s.recID,
                            s.room_nbr,
                            y.roomPath,
                            s.class_nbr,
                            y.classpath,
                            CHARINDEX('/' + CAST(s.room_nbr AS VARCHAR(MAX)) + '/', y.roompath)
                            + CHARINDEX('/' + CAST(s.class_nbr AS VARCHAR(MAX)) + '/', y.classpath) AS isPresent -- See if room or class is already taken. If so, isPresent is greater than 0, otherwise it will be 0.
                FROM        @Source AS s
                INNER JOIN  cteYak AS y ON y.recID + 1 = s.recID
        ) AS d
)
SELECT  room_nbr,
        class_nbr
FROM    cteYak
WHERE   isPresent = 0 -- Only present the combination never taken/found before
OPTION  (MAXRECURSION 0) -- Allow up to 32767 possible combinations.

Execution plans! Don’t you just love them? They’re the first thing you look at when tuning a query or a stored procedure. But what do you do if you have a gigantic query play with 10’s of nodes? how do you find the most complex one? Where do you start?

What I’ve usually done in situations like that is to first find the node/statement with the highest cost and work from there. Now the highest cost can be IO, CPU, Row number or the good old SubTree cost which gives us a number based on all those counters. Let’s see how.

With SQL Server 2005 we got the option to show our execution plans in XML in two different ways: using the SET SHOWPLAN_XML ON option or choosing the Show Execution Plan XML option in the execution plan context menu.

These are the two queries we’ll work with:

SELECT  SalesOrderID, OrderDate
FROM    Sales.SalesOrderHeader SOH

SELECT  *
FROM    Sales.SalesOrderHeader SOH
        join Sales.SalesOrderDetail SOD on SOH.SalesOrderID = SOD.SalesOrderID

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.2531.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="..." StatementId="1" StatementCompId="1" ... >
          <StatementSetOptions ... />
          <QueryPlan CachedPlanSize="8" CompileTime="1" CompileCPU="1" CompileMemory="152">
            <RelOp ... > ... </RelOp>
          </QueryPlan>
        </StmtSimple>
        <StmtSimple StatementText="..." StatementId="2" StatementCompId="2" ... >
          <StatementSetOptions ... />
          <QueryPlan CachedPlanSize="40" CompileTime="6" CompileCPU="6" CompileMemory="512">
            <RelOp ... > ... </RelOp>
              </Merge>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

First thing to do is to remove the xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan attribute part from the ShowPlanXML node. This is needed so that we don’t have problems with namespaces further on which simplifies the querying. You can leave the xmlns attribute but then you’ll have to use WITH XMLNAMESPACES to query it properly. From this XML we can extract the costliest nodes and statements with the following code:

-- notice the removed
-- xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan 
-- attribute
declare @xml xml = '
<ShowPlanXML Version="1.1" Build="10.0.2531.0">
... Shortened XML data...
</ShowPlanXML>'
-- go through all the execution plan nodes, get the attributes and sort on them
SELECT c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') as EstimatedTotalSubtreeCost,
       c.value('.[1]/@EstimateRows', 'nvarchar(max)') as EstimateRows,
       c.value('.[1]/@EstimateIO', 'nvarchar(max)') as EstimateIO,
       c.value('.[1]/@EstimateCPU', 'nvarchar(max)') as EstimateCPU,
       -- this returns just the node xml for easier inspection
       c.query('.') as ExecPlanNode        
FROM   -- this returns only nodes with the name RelOp even if they are children of children
       @xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC

-- go through all the SQL Statements, get the attributes and sort on them
SELECT c.value('.[1]/@StatementText', 'nvarchar(max)') as StatementText,
       c.value('.[1]/@StatementSubTreeCost', 'nvarchar(max)') as StatementSubTreeCost,
       c.value('.[1]/@StatementEstRows', 'nvarchar(max)') as StatementEstimateRows,
       c.value('.[1]/@StatementOptmLevel', 'nvarchar(max)') as StatementOptimizationLevel,
       -- this returns just the statement xml for easier inspection
       c.query('.') as ExecPlanNode
FROM   -- this returns only nodes with the name StmtSimple
       @xml.nodes('//child::StmtSimple') T(c)
ORDER BY StatementSubTreeCost DESC

In the results from the upper queries we can see that the ExecPlanNode is the XML of the processed node. By clicking on it we can inspect just that single node for further details.

We can see that the results are nicely sorted by cost and finding most expensive parts of your plan is easy. This may not look like a big deal but imagine having a stored procedure that generates 50 complex execution plans. Try going through that by hand and see how long you last.

What three events brought you here?

Paul Randal recently blogged about three life-changing events that brought him to where he is today.  His blog is a very interesting read and idea, and so I bring to you my three events that brought me to where I am today in my technical career as a SQL Server DBA.

Right out of high school and during the first two years of college, I worked as a student worker for the District Attorney's Office at the County of San Diego performing data entry and other forgettable tasks.  During this job, I worked with a previous classmate whose mom worked in another division at the County of San Diego.  It was communicated to her mom that I was interested in a technical career.  The mom was able to get an interview for me with the DBA team in the Information Services division at the County of San Diego.  The position was only as a student worker, but it was a technical position so I jumped at the opportunity when the job was offered to me.  I remained a student worker in the DBA team until I graduated from college with a Bachelor of Science in Mathematics with emphasis in Computer Science.  I was then hired full-time to be a Junior DBA in the same group.

I remained a Junior DBA until the IT department was outsourced to Computer Sciences Corporation.  This is the second significant event.  Rather than take the generous severance package that was offered to any employees who did not wish to be part of the outsourcing, I decided to instead transfer to CSC as an Intermediate DBA.  CSC was located in San Diego, so I didn't need to move or commute far away.  At CSC, I met my husband which is a very significant event in my life but not relevant to this blog. 

After about two years at CSC, I was so unhappy with my management that it drove me to find another job.  I had been interviewing for about six months without any success when I happened to be at an interview and saw a former co-worker there.  It turns out that I was interviewing for his position which he had already vacated (he was just there visiting and picking up some of his things).  I surely wasn't qualified for the position, but this chance encounter is the third significant event.  He contacted me a few weeks later and let me know of a DBA position that had opened up in his group at the new company, Qualcomm Incorporated.  I interviewed for it a few days later.  Although they were looking for a Senior DBA, my former co-worker decided to take a chance with me since he had mentored me at the County of San Diego and was aware of my possibilities.  They hired me as an Intermediate DBA.  The rest is history. 

I have been at Qualcomm for 8 years and love every minute of it.  I am now a Staff DBA (one level higher than Senior).  My mentor still works at the same company as me, however he has moved on to other things.  I am eternally grateful to him for all of the things that I learned and for taking a chance when there were more seasoned candidates interviewing for the position. 

If it weren't for my former classmate's mom landing me an interview for a student worker position in the DBA team, I don't know where I would be in my technical career.  I suppose I would have eventually made it into a DBA position since the skills match me perfectly, but it probably would have been a long journey to get there.

If it weren't for transferring to the new company during the IT outsourcing, I may never have ended up at my current company where I absolutely love my job.  I also wouldn't have met my husband.

And finally if it weren't for my mentor and former co-worker, I wouldn't be where I am today.  I owe my career to him.  Hopefully he accepts my gratitude instead of a monetary gift, haha!

The Bit Bucket (Greg Low): IDisposable : Stored Procedures - Time for a real contract? [sqlblog.com/blogs/greg_low/]

Interesting case of watching log file growth during a perf test [www.sqlskills.com/BLOGS/PAUL/]

Improvement in minimizing lockhash key collisions in SQL Server 2008R2 and its impact on concurrency [blogs.msdn.com/sqlserverstorageengine/]

Microsoft and Intel Push One Million iSCSI IOPS [blog.fosketts.net]

The Truth About Hyper-V Memory Overcommit [www.vcritical.com]

A Few Useful Queries for SQL Server 2008 Integrated Full Text Search (iFTS) [glennberrysqlperformance.spaces.live.com]

SYS2 DMVs on CodePlex [sqlblog.com/blogs/davide_mauri/]

Intel Nehalem-EX and Database Performance [glennberrysqlperformance.spaces.live.com]

Iron and Cloud [www.sqlmag.com] - Interesting editorial about SQLAzure

PASS Processes and Results posted [www.allenkinsel.com]

Free eBook on SQL Server Maintenance Plans Now Available [www.sqlservercentral.com/blogs/aloha_dba/]

SQL Server Waits and Queues [www.sqlservercentral.com/blogs/pearlknows/]

How to increase SQL Database Full Backup speed using compression and Solid State Disks [henkvandervalk.com] - Great post on backup performance.

10 Hot Scalability Links for January 13, 2010 [highscalability.com]

Star Trek Online Open Beta, Thumbnail Review

I figure there's got to be some crossover between the SQL Server folks and the MMO folks, so here ya go.

I downloaded and played the new Star Trek: Online open beta this week, just to see if it was going to be worthwhile.  So here's some of my thoughts on it.

It's an MMO, Massively Multiplayer Online game.  Typically that means you'll buy a box at your local retail outlet for $49.99 and then pay a monthly subscription fee, probably $15. 

It's also a Role Playing Game.  I was looking forward to it, thinking it'd be a really interesting social experiment to get people to work together to run a ship.

Wrong.

So it's an MMO, and you're basically a one-man army, you have a ship of your very own, that you can fly using your mouse.  As long as you don't want to go upside down.  It's the Federation, son, YOU WILL FLY UPRIGHT AT ALL TIMES.  The weapons and upgrades are the predictable "more power" upgrades you'd get at the beginning of any MMO.  There seemed to be a lot more MMO jargon used in the weapon descriptions (weapons have DPS, armor/shield penetration, etc) that might be opaque to people who haven't done a lot with MMO's before.  That is likely to change, this is a beta. 

Basically there are 2 "modes" to the game.  Ship mode, where you're flying your ship, and ground mode, where you're running around hallways as a person. 

The ship combat was...well, boring.  Fly around (but not upside down) and shoot things.  make sure your weakest shield is facing away from your enemy.  Hold down the space bar to auto-fire.  It takes about 2 minutes to master.  The shield thing is interesting too, if by "interesting" you mean "really simple and cheesed-out".  There are 4 quadrants to your shield:  fore/aft/starboard/port.  If you fly above or below someone, it's pretty much *random* what's going to get hit.  Your energy weapons have a 200 degree firing arc, facing either forward or aft, but ignoring up and down.  Last time I checked, space combat was supposed to be 3D.  So it's a 2-d based combat where you can fly in 3 dimensions, sort of. 

You can customize your ship pretty much on the fly.  Whenever you're out of combat, you can completely replace your weapons with any that you salvage (loot) off another ship you kill.  You know, what would take a drydock and several weeks takes 2 seconds in MMO-land. 

Then occasionally you'll touch down on the planet and be the away team.  Now this is Borg-era Star Trek, and they let you, the captain, the person responsible for the ship, beam down into dangerous situations with the away team.  Hrm.  Yeah, so that regulation obviously didn't make it into the RPG.  When you're on the ground (or in another ship) the game acts like the world's most dumbed-down third person shooter.  Click your target, click your attack.  Repeat until target dies.  You have 3 attacks (2 shooting and a melee knockback) and that's...well, that's it.  You have medical packs too.  I never really felt in enough mortal peril to use one though.

One of the things I like about MMO's is specialization.  I can pick a character class, learn that character class completely, and then I'll be a fairly good player.  ST:O went a different way.  You choose a character class, but I'm not exactly sure why, since no matter what class you pick you get the same skill trees, leveling track, and role.  You're the captain.  That's it.  Except when you're not the captain, and you're having to learn the roles of all the other classes so you can populate your bridge with good officers.  And then know all the other skill trees, so you can level those officers as they gain experience.

It's like they took the worst parts of most RPG's (having to micro-manage a team of NPC's), the worst space flight game ever, and a really bad 3rd person shooter and crammed them into one package.

So far: Bad character management, bad space flight, and bad third person shooter.  So how's the social part?

Um.  Yeah.  There's a chat box, and it scrolls the Star Trek equivalent of Barrens chat.  People asking where different things are.  Someone actually (jokingly) asked where Mankrik's Wife was (a WoW joke) just to make the scene complete.  You *can* team up and there's a system called "Fleets" which look like a guild based system.  I didn't stick around long enough, the game mechanics were, frankly, boring me to tears. 

The graphics are...fine.  I guess.  I have a midrange video card, there was no video lag (lots of server lag though).  Everything looked pretty enough, which for an MMO means "about 2 generations behind any other rendering system".  Phasers look like phasers, photon torpedos look like photon torpedos.  Nothing earth-shatteringly pretty or mind-numbingly awful.

There are lots of loading screens.  In the WoW universe you can travel from the far southern end of the world to nearly the northern tip of the world with no loading screens and smooth transitions.  If you attempted to walk that distance it would take probably a couple of hours, which is why there are lots of fast-travel options in WoW.  There were loading screens inside ST:O within the same building.  Small building.  An area that took 30-45 seconds to run across would have a loading screen to get to it. 

There were lots of glitches, which I mainly account to server lag.  I'm not sure that a shaky launch is going to do these guys any favors though when it comes to winning enough market share to be viable.  The only thing that Age of Conan did right was get have stable servers at launch and through beta. ST:O's operations people really don't understand how to seed torrents, it took me about 2 days to download the client software through their system, and the torrent wasn't seeded at all before it went live.  You know it's going to be fun when uTorrent says under "estimated time" 3y3d. 

There were running glitches (run along, suddenly you rewind to 50 yards back down the hall), ship transition glitches (you beam up and you see your character running in space).  Ship flight glitches (you'd rewind to about 50...uh...probably meters back sometimes).  I'm going to attribute most of the glitchiness to the beta quality server software.  That's fine, that's why you have betas.  The gameplay mechanics, however, were just awful, and that's something you do in design, not in beta.

A pretty horrible game so far.  I hope it gets better, but I'm not going to be buying it.

Adam Machanic launced his second TSQL tuesday: Invitation for T-SQL Tuesday #002: A Puzzling Situation. The theme is to show something interesting and not expected by the first look. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.

Ok here we go. Let me ask you this: How many times will the subquery in the below SQL statement be executed?

SELECT  (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM    Sales.SalesOrderHeader SOH
ORDER BY c DESC

How about the subquery in this in the below SQL statement be executed? It’s the same query only we’re repeating the subquery in the order by

SELECT  (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *
FROM    Sales.SalesOrderHeader SOH
ORDER by (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) DESC

If you said once you’d be wrong. At first look I also thought it would run once because SQL server query optimizer would be smart enough to realize we’re ordering by the same values as we’re selecting. However this isn’t a case. Let’s take a look at the IO cost of both queries:

Query #1:

-- Table 'SalesOrderHeader'. Scan count 1, logical reads 703
-- Table 'SalesOrderDetail'. Scan count 1, logical reads 1238

Query #2:

We can see the table SalesOrderDetail being scanned twice. Once for select part and once for order by part.

-- Table 'SalesOrderHeader'. Scan count 1, logical reads 703
-- Table 'SalesOrderDetail'. Scan count 2, logical reads 2476 <- READ TWICE! ONCE FROM SELECT AND ONCE FROM ORDER BY!

Of course the second example isn’t a way to write SQL but it sure is interesting how stuff works.