Drupal 7 database plans

Submitted by Larry on 28 July 2007 - 2:04am

I've been talking up some evil plans I have for Drupal's database system in Drupal 7 lately, without going into a great deal of detail. For the most part, I've been trying to avoid distracting people, myself included, from the considerable work still remaining on Drupal 6. However, there has been recent work and discussion of making post-freeze changes to Drupal 6's database system, and even backporting them to Drupal 5. Those changes revolve mostly around database replication, which Drupal currently doesn't support in any meaningful way. That becomes important, though, on very heavy sites like, say, I don't know, Drupal.org. :-)

Those changes, though, impact the API changes we can make in Drupal 7. (OK technically they don't, since we change APIs all the time, but if we can set things up so as to minimize API changes over time that does make life easier for everyone.) For that reason, at Dries suggestion, I'm going to try and lay out now a skeleton of what I'm planning for Drupal 7's database system and how we can start adding replication support to Drupal 6 in a way that flows into it.

Before I begin, I just want to clarify that I am in no way trying to undermine the database-related work that others have done latey, particularly Barry Jaspan with the schema system and David Strauss with numerious SQL cleanup patches that have kept drupal.org from dying completely in recent weeks. Both have done great work and my hat's off to them. My goal here is to avoid duplicate or conflicting work, not to disparage the great work that has already been done.

That said, here's the short version of where I intent to try and push Drupal 7's database API.

Step one, replace the PHP 3-era PHP APIs for databases (mysql, pgsql) with PHP 5's PDO. It should be no surprise to anyone that I'm planning to port the backend to PDO, for reasons already stated. :-) That will allow us a number of new options, which in turn has a knock-on effect to other things we can do. The main one is type-agnostic prepared statements. Drupal already emulates prepared statements in user-space and has for years, but pushing that down into C code simplifies code and lets us focus on more important things. PDO's prepared statements have two key features we care about at the moment:

  • They're type-agnoistic. That is, you don't need to specify in advance if a field is an int, a string, a float, or whatever. PDO itself knows how to quote/cast things properly. That means at the PHP level you can simplify your code without a loss in security.
  • They're associative-array-based. And let's face it, if you don't like associative arrays you're dealing with the wrong CMS. :-)

There's plenty of other things to like about PDO, but those are the features that are relevant to us right now.

There's one other annoying factor. Right now, all Drupal queries go through db_query*(). db_query*() takes a variable number of parameters. Variable parameter count sucks, because it makes default parameter values difficult or impossible. db_query() also takes an array of parameters, but that's not used very often.

Now, let's consider database replication. Without going into detail about how database replication works (I'll be honest, I am not an expert in the subject), in order to be able to write queries against a master server or a slave server you need to know something about the query. You need to know if it's a mutator query (INSERT, UPDATE, DELETE, CREATE, etc.) -- and if so if a delay in writing the data to disk is acceptable -- or if it's a read query (SELECT), and if so whether data that's potentially a few seconds stale is acceptable. Right now, db_query*() gives us none of that information.

So let's give ourselves that information, and simplify SQL's sometimes nasty syntax at the same time.

The API

First, we modify the $db_url variable structure. Specifically, we extend the array depth by one level so that any given database entry can include master and slave variants that list the connection strings for the master and slave servers (in PDO's connection string syntax, so we don't need to parse and repack it). Both of those can themselves then be an array, in which case one is selected at random. That gives us support for multiple slave servers as well as a way to define multiple master servers. Naturally we still have a single server degenerate case as now. The various forms for $db_url then include:

<?php
$db_url = 'mysql://u:p@host/dbname';

$db_url['default'] = 'mysql://u:p@host/dbname';

$db_url['default']['master'] = 'mysql://u:p@host/dbname';
$db_url['default']['slave'] = 'mysql://u:p@host/dbname';

$db_url['default']['master'] = 'mysql://u:p@host/dbname';
$db_url['default']['slave'][] = 'mysql://u:p@host/dbname';
$db_url['default']['slave'][] = 'mysql://u:p@host/dbname';
$db_url['default']['slave'][] = 'mysql://u:p@host/dbname';
?>

Now that we have multiple servers defined, how do we tell the system which to use? We could use a dedicated slave function, or specify a flag in the db_query*() call itself. It would be cleaner to do it in the db_query*() call itself, but there's that pesky variable parameter count problem. But, since PDO wants an array for prepared statements anyway... get rid of it. db_query() takes only an array of values, not an array or variable parameter count. We then have a function signature that looks like this:

<?php
db_query("SELECT ... ", $values=array(), $slave_safe=FALSE) {...}
?>

Arguably we could assume that a query is slave-safe unless specified otherwise, but I don't know which case is more common in Drupal right now. We can sort that out later. We could also, potentially, merge db_query_range() in here as well by adding two more optional parameters, but that's a discussion for later as well.

