How to improve performance of PocketQuery?

[lcc]

I've been using PocketQuery to build automated dashboards for many of our development teams. We want to create a summary page in confluence that has multiple dashboards on one page. When we do this we can have quite a few queries that get fired when the page is loaded. When the page is loading I only see about 4-5 database connections from Confluence. The page in question takes between 90-120 seconds to load. Are there any configuration options that would allow us to change the number of allowable database connections, or anything else that might improve speed?

[lcc · Accepted solution]

Felix,

I tried the Cache for Confluence plugin and it works quite well. I still have a couple of combinations I want to test out. But it took the page load from 90+ seconds to 3-4 seconds once you have the cache created.  

Thanks for the suggestion!

Lynn

[Felix Grund (Scandio)]

Very nice! I was kind of expecting it's a good combination. Maybe you could mark the last answer as accepted for now?

[Felix Grund (Scandio)]

Hey Icc! If you like PocketQuery, would you consider leaving a review at the Atlassian marketplace? We'd be very glad ;).

[lcc]

Felix,

Thanks for the suggestion. I will have to give it a try. I will let you know what I find.

Lynn

[Felix Grund (Scandio)]

Just an idea: could you try the Future Macro of the Cache for Confluence Plugin? It's not free, but I would really be interested if this helped in your scenario. Maybe you could try the evaluation version and give some feedback?

[Felix Grund (Scandio)]

Hi Lynn!

The database connections PocketQuery establishes are currently implemented with a SingleConnectionDataSource. This means that with every PocketQuery macro, a connection is created, the statement is run, the connection is disconnected. I see that for your use case, this pattern is not adequate.

I can see that in the long-term, a pooled data source would make sense performance-wise. I had actually never expected that someone would insert more than let's say five PocketQuery macros into a page. I created a task for the pooled datasource connection. However, I can't tell anything about when we would implement this feature. So, besides revealing our plans for the features listed in my last post and the connection pool, I don't really have a solution for you at the moment - sorry!

Let me know if you found another workaround.

Regards, Felix [Scandio]

[lcc]

Hi Felix!

Thanks for your reply. In our case it's not an issue of multiple users requesting the same page. But the number of queries we have on a page. Our page has 40+ queries on the page. We wanted to consolidate several pages into one, which put several hundred queries on one page. This is when we would see a noticeable delay in loading the page. 

In watching the database while the page is loading, it looks like all the queries get processed one at a time via one connection to the database. The two improvements you mention sounds promising. 

We are looking at reducing the number of queries we are using, but would like to know if there are any configurations or options that would help.

The database we are using is MySQL version 5.1.61 and the JDBC Driver for MySQL.

Thanks!

Lynn

[Felix Grund (Scandio)]

Hi Lynn!

Do you mean that your database connections are queued when multiple users request your pages that contain PocketQuery macros? i.e. users are waiting for other users' requests on these pages being finished?

There is currently no application-level configuration for this purpose. You might check out how to configure the JDBC driver of your database.

In the long-term, we are having two improvements of PocketQuery on our roadmap that target performance:

• Caching of database results
• Asynchronous loading of results (AJAX)

I guess you'd also benefit from these new features. In the meanwhile, let me know what database type and driver you're using and I'll check if there are any other possibilities that might help you.

Regards, Felix [Scandio]