blocked in work because of selecting information_schema

Discussion of open issues, suggestions and bugs regarding database management and administration tools for MySQL
Post Reply
michabbb
Posts: 178
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

blocked in work because of selecting information_schema

Post by michabbb » Thu 18 Jun 2020 19:12

hi alex,

i fully understand, that dbforge wants to select "information_schema" for the schema.
my big problem is: i have some very large tables, that get re-created every X minutes. this sometimes
takes more than 20minutes. so i got stuck more than 1 or 2 minutes when dbforge is trying to fetch the information_schema in exact that moment. this is super annoying ;-((

maybe there is a way - a switch or something - that dbforge fetches the information_schema without getting blocked,
like read uncommitted or something like that.

getting blocked several times the day makes working no fun ;(

so maybe there is an option to do more caching of the schema or fetching schema without getting blocked.
in case the user realizes, that something is missing, there could be a hotkey to fetch schema the regular way,
like now.

thanks,
micha

alexa
Devart Team
Posts: 2871
Joined: Fri 24 Jun 2011 14:17

Re: blocked in work because of selecting information_schema

Post by alexa » Fri 19 Jun 2020 07:19

You could unselect or increase the value of the 'Automatically refresh local cache every...' field in the 'Text Editor -> Code Completion -> Suggestions' section of the 'Options' window.

Also, you could specify the database in the 'Do not load suggestions for these databases...' field in that options section.

michabbb
Posts: 178
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Fri 19 Jun 2020 11:33

hi alex,

thanks for your feedback. i appreciate your ideas, but please understand, this is not a solution.
the problem is not, how often one side is doing anything, it´s impossible to avoid the situation,
where dbforge selects the schema while a table gets renamed.

i don´t want to miss suggestions, that´s one of dbforges strengths, that why i paid for the software ;)
so of course disabling that is absolutely no option.

a table rename can take several minutes in a very large database.
because of mysqls nature, the meta-data gets locked in that moment.

and that means: i am unable to work with your software. and that is a blocker, I'm sorry.

what we need in that case is very very simple: instead of

Code: Select all

SELECT * FROM information_schema.TABLES 
INNER JOIN information_schema.COLUMNS c
ON TABLES.TABLE_NAME = c.TABLE_NAME
there should be an option in dbforge, to fetch this data "READ UNCOMMITTED":

Code: Select all

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM information_schema.TABLES 
INNER JOIN information_schema.COLUMNS c
ON TABLES.TABLE_NAME = c.TABLE_NAME
in that case, everybody is able to work without any interruptions!
for people working several hours a day with MySQL and dbforge, this can be
very frustrating, if the software forces you to have a break, despite the fact that we should
have more breaks, but you get it ;)

so, when dbforge would have an option like:

Read Schema uncommitted: [X]yes [ ]no (default)

the user would be able to work without any interruptions.

please understand, a heavy big database is always a living object,
many companies use cronjobs to re-create tables all the time for
aggregation or whatever. that is very common thing. but as soon as a table
gets renamed or the meta-data gets blocked, i get blocked too, and that shouldn´t happen.

people who are using my "switch" i am suggestion here, are aware that dbforge "might not get all data"
and something could be missing. but hey, that is better than being forced having a break when you
are in the middle of your daily work.

please forward that issue to your devs, i am sure they will understand that this is not a simple feature request,
it´s an important issue for heavy MySQL admins working with large databases in production.

thanks,
micha

alexa
Devart Team
Posts: 2871
Joined: Fri 24 Jun 2011 14:17

Re: blocked in work because of selecting information_schema

Post by alexa » Fri 19 Jun 2020 13:50

Thank you for the reply.

We will investigate this case.

michabbb
Posts: 178
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Fri 19 Jun 2020 16:09

thank you!!!

fyi... today while i was watching mytop (the mySQL processlist), i was able to catch these 3 minutes where
a large table got renamed. the regular schema select was "waiting", and so dbforge has its freeze.

then i stopped that query and made the same query with "read uncommitted" again, and no problem,
i get the result without any waiting.

and that´s great ;-) hopefully your people will understand.

and before someone says: working with a possible incomplete schema is not a good idea, i can say:
in my explanations, i mentioned cronjobs, that re-create tables, every X minutes. so.... even i get an old
schema (while reading uncommitted) - i still have all info, because these tables, that get created, renamed,
deleted... whatever... all the time... these tables are always the same.... nothing is really missing.

i am sure, that most people will say: renaming a table shouldn´t take longer that 0,5 seconds, sure...
but these people - i´m very sure - are not working with that many records, i am ;)

thanks,
micha

michabbb
Posts: 178
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Mon 27 Jul 2020 14:08

i know: nobody cares.... just a little example why i got blocked today for 6 minutes (in total)..... ;-(((((((((

Image

opening the "columns" in the tree took me 2 minutes just because a table got created (as every hour)... ;-((((

Alex: reading the "blocked" schema is terrible.... still..... and it would be so easy to fix... just reading that uncommited....

alexa
Devart Team
Posts: 2871
Joined: Fri 24 Jun 2011 14:17

Re: blocked in work because of selecting information_schema

Post by alexa » Mon 27 Jul 2020 16:00

We will pass it to our developers team for investigation.

michabbb
Posts: 178
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Mon 27 Jul 2020 16:35

thank you. I know it's frustrating reading problems like these. but dbforge is a tool I am using 8 hours a day, so when you wait for Infos, just because a table gets created you have nothing to do with, that makes you crazy... 🙈

michabbb
Posts: 178
Joined: Mon 21 Nov 2011 02:35
Location: DE
Contact:

Re: blocked in work because of selecting information_schema

Post by michabbb » Tue 28 Jul 2020 14:07

hi alex,

pardon, another example: today i have to change several columns (the type),
so after changing the column type, the whole app is frozen.... why? guess:

Image

just because: after every "alter table" within dbforge, there is this schema-select, and of course,
right at the moment when a tables gets created.. and i was stuck for nearly 3 minutes ;-((( again...
but this time dbforge was frozen, because waiting for the schema-select....

super annoying ;-(

i know, you already said you will let your people know, i just wanted to show another worse example
of this effect.

thanks,
micha

Post Reply