How can I speed up drupal's node_save() function?

Question

I am having a lot of trouble with the inefficiency of node_save(). But is node save my problem? That's ultimately what I am trying to find out.

I created a loop with 100,000 iterations. I created the bare minimum for the node object to be valid and save correctly. Here is the node save code:

$node = new stdClass();
        $node->type = "test_page";

        node_object_prepare($node);

        $node->uid = 1;
        $node->title = $node_title;
        $node->status = 1;
        $node->language = LANGUAGE_NONE;
        if($node = node_submit($node)){
            node_save($node);
}

Here are the results:

100,000 nodes were saved, each using node_save(). It took 5196.22 seconds to complete. That is ONLY 19 saves a second.

To say the least, that is not acceptable, especially when this person is getting around 1200 individual insert queries per second, and this person is getting 25,000 inserts per second.

So, what's going on here? Where is the bottleneck? Is it the with the node_save() function and how it's designed?

Could it be my hardware? My hardware is a development server, no one on it except for me - Intel dual core, 3Ghz, Ubuntu 12.04 with 16 gigs of ram.

While the loop runs my resource usage is: MySQL 27% CPU, 6M RAM; PHP 22% CPU 2M RAM.

My mysql configuration was done by the percona wizard.

Mysql says that if my CPU usage is under 70% my problem is disk bound. Granted, I have only a run of the mill WD Caviar 7200 RPM, but I should be getting more than 19 inserts a sec with it I hope!

Not too long ago I wrote about saving 30,000 nodes in a day. However, to be clear, this node has nothing to do with any external forces. It's purely a benchmark to learn about how to increase the speed of calls to node_save().

Realistically, I need to get 30,000 items into the database every minute using node_save. If node save is not an option, I wonder if I can write my own drupal api function "node_batch_save()" or something that takes advantage of mysql's ability to to do bulk inserts with the INSERT query. Thoughts on how to approach this?

Answer 1

You will never get 30 000 inserts a minute using node_save. No way.

An INSERT is fast because that's all it does. Node save does multiple inserts (main table, revision table, a table for each field), clears any entity caches, and fires hooks. The hooks are the tricky part. If you have many contrib modules (or even one that misbehaves) that can really kill performance, especially if the author didn't account for the "I am saving a ton of nodes at once" use case. For instance, I had to add this to my Migrate class:

  public function processImport(array $options = array()) {
    parent::processImport($options = array());
    // Do not force menu rebuilding. Otherwise pathauto will try to rebuild
    // in each node_save() invocation.
    variable_set('menu_rebuild_needed', FALSE);
  }

On the other hand, if you write a custom save function that invokes no hooks, you are in clear danger of getting inconsistent data, in a state that is unexpected by the system. I would never recommend doing that. Fire up xhprof and see what's happening.

Answer 2

First of all, install XCache/APC (for PHP < 5.5) and configure memcached for Drupal.

Then you can optimize your MySQL configuration for heavy queries by using mysqltuner script available at: http://mysqltuner.pl

E.g.

# performance tweaks (adjusted based on mysqltuner.pl)
query_cache_size = 32M
query_cache_limit = 256M
join_buffer_size = 32M
key_buffer = 8M
max_allowed_packet = 32M
table_cache = 512
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 8M

# When making adjustments, make tmp_table_size/max_heap_table_size equal
tmp_table_size = 16M
max_heap_table_size = 16M

thread_cache_size = 4

Other suggestions:

• disable modules which you don't need (e.g. Devel, core Database Logging module, etc.),
• upgrade your PHP to the latest or higher branch,
• recompile your PHP for 64-bit or higher architecture depending on your CPU,
• use the faster storage device for your db files or whole LAMP environment (e.g. SSD or memory-based filesystem),
• use PHP debugger or profiler to find out any performance bottleneck (e.g. XDebug Profiler, DTrace or NuSphere PhpED PHP Profiler),
• run some time-consuming drush command under gprof profiling tool, so you can find some performance bottleneck as well

Answer 3

Use Mongodb module to store fields https://drupal.org/project/mongodb Results here: as per http://cyrve.com/mongodb