I am using Piwik v3.2.1 with apache webserver, We noticed that visitor log query for one year range takes more than 10 + secs to render the results or gives up with oops error while same db query is completed within sub-second.
Is there any performance tuning available to make it faster? I tried adjusting the php memory list , apache parameters tuning etc., doesn’t have any effect on the slowness.
The Visitor-Log does tens of queries and JOINS per row (actions, conversions, custom dimensions, etc.), so this is not really comparable. Consider minimizing the range you’re querying for.
We ran the same query on the database and it showed up the results quickly and we don’t have that much data. On the dashboard it takes more than 20 + secs even for 6 months data.
I suspect php even though there are no errors on the apache logs pertaining to this. Is there any php tuning available for performance?
Thanks
The time taken to transfert the data to the browser + the time the browser takes to render the data can make a big difference compared to the result set in the database. PHP / Apache should not cause a huge impact here.
About how much data are we talking about here? Cached vs. non cached query can also make a difference in response speed. While waiting for the query to return, have a look into the mysql processlist to check the underlying query that takes so long.
It’s pretty less around 200 MB.
If you’re requesting 200MB API text data in one go, your application design is definitely flawed. Try splitting that in smaller chunks as needed.
Then,how do we view one years’ worth of data on the dashboard if it is not able to handle less than 200 MB data? Can you please suggest me how the application design can be improved ?
Hi,
I think we are talking past each other.
You want to view the full details of every single visitor in a year (which is 200 MB and therefore probably 1000+ pages of logs) on your screen at the same time without a pagination?
If not, what am I misunderstanding? How many visits are you displaying on one page?
I get this error " Oops… there was a problem during the request. Maybe the server had a temporary issue, or maybe you requested a report with too much data. Please try again. If this error occurs repeatedly please contact your Piwik administrator for assistance" as soon as I select year in the period. For month it shows the data, for date range it shows same error if I select more than 6 months.
That means that the webserver cancels the request because it ran into a timeout while parsing the data or waiting for mysql to come up with the huge resultset. That’s not matomo cancelling the request.
a) You can try to set max_execution_time to a higher value to tell the webserver to wait longer for the response data. (not recommended)
b) You should design your application to request smaller parts of the data. (recommended)
Thanks. Can you please elaborate how do I design the application to request smaller parts of the data?
E.g. query the API just for one month, not for a whole year. But we can only guess as long as we don’t know what you’re trying to achieve.