0

I have a PC with a small disk:

Filesystem      Size  Used Avail Use% Mounted on
/dev/root       3.5G  3.1G  249M  93% /
devtmpfs        459M     0  459M   0% /dev
tmpfs           463M     0  463M   0% /dev/shm
tmpfs           463M   36M  428M   8% /run
tmpfs           5.0M  8.0K  5.0M   1% /run/lock
tmpfs           463M     0  463M   0% /sys/fs/cgroup
/dev/mmcblk0p1   63M   25M   38M  40% /boot
tmpfs            64M  140K   64M   1% /mnt/ramdisk

I have a simple database, but one of the tables has more than 5 million rows, taking up a lot on such a small record:

# ls -lh /var/lib/mysql/datalogger/
total 1.1G
...
-rw-rw---- 1 mysql mysql 1.1G Nov 10 09:56 avg_values.ibd
...

My goal, done on other similar computers without so much space occupied (about half in that table) was to run a query that deleted data more than 6 months old. The query is irrelevant to the case. after the query, running optimize NO_WRITE_TO_BINLOG table avg_values; free up ibd space.

The problem with the computer in question, is that -as I believe- the available space is very limited, you can not perform queries on such a large table (by cache, I understand), causing instability in communications, even when the query has a limit:

MariaDB [datalogger]> select * from avg_values limit 10;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: datalogger

ERROR 2013 (HY000): Lost connection to MySQL server during query

With other smaller tables there are usually no problems (although it also indicates error):

MariaDB [datalogger]> select * from raw_values;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: datalogger

+----+---------------------+-------------------+------+-------+
| id | timestamp           | channel           | raw  | value |
+----+---------------------+-------------------+------+-------+
|  1 | 2021-11-10 09:46:51 | AI1               |    1 | 0.011 |
|  2 | 2021-11-10 09:46:51 | AI2               |    1 | 0.011 |
|  3 | 2021-11-10 09:46:51 | AI3               |    2 | 0.022 |
|  4 | 2021-11-10 09:46:51 | AI4               |    2 | 0.022 |
|  5 | 2021-11-10 09:46:51 | pyr1_sensor1_data |  113 |   113 |
|  6 | 2021-11-10 09:46:51 | pyr1_body_temp    |   42 |    42 |
|  7 | 2021-11-10 09:46:51 | pyr2_sensor1_data | NULL |  NULL |
|  8 | 2021-11-10 09:46:51 | pyr2_body_temp    | NULL |  NULL |
|  9 | 2021-11-10 09:46:46 | AI1               |    1 | 0.011 |
| 10 | 2021-11-10 09:46:46 | AI2               |    1 | 0.011 |
| 11 | 2021-11-10 09:46:46 | AI3               |    2 | 0.022 |
| 12 | 2021-11-10 09:46:46 | AI4               |    2 | 0.022 |
| 13 | 2021-11-10 09:46:46 | pyr1_sensor1_data |  115 |   115 |
| 14 | 2021-11-10 09:46:46 | pyr1_body_temp    |   42 |    42 |
| 15 | 2021-11-10 09:46:46 | pyr2_sensor1_data | NULL |  NULL |
| 16 | 2021-11-10 09:46:46 | pyr2_body_temp    | NULL |  NULL |
| 17 | 2021-11-10 09:46:52 | AI1               |    1 | 0.011 |
| 18 | 2021-11-10 09:46:52 | AI2               |    1 | 0.011 |
| 19 | 2021-11-10 09:46:52 | AI3               |    2 | 0.022 |
| 20 | 2021-11-10 09:46:52 | AI4               |    2 | 0.022 |
| 21 | 2021-11-10 09:46:52 | pyr1_sensor1_data |  112 |   112 |
| 22 | 2021-11-10 09:46:52 | pyr1_body_temp    |   42 |    42 |
| 23 | 2021-11-10 09:46:52 | pyr2_sensor1_data | NULL |  NULL |
| 24 | 2021-11-10 09:46:52 | pyr2_body_temp    | NULL |  NULL |
+----+---------------------+-------------------+------+-------+
24 rows in set (0.02 sec)

Is there any way I can run these queries, especially with this little disk in order to free up space?

Improve this question
3
  • 1
    uffff no, when your database server crashes, that's not something simple like "ran out of space". Something's broken about your data. Can't you just pull off the whole database (via network, USB, …) and run the cleanup on a "properly sized" machine? Do simple SELECTs on the large table work on a PC with enough space? Commented Nov 10, 2021 at 9:37
  • 1
    (note that your deletion of rows explicitly disabling the logs will have made things worse if the database store was in an inconsistent state due to disk being full – before, you could have repaired it from the logs, now with such an operation in between, the logs are out of sync with the database file. I hope you've got backups!) Commented Nov 10, 2021 at 9:58
  • 1
    (also, a 4GB storage machine sounds a lot like MySQL isn't the right choice of database, but I'm not willing to advise you on your database selection. It does seem like a single writer use case, so a much simpler embedded database like SQLite would have been easier to deploy and used less resources for things you're not going to use.) Commented Nov 10, 2021 at 10:00

1 Answer 1

Reset to default
2

There is no easy way to do optimize with limited storage space or any sane query for that matter, because you may be running out of space during the query execution. You have two options:

Mount an NFS drive from a remote machine and point mysql's tmpdir to it Or Point mysql's tmpdir to /dev/shm. Be very cautious with this and always have a backup before performing such operations

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.