23

Ever since the beginning, WPML performed way too many database accesses. If your site was on a busy shared host, you might have noticed a serious drop in performance with WPML. Version 1.3.3 fixes this, by eliminating 90% of these queries.

You can grab the first beta release and try it. We’re already using it on wpml.org and it looks fine.

There are no new functions (a few minor bug fixes did make it in) and if you’re running from a very fast server, you may not notice any change. However, busy sites running on shared hosting will see an immediate improvement in site response.

Caching and prefetching for language information

WPML now includes a new caching class which is responsible for holding information that’s used more than once. Before, whenever any function needed something, it asked the database server. Now, it asks the cache which only accesses the database if it doesn’t yet have that information.

Additionally, some pages like the blog page (or home) contain multiple posts. WPML now gets the language information for all posts in one database access and saves in it the cache for when it’s needed.

The results are:

  • Before language caching – 160 queries
  • After caching – 17 queries

Caching for CMS navigation

We thought that there’s a problem with the language information, but the CMS navigation actually took even us by surprise.

To generate the navigational elements in wpml.org, it took no less than 250 database queries. There’s a good reason for that too. WPML’s sidebar and top navigation are complex elements. They go up and down the page hierarchy, determining what needs to be displayed.

Caching for those is now done on the HTML level. E.g., the navigation elements are produced once and then saved. Subsequent page displays will require just one single call to fetch from the cache.

It turns out that we’re not alone. If you’re using a different plugin for drop down menus, check how many database accesses it’s causing. You’re in for a surprise…

How to test database queries

If you’re wondering how to test the number of database queries WordPress does, look no further than the great Debug Queries plugin. It will show you the accesses, how long they actually took and the breakdown between PHP processing and database access time.

Let us know how it’s going. We’re looking to release WPML 1.3.3 soon and want to know if there are any problems.