So now we can easily flag those SELECT queries that should get run against the slave server du jour, if available. Otherwise, the query gets run against the master server, of which we could potentially have multiple. (Master-master replication configuration is way out of scope for this blog.) Next, we restrict db_query() to just SELECT statements, or possibly SELECT and schema statements. Perhaps it should even be renamed db_select().

Because the return value is a PDO result object rather than a primitive mysql result resource, and because the API is the same for all databases, we don't need db_fetch_array() and db_fetch_object() anymore. The result object has its own methods that work just fine, and even implements SPL object iterators so you can foreach() over it. All that for free. I like free.

What of INSERT, UPDATE, and DELETE queries then? Those get their own functions, too. Rather than hand-writing those out, though, we can take advantage of the fact that those query types have very simple, regular syntax. They're really just associative arrays broken out in different ways, so we can setup a function to just pass an associative array of values in and let the code do the rest. For a look at how they might work, see the db_helpers module in the helpers module package. For a lengthy explanation of why I think they're a really good thing, see the original thread and this blog comment. PDO's type-insensitive prepared statements, though, solve most of the security concerns they have. The signatures, then, looks like this:

<?php
db_insert($table_name, $fields, $delay=FALSE) {...}
db_update($table_name, $fields, $where, $delay=FALSE) {...}
db_delete($table_name, $where, $delay=FALSE) {...}
db_replace($table_name, $fields, $where, $delay=FALSE) {...}

// So for example:
db_insert('node', array(
  'nid'=>1,
  'vid'=>1,
  'title'=> 'my first node',
  // ...
));
?>

Most of the reasoning for such syntax is in the links above, but I will note the $delay parameter which can toggle INSERT DELAYED and UPDATE LOW_PRIORITY under MySQL or the equivalents in other DBMSes. In MySQL those are only useful on MyISAM tables, but given the number of sites that run MyISAM it's a nice addition.

So we now have all of our query types split out semantically, in a PDO-friendly array syntax, with clear ways to determine the master/slave database that should be used, with type-safe prepared statements, and with meta-data useful for some database systems, in a database-agnostic way. Score. :-)

First steps

The short-term baby step in that direction, then, is slave server support. For that, we do three things.

  1. Enhance $db_url's syntax to handle the above configuration for declaring slave servers.
  2. Modify db_query(), db_query_range(), and pager_query() to take an extra parameter, $slave_safe, but only in the array version.
  3. Convert core to use array-based db_query*() and $slave_safe=TRUE where appropriate. The non-array version still exists for now, so code that doesn't take specific advantage of $slave_safe has no change and we minimize the post-freeze API changes.

That is a bit different from the currently pending patch's implementation, but I believe it is a better long-term structure.

Concept needs feedback

That status doesn't exist on Drupal.org yet, so I'll fake it. :-) There's still some internal design I haven't nailed down yet and feedback on the API is certainly appreciated, even though major work on it won't happen until at least a Drupal 6 RC.

One of these days I will also learn to not say something will be a short explanation, because I should know by now that I never, ever give one.

In any case, let's make Drupal.org faster.

I think it is also important to support multiple distribution database,for example,I want the session table installed in another database server,to let more site to share it .Also,I want some drupal site config info such as locale language ,menu,type info installed in a database,and node data installed in another database.I think these also can improve performance for drupal.

sorry to my poor English.

Yeah, figuring out how to leave room for table-level partitioning support might be a good design consideration.

I believe this is called "horizontal partitioning" (vs "vertical partitioning") -- here tables (or rows) can be distributed among multiple databases. Thus, each database contains a subset of the data rather than a copy of the data.

This is useful in cases where you have 50,000 concurrent users hammering your database. As it stands, Drupal 7's recommended solution would be to scale using database replication. However, in extreme scenarios database replication might not be desired. Support for database partitioning has two important advantages compared to replication based solutions:

  1. Your data is always up-to-date. No master-slave synchronization woes, no need to deal with lag, etc. In environments where you can't accept delays (i.e. a big e-commerce website that does a lot of financial transactions?), this can be very important.
  2. You don't have to replicate your data 10 times. When you have a couple hundred gigabytes worth of data, having to replicate that on 8 slaves and 2 masters isn't too bad. However, if you're dealing with petabyte storage ... you might not want to replicate your database more than strictly necessary? One copy for backup purposes will do.

But not just that, for the longest time, people have wanted easier partitioning support for completely different reasons -- just re-read the comments above ...

People want to setup a shared database (not necessarily a dedicated server) to store user names and user profiles. There is a lot of data that you might want to share among different sites. Not because you have to (scalability) but because you can (convenience). Random example: I want association.drupal.org, groups.drupal.org and drupal.org to share the same user profiles. Or: I want to share my taxonomy terms among two sites. Or: imagine I'm a big media corporation with 10 Drupal sites -- I might want to create a "node server" so that I can push nodes out through different channels (i.e. websites).

