Posted on

Analyzing WordPress PHP Memory Consumption

This weekend I have been processing a large 200,000 location data file for a Store Locator Plus customer.   This is one of the larger files I have processed on my test system and it is the first file over 60,000 locations I’ve processed since Store Locator Plus 4.2 and WordPress 4.x have been released.    This large file processing and the geocoding required is taxing several systems in the Store Locator Plus hierarchy.  WordPress, Google OEM API calls, and the locator are all showing their weak spots with this volume of data processing.   They can all handle it to some degree, but maximizing efficiency is the key.

The temporary solution to most of the issues is to increase memory and process limits.   These are some of the key findings, as posted on the CSV Import documentation pages for Store Locator Plus:

Check your php.ini post_max_size setting if doing a direct file import versus a cron URL based import. post_max_size is typically set to 8 (MiB) on most servers.   This is typically enough for around 25,000 locations but it depends on how long your descriptions are and how many data fields you have filled out.   SLP 4.2.41 will warn you if you try to upload a file larger than your post_max_size limit.

Check your php.ini memory_limit setting and make sure it is large enough to handle the WordPress overhead plus the size of your CSV file times two.   The WordPress database interface and the CSV file processing will consume lots of memory.  The more plugins, widgets, and advanced theme features you have more more memory WordPress will use and the more PHP memory will leak over time. A setting of 256M is enough for approximately 15,000 locations.

Check your wp-config WP_MEMORY_LIMIT.   You may need to add this define to wp-config.php.  define(‘WP_MEMORY_LIMIT’ , ‘256M’).  The number needs to be equal-to or less-than the php.ini memory-limit.    It is the WordPress-specific memory limit and works with php.ini memory_limit.

Check your wp-config WP_MAX_MEMORY_LIMIT.   You may need to add this define to wp-config.php.  define(‘WP_MAX_MEMORY_LIMIT’ , ‘256M’).   This is the WordPress admin interface memory limit and works like WP_MEMORY_LIMIT for admin pages.

Set Duplicates Handling to Add especially if you know you do not have duplicate locations in your data.  SLP 4.2.41 further improves the performance when using ‘add’ mode by eliminating extra data reads from the database.

Set Server-To-Server speed to Fast under the General Settings tab unless you are on a shared host or experience a large number of uncoded locations during import.

Set the PHP Time Limit to 0 (unlimited) under the General Settings tab.   For hosting providers that allow your web apps to change this, the unlimited value will let the import run to completion.

Keep in mind Google limits you to 2500 latitude/longitude (geocoding) lookups per 24 hours per server IP address.  If you are on a shared host you share that limit with all other sites on that host.

However, even with all of these settings tweaked to fairly high values for my VirtualBox development system running on a MacBook Pro Retina host, the 4GB of RAM allocated to WordPress still is not enough.   The system eventually runs out of memory when the file gets close to the 45,000 location mark.  Luckily the “skip duplicate addresses” option allows the process to continue.    The “out of memory” error still rears its ugly head in the wpdb  WordPress database engine and is a problem for handling larger files.

Enter Xdebug and memory profiling.   Somewhere buried in the Store Locator Plus code, WordPress code, PHP MySQL interface, or PHP core engine there is a memory leak.  With a complex application environment finding the leak is going to be a monumental task.  It may not be something I can fix, but if I can mitigate the memory usage when processing large files that will help enterprise-class sites use Store Locator Plus with confidence.

Getting Xdebug On CentOS 7

If you follow my blog posts on development you will know that I run a self-contained WordPress development environment.  The system uses Vagrant to fire up a VirtualBox guest that runs CentOS 7 with GUI tools along with a full WordPress install including my plugin code.   This gives me a 2GB “box file” that I can ship around and have my full self-contained development environment on any system capable of running VirutalBox.   Here is how I get Xdebug connected to my local Apache server running WordPress.

Install xdebug from the yum install script.

# sudo yum install php-pecl-xdebug.x86_64

Turn on xdebug in the php.ini file

# find / -name


#sudo vim /etc/php.ini


Check if xdebug is installed:

# php --version

... PHP 5.4.16
.... with xdebug v2.2.7

Enable some xdebug features by editing php.ini again.

Read about XDebug Profiling.

Read about XDebug Tracing.

# sudo vim /etc/php.ini

xdebug.default_enable=1  ; turns on xdebug any time a PHP page loads on this local server

xdebug.idekey="PHPSTORM" ; in case I turn on the automated listener for built-in PHP Storm debugging/tracing

xdebug.profiler_enable = 1 ; turn on the profiler which creates cachegrind files for stack trace/CPU execution analysis

xdebug.profiler_enable_trigger = 1;  turn on a cookie "hook" so third party browser plugins can turn the profiler on/off with a bookmark link

xdebug.profiler_output_dir = "/var/www/xdebug" ; make sure this directory is writable by apache and readable by your local user

