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 …

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 …

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 

MySQL uses locks for concurrency control. Whenever a client/thread acquires a lock, it will have exclusive access to that table or row (depending on the granularity of the lock). Other clients however, will be prevented from writing and possibly reading to/from the locked resource. The two main existing locks are:

READ LOCK – A read lock will allow the other clients to read from the locked resource but not write to it.
WRITE LOCK – a write lock will prevent the other clients from reading or writing to the locked resource.

Also, different storage engines have different lock granularity. For example, MyISAM will lock the whole table while InnoDB will only lock the rows it needs.

Let’s try this out using a MyISAM table. We open two concurrent sessions and in the first lock the City table from the world database (get it …

Slave delay can be a nightmare. I battle it every day and know plenty of people who curse the serialization problem of replication. For those who are not familiar with it, replication on MySQL slaves runs commands in series – one by one, while the master may run them in parallel. This fact usually causes bottlenecks. Consider these 2 examples:

• Between 1 and 100 UPDATE queries are constantly running on the master in parallel. If the slave IO is only fast enough to handle 50 of them without lagging, as soon as 51 start running, the slaves starts to lag.
• A more common problem is when one query takes an hour to run (let's say, it's an UPDATE with a big WHERE clause that doesn't use an index). In this case, the query runs on the master for an hour, which isn't a big problem because it doesn't block other queries. However, when the query moves over to the slaves, all of them start to lag because it plugs up the single …

One of the sessions at DrupalCon I attended was Larry Garfield's talk about "Drupal Databases: The Next Generation", which gave me a good insight into the current state of the Drupal database layer and how they plan to overhaul it for Drupal 7. The key points that I took away:

• A new API based on PDO
• Object-oriented, requiring PHP5
• Support for using prepared statements
• A unified access API
• A query builder
• More support for other database systems (currently Drupal supports MySQL and PostgreSQL only). In particular, they are …

It’s important that for any software application good standards exist. Standards ensure a number of key considerations. Standards are necessary to enforce and provide reproducible software and to provide a level of quality in a team environment, ease of readability and consistency.

If you were going to create a MySQL Naming Standard you have to make a number of key decisions. Generally there is no true right or wrong, however my goals tend towards readability and simplicity. In 2 decades of database design I’ve actually changed my preference between some of these points.

1. Pluralism

Option 1
All database objects are defined in the logical form, that being singular.

For example: box, customer, person, category, user, order, order_line product, post, post_category

Option 2

For database tables & views, objects are defined in plural. …