Database seems to be growing too quick

[fsousa]

Hi,

Thank you, interesting article.

The innodb_buffer_pool_size should be 75% of total available memory if I have a dedicated MySQL server. In this case my zabbix server and mysql server are in the same server.

I dont' understand what is the recommend value.

[ingus.vilnis]

How much RAM have you got in total on that server? Show "free -m" command output please.
What is the current value set for innodb_buffer_pool_size?

[fsousa]

ree -m
              total        used        free      shared  buff/cache   available
Mem:           3789         999         177          80        2612        2436
Swap:             0           0           0

I don't have any value defined for innodb_buffer_pool_size

I can expand my memory ram if I need it.

[ingus.vilnis]

The default value should be 128M which is not enough at all.

In your case since you don't have plenty of RAM, you need to leave it for other services and caches too.
To be on the safe side and don't start swapping, set innodb_buffer_pool_size=1G and restart mysql service to apply.
You could allocate a bit more too but then you have to keep very close look at overall Linux performance readings.

[fsousa]

And if I alllocate 16GB of memory ?

[ingus.vilnis]

Then just wait for the system to stall in happiness.
You don't physically have 32G, and remember you have to leave memory for OS and other resources too, mysql binary logs, Zabbix etc.

[fsousa]

ok, thank you.

[fsousa]

Hi @ingus,

So I run the ALTER TABLES with success and the zabbix_partitioning.pl script.

So, if I understand with the script my tables before my start day of partitions are dropped and with the my $amount_partitions = 10 option 10 more days partitions are created, right?

So without partitions my disk usage was 98 GB available space and after the partitions I got 102 GB.

It is because mu keep history parameter?

my $tables = {    'history' => { 'period' => 'day', 'keep_history' => '60'},
        'history_log' => { 'period' => 'day', 'keep_history' => '60'},
        'history_str' => { 'period' => 'day', 'keep_history' => '60'},
        'history_text' => { 'period' => 'day', 'keep_history' => '60'},
        'history_uint' => { 'period' => 'day', 'keep_history' => '60'},
        'trends' => { 'period' => 'month', 'keep_history' => '12'},
        'trends_uint' => { 'period' => 'month', 'keep_history' => '12'},

Thank you.

[ingus.vilnis]

Hi,

Yes, your understanding of partitions seems to be correct.

Now you got 4GB more available because the data older than 60 days was dropped and if I remember correctly you had some pretty old entries there.

[fsousa]

Hi Ingus,

Thank you for you reply and I'm sorry for my delay.

Unfortunately the partitioning of my tables doesn't going like I'm expected.

I'm trying to partitioning history_uint.ibd but I got this error:

2019-01-09 09:11:09 31623 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.37'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2019-01-09 15:16:24 7fc2e1150700 InnoDB: Error: Write to file ./zabbix/#sql-7b87_3#P#p2018_11_26.ibd failed at offset 1533018112.
InnoDB: 1048576 bytes should have been written, only 405504 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.

The history_uint.ibd have 123GB and I'm only 52 Gb free on my disk. For partitioning this table I need the 123Gb available?

Thank you.

[ingus.vilnis]

Yes, during the process of partitioning (alter table) the data is copied to temporary tables and that is where you need the disk space.

Have you got any possibilities to extend the drive or mount another one?

[fsousa]

Yes, I can do that. But how I have to proceed to only partitioning the history_uint .ibd there?

[ingus.vilnis]

You won't get further till you fix the lack of disk space.

[fsousa]

So the only solution is migrate all the database to a new disk and then partitioning the tables? At this the unique table is missing the partitioning is the history_uint.

[ingus.vilnis]

If I remember correctly MySQL has a possibility to store tmp tables on a different mount point than current DB. So if you temporary can add a disk of sufficient size and at that moment tell MySQL to use that location for tmp tables then in theory you should be ok.