Performance Issues and Timeouts

[derDieDasJojo]

Describe the bug
We are using commonsbooking to manage around 100 things and have around 4000 bookins (within the last years).
The website is having more and more timeouts especially when changing bookings via the admin Interface.

To Reproduce
Steps to reproduce the behavior:

• install commonsbooking
• create arount 100 things
• create arount 4000 bookings
• edit a booking

Expected behavior
There should be no timeouts using commonsbooking and commonsbooking should respond significantly faster.

Research & Assumptions
I did a little research in the Code an in the Database. And as far as I can see every single booking is a new post.
The problem with posts in wordpress is, that only the main information are stored in the table wp_posts and all additional information including that for commonsbooking are stored in the table wp_postmeta. This table does not have a seperate column for every kind of metadata but instead uses a only the three columns post_id, meta_key, meta_value. So one booking contains several entries of in wp_postmeta. And the column meta_value does not even have an index. This means that searching for an booking with an certain date of return makes it necessary for mysql to look at every entry (of this meta_type) in the table ẁp_postmeta. This datastructure seems to be the root problem of the performance challenges in commonsbooking.

Possible Solutions
I think the best solution would be to migrate all relevant (additional) information about a booking to an own table eg. wp_cb_booking and add indices to all columns the system could possibly need to search for.

As a first measure of mitigation it could possibly also help to add indices to the column meta_key of wp_postmeta.

[datengraben]

Thanks for the writeup! Speaking for me, the issue is known at least internally.

The design decision was on purpose after the re-write of cb1 (wielebenwir/cb-legacy), which had your proposed table design implemented. The argument was to leverage wordpress's system around posts and meta fields. Though I did not decide it.

You already gave the answer to the problem. Because you are not the first one to encounter the problem (for wordpress and also for commonsbooking), there is already an plugin for the purpose of indexing wp posts and meta fields!
https://wordpress.org/plugins/index-wp-mysql-for-speed/

Because I also encountered the problem, I have at least some experience with some staging depoyments of commonsbooking 2.8.5. One with a similar data environment, with 100+ items and also multiple thousand bookings. The update of the indices went smooth and site was functional!
If you can, maybe you can share some benchmark infos? (Check out the settings of the plugin, you can enable monitoring your SQL statements prior to the UPDATE INDEX step).
I also will try to gather before and after UPDATE INDEX benchmark data!

[datengraben]

@nelarsen I gathered some data pre-activation (7k queries) and post-activation (~13k queries) of the index-plugin but at the moment I find it hard to closely match the rows with their query patterns AND the tracebacks. Do you have the same problems?

Because of this I will split them into two parts Fully matched and Match only via query pattern. I think the input size of for example the ilist can vary much, which depends on the actual traceback.

Fully matched

When	Area	Measurements	Total	Mean	Spread	p95	query-pattern	stacktrace
Pre-activation	Site	34	41.9ms	1,23ms	0,69ms	4.11ms	1	2
Post-activation	Site	33	28,8ms	0.872ms	0.36ms	1.6ms	1	2

Match only via query pattern

TBD

Footnotes

1. Query Pattern 1

   SELECT post_id, meta_key, meta_value FROM pPiG_postmeta 
   WHERE post_id IN (ilist) ORDER BY meta_id ASC
   

   ↩ ↩²

2. Traceback 1

   require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), 
   include('/themes/kasimir-theme-master/page.php'), get_header, locate_template, 
   load_template, require_once('/themes/kasimir-theme-master/header.php'), 
   wp_nav_menu, wp_get_nav_menu_items, get_posts, WP_Query->query, 
   WP_Query->get_posts, update_menu_item_cache, _prime_post_caches, 
   update_postmeta_cache, update_meta_cache
   

   ↩ ↩²

[nelarsen]

