Blog

Blesta 3.0: Efficiency

May 18, 2012 | Posted by Cody


As lead developer, my primary focus has been on designing an efficient system. Since our primary data store (where we make the most reads/writes) is the database, naturally this is where the smallest change can have the biggest impact. I’ve already discussed a number of changes we’ve made to the database system including switching to InnoDB and the use of PDO. The last is the use of unbuffered queries.

Buffered Queries

Buffered queries fetch (e.g. buffer) all records into memory from the database server and automatically close the cursor, allowing other queries to be executed. You might take this for granted. For example, you may have something like:

<?php
// What looks like 'nested' queries...
$statment = mysql_query($sql);
while ($data = mysql_fetch_assoc($statment)) {
    $statment2 = mysql_query($sql2);
    while ($data2 = mysql_fetch_assoc($statment2)) {
        ...
    }
}

This might be all fine and dandy, but assume that the first query finds 1,000,000 rows. If each row contained just 1 KB of data, that’s 976 MB of memory consumed! Keep in mind that the memory is consumed at mysql_query(), so it doesn’t matter how many rows you actually fetch (using mysql_fetch_*). Moreover, the memory isn’t freed until the program terminates or you explicitly invoke mysql_free_result().

Unbuffered Queries

Unbuffered queries, on the other hand, are not buffered into memory. Each row remains on the database server until fetched. This can drastically reduce memory consumption on the web server.

The only downside is that you can’t create what look like “nested” queries (as in my example above). But that really isn’t a downside at all, because it forces you to look at better methods for fetching or querying data. Like limiting your result set and fetching all results.

The Record component in Blesta, in conjunction with PDO, make unbuffered query support almost seamless. You really only need to be concerned about closing the cursorif you’re explicitly working with PDOStatement objects.

<?php
// Manually work with the statement...
$users = array();
$user_stmt = $this->Record->select()->from("users")->getStatement();
foreach ($user_stmt as $user) {
    $users[] = $user;
}
$user_stmt->closeCursor();
 
// Or just let the Record component to handle it all...
$users = $this->Record->select()->from("users")->fetchAll();

While the benefit of unbuffered queries may not be entirely evident on small data sets, there’s no doubting it improves efficiency. And that’s what creating an enterprise-level application is all about.

Blesta 3.0: The database

March 3, 2012 | Posted by Cody


A lot of effort has gone into designing and interfacing with the database in version 3, so I thought I’d share a little insight into some of the improvements we’ve made over version 2.

We’ve beefed up the database by making use of transactions, which allow us to add, edit, or remove items from the database with the ability to undo those changes should something go wrong. Because of this we’ve made the switch from a MyISAM storage engine to InnoDB (the default for MySQL as of version 5.5), making Blesta ACID-ic.

Another major improvement is the use of UTF8 collation, which will now allow users to more easily search the database in their native tongue as well as input and output data without conversion. This is a huge improvement for developers, and we are all about developers with version 3.

Speaking of developers, another great improvement is the introduction of the Record component. The record component is a database access object that creates queries using a series of method calls. Never again will you have to worry about which comes first, GROUP BY or ORDER BY.

In addition, the Record component uses the PDO library, making queries safe and secure. But that’s not the only benefit of PDO. Can we say “multi-database support”, as in MSSQL, PostgreSQL, and others? Well, no, not at the moment, but that’s definitely a possibility.

From the Developer Documentation: