Performance in Pdo/Php/Mysql: Transaction Versus Direct Execution

Performance in PDO / PHP / MySQL: transaction versus direct execution

If your queries are INSERTs, the page 7.2.19. Speed of INSERT Statements of the MySQL manual gives two interesting informations, depending on whether your are using a transactionnal engine or not :

When using a non-transactionnal engine :

To speed up INSERT operations that are
performed with multiple statements for
nontransactional tables, lock your
tables.

This benefits performance because the
index buffer is flushed to disk only
once, after all INSERT statements have
completed. Normally, there would be as
many index buffer flushes as there are
INSERT statements. Explicit locking
statements are not needed if you can
insert all rows with a single INSERT.

And, with a transactionnal engine :

To obtain faster insertions for
transactional tables, you should use
START TRANSACTION and COMMIT instead
of LOCK TABLES.

So I am guessing using transactions might be a good idea -- but I suppose that could depend on the load on your server, and whether there are multiple uses using the same table at the same moment, and all that...

There are more informations on the page I linked to, so don't hesitate to read it ;-)

And, if you are doing update statements :

Another way to get fast updates is to
delay updates and then do many updates
in a row later. Performing multiple
updates together is much quicker than
doing one at a time if you lock the
table.

So, I'm guessing the same can be said than for inserts.

BTW : to be sure, you can try both solutions, benchmarking them with microtime, on the PHP side, for instance ;-)

PHP - Difference between all PDO transaction and stricly MySQL transaction?

As I read the PDO documentation on transactions, both methods are equivalent. That is, PDO will use the driver-level transaction capabilities which, in the case of pdo_mysql, should be starting a native MySQL transaction.

I think the bigger issue to consider is exactly what you want to achieve. Are you using a transaction for performance reasons, or because the data is related and nothing should be inserted if one statement fails?

If the former, I would recommend benchmarking it. If the latter you need to structure the code such that logically related data are within the same transaction.

---

Edit in response to question update:

I'm inclined to say that issuing one large SQL statement would be quicker since there should be a bit less network traffic overhead (assuming the web and database servers aren't on the same host). With 'good' hardware and the correct configuration for your application requirements MySQL should be able to handle many thousands of inserts a second.

Of course, without knowing the details of your setup, this is pure speculation. But I suspect this kind of application-level optimisation is much less effective than tuning the database server.

The only way to be sure is to benchmark both ways and see if one is significantly better than the other.

PHP: What's the fastest way to query MySQL? Because PDO is painfully slow

Fastest possible :

mysqli_connect("servername", "user", "pass") or die("can't connect");
mysqli_select_db("dbname") or die("can't select database");

list($price, $sqft, $zipcode) = mysqli_fetch_array(mysqli_query("SELECT price, sqft, zipcode FROM homes WHERE home_id = ".mysqli_real_escape_string($home_id)));

[EDIT]: Now using mysqli instead of mysql.

MySQLi Prepared Statements and Transactions

Prepared statements and transactions are unrelated techniques and technologies.

You may wish to issue the START TRANSACTION and COMMIT/ROLLBACK commands directly instead of using the dedicated methods. They are functionally equivalent.

For your loop, you'd issue the START TRANSACTION before your prepare, then your COMMIT after the loop exits. You probably should not try to open a transaction after a prepared statement has been started but before it's been executed.

For some reason, they didn't add a "start transaction" command in favor of turning off autocommit. It's one of those weird things about mysqli that makes me always recommend PDO instead. :) Opening a transaction implicitly turns off autocommit for the duration of the transaction.

PDO/MySQL memory consumption with large result set

After creating the connection, you need to set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false:

<?php
$pdo = new PDO('mysql:host=127.0.0.1', 'foo', 'bar', array(
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
));
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// snip

var_dump(memory_get_usage());
var_dump(memory_get_peak_usage());

This outputs:

int(39508)
int(653920)
int(668136)

Regardless of the result size, the memory usage remains pretty much static.

Tactics for using PHP in a high-load site

No two sites are alike. You really need to get a tool like jmeter and benchmark to see where your problem points will be. You can spend a lot of time guessing and improving, but you won't see real results until you measure and compare your changes.

For example, for many years, the MySQL query cache was the solution to all of our performance problems. If your site was slow, MySQL experts suggested turning the query cache on. It turns out that if you have a high write load, the cache is actually crippling. If you turned it on without testing, you'd never know.

And don't forget that you are never done scaling. A site that handles 10req/s will need changes to support 1000req/s. And if you're lucking enough to need to support 10,000req/s, your architecture will probably look completely different as well.

Databases

• Don't use MySQLi -- PDO is the 'modern' OO database access layer. The most important feature to use is placeholders in your queries. It's smart enough to use server side prepares and other optimizations for you as well.
• You probably don't want to break your database up at this point. If you do find that one database isn't cutting, there are several techniques to scale up, depending on your app. Replicating to additional servers typically works well if you have more reads than writes. Sharding is a technique to split your data over many machines.

Caching

• You probably don't want to cache in your database. The database is typically your bottleneck, so adding more IO's to it is typically a bad thing. There are several PHP caches out there that accomplish similar things like APC and Zend.
• Measure your system with caching on and off. I bet your cache is heavier than serving the pages straight.
• If it takes a long time to build your comments and article data from the db, integrate memcache into your system. You can cache the query results and store them in a memcached instance. It's important to remember that retrieving the data from memcache must be faster than assembling it from the database to see any benefit.
• If your articles aren't dynamic, or you have simple dynamic changes after it's generated, consider writing out html or php to the disk. You could have an index.php page that looks on disk for the article, if it's there, it streams it to the client. If it isn't, it generates the article, writes it to the disk and sends it to the client. Deleting files from the disk would cause pages to be re-written. If a comment is added to an article, delete the cached copy -- it would be regenerated.

Optimizing Table Creation in PHP PDO using Prepared Statements or Other

After trying multiple approaches including concatenating SQL queries, I still did not get the performance improvement I wanted.

However, by switching away from PDO to MySQLi, I noticed that I got a significant performance improvement, albeit "on paper".

When I use PDO, the SQL query must execute the entire script before handing back control to the page. However, with MySQLi it somehow hands the SQL query off (to the background), and then sends control back to the page.

So instead of waiting 24 seconds to execute 120 tables, I wait for 0.1 second using MySQLi. It takes about the same time in theory (because if you use PHPMyAdmin or MySQL Workbench you'll notice that it'll take a couple of seconds before all your tables are listed complete using SHOW TABLES), but because my script doesn't wait for the entire SQL query to execute before my page comes back, the user experience is better.

Also, being that it would likely take more than 30 seconds before I need to use the tables for anything anyway, I don't see any major disadvantages, plus your script just appears to be much quicker.

So, if you need to create a lot of tables with an install script, I would suggest not using PDO, use MySQLi instead. You should still use PDO if you're creating one table at a time, or for your other database tasks.

Thanks for all the contributions.

---

Related Topics