Slow (10 seconds) loading of piwik's url

General questions
1

Hello everyone, this is my first post, so please be kind.

I’ve been using Piwik for about two months now, to track usage on a single website (wow, piwik is amazing did you know that?). Anyway, things are running smoothly and without problems, but I did notice something interesting.

While testing the performance of my site, I discovered that page loads with disabled cache are timed at 12 seconds! Debugging further, I found out that the entire home page loads in 2 seconds and and extra 10 seconds just for piwik’s URL to load. That is quite a considerable amount of time to spend on loading piwik!

I went to look at the server (which runs on a Linode server, CentOS 7) and the server seems to have plenty of ram, lots of disk space and nearly zero cpu usage (90% of my site is pre-cached anyway, very optimized). So that didn’t give me any clues as to what is going on.

Next I looked at the database, which runs on MariaDB, tests show that its fast and responsive to general queries, also MySQLTuner reports that almost everything is optimized (but it wants me to raise the table_open_cache parameter).

In case it helps, the piwik database is a bit over 300MB and it has been running for nearly 2 months.

I’m also using “Piwik Proxy Hide URL”, in case that makes a difference.

So here I am, asking for your help, in case you have any pointers on what I should do next. Any help would be appreciated.

Thank you.

PS:
Delete old visitor logs is: Yes
Delete logs older than: 7 days
Delete old archived reports: No
Geolocation set to: GeoIP (Php)
Archive reports when viewed from the browser: Yes
Archive reports at most every X seconds: 150

2

Replying to my own question…

Apparently the problem was with the Proxy Hide URL. It uses file_get_contents() which causes a huge delay when loading an HTTPS stream.

The solution was to use CURL instead. I cut down the script, removed old and deprecated commands, cleaned it up a bit and converted it to use CURL.

Now page load is down to around 2 seconds. yey :slightly_smiling:

so my next quest, is to figure out why the database is 300MB after only 2 months… I can’t imagine how many gigs in size it will grow in 6+ months…

1 Like
3

@ForeachDo could you create an issue here with some ideas how the tracker proxy script can be improved?

Delete logs older than: 7 days

With such a setting you are not able to calculate unique visitors for a month period (it will be based only on 7 days of data). I suggest keeping at least 31 days of raw data.

4

I’ve created a new issue with some code snippets.

Thank you for the suggestion, I’ve set the setting to 31 days.

1 Like
5

any updates on Use cURL if fopen is not available by braekling · Pull Request #3 · matomo-org/tracker-proxy · GitHub ? :slight_smile: or to force curl usage instead ?