xdebug.auto_trace = 1 ; when any page loads, enable the trace output for capturing memory data

xdebug.show_mem_delta = 1 ; this is what tells trace to trace memory consumption changes on each function call

xdebug.trace_output_dir = "/var/www/xdebug" ; same idea as the profiler output, this will be where trace txt files go

Restart the web server to get the php.ini settings in effect:

# sudo service httpd restart

At this point I can now open any WordPress page including the admin pages.   Shortly after the page has rendered the web server will finish the processing through xdebug and a trace* file will appear in /var/www/xdebug.   I can now see the stack trace of the functions that were called within WordPress with the memory consumption at each call.     This is the start of tracking down which processes are eating up RAM while loading a large CSV file without adding thousands of debugging output lines in the web app.

Be warned, if you are tracing large repetitive processes your trace file can be many GiB in size, make sure you have the disk space to run a full trace.

Posted on

WordPress Site Performance

The Charleston Software Associates web server came crashing down again this afternoon.  About once-per-month the server has been going into an unresponsive state.   Today I finally had enough logging turned on to track down the issue.   The problem?   The Apache web server was running out of memory.

The server was not under heavy load, but just the right combination of visitors and background processes triggered critical mass.   The wait times for a process to finish were long enough to start putting more things in the queue than could be emptied.   Memory soon ran out and the server stopped responding.

In researching the problem I came across two things that have made a substantial impact on the performance of my WordPress site.   If you are running a WordPress site, even with a limited number of visitors, you  may want to employ these techniques.

W3 Total Cache

W3 Total Cache is one of the most popular and top recommended WordPress plugins.    It is noted in several areas of the WordPress Codex as well as the WordPress forums and on core contributor blogs.    It is a powerful site caching plugin that can yield significant improvements in page loading time.

In the simplest configuration, you can turn on page caching which will run the PHP script that builds your WordPress page and create a static HTML file.   All future requests for your static pages will serve the HTML file versus loading the entire PHP and WordPress codebase.   This is a significant performance boost for many sites.     If your content changes or you change the style of your site, the plugin will re-generate the pages automatically.

This is just one simple way that W3 Total Cache can improve your site performance.     After reviewing the technology, the configuration parameters, and the various options available, using W3 Total Cache can be a great way to improve the visitor experience on your site.

W3 Total Cache and Store Locator Plus

Just be careful with the extra options for JavaScript minify and caching as it can wreak havoc on more complex scripts.   The Store Locator Plus scripts, for instance, can have issues on sites that either minify the script and/or add date/time stamps or version stamps to the JavaScript calls.   Those timestamps create unexpected parameters for the backend AJAX listener.


PHP APC is a PHP caching system that can be implemented by any PHP application.   Enabling this feature is typically done at the system admin level and is a system-wide setting.   Thus, this is more appropriate for people running a dedicated server.  If you are on a shared server you will likely be limited to disk storage caching in plugins like W3 Total Cache or Super Cache.

After installing W3 Total Cache, I noticed settings for Opcode style caching.    After some research I found the simplest way to implement the more advanced Opcode cache was to install PHP APC.   PHP APC, or the Alternative PHP Cache, is a simple install on most Linux systems running PHP.  On my CentOS boxes I can just run the yum install php-pecl-apc command.  There is a simple command on most Linux servers.  The APC module needs to special compilation, simply install and restart Apache.

Once you have PHP APC installed the easiest way to take advantage of it is to go into W3 Total Cache and enable Object Cache and set the cache type to Opcode : APC.    This is the recommended option and should be used, when possible, over the database cache.

One side note here, this can be memory intensive.   Thus it is best to only use the APC cache for memory-centric applications, such as the storage of PHP code modules.    Thus, enabling this for object cache is a great use of APC.    However, using it to store cache pages is not optimal use of the memory stack.   Your WordPress site probably has more pages that are accessed on a regular basis than will fit in the memory cache, so use the disk storage setting for the page cache and reserve the APC cache for objects.

When you configure W3 Total Cache to use APC to store objects, the most often used sections of the WordPress core and more popular plugins will load into memory.   Now, whenever someone is visiting your site much of the calculation and algorithmic “gyrations” that happen to build a page or to load configuration settings are already pre-calculated and stored in RAM.   Through W3 Total Cache, WordPress can simply fetch the “ready to go” information directly from RAM, saving on disk I/O an dramatically increasing performance.

Configuring APC

It should be noted the out-of-the-box, APC is set for a small-to-moderate environment.  WordPress with W3 Total Cache is a bit heavier than a simple web app, so you will likely want to change the default APC parameters.    You can find the settings in the php.ini file, or on newer configurations in the php.d directory in the apc.ini file.     The first thing you should consider changing is the base memory size reserved for APC.   The default is set to 64M which is not really enough for WordPress to make good use of it.  On my site I find that 128M seems adequate.

