the CSS is not applied in Safari, the table displays black and white (Tested in Safari5 [sic])

the top table "Today / Last 30 minutes" shows a cursor on hover but is not clickable: it should show the normal pointer.

~~Performance issues: the visitor log just loads 1000 visits in memory. Instead it should use queries like WHERE id < (last min ID displayed) and idsite=X LIMIT 30. The search field should then be removed. ~~

referer URL could be clickable in the visitor log

the Visitor log should display icons for browsers, search engines and OS.

the icon for returning/known visitor takes up one row for each visitor; it could maybe be written after the browser/OS icons?

there could be a link "more" that when hover, a tooltip is displayed with the extra information about the visitor: the IP (rather than displaying it by default), the Time on site, etc. The code for the existing tooltip can be used (the blue tooltip used when page names are too long in the Piwik Page reports)

When the query takes longer than 5s to return, the new request is still thrown to fetch more visits. This means that you keep adding new requests to the queue of requests. Instead, you should never send a new request until the previous one was received.

To understand the problem, you can enable mysql slow query log and try to generate the Live! on a test website with 50,000 visits per day, generated using the visits generator.

To solve the issue, we can either: add new INDEXes on the log_ tables (not really something we would like to do), or we can denormalize the log_ tables so that we can fetch the data without doing JOINs.

jr-ewing, adding a new index is possible but very heavy update task. Can you please document why this index is needed and why is is sufficient to make Live! plugin work fine on high traffic website? for example, can you post mysql slow logs before and after the INDEX, details of the website you are testing against (how much traffic today, yesterday, the last N days, etc.)

Problem:
Live-Plugin triggers slow query, that locks up the table. This is especially critical, when it is used over the desktop client since it doesn't wait for a query to finish. The query stack then fills up until - in worst case - the database crashes.

Adding an index to the single col visit_last_action_time may solve the problem.

For testing, I used a simplified SQL-Query in a simplified test-case (query cache off / flushed):

A deeper view in this explains, why this key changes the query times so much:
SQL: (EXPLAIN SELECT [...] INTERVAL 10 DAYS..) - changed interval to 10 days, because the effect is manifesting better here:
previosly (without the index on visit_last_action_time), MySQL uses the index_idsite_date_config-key for the query (since idSite is in the where-clause). MySQL then scans all Rows, where idSite is x and then sorts out the rows where the date-range definition of the query doesn't fit. This causes a big data throughput because many rows are queried that aren't needed in the end.

With an index on visit_last_action_time, MySQL changes the query-type to "range" and uses the visit_last_action_time-index for the query. So it doesn't fetch any other rows that aren't needed in the result set. A lot of data overhead could be saved here to speed the query up extremely.

These multiple Joins are extremly expensive for performance because MySQL is using nested loops. In this case, MySQL joins over a Million rows (using a tmp table) and then limits them to the ten highest idvisit-id's. This is a huge piece of data to process. As we can't know how large the tables will grow in a high-traffic environment, we should avoid table joins here but rather use a stepwise approach: fetching the relevant idvisit-id's from the first table, query them from the second one and so on. So it will be possible to build an array of correlated data and it's mich faster.

2.4s sounds reasonnable, but still quite slow; is the INDEX fully hit during the query?

Also, would it be possible to add the request throttle, so that you only send one request at a time, and not queue up requests on the server? Currently, I believe that if the request hits the DB and takes 60s to return, the Live! plugin will queue up dozens of requests, killing the webserver. What do you think?

jr-ewing, my comment meant that the Javascript should only send the request to the server if the previous AJAX request was received. You could add a test in the response handler that would set a flag, allowing other AJAX to trigger. If the flag is not set, it means that the AJAX request wasn't received yet and no other requests should go to the DB until the first request was received.

add a server_date > $sevenDaysAgo to ensure that the limit is bounded and doesn't full scan of the log table.

Live! Widget

Queries now run fast, except the first one that is the same as the one above.

fixing this query should therefore fix performance issues with the Live! widget. I'm not sure whether to limit the Visitor log to the last day, or more. Users might want to access logs from older dates too. Maybe the query for the Live! widget only should restrict to the last 24 hours, when the visitor log LIMIT 1000 would limit to the last 7 days?

The proper solution would be to have the visitor log fetch each day separately automatically when clicking Next or Previous (and have the last date as a parameter).

Fixes performance issue with the Live! widget by changing the primary key on the log_visit table: all sql queries must hit the idsite first, then the idvisit, to ensure the INDEX is used for the ORDER BY LIMIT (without doing full table scan).

It seems that a double visit to the same page aren't shown.
To replecate this Error go to your Page an click the same page twice or more.
The Live Plugin shows only the first Visit of each Page.

May be it's desired to see only the uniqe pages the user visited?! For me it is useful to see the whole path the user is gone, to see on which pages the user may be confused and are not going the path that i prefer for him.

I'm working on adding customizable icons to the Visitor Log - basically, if a specified regex matches the URL, use a specified icon instead of the default. This can be set up to display the main types of pages on a site at-a-glance or to highlight key pages.

I'm doing this inside visitorLog.tpl. Couple questions - first, where should I be storing/loading the configuration from? Also, and more importantly for this ticket, what needs to be done to make this something that could be rolled in to the core plugin?

That's a huge difference, unless I'm missing something and this functionality is used also somewhere else (in that case I'd suggest creating separate functions for counting visits and pageviews and fetching those data).

Yes, where the view is only displaying the number of visitors, it would be more efficient to use a SQL query that COUNT()s. However, changing that private method in API.php changes the semantics of the Live API.