Bug #52916 Log performance is excruciatingly slow
Submitted: 17 Apr 2010 9:31 Modified: 24 May 2012 14:17
Reporter: Marc Schipperheyn Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Workbench Severity:S1 (Critical)
Version:5.1, 5.6.99, WB 5.2.16-5.2.21, 5.2.22 OS:Windows
Assigned to: CPU Architecture:Any
Tags: log, performance

[17 Apr 2010 9:31] Marc Schipperheyn
When you open the log tab on the admin panel, it takes minutes to display the pane on my system. The harddisk is swapping like crazy, takes forever.  Want to do a refresh to get the latest hits. It goes at it all over again. Clear the log database? No option. Autoclear after x days, doesn't work, compounding the issue.

I will tell you what. The biggest cause of entries in the slow query log on my system is the log pane. Could some indexes be added or something please? Top 10 items etc. This is unworkable. 

I long back to the days when I could just open query log files in MySQL Administrator and get my stuff done. 

How to repeat:
Open the log pane. Using table based logs. 

Suggested fix:
Some indexes might help
[18 Apr 2010 17:23] MySQL Verification Team
Thank you for the bug report. Could you please provide the my.ini of the offended server?. Thanks in advance.
[19 Apr 2010 7:22] Marc Schipperheyn

Attachment: my.ini (application/octet-stream, text), 8.89 KiB.

[18 May 2010 12:43] Marc Schipperheyn
I provided the requested feedback. But I see this bug is still set to waiting  for feedback. Since the 2.21 release still has this issue for me (as I've search for this bug and are typing this message, the Logs tab is still busy loading, hammering away at my harddisk). Hope this issue still has the attention it deserves
[19 May 2010 7:44] Susanne Ebrecht
how big are your log files?
[19 May 2010 9:04] Marc Schipperheyn
Well, quite huge. 

ib_logfile0 24Gb
ib_logfile1 24Gb
ibdata1 59Gb

This is because Workbench is not honouring the log configuration "expiry log days = 2" and cleaning up logs. 

But my log settings is "write to Tables" because earlier versions of Workbench required that.
[3 Jun 2010 8:27] Sveta Smirnova
Thank you for the report.

Verified as described.

mysql> select count(*) from mysql.general_log;
| count(*) |
|  1277453 |
1 row in set (22.68 sec)
[8 Jun 2010 13:46] Johannes Taxacher
this looks actually more like a server issue than a problem in workbench. The fact that a "select count(*) .." on the log table takes 22 secs to complete looks like a sign that theres a problem with the log-table in server.

Marc, what server version are you working with?
i'd categorize that as server bug.
[9 Jun 2010 5:09] Sveta Smirnova
I used 5.1.43 server and 5.1.48 (latest from BZR).

Johannes, yes, log tables are slow. This is known problem. But problem with WB is it doesn't display all rows from log table at once and waits not for 22 sec, but for minutes.
[9 Jun 2010 12:44] Johannes Taxacher
what i wanted to say is, that if "select count(*) ..." from log table already takes 22sec, retrieving actual data (using sth. like "select * from log_table limit 1234, 1000") will of course take (much) longer.
[16 Jun 2010 8:49] Johannes Taxacher
i changed category as this is a problem with log-tables handling in server.
[24 May 2012 14:17] Alfredo Kojima
Closing as Not a Bug because this is a server problem. In WB there's now support for viewing log files,
which will be faster than table logs for larger logs.