@anonymous-piwik-user opened this Issue on March 14th 2014

I'm importing some log using the python script (5M records), I'm using 6 parallel job and I've notice "often" in the mysql show processlist

Waiting for query cache lock

So I've investigated a bit and concluded that PIWIK bulk import is faster having the query cache disabled.
This is due to the continuous invalidation of the table log_visit, the query cache has a global mutex so is a bit enemy of multithreading ecc ecc...

In my case having the query cache with a size of 12Mbyte lead to around 356 records/sec, having it disabled averages to about 365.

I thought the best option is to left the cache on, AND only the SELECT who access the log_visit use the SQL_NO_CACHE, so the query of log_action is served from the cache.

Changing in piwik/core/Tracker/Visit.php at row 411 and inserting the SQL_NO_CACHE lead to near 420 recors/sec

Of course this will 'boost' also the realtime tracking.

I choose to report the priority as major due to the "easiness" of the fix...

@anonymous-piwik-user commented on March 19th 2014

The "normal" tracker don't like this mod, because it make a UNION, and in recent version of mysql you can't have two SQL_NO_CACHE

So i've added

    $select = "SELECT
                    visit_last_action_time,
                    visit_first_action_time,
                    $selectFields
                    $selectCustomVariables
";

    $select_a = "SELECT SQL_NO_CACHE
                    visit_last_action_time,
                    visit_first_action_time,
                    $selectFields
                    $selectCustomVariables
";

And later the $sqlConfigId = "$select ,
Is now
$sqlConfigId = "$select_a ,

Now everything is working well

@mattab commented on March 24th 2014 Owner

Thanks for the report and suggestions.

@mattab commented on October 11th 2014 Owner

now that we use transactions i dont think we need it

This Issue was closed on October 11th 2014
Powered by GitHub Issue Mirror