If you are not sure about how your server is performing with the setting defaults, hunt down the apc.php script on your server (it was installed when you installed php-apc) and put it somewhere in your web server directory path.  I do NOT recommend putting it in the document root, as noted in the APC hints.   Instead put it in a protected sub-directory.   Access it by surfing directly to the URL path where you installed apc.php.

The first thing you should look at is the “Detailed Memory Usage and Fragmentation” graph.    If your fragmentation is over 50% then you probably need to adjust  your working memory space or adjust which apps are using APC (in W3 Total Cache, unset Opcode : APC and use Disk Store for everything, then turn on Opcode : APC one-at-a-time).

memory fragmentation chart in apc
memory fragmentation chart in apc

The second thing to look at, once you’ve adjusted the default memory share, is the free versus used memory for APC.   You want to have a small amount of free memory available.   Too much and your server has less memory to work with for doing all the other work that is required to serve your pages, the stuff that is never cache.    Too little (0% free) and your fragmentation rises.

Here is what my server looks like with the 128M setting.   I have a little too much allocated to APC, but changing my setting from 128M to something like 112M isn’t going to gain me much.  The 16M of extra working memory pales in comparison to the average 2.7GB I have available on the server.

memory usage chart in apc
memory usage chart in apc

My Observations

On my server I noticed a few things immediately after spending 30 minutes to install W3 Total Cache and turning on/tuning APC with 128M of APC memory.   This is on a dedicated server running CentOS 6.4 server with 3.8GB of RAM and 2 1.12Ghz cores.

Server Load Average went from 1.8 to 0.09. 

Load average is an indicator of how badly “traffic is backed up” for the cores on your server.  On a 2-core server, like mine, you can think of the load average as the “number of cars waiting to cross the 2-lane bridge”.   On my server, if the number is less than 2 that means there is no bottleneck and the CPUs can operate at peak efficiency.  On a 2-core system the goal is to have the number always less than 2 and preferably less than 80% of 2.    At 100% utilization you consume more power and generate more heat which decreases the life span of the CPU.

Memory Consumption went from 1.8GB under light load to 0.6GB under the same load.

Page load time for the home page went from 750ms to 120ms on average.

cached page load time comparison
cached page load time comparison

By the way, that 18.31s spike in response time?  That is when the server started tripping over itself when memory and page requests could not keep up, the server load crossed the 2.0 line around 2PM and because it was a on a light-traffic day (Saturday afternoon) it took nearly 2 hours for the traffic jam to get so bad the server just gave up trying.

The bottom line, even if you are not running a site that is getting a  lot of visitors you can still improve the user experience by installing a simple plugin.  If you want to take it a step further, look into the PHP APC module.   Just be sure to have a backup and if possible test first on a staging environment.  After all, every server configuration and WordPress installation is different.  Not all plugins and themes will play well with a caching system.


Posted on

PostgreSQL – Exclusive Locking On Triggers

We have been working on a client’s project and dealing with an especially complex view in a PostgreSQL database. The view has a few tables linked together as well as several sub queries, a case statement, and a number of elements that do aggregate functions to build a single record of the view.

After updating the admin panel to allow them to use the database & admin page for processing orders (versus an email + excel system) we ran into several issues. The first issue was a significant degradation in performance whenever the system was under moderate load. This is important to note as our baseline release testing did not uncover the problem. The logic in the app was syntactically and functionally correct. However the application misbehaved under load, causing a bottleneck and essentially shutting down the app.

The Table Trigger Loop

We implemented a series of update triggers and stored procedures to propagate certain data elements between tables. Part of the design allowed for 2 tables to update each other. The UI allowed for an admin user to update Table A, which called a trigger to push a status field update into Table B (the legacy data). In other places Table B *could* be updated first, so we had a trigger to update Table A. The problem is the triggers would loop. Table A triggers an update in Table B triggers an update in Table A etc.

The initial designed called for turning off the triggers in the other table within the stored procedure. This requires an ALTER TABLE command. ALTER TABLE forces an exclusive lock on the database. NOTHING else can happen during this command, it is one of the few (if not the only) truly exclusive locks on the locking hierarchy.

This really slows down bulk updates. “Lock entire table”… insert… “unlock entire table”.

Master/Slave Tables Is A Better Solution

A better solution was to take out the alter table commands completely and remove the Table B updating Table A trigger. We made an application logic decision to make Table A the master and Table B the slave and eliminate (and/or log) any where Table B gets updated first. That should NEVER happen in the new app but some legacy stuff may still do that.

This is much faster as it generates no exclusive DB locks.

Entire Application Assessment v. Technical Complexity

In this case thinking about the overall solution which includes the application design and implementation as well as the technical syntax on the application and DB layer all work together to improve performance. Knowing the application and coming up with an overall design solution is often as effective as fancy technological solutions. Sometimes less complexity or technical “mojo” is the right answer to a complex problem.