Description:
When the autosave interval is low and a large number of SQL windows are open MySQL Workbench freezes for several seconds when running the autosave. My current freeze is about 10 seconds and locks up the entire application.
Moving ~/.mysql/workbench into /dev/shm resolves the issue as the files are updated in memory and do not have to wait for the disk, disabling the autosave or setting the interval to be a long time (however this is not so helpful when it crashes and you lose your query)
Here is an extract from strace when the freeze is occurring:
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19
write(19, "type=scratch\norig_encoding=\ntitl"..., 120) = 120
close(19) = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.scratch.PHHV1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19
fallocate(19, 0, 0, 106) = 0
write(19, "select fieldname"..., 106) = 106
fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0
lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=106, ...}) = 0
fsync(19) = 0
close(19) = 0
rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.scratch.PHHV1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.scratch") = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19
write(19, "type=scratch\norig_encoding=\ntitl"..., 106) = 106
close(19) = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.scratch.MAYU1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19
fallocate(19, 0, 0, 483) = 0
write(19, "SELECT fieldname "..., 483) = 483
fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0
lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=483, ...}) = 0
fsync(19) = 0
close(19) = 0
rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.scratch.MAYU1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.scratch") = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19
write(19, "type=scratch\norig_encoding=\ntitl"..., 120) = 120
close(19) = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.scratch.HDZS1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19
fallocate(19, 0, 0, 452) = 0
write(19, "update fieldname"..., 452) = 452
fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0
lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=452, ...}) = 0
fsync(19) = 0
close(19) = 0
rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.scratch.HDZS1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.scratch") = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19
write(19, "type=scratch\norig_encoding=\ntitl"..., 107) = 107
close(19) = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.scratch.7RQS1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19
fallocate(19, 0, 0, 1009) = 0
write(19, "alter table fieldname"..., 1009) = 1009
fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0
lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=1009, ...}) = 0
fsync(19) = 0
close(19) = 0
rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.scratch.7RQS1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.scratch") = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19
write(19, "type=file\norig_encoding=\ntitle=S"..., 107) = 107
close(19) = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.scratch.3M8R1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19
fallocate(19, 0, 0, 2591) = 0
write(19, "alter table fieldname"..., 2591) = 2591
fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0
lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=2591, ...}) = 0
fsync(19) = 0
close(19) = 0
rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.scratch.3M8R1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.scratch") = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e5-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19
write(19, "type=scratch\norig_encoding=\ntitl"..., 114) = 114
close(19) = 0
open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e5-3075-11e5-a0da-989096bb62a8.scratch.MP8P1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19
fallocate(19, 0, 0, 50) = 0
write(19, "SELECT * FROM database.a"..., 50) = 50
fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0
lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e5-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=50, ...}) = 0
fsync(19) = 0
close(19) = 0
How to repeat:
Open a large number of tabs and set the autosave interval to be a low number (10/5) seconds to see an immediate result.
You should additionally have moderate load on your disk whilst this is happening for maximum effect
Suggested fix:
1. Separate the thread to cause the autosave not to directly impact the rest of the application.
2. Stagger the autosaves so they do not run all in 1 go
3. Only make autosaves to windows that have been active/changed since the last autosave.
Description: When the autosave interval is low and a large number of SQL windows are open MySQL Workbench freezes for several seconds when running the autosave. My current freeze is about 10 seconds and locks up the entire application. Moving ~/.mysql/workbench into /dev/shm resolves the issue as the files are updated in memory and do not have to wait for the disk, disabling the autosave or setting the interval to be a long time (however this is not so helpful when it crashes and you lose your query) Here is an extract from strace when the freeze is occurring: open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19 write(19, "type=scratch\norig_encoding=\ntitl"..., 120) = 120 close(19) = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.scratch.PHHV1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19 fallocate(19, 0, 0, 106) = 0 write(19, "select fieldname"..., 106) = 106 fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0 lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=106, ...}) = 0 fsync(19) = 0 close(19) = 0 rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.scratch.PHHV1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d6-3075-11e5-a0da-989096bb62a8.scratch") = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19 write(19, "type=scratch\norig_encoding=\ntitl"..., 106) = 106 close(19) = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.scratch.MAYU1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19 fallocate(19, 0, 0, 483) = 0 write(19, "SELECT fieldname "..., 483) = 483 fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0 lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=483, ...}) = 0 fsync(19) = 0 close(19) = 0 rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.scratch.MAYU1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710d9-3075-11e5-a0da-989096bb62a8.scratch") = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19 write(19, "type=scratch\norig_encoding=\ntitl"..., 120) = 120 close(19) = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.scratch.HDZS1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19 fallocate(19, 0, 0, 452) = 0 write(19, "update fieldname"..., 452) = 452 fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0 lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=452, ...}) = 0 fsync(19) = 0 close(19) = 0 rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.scratch.HDZS1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710dc-3075-11e5-a0da-989096bb62a8.scratch") = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19 write(19, "type=scratch\norig_encoding=\ntitl"..., 107) = 107 close(19) = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.scratch.7RQS1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19 fallocate(19, 0, 0, 1009) = 0 write(19, "alter table fieldname"..., 1009) = 1009 fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0 lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=1009, ...}) = 0 fsync(19) = 0 close(19) = 0 rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.scratch.7RQS1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710df-3075-11e5-a0da-989096bb62a8.scratch") = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19 write(19, "type=file\norig_encoding=\ntitle=S"..., 107) = 107 close(19) = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.scratch.3M8R1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19 fallocate(19, 0, 0, 2591) = 0 write(19, "alter table fieldname"..., 2591) = 2591 fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0 lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=2591, ...}) = 0 fsync(19) = 0 close(19) = 0 rename("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.scratch.3M8R1X", "/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e2-3075-11e5-a0da-989096bb62a8.scratch") = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e5-3075-11e5-a0da-989096bb62a8.info", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 19 write(19, "type=scratch\norig_encoding=\ntitl"..., 114) = 114 close(19) = 0 open("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e5-3075-11e5-a0da-989096bb62a8.scratch.MP8P1X", O_RDWR|O_CREAT|O_EXCL, 0666) = 19 fallocate(19, 0, 0, 50) = 0 write(19, "SELECT * FROM database.a"..., 50) = 50 fstatfs(19, {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=474310270, f_bfree=430919700, f_bavail=406820372, f_files=120479744, f_ffree=119973760, f_fsid={-1424970731, 161733912}, f_namelen=255, f_frsize=4096}) = 0 lstat("/home/tom/.mysql/workbench/sql_workspaces/locallocal-1.autosave/0c6710e5-3075-11e5-a0da-989096bb62a8.scratch", {st_mode=S_IFREG|0664, st_size=50, ...}) = 0 fsync(19) = 0 close(19) = 0 How to repeat: Open a large number of tabs and set the autosave interval to be a low number (10/5) seconds to see an immediate result. You should additionally have moderate load on your disk whilst this is happening for maximum effect Suggested fix: 1. Separate the thread to cause the autosave not to directly impact the rest of the application. 2. Stagger the autosaves so they do not run all in 1 go 3. Only make autosaves to windows that have been active/changed since the last autosave.