23 Responses to “WPML 1.3.3 with database caching”

  1. Great idea and also great, you use myn plugin Debug Queries, thanks for reply and use it. I hope it helps to see the problems. More informations and a adition to this plugin is my plugin Debug Objects.
    Best regards

    • Your plugin is a real saver. And, I’m sure that we’ll also find very good use for Debug Object. Thanks for pointing it out.

  2. I am running on a shared web hosting. Here are some results from my index page (Using Debug Queries)

    First time run:
    * Total query time: 1.31704s for 238 queries.
    * Total num_query time: 3.077 for 240 num_queries.
    * Page generated in 2.96604s, 55.60% PHP, 44.40% MySQL

    Second (cached) time run:
    * Total num_query time: 1.155 for 134 num_queries.
    * Page generated in 1.12049s, 87.87% PHP, 12.13% MySQL

    Great job!

    • Thanks for sharing. It is a great improvement.

      So, I guess you’re using both the multilingual and CMS navigation, right?

      I hope that those remaining 134 queries don’t come all from WPML. If so, let me know and we should look and see why.

  3. Yes, I am using both the multilingual and CMS navigation (Top and Sidebar).
    I disabled WPML and now they are 81 queries (50 queries less than before). I don’t know if that sounds right or not.
    * Total query time: 0.86209s for 81 queries.
    * Total num_query time: 2.219 for 83 num_queries.
    define(‘SAVEQUERIES’, true);in your wp-config.php
    * Page generated in 2.19522s, 60.73% PHP, 39.27% MySQL

    I found out that the pages widget was using lots of queries so I removed it (Nice Debug Query).
    * Total query time: 0.08682s for 36 queries.
    * Total num_query time: 1.379 for 38 num_queries.
    * Page generated in 1.36932s, 93.66% PHP, 6.34% MySQL

    However, under my top navigation bar. I want to list children of the children pages. Ex: Parent>child_1>child_a. How do I do that? This will make the pages widget obsolete. I’d like to request a way to control the way things are ordered in the top navigation bar. What categories to put, their order, and the ability to place them outside the News (or home) drop down list. I will put that in feature request.

    • That’s good to hear. WPML is supposed to take about 50 queries for the index page now.

      I remember how your site was a couple of weeks ago and with the recent changes, it’s a huge difference. Both cutting down WPML’s accesses and the pages widget help a lot.

      We’re not planning on adding more navigation levels for now. Showing more levels is actually the job of the sidebar navigation. Can you show me an example of a page which is difficult to get to?

  4. Thanks for your quick response!

    I am doing a pathology section for dental students. So the headline is called Pathology which talks about different diseases. Under this section you have different sub categories depending on the lesion characteristics (cysts, benign tumor…etc). Under those subcategories, you have list of all the diseases in that subcategory. Say you want to access a disease very quickly, you know which category this disease falls under (educated guessing). In addition, if you are able to list the pages under that subcategory you can immediately see what diseases are included instead of opening each category, waiting for the page to load then look under the sidebar. That’s why I was using the pages widget. But it is a performance killer! Showing more levels will improve the usability of the website for non tech savvies (Dentists and dental students)

    Thanks!

    • That makes sense. We had a similar situation in WPML.org where it became more and more complex, not allowing each page to be directly accessible from the home page.

      I think that a good search box is the solution. Google taught people to search for everything, even if it’s obvious and in front of their eyes. If you highlight the search box and use the standard WP search, people would use it more to find what they’re interested in.

      • I believe in search too. I am using Google search on my website. Sometimes I feel having more than a way to do things can bring benefits to the table and save time. This is very true if you are familiar with the website and you know where to look for the stuff.

  5. I also have tested beta and following are my results.

    before beta
    # Total query time: 0,01964s for 243 queries.
    # Total num_query time: 1,130 for 244 num_queries.
    # Page generated in 1,00000s, 98,04% PHP, 1,96% MySQL

    after beta
    # Total query time: 0,01542s for 165 queries.
    # Total num_query time: 1,118 for 166 num_queries.
    # Page generated in 1,00000s, 98,46% PHP, 1,54% MySQL

    huge difference of total # of queries but total time change is very limited isn’t it ?
    or is it normal 🙂

    • The small difference in processing time is due to the fact that your database server is fast, so having many queries doesn’t load it too much. The time measurement also appears skewed. I don’t really believe it takes 1,000 seconds for a page to load…

      There’s still a considerable number of queries (165) and I suggest that you try to see which other plugins are causing them. WPML is responsible for about 50. You can do as Wisam did – disable other plugins and see when the query count drops. Then, you can decide if that extra functionality justifies the load or not.

      Overloading the database server is something that needs to avoided. It’s a central and unique resource and even if today it’s responding very fast, that can change in the future, as your server load increases.

      • thanks for your response, now I understand it better. just as your recommendation, I did consider removing other plugins and found which plugin causes it; Nextgen Gallery plugin

        after disabling NGG Plugin

        # Total query time: 0,12076s for 105 queries.
        # Total num_query time: 0,985 for 106 num_queries.

        and others plugins use very few queries.

        so what is the acceptable # of queries ?

        and 1,54% MySQL use is good isn’t it ?

        I do not want to make this page out-of-topic, wpml’s cache fix, so sorry for my further questions.

        best~

        • I don’t have a good answer for you. Your site worked fine with 243 queries too and wpml.org worked with more than that.

          It’s just good to know where these extra queries are coming from. Other people use far slower servers, so for them, those extra queries were critical.

  6. You can compare my readings with yours. For me (on a shared web hosting) @ 238 queries I am using 44% of MySQL but for you at 243 you are using only 1.96%! with caching enabled my MySQL usage dropped to 12.13% while yours to 1.54% which would explain the difference in performance with the new WPML caching between my website and yours.

  7. how about refreshing this cache?

    I use as default language Estonian (et).
    I changed manually in database translation for et-et from “Estonian” to “eesti” and replaced language flag too, because for et there is an Ethiopian flag not Estonian.

    After upgrade all is back.

    Tried again to change language translations in _icl_languages_translations but in web it didn’t get correct things using icl_get_languages(). After investigation I saw that this caching mechanism took from cache those values. after manually clearing this key using icl_cache_clear() I got my key value back.

    How would be the correct way to do this?

    Can I change in admin translatsions for table “icl_languages_translations” ?

    Could You please change correct flag for language code et (Estonian):
    http://www.ee/img/icons/flag_et.gif
    Country code for Ethiopia is “ET”, but language iso code “et” is estonian:
    http://www.mathguide.de/info/tools/languagecode.html

    brg.

      • After upgrade word in table icl_languages_translation for et-et went back to “Estonian”

        changed in database to “eesti” and tried to clear cache in CMS navigation, but nothing happened. Worked when disabled ICL cache. After enabling again, old word was again used.

  8. Preview still does not work on my website when WPML is active even with the new update installed. Publishing posts to private then when I make sure everything looks okay. I publish the post again as Public. However, breadcrumb trails still shows Private:post_name
    So I have to go and purge the cache manually by going to WPMP>CMS Navigation every time.
    Any help is appreciated to resolve my problem.

  9. Excellent under-the-hood work of the variety that goes under-appreciated. This is one of the key things I was worried about when considering your plugin, but you’d already licked it around the time that I began playing with it. Kudos!

  10. At the very first configuration, I have accidentally set the CMS language to Chinese Traditional, instead of English. Now all my existing eng pages got pulled into the Chinese Translation. I tried to uninstall the plug-in and reinstall, but it does not take me to the first CMS configuration menu, instead it remembered my previous mistake.

    Is there a way to flush the cache, or delete database table, so I can do a fresh install of this plug-in? or is it too late now? Please help!

    Many thanks!

    • To wipe out all the language information, go to WPML->Overview. Scroll the page to the bottom and you’ll see a link to ‘Troublshoot’. Click on that.

      There, scroll again to the bottom. You’ll see a button for clearing all the language information. After you click on this, all of WPML’s tables will be deleted. Then, you can activate it again and configure it from scratch.