Over the past few weeks, there has been a significant amount of commercial activity relating to the open source database, PostgreSQL. EnterpriseDB raised $20 million US in its Series B funding round (press release), and signed a deal with Sun Microsystems to provide PostgreSQL support to Sun’s customers (article). GreenPlum and Sun announced a partnership for an open source data warehouse appliance powered by PostgreSQL (press release). While all of this new activity is taking place around PostgreSQL, Pervasive exited this market entirely ( …

Partitioning is a way of pre-organizing table storage. You can say "some of the table's rows will go here, some will go there, still others will go to to still other places". Often, depending on the storage engine, the effect is to spread the table's rows over different files or even different disks.

Why would you ever want to deliberately cause a deadlock? Sometimes a very large deadlock in MySQL will fill the output of SHOW ENGINE INNODB STATUS until it truncates, so you can’t see information about transactions, log and I/O, buffers, and so forth. I know only two solutions to this problem: 1) restart MySQL and 2) cause a small deadlock so the LAST DETECTED DEADLOCK section shrinks to an acceptable size.

I'll warn you right now, this is going to be a long article. More than likely, I'll put a version of this up on the MySQL developer zone and PHP zone. This article is intended to highlight various basic topics concerning proper methods of handling connections to MySQL databases in PHP, guidelines for caching dynamic content, and a technique called "lazy loading". Hopefully by the end of the article you'll have learned how to combat a very widespread and potentially devastating scalability problem seen in an enormous number of PHP web applications.

An introduction to the problem

Before I start the discussion on connecting to MySQL servers via PHP, it's worth pointing out that the relative cost of connecting to a MySQL database, versus connecting to a PostgreSQL or Oracle installation, is very, very low. However, the fact that connecting to a MySQL resource is inexpensive does not mean that connection resources can be abused. …

In an earlier article I explored the finer points of multi-table, cross-database deletes in MySQL, but I didn't get it quite right. This article explains the subtleties I've discovered since then. This is more complex than the MySQL manual documents.

MySQL will be hosting two separate one day European Customer Conferences in October.  This sounds like a long way away, but time moves quickly, so if you're in the UK, Germany or nearby, you may want to make plans to attend one of these events.  Think of these as one-day "mini-conferences" that jam the best of our technical presentations along with real world case studies and networking opportunities.  Last year's event in London sold out, so this year, make your plans early.

Topics to be covered include Detailed Technical Roadmap, High Availability Strategies, Using MySQL Cluster, Performance Tuning, Using MySQL in Web 2.0 applications and examples from customers and partners such as the BBC News, Neckermann, Unisys and more. 

• MySQL: …

Basics

Btree-Indexes must be less then 1024 bytes, so if you have a utf8 column that is 255 characters mySQL assumes 728 bytes, if you combine that with other varchar(255) utf8 columns you'll get an error.


Compound, composite indexes:

This term is used allot. It basically means 1 index across multiple columns.

For instance you have a table with

A tinyint
B tinyint
C tinyint
D tinyint

and a index on A,B,C taking 3 bytes.

following the principles of left-most-prefix key lookups (assume AND between columns)

A,B,C is an index lookup using 3 bytes
A,B order by C is a index Lookup taking 3 bytes 1 is used for a efficent sort.
A,B is an index lookup using 2 bytes
A is an index lookup using 1 byte

A, C is an index lookup using 1 byte. Notice that A,C does not use 2 …

utf8_bin collation on UNIQUE columns needs to be used with caution. In latin1

f == F so a duplicate error would be thrown if a user entered in 'F' when 'f' already existed.

With utf8_bin f != F so, make sure to normalize your data before sticking it into the dblayer i.e. put all email addresses, tags, etc in your db as lowercase :)

Thanks for reminding me Peter Z!!

Its a dirty job, but somebody's got to do it! I am talking about testing and debugging, of course, which is what I have been doing for the last month. But at least I have the satisfaction of knowing that XT is a lot more stable.

Over 95% of the "mysql-test-run" scripts now run through correctly with PBXT as the default engine. I have documented the changes made to the test scripts here: pbxt-test-run-changes.txt. This file also explains the major differences between MyISAM and PBXT, and lists all features are not yet implemented.

If you have compiled XT and would like to run the tests, then enter the following commands:

cd mysql-test
./mysql-test-run --force --mysqld=--default-storage-engine=pbxt

I have also compiled and tested XT on a multi-processor, 64-bit machine. I used …