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...
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 ,
$sqlConfigId = "$select_a ,
Now everything is working well
Thanks for the report and suggestions.
now that we use transactions i dont think we need it