At work, we are starting to use Vagrant to manage our virtual machines, and are largely having great success with it. Through mapping a virtual directory back to a source folder on our Mac, we are able to use the same git repositories across multiple virtual servers and operating system environments, and thus Vagrant automates most of the VM build and configuration process for us.

One area that isn’t automated, that we decided should be, is mapping the Apache configuration files stored in our git repositories to Apache automatically. I wrote this small script and placed it in our Puppet / PuPHPetfiles/exec-always directory, which crawls our git folders looking for local configuration files and mapping them into an Apache conf file at initial vagrant up and then at every reboot:

We have PuPHPet set up to map /Volumes/Sites to /var/www, so that /var/www contains all of our repos. Apache config is found in /var/www/reponame/apache/local.conf – if your configuration differs the above would need to be modified. However, it automates the process of setting up Apache config, which just helps us develop faster. Combine this tactic with the use of dnsmasq to automatically resolve all addresses ending with a custom TLD directly to the virtual machine, it also eliminates the need to modify the hosts file for each new site.

EDIT: Depending on how fast the mapping to /var/www happens, it is possible that Apache will attempt to load configuration from /var/www before it is actually mounted, causing Apache to error out and fail to start. I changed the Apache reload directive for a restart directive to ensure that Apache starts after loading the config, which is already being delayed by 30 seconds to give the filesystem time to fully mount.

WP_Meta_Query has a problem – it doesn’t perform well when using the OR operator with multiple key/value pairs for comparison. The performance worsens exponentially with each additional key/value pair added to the query. The problem is due to how the SQL query is constructed. Let’s take an example WP_Query containing a meta query:

PHP

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

$query=newWP_Query(

array(

'meta_query'=>array(

'relation'=>'OR',

array(

'key'=>'key1',

'value'=>'value1',

'compare'=>'LIKE',

),

array(

'key'=>'key2',

'value'=>'value2',

'compare'=>'LIKE',

),

array(

'key'=>'key3',

'value'=>'value3',

'compare'=>'LIKE',

),

),

)

);

The above query produces the following SQL statement:

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

SELECTSQL_CALC_FOUND_ROWSwp_posts.ID

FROMwp_posts

INNER JOINwp_postmetaON(wp_posts.ID=wp_postmeta.post_id)

INNER JOINwp_postmetaASmt1ON(wp_posts.ID=mt1.post_id)

INNER JOINwp_postmetaASmt2ON(wp_posts.ID=mt2.post_id)

WHERE1=1

ANDwp_posts.post_type='post'

AND(wp_posts.post_status='publish')

AND(

(wp_postmeta.meta_key='key1'

ANDCAST(wp_postmeta.meta_valueASCHAR)LIKE'%value1%')

OR(mt1.meta_key='key2'

ANDCAST(mt1.meta_valueASCHAR)LIKE'%value2%')

OR(mt2.meta_key='key3'

ANDCAST(mt2.meta_valueASCHAR)LIKE'%value3%')

)

GROUPBYwp_posts.ID

ORDER BYwp_posts.post_dateDESC

LIMIT0,10

On the install where I ran this test, the raw SQL query execution time was 9.154 seconds on a WordPress install with 4,445 rows in the posts table and 40,453 rows in the postmeta table.

Why is query execution performance so poor?

The root of the problem is the fact that the query is performing three INNER JOIN operations before filtering the results using the WHERE clause. The problem worsens exponentially with every additional meta query parameter, because each parameter results in another INNER JOIN. In this example, the first record has 8 meta values, and is joined to itself three times, which results in 8 * 8 * 8 = 512 rows for one post. Multiply this times the total number of posts, and you can begin to see why it takes so long to run the query.

Additionally, the INNER JOIN operation is tacking the postmeta columns onto the columns for the post, once for each time the INNER JOIN is executed. Although only the ID column is being selected, the key and value columns are needed for comparison, so they need to exist in memory in order to complete the query.

How this can be improved

Basically, WordPress is compiling a massive temporary table in memory, and then paring it down with a series of WHERE clauses. We can improve on that by inverting the logic, and instead selecting from a series of smaller result sets using subqueries:

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

