History with External MariaDB is extremely slow | Home Assistant | Page 1

haughty dragon Feb 10, 2026, 1:17 PM

#

Hi all,

I am running an external mariadb on the same proxmox instance as homeassistance VM,
Whenever I request data, it is loading extremely slow,

What could be the cause?

#

Please let me know if there is more information required 🙂

short zinc Feb 10, 2026, 6:31 PM

#

What are the MariaDB settings? What hardware is it hosted on? What's top -co%CPU look like on that hardware? How large is the database/tables? Is MariaDB running on the same host as HA? If not, how are they connected? How much data and what kind are you requesting? Why are you not using SQLite? There's butt load of variables.

haughty dragon Feb 17, 2026, 9:28 AM

#

Running on N150 in Proxmox,
Running mariadb because SQLITE was even slower as heck as well as that I wanted to have much longer data retention and because have some other applications using mariadb

For instance when I am requesting 2 weeks of data its already crunching gears

#

#

The current hardware settings

#

I forgot I put out this thread, my apologies 🙂

#

And MariaDB

short zinc Feb 17, 2026, 9:31 AM

#

Note that modifying purge_keep_days is generally not a good idea. In my experience things become slow at a certain size (1g+) no matter which recorder database you use. Due to long term stats it's also not needed: https://www.home-assistant.io/blog/2023/12/06/release-202312/#history-dashboard-showing-long-term-statistics
Use InfluxDB/VictoriaMetrics if you need highly granular statistics.
2 Sockets is wrong for the N150. kvm64 is also a poor and slow choice. Either use host or AES v2.

haughty dragon Feb 17, 2026, 9:32 AM

#

Ahhh that was all set by the helper-scripts script I used

short zinc Feb 17, 2026, 9:32 AM

#

.raw disks are a bad idea and slow too: https://gist.github.com/Impact123/3dbd7e0ddaf47c5539708a9cbcaab9e3#why-not-use-local-for-guest-disks
Seems like you have it on network storage too 👀

#

How large is the database? How large is buffer pool size? You usually want to have all the data in RAM.

haughty dragon Feb 17, 2026, 9:34 AM

#

I dont have access to it right now but I haven't done changes to the database, just purely logged it in into the recordersettings which did all the creation for me

#

Yes its on network storage poor beelink is running out of space while my NAS is doing nothing but plex 😭

haughty dragon Feb 17, 2026, 9:35 AM

#

short zinc `.raw` disks are a bad idea and slow too: <https://gist.github.com/Impact123/3db...

Cool I'll read this through when I have the time

short zinc Feb 17, 2026, 9:35 AM

#

Go here and select System Information at the top right: https://my.home-assistant.io/redirect/repairs/
Then share the recorder info at the end there.

haughty dragon Feb 17, 2026, 9:35 AM

#

It was just annoying that I wanted to compare long term data, example; what was the commute time last year/month/week around this time

It wasn't there

short zinc Feb 17, 2026, 9:36 AM

#

The issue is all your settings are kind of bad 😐

#

Not everything goes into the Long Term stats but you can customize entities to go there. Just depends what you store exactly.

haughty dragon Feb 17, 2026, 9:37 AM

#

short zinc The issue is all your settings are kind of bad 😐

Check! It was all default settings done by the helper-scripts so that explains why,

I wanted to go about optimizing it all this weekend in any case because I keep running into issues due to numpy

short zinc Feb 17, 2026, 9:37 AM

#

The recorder database is very inefficient for storing this kind of data. LTS summarizes it and makes it manageable.

haughty dragon Feb 17, 2026, 9:37 AM

#

short zinc How large is the database? How large is buffer pool size? You usually want to ha...

Right now tho I think I am at 15gbs of data?

short zinc Feb 17, 2026, 9:37 AM

#

Then you probably want to give the Database 16G of RAM and modify its settings accordingly.

haughty dragon Feb 17, 2026, 9:38 AM

#

Wuuuuuffff okay. Thats 100% of ram 😄

short zinc Feb 17, 2026, 9:38 AM

#

I'd first check if the recorder data goes into LTS or not though. You can likely just reduce the purge days to the default.

haughty dragon Feb 17, 2026, 9:38 AM

#

Sooo, first of, home assistant side,

You mentioned the 2 sockets are wrong?

#

And the HOST or AES V2, which one would you recommend?

short zinc Feb 17, 2026, 9:39 AM

#

If you go to node > Summary you can see the CPU layout of your node.

haughty dragon Feb 17, 2026, 9:39 AM

#

short zinc If you go to `node > Summary` you can see the CPU layout of your node.

Ahhhhhhhhhhhhh 4 cores 1 socket

#

Makes sense

short zinc Feb 17, 2026, 9:39 AM

#

For a N150 just give the VM 2 to 4 cores and use host or the default AES v2 model.

haughty dragon Feb 17, 2026, 9:40 AM

#

Only recently migrated to proxmox, have been rawdogging raspberry pi's for the past what, 4 years? 😂

short zinc Feb 17, 2026, 9:40 AM

#

I generally always give at least 2 cores to things. Over allocating is fine. You don't limit a process on your PC to a core either, right?

haughty dragon Feb 17, 2026, 9:40 AM

#

short zinc I generally always give at least 2 cores to things. Over allocating is fine. You...

Yeah, its not doing a whole lot at the moment other than that

#

Ur correct 🙂

#

This is already great advice 🥰

short zinc Feb 17, 2026, 9:41 AM

#

I give frigate a core and a 0.5 CPU limit to reduce its overall usage for example. Opengrok too. The rest just get what they need with sane minimums.

#

For a VM if you give it 4G then you can consider it gone. The VM will use it for caches and such. For a CT it will only use what it actually needs as the kernel is shared with it. It's just a fancy isolated process.

haughty dragon Feb 17, 2026, 9:43 AM

#

Yeah the ram on hass was constantly bumping into 100% so I kept upping it

#

It was already a godsend going from 2gb to 4

short zinc Feb 17, 2026, 9:43 AM

#

I gave my VictoriaMetrics CT 32G and 8 cores for example. It only temporarily needs that if I go back years in grafana. A VM would be a bad idea here.

haughty dragon Feb 17, 2026, 9:43 AM

#

😂

#

Ok changed it, hope it doesn't break so the household doesn't get upset 👍

short zinc Feb 17, 2026, 9:46 AM

#

If it's just a single node you can use host too.

haughty dragon Feb 17, 2026, 9:47 AM

#

Yeah, more nodes will be added later down the road

#

But yknow, ram prices 🙁

#

So, mariadb size I'll have to take a look at buffersize

short zinc Feb 17, 2026, 9:48 AM

#

You can also try this: https://github.com/major/MySQLTuner-perl

haughty dragon Feb 17, 2026, 9:55 AM

#

It already seems a bit faster, or its just placebo

#

Thanks! Now I can finally also update whisper and piper 🙂

#

People on the internet are quite contradicting about .raw vs .qcow2

haughty dragon Feb 17, 2026, 10:35 AM

#

Ok.. strange, I gave the MariaDB LXC 40GBs but the container only tells me theres 20GB total available..

#

But this of course is more of a proxmoxproblem than a hass problem 🙂

#

Which is now also fixed.

short zinc Feb 17, 2026, 11:14 AM

#

haughty dragon People on the internet are quite contradicting about ``.raw`` vs ``.qcow2``

My point is to use neither. If you have to use file based disks pick QCOW2 as it supports thin provisioning and snapshots.
The best is a raw block volume. ZFS or LVM-Thin.

#History with External MariaDB is extremely slow