Hallo @datengraben, unfortunately I don't quite understand what you did and what you are asking me. Could you elaborate a bit? I'm still very interested in the performance issues and I can do the tests, you are asking for - as soon as I understand (-;

[datengraben]

@derDieDasJojo If you want, it's possible to contact us via commomsonbooking.org and you can share more info that you consider private (your setup/config or your website), which you maybe won't share here in public.

[nelarsen]

I can partly confirm the problem (100 items and 4000 bookings leading to some operations being very slow) on a Wordpress installation which was setup several years ago (hoster: all-inkl.com) and still had only isamDb tables rather than innoDB tables. The same test with 4000 bookins and 100 items on another setup (fresh WP installation on the smallest available Strato vServer) was MUCH faster even with the default configuration and no special plugin for creating indices.
After installing the recommended plugin (thank you for the recommendation @datengraben ), converting tables and adding index to the postmeta-table, also the old Wordpress installation on all-inkl.com is now blazing fast for the test case.

BTW: Please continue sharing your findings here and not only in private (-; I'm still very interested.

[hansmorb]

I just converted this to a discussion because I also have a lot to say about this :)

I am currently in the process of migrating an instance with 300 item & locations & timeframes as well as 80.000 bookings. The performance is not great and I am trying to figure out the issues. Running the aforementioned plugins did not work. The main problem is with the cb_items_table shortcode. The issue there is, that the way this table is created is very inefficient.

Every single cell in this table corresponds to one database call. The surroounding query functions and each row of the table are cached but when it is first built every morning it can take around 20 minutes for this specific instance. One way to improve performance imo would definitely to do some work in the Calendar::renderItemLocationRow and Calendar::getCalendarDataArray .

I have created a branch called experiment/queries-test where you can test the performance of this uncached shortcode using the wp-cli command "table". So you can create a testing enviroment which has a lot of data and try running the wp table command. It will output how long each row of the table takes to render fully. It will clear the cache beforehand but not during the table creation process so it should be an accurate representation of what happens when you render the table each morning.

[hansmorb]

What I think also might have an impact:

The

commonsbooking/src/Repository/Timeframe.php

Line 176 in f5d0a32

public static function getPostIdsByType( array $types = [], array $items = [], array $locations = [] ) {

function is called by the respective get functions for Timeframes and booking and will return all post IDs that we MIGHT concern ourselves with because they belong to our installation. The subsequent filtering is then done on the postIDS using the

commonsbooking/src/Repository/Timeframe.php

Line 275 in f5d0a32

private static function getPostsByBaseParams( ?string $date, ?int $minTimestamp, ?int $maxTimestamp, array $postIds, array $postStatus ): array {

function while including all of the postIDs that the getPostIdsByType function got into the custom SQL query. In my test instance this can sometimes create SQL queries that contain over 4000 post IDs, that can't be good, right?

[datengraben]

Yes, I can confirm performance regression on getPostsByBaseParams. A page with cb_items_table and cb_map is rendered.
The table can be extracted via query monitor plugin

But I did not check if an sql, which includes both operations, is significantly faster. For example bc of mysqls query optimization.

Calling function	Count	Time (seconds)
CommonsBooking\Repository\Timeframe::getPostsByBaseParams()	120	2,0300
CommonsBooking\Repository\Timeframe::getPostIdsByType()	11	0,1758
CommonsBooking\Repository\Restriction::queryPosts()	98	0,1110
update_meta_cache()	215	0,0496
WP_Post::get_instance()	198	0,0375
get_option()	49	0,0163

Example sql query:

**SELECT** **DISTINCT** pm1.* from pP5G_posts pm1  
**INNER** **JOIN** pP5G_postmeta pm4  
**ON** pm4.post_id = pm1.id  
**AND** pm4.meta_key = 'repetition-start'  
**AND** pm4.meta_value **BETWEEN** 0  
**AND** 1700179140  
**INNER** **JOIN** pP5G_postmeta pm5  
**ON** pm5.post_id = pm1.id  
**AND** ( ( pm5.meta_key = 'repetition-end'  
**AND** pm5.meta_value **BETWEEN** 1700092800  
**AND** 3000000000 )  
**OR** ( pm1.id not in (  
**SELECT** post_id  
**FROM** pP5G_postmeta  
**WHERE** meta_key = 'repetition-end' ) ) )  
**WHERE** pm1.id in (1173,1582,1593,1627,1630,1644,1645,1658,1659,1660,1661,1665,1667,1670,1673,1691,1697,1700,1701,1727,1728,1731,1732,1733,1739,1740,1773,1774,1777,1778,1779,1780,1782,1809,1811,1815,1825,1835,1844,1845,1858,1860,1891,1896,1916,1919,1924,1931,1932,1936,1943,1946,1947,1967,1977,2007,2013,2069,2089,2091,2093,2101,2134,2165,2167,2191,2200,2212,2213,2225,2226,2229,2237,2252,2255,2279,2314,2319,3879,3915,3998,4002,4006,4219,4220,4329,4408,4414,4418,4453,4496,4502,4503,4504,4505,4514,4517,4550,4563,4575,4578,4592,4596,4603,4609,4623,4638,4656,4657,4668,4686,4698,4699,4707,4708,4741,4754,4756,4775,5078,5082,5201,5332,5510,5511,5556,5580,5696,5806,5813,5870,5920,5949,6083,6260,6502,6691,6720,6732,6759,6827,6926,6968,7103,7115,7127)  
**AND** pm1.post_type **IN** ('cb_timeframe','cb_booking')  
**AND** pm1.post_status **IN** ('publish','confirmed')

[hansmorb]

So, I've just created a script on the aformentioned testing enviroment that allow you to create a bunch of test data to run performance tests.

I usually use wp-env for this, since the wp-env clean all command allows me to re-build the entire enviroment if I want to.

So what you can now do to test the performance (and also install some shortcodes to view)

git checkout experiment/queries-test
npm run start
npm run env:start
npm run cypress:setup
./bin/wp-env-cli tests-wordpress "wp --allow-root flood" 
./bin/wp-env-cli tests-wordpress "wp --allow-root table"

Or just run the new "wp flood" and "wp table" command in your own testing enviroment.

What I think is also noteworthy here is, that this enviroment does not have any current bookings but is just slowed down by old bookings that should no longer be relevant.

As of right now, it takes me about 4028.124368 milliseconds to render one row,

Average mapping time per day: 123.89236746875 milliseconds.
Timeframe::getPostIdsByType() took 63.114351ms and returned 51 posts
Timeframe::getPostsByBaseParams() took 42.563249ms
Timeframe::filterTimeframes() took 0.88971ms
Took 107.316281 milliseconds to query timeframes
Took 107.589677 milliseconds to map timeframes
Took 4.245017 millisceonds to map restrictions
Took 0.159765 milliseconds to sanitize slots
Took 112.468371 seconds to generate timeframe slot

Those are the times I am currently averaging, 200 rows for the item table take about 13 minutes which is less than ideal.
This is also not an unrealistic scenario because we often need to get all availabilites of items, for example on the map.
Properly warming up the cache could help here but we need the page to be more responsive for first time loads too.

Oh, also wp-env seems to have an issue with the /tmp/ directory, so I just create a cache directory in the root folder of the plugin and give it 777 permissions and point the cache directory (in advanced settings of the plugin) to /srv/http/www/wp-content/plugins/commonsbooking/cache

EDIT: wp-env has some Caching issues for me so maybe you are better off with just running it on a regular site.

[datengraben]

If someone still follows this thread, hans and I had a discussion about cron jobs (system scheduler), an experimental branch of an alternative solution and its SQL table indicies here: #1679

I am still interested in progress/opinions/data from everyone else.