MySQL data (exec_context etc.) 50GB?

If you think that something doesn't work in Aware IM post your message here
Post Reply
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

Hi support,
This is a question and potential bug report. I happened to look at disk space today for a Windows server and saw it hadn´t much space left and after looking over various parts on the C:, the culprit was MySQL (total folder size over 50GB) and execution_context.idp and various binlog files (binlog.000214, binlog.000215 etc. etc.):
- execution_context.idp takes up 27.5GB and when I look at the table in the BASDB database, is has a few rows only? Is this how it usually is and should be?
- The various binlog files (approximately 10 of them) all take up around 1GB each. What are these files and is this how it usually is and should be?

I recently upgraded (not sure if this started now or was an existing thing) to 8.5 (build 2830) and on MySQL 8.0. The MySQL folder that takes up so much space is "C:\ProgramData\MySQL\MySQL Server 8.0\Data".

Please advise? Thanks
Henrik (V8 Developer Ed. - Windows)
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

So, talking to myself is fun :lol:
Henrik (V8 Developer Ed. - Windows)
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

Nobody knows what these files are?
Henrik (V8 Developer Ed. - Windows)
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

BUMP
Henrik (V8 Developer Ed. - Windows)
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: MySQL data (exec_context etc.) 50GB?

Post by aware_support »

Sorry, no idea...
Aware IM Support Team
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

Ok, so this is not a Aware generated file i.e Aware is not dependant on it, using it etc. in any way?
Henrik (V8 Developer Ed. - Windows)
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: MySQL data (exec_context etc.) 50GB?

Post by aware_support »

No
Aware IM Support Team
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

This has to be related to Aware in some way. The execution_context.idp file keeps on growing and is now 37GB and I got problems on the server in question today due to this. To try to fix the problem, I did a backup of the DB and then deleted the execution_context.idp file and then Aware couldn´t even start up again telling me the execution_context table is missing (which it wasn´t, the .idp file was missing but I could still see the table in the DB). How is Aware using local .idp files and why isn´t Aware clearing the files again after use or at least once in a while? It SEEMS as though the large .idp file is a build up of old data in some way as again the table is empty most of the time so why does the table has a connected file of 37GB?
Henrik (V8 Developer Ed. - Windows)
aware_support
Posts: 7523
Joined: Sun Apr 24, 2005 12:36 am
Contact:

Re: MySQL data (exec_context etc.) 50GB?

Post by aware_support »

The fact that execution_context table is growing is a concern - there must be something wrong in the app. Please check which processes end up in there and when.

Aware IM knows nothing about IDP files. On startup it pings the table by selecting a single record there and if it gets an exception from the database it reports that the table is missing or corrupt. So you did something there that causes the database to throw an exception.
Aware IM Support Team
Rennur
Posts: 1191
Joined: Thu Mar 01, 2012 5:13 am
Location: Sydney, Australia

Re: MySQL data (exec_context etc.) 50GB?

Post by Rennur »

Some bugs with BLOB types in MySQL 8 (v8.0.17 & v8.0.19) were identified that may be causing this.
https://bugs.mysql.com/bug.php?id=96466
https://bugs.mysql.com/bug.php?id=99339
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

Rennur wrote: Thu Apr 15, 2021 2:42 am Some bugs with BLOB types in MySQL 8 (v8.0.17 & v8.0.19) were identified that may be causing this.
https://bugs.mysql.com/bug.php?id=96466
https://bugs.mysql.com/bug.php?id=99339
Thanks and looks like this could potentially be the cause.
Henrik (V8 Developer Ed. - Windows)
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

aware_support wrote: Thu Apr 15, 2021 12:02 am The fact that execution_context table is growing is a concern - there must be something wrong in the app. Please check which processes end up in there and when.

Aware IM knows nothing about IDP files. On startup it pings the table by selecting a single record there and if it gets an exception from the database it reports that the table is missing or corrupt. So you did something there that causes the database to throw an exception.
I have identified a problem with a connection between an Aware app DB and another DB where I use an Aware process to read and parse data from the other DB and this process sometimes creates some bug loop (when there are Scandinavian å æ ø characters in the strings of the data read from the other DB) where it repeats a part of the process (sub process) and/but the error occurs (invalid character error something in Aware server logs) and/but then the same part of the process (sub process) restarts for some reason i.e loops and it does this until the memory on the server is used up and the app crash and when looping it fills the execution_context table with a row per subprocess loop/execution. I haven´t had time to fully troubleshoot this bug and/but for now I am picking up when the error occurs by experiencing the app as becoming slow and unresponsive (in house app so isn´t a big deal) and then I manually empty the execution_context table (which has many many rows of the same sub process which bugged as explained above) and manually transfer the faulty data from the other database to the Aware app. Could these manual operations i.e me emptying out the execution_context table manually be the cause for the buildup of the execution_context.idp file? (e.g the DB using the file for temporary storage or something like that and when I delete the rows in the table, the reference to the temp data in the idp file is lost leaving me with this problem?).
Henrik (V8 Developer Ed. - Windows)
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

I still have issues with large log files. Support, can you verify if the binlog files ("C:\ProgramData\MySQL\MySQL Server 8.0\Data") are used in Aware in some way or if they are purely MySQL files used for various backup and logging purposes? It is possible to configure MySQL to automatically purge these every 3 days or so which I intend to do IF Aware doesn´t use them.

Thanks
Henrik (V8 Developer Ed. - Windows)
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: MySQL data (exec_context etc.) 50GB?

Post by customaware »

I did this in the .ini file Henrik.

[mysqld]
datadir=C:/Program Files/MariaDB 10.3/data
innodb-page-size=32768
innodb_buffer_pool_size=1023M
character-set-server=utf8
log-output=FILE
general-log=1
general_log_file="C:/MariaDB/g_log.log"
expire_logs_days = 2
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
hpl123
Posts: 2579
Joined: Fri Feb 01, 2013 1:13 pm
Location: Scandinavia

Re: MySQL data (exec_context etc.) 50GB?

Post by hpl123 »

eagles9999 wrote: Wed Feb 23, 2022 5:18 am I did this in the .ini file Henrik.

[mysqld]
datadir=C:/Program Files/MariaDB 10.3/data
innodb-page-size=32768
innodb_buffer_pool_size=1023M
character-set-server=utf8
log-output=FILE
general-log=1
general_log_file="C:/MariaDB/g_log.log"
expire_logs_days = 2
Yeah, the expire_logs_days one is the one I have set in MySQL (called binlog_expire_logs_seconds there) and I set it directly in the DB via the command line, not sure if I also need to set in the ini files but will test it. It was preset to 30 days so IS set as default in MySQL. Not sure why DB´s use these files and if Aware does and hoping support can clarify that.
Henrik (V8 Developer Ed. - Windows)
Post Reply