SELECTSQL_CALC_FOUND_ROWSwp_posts.ID

FROMwp_posts

WHERE1=1

ANDwp_posts.post_type='post'

AND(wp_posts.post_status='publish')

AND(wp_posts.IDIN(

SELECTpost_id

FROMwp_postmeta

WHEREwp_postmeta.meta_key='key1'

ANDCAST(wp_postmeta.meta_valueASCHAR)LIKE'%value1%'

)

ORwp_posts.IDIN(

SELECTpost_id

FROMwp_postmeta

WHEREwp_postmeta.meta_key='key2'

ANDCAST(wp_postmeta.meta_valueASCHAR)LIKE'%value2%'

)

ORwp_posts.IDIN(

SELECTpost_id

FROMwp_postmeta

WHEREwp_postmeta.meta_key='key3'

ANDCAST(wp_postmeta.meta_valueASCHAR)LIKE'%value3%'

)

)

GROUPBYwp_posts.ID

ORDER BYwp_posts.post_dateDESC

LIMIT0,10

This query runs in 0.035 seconds, which is about 260x faster for a meta_query with three OR values on this database than the native WordPress meta query SQL.

Increasing the query limit to 100 results in negligible increases in the new query’s execution time – increasing by only 0.001 seconds – whereas the WordPress native meta query increases by 0.795 seconds.

How to Reproduce: Setting the owner as a user other than the web server user, and trying to manage permissions using group membership by giving write permissions to the web server user’s group. For example:

I was having a rather large problem with Chrome in my virtualized Ubuntu 12.04 Desktop build causing a glitch with the virtualized graphics driver that would crash not just Chrome, not just my VM, but my entire OSX session. I wouldn’t be able to break out of fullscreen/mouse/keyboard capture and therefore couldn’t force-close the VM, and I would have to reboot my entire system. The display glitch seemed to only happen on sites with somewhat advanced layouts, and might be related to either Z-indexing or transparent 24-bit or 32-bit PNGs.

One notable example was the Chrome Web Store – I knew something was amiss when the display wouldn’t redraw on an action (such as a link click) without switching to a different tab and switching back. Other things were happening as well – sometimes my mouse wouldn’t be visible inside the Chrome window that was affected, or tabbing to another window to put it on top of the Chrome window would show the affected area of the Chrome window on top of the program that should have been visible. Sometimes the VM would crash of its own accord, and sometimes it was tied to doing certain things – clicking, switching tabs, closing tabs, etc.

After a bit of Googling, I discovered a post about a related issue that I think has fixed my problem. Edit both /opt/google/chrome/google-chrome.desktop and /usr/share/applications/google-chrome.desktop and modify all lines that begin with Exec= to include the following flag at the end:

–blacklist-accelerated-compositing

It appears that the problem is caused by the virtualized graphics acceleration not playing nicely with some of the code in Chrome. I haven’t had this problem with Firefox, but I did have it with Chromium, so I would imagine that a similar execution flag for Chromium would solve the problem for those users as well. I debated about just using Firefox, but I use the Chrome Inspector heavily in my development, and prefer it to Firebug / Firefox Inspector.

Many of my colleagues use ExpanDrive for connecting to servers over SFTP, since OSX does not have a native client for doing so. ExpanDrive is nice because it integrates with the Finder and mounts a drive, instead of needing to interact with the filesystem using an application, such as FileZilla. However, ExpanDrive is not free, so I don’t use it. Normally, I spend my time developing in an Ubuntu 12.04 LTS virtual machine on VirtualBox, because Nautilus natively supports connections over SFTP.

ExpanDrive recently released an update that didn’t do a great job of announcing that it would be updating the software from version 2 to version 3, which invalidates your current license, and requires you to purchase a v3 license. Therefore, one of my coworkers upgraded to v3 unintentionally and was then locked out of using ExpanDrive until he bought a new license. This experience prompted me to revisit Macfusion as a method of connecting over SFTP from OSX.

Macfusion was built on MacFUSE, which is no longer actively maintained, and does not work with current versions of OSX. However, there is a successor to MacFUSE called OSXFUSE, and Macfusion can be configured to use the SSHFS filesystem plugin for OSXFUSE. Here’s how to get it set up: