Spectrum Mysql tmp files run the disk out of space in 22.x 23.x 24.x

Products

Network Observability Spectrum

Issue/Introduction

There are instances in which we see a SpectroSERVER is running fine and then all of a sudden it runs out of disk space:

1. When processd is starting mysql it may run the disk out of disk space.

2. During normal operations mysql may fill mysql temp #innodb_temp

3. After an online backup mysql may fill mysql temp #innodb_temp

The mysql.out will show something similar:

2023-08-16T17:54:09.603380Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2023-08-16T17:54:09.603839Z 0 [System] [MY-010931] [Server] /opt/CA/Spectrum/mysql/bin//mysqld: ready for connections. Version: '8.0.31-commercial'  socket: '/opt/CA/Spectrum/mysql/tmp/mysql.sock'  port: 3306  MySQL Enterprise Server - Commercial.
2023-09-10T06:01:23.835316Z 269 [Warning] [MY-012637] [InnoDB] 290816 bytes should have been written. Only 16384 bytes written. Retrying for the remaining bytes.
2023-09-10T06:01:24.547266Z 269 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2023-09-10T06:01:24.547308Z 269 [ERROR] [MY-012639] [InnoDB] Write to file (ddl) failed at offset 88633344, 290816 bytes should have been written, only 16384 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2023-09-10T06:01:25.141538Z 269 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'


2023-09-27T17:54:03.209252Z 476 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_6.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T17:54:03.209252Z 481 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_2.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T17:54:03.209251Z 479 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_7.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T17:2023-09-27T19:27:56.577201Z 487 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_4.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T19:27:56.616274Z 499 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_20.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T19:28:18.999471Z 487 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./#innodb_temp/temp_4.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2023-09-27T19:28:19.001050Z 487 [Warning] [MY-012637] [InnoDB] 1048576 bytes should have been written. Only 102400 bytes written. Retrying for the remaining bytes.
2023-09-27T19:28:19.001104Z 487 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.

Environment

Spectrum and Mysql 8

Cause

Bug in the processing of Spectrums mysql

Resolution

Stopping and restarting the Archive Manager and mysql will temporarily resolve this as the mysql temp area is cleaned up when the processes are stopped. This may need to be done more than once if you find the #innodb_temp directory fills on mysql startup.

This is resolved in 23.3.6 and above with a change to mysql indexing. A manual change to create the index is needed on SpectroSERVERs that have already been installed.

Symptom: Large dataset queries on the primary Archive Manager cause MySQL to consume the entire disk space.

Resolution: With this fix, Spectrum incorporates the required code changes to fine-tune the large dataset queries so that only the necessary disk space is consumed. As part of this fix, users need to manually create an index (event_ie3). For more information, see MySQL Consuming Entire Disk Space. (DE580556, 33564388, 33591892, 33567337, 23.3.6)

As part of this fix, users need to manually create the index event_ie3 in the primary SpectroSERVER on the upgraded/older loaded database. After creating the index, restart the Archive Manager to resolve the issue.

Follow these steps to create the index:

1. Leave all Spectrum processes running
2. Log in to the MySQL command prompt from $SPECROOT/mysql/bin:
Windows:
./mysql -uroot -p<password> ddmdb;

Linux:
./mysql --defaults-file=../my-spectrum.cnf -uroot -p<password> ddmdb;
use ddmdb;
create unique index event_ie3 on event (utime, counter, model_h, clk_seq, version, node_id);

Creation of the new index takes considerable amount of time depending on the DDMDB size.

If you have already created the index mysql will return a message noting it is already created.

5. To verify the creation run: show indexes from event;

You will see reference to event_ie1, event_ie2, and event_ie3. Prior to running the create you would only see reference to event_ie1 and event_ie2

6. quit

This should be run on all SpectroSERVERs in your environment, including FT secondary SpectroSERVERs.

In rare instances creating the additional index does not resolve the issue. If that does not resolve the issue then you need to save, initialize, and reload the DDM db

1. Stop the ArchMgr

Obtain the pid of ArchMgr process

Navigate to $SPECROOT/bin

Run: ./cmdC localhost 1 pidofArchMgr

2. Save the ddmdb from using the bash -login shell in $SPECROOT/SS/DDM and running:

./ddm_save.pl -f DDM_savefile.mbi

3. Once complete, initialize the ddm - obtain the landscape handle of the box you are on:

./ddm_load.pl -il <lh>

for example: ./ddm_load.pl -il 0x3000000

4. Then load the ddm save file from step 2

./ddm_load.pl -f DDM_savefile.mbi

5. Start the ArchMgr from $SPECROOT/bin: ./startSS.pl

Additional Information

ref:
MySQL Consuming Entire Disk Space