9

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?

  • 2
    There's a big difference between raw insert performance and what node_save will do. For one thing node_save performs a series of reads and writes. But there's no point discussing possible bottlenecks and optimizations without more data. – Alfred Armstrong Apr 9 '13 at 9:16
  • You need to consider why you are using Drupal in this way for your purposes. If you simply want to capture a lot of data in a flat table and display it using Drupal, you might want to bypass Drupal altogether when writing it and use a custom module to integrate the data using Views etc. – Alfred Armstrong Apr 9 '13 at 9:21
  • I doubt the bottle neck is on the database side. Node save does a lot of things in the background: it will invoke a number of hooks (hook_node_presave, hook_entity_presave, hook_node_insert, hook_entity_insert, etc.), each of which might call any number of modules. In addition node_save will rebuild the permissions for that node and it will clear the cache for that node... – Alice Heaton Apr 9 '13 at 9:26
  • @AlfredArmstrong I'm creating nodes based off data that is in another database. I mold the data to the correct drupal content type and node_save it. My clients are mainly universities wanting to switch to drupal. It's not uncommon for them to have between 200,000 and 1,000,000 nodes (depts' site contents, student and faculty records, etc) they would like to migrate over after a decade of using their own in web solution. I read this, which is encouraging, but still less than desirable approach. evolvingweb.ca/story/… – blue928 Apr 9 '13 at 9:47
  • .. so, I would prefer to stay as drupally as possible. Using node save with this much data ensures the integrity. If I can't get that to work, I'm willing to get creative. – blue928 Apr 9 '13 at 9:48
9

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.

  • Some of the migration modules out there, how do they end up bulk saving nodes? I mean, at the end of it all, it all boils down to an INSERT statement, right? How does your migration class ultimately insert from 'source' to 'target' when not using node save but still needing to maintain data integrity across tables? – blue928 Apr 9 '13 at 9:30
  • All migration modules I have come across do use a node_save. – Alfred Armstrong Apr 9 '13 at 9:35
  • 1
    @blue928 He's saying he does use node_save(), but adds some code to mitigate known problems that can be caused, like Pathauto rebuilding the menu cache after every node save – Clive Apr 9 '13 at 9:35
  • ah, Ok, I see. Bojan is your code available in a module or online where I could see how you have dealt with bottlenecks like path auto? Good idea with the xhprof. I'll check into that. – blue928 Apr 9 '13 at 9:54
4

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
  • 1
    Tuning MySQL seems to make a big difference. I went from about 80 node_saves a minute to about 700 just by following the tips given by mysqltuner.pl. – John McCollum Feb 4 '14 at 16:03
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.