Mysql Database Grow | How Optimize parameters

[pnenov]

In your role as a database designer, look for the most efficient way to organize your schemas, tables, and columns. As when tuning application code, you minimize I/O, keep related items together, and plan ahead so that performance stays high as the data volume increases. Starting with an efficient database design makes it

indeed, only small companies are using one single database server

Zabbix's schema is lacking enterprise capabilities

I am using galera cluster to host Zabbix' database and i cant take benefit from distributed resources and multithreading capabilities of my cluster because of the way Zabbix schema has been designed - it is virtually impossible to partitioning most of the tables
Many people are using multi disks partitioning approach too and they face the same issue i have

it seems to me the Zabbix's team prefer to cover the needs of the small companies and single users rather to redesign Zabbix in a way to be able to fit the enterprise architecture
The changes they make in the new releases are mostly on the web front-end

[slartibartfast]

I am using galera cluster to host Zabbix' database and i cant take benefit from distributed resources and multithreading capabilities of my cluster because of the way Zabbix schema has been designed

Do you have any tips how to fully utilize Zabbix under galera cluster with three nodes? Or you just point mysql server to one of the nodes? Are you deploying MySQL or MariaDB galera cluster?

[rzweeres]

Delete me.

[Alfista]

Hi,

I dont have exactly the same problem, but as I use the preinstalled VM Appliance from Zabbix, I have an error: [Z3005] query failed: [1114] The table 'history_uint' is full

and I don't know how can I expand the DB. The Zabbix has only 50% full of the storage.

Please can you help me?

Thanks.

[Alfista]

Don't understand Sancho.

[zabbixuser1]

Hello,

My zabbix server also started increasing recently. It was fine few months ago. i can see that history_uint.ibd table is using 300G appx disk space. Any idea why this table started consuming such huge amount recently? Any setting in zabbix needs to ab tweaked?

[cesarsj]

Hi everybody.
I have a question: can I schedule MySQL optimization while Zabbix Server is running?

Thanks!

I am trying to do OPTIMIZE with the database in production, using the Percona Toolkit, but the link referenced command below gives error because history tables do not have primary keys. If anyone knows how to get around this problem, it would be of great help.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

pt-online-schema-change error: The new table `zabbix`.`_history_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
Please check you have at least one UNIQUE and NOT NULLABLE index.

[cesarsj]

Unfortunately, I just read from the documentation on the link I even mentioned: The following limitations apply which, if attempted, will cause the tool to fail in unpredictable ways:

" In almost all cases a PRIMARY KEY or UNIQUE INDEX needs to be present in the table. This is necessary because the tool creates a DELETE trigger to keep the new table updated while the process is running."

[rinkymehra]

I made a change, mysql did not like that the log file info was different, and gave me a message that the .frm files were invalid.

[Babanaqash]

the innodb_buffer thingy is global for your database.
Most other buffers and cache sizes are per connection (or at least have a multiplier for something).

How is your swap usage?