-1

Little bit of context before diving into the issue,

I have a Wordpress based blog running on 1 GB (RAM), 20 GB (disk space) linode instance. The database itself is around ~105 MB. Recently, I noticed that the entire disk space was used (from ~4 GB) with the database directory /var/lib/mysql/DATABASE_NAME consuming about ~15 GB of space!

Worst offenders in this directory were files with name's ending _INDEX_1.ibd, each one of them using ~700 MB to ~1.5 GB! A quick search didn't really return much of information about the possible cause of this issue. The database is running just fine.

Database directory file listing example

In my limited experience with managing servers, I haven't really encountered something like this. It'd be great to know why this happened, possible solution and a way to prevent this from happening in future.

P.S - The database tables use InnoDB engine. And error logs are white as freshly fallen snow!

Paritosh
  • 99
  • 2
  • 1
    Check your database indices, their disk space usage should match the space used. – Tero Kilkanen Jul 01 '17 at 18:38
  • Did so, disk usage doesn't match the one shown using `SELECT table_schema "DB_NAME", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Size in MB" FROM information_schema.tables GROUP BY table_schema;` – Paritosh Jul 03 '17 at 14:15
  • Look at this question, it most probably is relevant to your issue: https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine – Tero Kilkanen Jul 03 '17 at 19:39
  • Question pointed out wasn't really relevant to my problem. Thanks for the share though. Learned something out of it :) – Paritosh Jul 11 '17 at 01:34

1 Answers1

0

So, first thing first, I am not really sure about the negative vote. Just trying to sort out the core issue. Anyhow on to the issue itself, here's what happened,

  1. Recently upgraded the MariaDB version from 10.5 to 10.6. Although the upgrade went just fine, there were some minor details which I missed.
  2. Unable to find anything in logs, I decided to inspect the table schemas which led me to the use of "FULLTEXT" on certain fields.
  3. Digging further, I found out some notorious Wordpress plugins introduced "FULLTEXT" to wp_posts table causing MariaDB to generate such indexes.
  4. The thing is MariaDB version 10.5 and older didn't really bother to create such indexes. This leads me to believe something changed in version 10.6. Haven't really done much reading of change log since I wanted to sort out this issue first.

Monitoring server for 2 days now and things seem to be back to normal! Ended up learning a bunch of MySQL/MariaDB stuff along the way :)

Paritosh
  • 99
  • 2