Some of this might affect the proposed design. It's just not clear how exactly. ;-)

I think between the Drupal 6 move to auto-increment IDs and existing support for table prefixing (which under MySQL can also be used for cross-database access on the same server) we've already got the ability to share users between sites on the same server without insane hackery. (I just wrote said insane hackery this month, so it's fresh in my mind. :-) ). There's also now OpenID.

For splitting tables between separate servers or splitting a single table across servers, I'm afraid my knowledge there right now is next to none. Wouldn't that break joins? If anyone who has worked with such a setup has input on how to make this API more extensible in that direction, please speak up. :-)

I'm not an expert in DB architectures and I started developing for Drupal only a month ago, but I can do two considerations about splitting tables across DBs (I was developing the concept for a CMS I was projecting).

  1. As long as the two tables we want to query (let's say, nodes and users.. a common case) are on the same server, the JOINs won't break. I wasn't sure about this, so I tried it, and it works. You'll need only to specify the DB name in the query (for example, instead of nodes, using db1.nodes and instead of users, using db2.users) to make it work. I don't know how it can affect performance, though.
  2. The easiest way I see to achieve this would be using the already existing table prefixing facility. In the config file there might be an index of the tables that aren't stored on the main DB (kind of $db_tables = array('table_name' => 'db_id') or $db_tables = array('table_name' => 'db_id.table_prefix_')).

My 2 cents, hope they're useful. :)

Overall, I'm in favor of the proposed design. Going with PDO seems like a natural evolution -- it's the logical next step for Drupal. (Still, are there any statements from the PHP core team with regard to PDO? Do they see the /old/ database backends disappear in PHP6 or PHP7? Such statements would make this decision an absolute no-brainer.)

Last time I checked, PDO was built-in into PHP 5.1, but not into PHP 5.0. Are you suggesting that we get rid of Drupal's current mysql, mysqli and pgsql backends in favor of a generic PDO one, or will we add a PDO driver just like we have database backends now?

Of course, we'd build our own layer on top of PDO -- if only to support load balancing through database replication -- but it still begs the question: how much of the existing database infrastructure do you intend to whack?

For example, I'd like to see us take one step further and properly support BLOB/CLOB handling.

The mysql extension hasn't been part of the default compile of PHP since 5.0, I believe. Virtually every shared host adds it back in, and any worthwhile Linux distribution makes it trivial to install, but it's not part of the standard package. PDO has been part of the default compile since 5.1. mysqli and pgsql have never been part of the default compile, AFAIK.

There apparently is still some degree of work happening on the old drivers, as the mysqlnd library was just committed to the PHP 6 tree this week as an alternative to libmysql, but not a great deal. I'm not aware of any plans for mysql_, mysqli_, or pgsql_ to be removed completley, but most PHP 5-only projects seem to be using PDO already, I believe. It is the current trend.

I am indeed suggesting we drop our existing mysql_, mysqli_, and pgsql_ implementations in favor of a PDO-based framework. We don't get the main advantage of PDO, type-safe prepared statements, otherwise. While mysqli has prepared statements, they're not as cleanly implemented as PDO's, IMO. They also don't seem to support named-parameters, which is a PDO feature I quite like for readability. Keeping mysql_ and pgsql_ around would effectively defeat the purpose.

You can extend the PDO and PDOStatement classes, so what I envision is extending both of those to Drupal versions where we can do extra stuff, then extending the DrupalPDO class for each database we support. That class would have any additional db-specific code required. The db_* functions then all become database-agnostic then wrappers around the active database. That then allows us multiple connections to multiple database types simultaneously as well. Something vaguely like (will probably word-wrap):

<?php
function db_query($query, $args, $slave_safe) {
  return DPDO::getConnection('active', $slave_safe)->query($query, $args);
}
?>

getConnection() would be a factory method that returns the specified database connection object (creating it if necessary singleton-style), and the connection object's query() method would contain any DB-specific code that was needed. Of course, for straight SELECT queries there probably won't be any, so that can go in the main DPDO class and simply be inherited. (Yay for PHP 5 having a working object model!)

So for how much of the existing code I intent to whack, well, most of it. :-) Some will become unnecessary, some will get moved up into a connection class, some will get rewritten for PDO-isms. The front-facing API I see as being what's described above.

In order to avoid a FAPI-sized patch, though, I believe we can write a compatibility layer into the new code to start with so that the printf()-style prepared statements still function, albeit not optimally. (See my earlier attempts at a PDO driver for how.) Then we go through piecemeal and convert the rest of core to the new APIs like we're doing now with the page-split stuff, then remove the BC code before freeze. That way we avoid a "break every patch in the queue and break this patch every time there's any sort of commit" patch, which is always a PITA. :-)

