The current US financial situation has claimed a victim in the Web 2.0 world — Uber. I’m not sure if this is the first significant name, but it will not be the last site running MySQL where investors will be quick to cut losses and move on.

Indexes are a great way to improve performed in a MySQL database, when used appropriately.
When used in-appropriately the impact can be a degradation of performance.

The following example from Movable Type shows how when reviewing the slow query log I found numerous occurrences of Inserts take 3 or more seconds, with no reported lock contention time for this insert.

# Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SET insert_id=6281;
INSERT INTO mt_comment
(comment_author, comment_blog_id, comment_commenter_id, comment_created_by,
 comment_created_on, comment_email, comment_entry_id, comment_ip, comment_junk_log,
comment_junk_score, comment_junk_status, comment_last_moved_on, comment_modified_by,
comment_modified_on, comment_parent_id, comment_text, comment_url, comment_visible)
VALUES (...)

The impact here, is that SELECT statements to the mt_comment table …

Recently I am been testing few pieces of Storage projects of OpenSolaris with PostgreSQL. One of tests involves using an iSCSI disk  with PostgreSQL.  Unfortunately the storage that's available is  in Colorado  while my PostgreSQL server is  located in Massachusetts. Latency will definitely be one of my top problems since storage  is halfway across the country (in Colorado). Plus the fact that I will be running a database server on my end  doesn't really sound like a good idea. Come to think about it, this could be a more common problem nowadays since Cloud Storage (for example Amazon S3 Webservice ) could be  optimistically  half way across the country and pessimistically be on the other side of the world.

 So what are my options to solve such problems?  …

After an amazing summer of getting married, graduating college, adopting a new dog, and taking a much needed extended vacation, I’m now back on track with where I left off. I’ve been making good progress on the asynchronous MySQL library I talked about in this post in the form of a new drizzle client library. This library is also compatible with MySQL since they share the same protocol, and if drizzle changes in the future I plan on supporting current and new MySQL protocols as well. All of the connection and I/O overhead is mostly done, and I’m just working through the protocol bits now. I’m hoping to have something ready to show and talk about for the OpenSQL camp.

In other news, I’ve recently started working with the …

Why you do not use GRANT ALL ON *.*?

I was with a client today, and after rebooting a MySQL 5.0.22 instance cleanly with /etc/init.d/mysqld service, I observed the following error, because you always check the log file after starting MySQL.

080923 16:16:24  InnoDB: Started; log sequence number 0 406173600
080923 16:16:24 [Note] /usr/libexec/mysqld: ready for connections.
Version: ‘5.0.22-log’  socket: ‘/var/lib/mysql/mysql.sock’  port: 3306  Source distribution
080923 16:16:24 [ERROR] /usr/libexec/mysqld: Table ‘./schema_name/table_name’ is marked as crashed and should be repaired
080923 16:16:24 [Warning] Checking table:   ‘./schema_name/table_name’

Now, I’d just added to the /etc/my.cnf a number of settings including:

myisam_recovery=FORCE,BACKUP

which explains the last line of the log file. When attempting to connect to the server via the mysql client I got …

It is no secret that I am the Program Chair for the MySQL Conference & Expo 2009, and am truly excited about it (big shoes to fill in from Jay). I expect it to be a great conference, with over 2,000 attendees and lots and lots of great talks. The paper submissions have been coming through, the excellent voters have been voting, and the progress is impressive. Its a great learning experience.

Now, I’m excited to tell you that I’m also going to organise the Open Source Databases MiniConf at linux.conf.au 2009. Its going to be in Hobart, Tasmania, in January 2009, and again, I’m excited. Read the …

While working for a client, I had need to produce canned results of certain different criteria, recording the result in a table for later usage, and keep the position within each result.

Knowing no way to do this via a single INSERT INTO … SELECT statement, I reverted to using a MySQL Stored Procedure. For example, using a sample I_S query and the following snippet:

  ...
  DECLARE list CURSOR FOR SELECT select table_name from information_schema.tables where table_schema='INFORMATION_SCHEMA';
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE;

  OPEN list;
  SET result_position = 1;
  SET done = FALSE;
  lab: LOOP
    FETCH list INTO table_name;
    IF done THEN
      CLOSE list;
      LEAVE lab;
    END IF;
    INSERT INTO  summary_table(val,pos) VALUES (table_name,result_position);
    SET result_position = result_position + 1;
  END LOOP;

However, in reviewing with another colleague after writing some 10+ different …

Knut Anders Hatlen writes about memcached user-defined functions (UDF) for Java DB on OpenSolaris.

Knut has also mentioned the recent release of Apache Derby 10.4.2. A corresponding Java DB release should be available for download soon. (Usually this happens immediately but we're all at a developers' conference for the next couple of …

Do you have a full time System Administrator? Do you have only a part-time SA, or none at all?

Packet General’s Data Security and PCI Compliance solutions run on a dedicated appliance, based on a “Just Enough Operating System” (JeOS) to minimize exposure.

This appliance actually improves not just the security of your data, but ensures your Operating System is secure and up to date. With only 4 services and a footprint < 600MB this is an ideal solution for running even a normal MySQL installation. Security upgrades can also be provided as an automated feature, eliminating the need for this management internally.

Tomorrow in the MySQL Webinar How to secure MySQL data and achieve PCI compliance which is being held Thursday, September 11, …

Good news fellow DBAs; adding to the already packed list of RSS/Atom aggregation sites out there on the internet, there is a new site catering to DBAs called Relationalnews. Feel free to add your feed(s) for aggregation, because what else do bloggers want but more visibility to search engines, right? This was basically a coding project to get familiar with CodeIgnitor as well as RSS and Atom xml feed processing in PHP. Pretty simple looking back on it, and it was generally a fun project.I’ll probably add more features to the site at a later time, with free time being what it is…So read the news! http://relationalnews.com