MySQL Bugs: #52916: Log performance is excruciatingly slow

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:	None
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

Description:
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

Thank you for the bug report. Could you please provide the my.ini of the offended server?. Thanks in advance.

my.ini

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

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

how big are your log files?

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.

Thank you for the report.

Verified as described.

mysql> select count(*) from mysql.general_log;
+----------+
| count(*) |
+----------+
|  1277453 |
+----------+
1 row in set (22.68 sec)

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.

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.

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.

i changed category as this is a problem with log-tables handling in server.

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.