Being primarily a MySQL guy my knowledge of BLOB/CLOB is minimal. That's something I'd have to talk to hswong about when we get closer to implementation.

From what I can see, the standard mysqli_* will not be removed any time soon.

mysqlnd will power mysqli_* _and_ PDO.

PDO is slower than mysqli_ and doesn't implement all the features mysqli_ offers. My advice would be; don't drop support for the 'native' functions.

Yes, PDO is slower than native mysql_ and mysqli_, but then it does more, too. Drupal <=6 re-implements so much stuff on top of it to get not-quite the same functionality that in my tests it ended up being a wash, performance-wise. I'm not sure what mysqli_ features PDO doesn't have, but Drupal doesn't actually use much in the way of really advanced database features as they're all database-specific, and if anything Drupal looks to be getting more database agnostic, not more MySQL-specific (for better or worse). Having to implement a wrapper that can handle both PDO and mysqli behind the scenes also makes it less-featured, more complex, and therefore slower.

There's also no mysqlnd driver for PDO at present. There may be in the future, but I don't know of anyone working on it.

One possible outcome or side-effect which might be interesting might be an ability to mark a slave as being used for snapshot backups, because current backup solutions for Drupal, although usable, are not really great and, for instance, typically do not address backup atomicity, the way a snapshot would. It might go that way:

  1. when a database backup task has to be started...
  2. pending updates are applied up to the snapshot point
  3. a designated slave stops receiving updates, which are queued somewhere (where ?)
  4. backup is performed from the slave
  5. it could still serve read-only queries during the backup, assuming these have been marked as those for which, in your words, data that's potentially a few seconds stale is acceptable
  6. the updates are applied in order from the queue
  7. it receives updates again ; normal processing resumed

Now, this doesn't address the potential of discrepancy regarding files vs database, but it would at least provide real-time consistent backups of the DB.

Anonymous (not verified)

6 August 2007 - 3:33pm

the insert syntax seems a bit limited. how would you do INSERT INTO SELECT queries?

Hi Larry,

I was wondering what you thought of using this type of abstraction and building out an active record type database abstraction. If we could combine this with Views UI and the work being done on Views 2, then we might be able to have a powerful database abstraction that has

a) a gui front end
b) powerful oo api

This would get us further into having a real "framework" that drupal developers could use in contrib module s and never have to think about database schemas. This would enable us to improve performance by improving the performance of the core and thus improve global performance.

Thoughts?

Gah, people always ask me about this. :-) I actually have a PDO-based active record-ish implementation that requires *no code generation* sitting on my hard drive, waiting for me to polish it off and publish it. I really need to do that sometime soon... :-) Of course, our node structure is considerably more elaborate than what a basic Active Record can handle nicely, and a lot of people in Drupal really dislike query builders for one reason or another. That said, that's something I am hoping to start a discussion on at DrupalCon Barcelona next month.

Hi there,

This all sounds wonderful as scaling this way is very important for big sites (as already mentioned above). I have only one thing to add to this since there are plans ahead for modifying core queries overall.

Don't forget about making Drupal use DB transactions. I can assist in doing this. Having Drupal core use transactions properly is, IMHO, the most important thing. How many times has anyone been coding modules and a query in their module's hook_whatever() implementation screws up yet the database is modified partially (in a way that doesn't make any sense).

This will move Drupal away from being a "toy content system" to being a more viable (and reliable) platform for corporate use even with the huge API changes between Drupal releases.

I hope that even if these changes don't go ahead for D7. I'll be given the opportunity to build transaction support for D7.

--
Sammy Spets
Synerger Pty Ltd
http://synerger.com

The "trailing" $slave_safe argument in db_query($query, $args, $slave_safe) bugs the heck out of me. If we're going through some trouble to cleanup the calling arrangement to get rid of a variable number of arguments, why not make sure the problem stays solved for the foreseeable future by doing it this way:

function db_query($query, $args, $options = array('slave_safe' => FALSE)) {...}

Or something equivalent. That way, some other important, perhaps database-specific option can't come along and bite us in the future. :-)

It may go without saying, but if we're going to use factories and singletons, we'd better be sure we get our classes *just* right. It'll be worth spending a little extra time on the design.

In general, I'm 95-99%+ in support of the proposal so far.

Hi Chris. Actually, I'm 100% with you on that. In the current version of the code in my sandbox, the $slave_safe variable has been replaced with an options array, one key of which is what database "target" to use (generally either "default" or "slave"). Other supported options include what fetch mode to use, whether to fetch into a classic object, etc.

I definitely agree that we want to get something like this right.