Migrate and update zabbix server (ubuntu -> centOS)

[sg42]

Hi!

Our current installation:
One Server for DB and Zabbix
OS: Ubuntu 13.04
DB: MySQL 5.5.34
Zabbix Version 2.2.9

Our goal:
One server for DB, one for Zabbix, (perhaps one for GUI)
OS: CentOS 7.2.1511
DB: PostgreSQL
Zabbix Version 3.0

Is there any HowTo for this scenario?
My plan is as follows. Is it to complicated? Are there any pitfalls?

1. new machine for database
1.1. migrate database to new server
1.1.1 dump DB from current server
1.1.2. restore DB on new db-server
1.2. let current zabbix-server use new database server (vi zabbix-server.conf)
2. new machine for zabbix (same Version as old server)
2.1. switch to new zabbix server (change DNS-Name, shutdown old server)
3. update new zabbix server

Thanks,
Stephan

[kloczek]

Hi!

Our current installation:
One Server for DB and Zabbix
OS: Ubuntu 13.04
DB: MySQL 5.5.34
Zabbix Version 2.2.9

Our goal:
One server for DB, one for Zabbix, (perhaps one for GUI)
OS: CentOS 7.2.1511
DB: PostgreSQL
Zabbix Version 3.0

Really .. stick with MySQL.
It is way better suited DB backend for zabbix than PostgresQL because this engine is simpler.
Zabbix uses only syntax SQL queries which is compliant with SQL92 specification (except only few queries zabbix uses exactly the same syntax of the queries on all possible to use types of SQL engines).
On bigger scale when you will need to partition history* and trends* tables handling tables indexes is not so good as in case MySQL.
It is nothing bad with PostgresSQL but in case of the zabbix is to complicated engine, and with quite basic zabbix needs and all those not used features are like 1kg of lead hanging between legs.
Or in other words migration to PostgresQL will give you nothing except this that you will train DB migration from MySQL to PostgresQL (if it is your target to train such operation that is fine )

Just make only upgrade to MySQL 5.6

https://kloczek.wordpress.com/2016/05/05/mysql-5-5-to-5-6-zabbix-db-backend-upgrade-surprise/

Again: remember about KISS principle

[sg42]

Hi!

Thanks for Your answer.

Really .. stick with MySQL.
It is way better suited DB backend for zabbix than PostgresQL because this engine is simpler.

Currently we have some performance problems with MySQL which is the reason why we want to switch to postgresQL. But perhaps we will give MySQL a second try under Zabbix 3.0

Do You have some ideas about the rest of my upgrade-plan?

Stephan

[kloczek]

Currently we have some performance problems with MySQL which is the reason why we want to switch to postgresQL. But perhaps we will give MySQL a second try under Zabbix 3.0

I can put on the table £10 note to bet that move to PostgreSQL will not solve you problems.
Only known miracle is that no one can find new miracles and situation that on the same hardware moving to PostgreSQL may solve zabbix DB issues would be a real miracle.

Please describe your performance issues.

[sg42]

Apart from the question MySQL vs PostgrsQL, are there any other comments to my upgrade plan?

[kloczek]

Apart from the question MySQL vs PostgrsQL, are there any other comments to my upgrade plan?

I have no any questions about MySQL vs. PostgreSQL.

Main cause of performance issues on zabbix DB backed are related to latency of most of the SQL queries.
To high latency may be caused by:
- lack of partitioning at least history* and trends* tables and using standard housekeeping on those tables which generates huge number of read and write IOs during housekeeping. However this is affecting badly zabbix DB only during housekeeping
- not enough amount of memory to cache enough data to reduce number of read IOs. With proper amount of RAM you should have almost none read IOs from the storage (latency IOs of the reads from spindles is >= 8ms, from SATA SSDs 2-1ms, from NVMe SSDs 200-80us, from RAM hundredths to tenths nanoseconds)

If you will not take care first of those two aspects after migration to other SQL engine you will have exactly the same performance issues and whole your migration is pointless.

Above is true in case of ALL SQL engines.

Solving reduction physical read IOs issue is crucial not only to have fast enough selects but to have enough fast inserts as well because most of the people are not aware that to write some data you need to read first some other data to make decision where exactly new data needs to be written.

Do you have IO stats storage device(s) used by your current DB backend?