Commit times after PG to MySQL conversion

[SteveBeauchemin]

I have migrated my Nagios XI off PostgreSQL to my Offloaded MySQL

The time it takes the system to fully recover from a commit is now different.

If I watch the ipcs queue and run commit, it used to take about 30 minutes to get back to zero

Code: Select all

watch -n 8 "ipcs -q"

Now, after stopping PostgreSQL completely, when I run a commit, the system gets back to normal in about 14 minutes. Meaning that the ipcs queue backlog drops back to zero.

That is a very large improvement in performance.

I highly recommend to others that if they are running the newer Nagios XI, migrate off PG as soon as you can.

Thanks

Steve B

[avandemore]

This behavior may be the result of several layers, first Postgres is going to use its own memory reducing the system's ability to optimize. Second, PG uses IPC while MySQL does not, so there may contention there. Third to determine performance characteristics, a full profiling would be order.

Regardless of all that having only one RDMS on the backend is probably the way to go. Thanks for the input.

[rkennedy]

Steve - if you wouldn't mind posting it for reference, could you share how the process for which you used to convert nagiosxi db from postgres -> mysql?

[SteveBeauchemin]

How did I do it? Start with my previous post... https://support.nagios.com/forum/viewto ... 16&t=41447

That was how I gained understanding and lost my fear of the unknown. I also read the DB Offload document again for more information.

Then I did this - these are the steps I documented for myself. This is a GUIDE... standard disclaimers and blah blah... you know.

1. Set PostgreSQL to allow remote connections. This is specifically to allow access for the system that will run the convert process - basically my Windows PC.

On the system running PostgreSQL there are 2 configuration files to change. This will allow your PC to open pgsql to read the old data.
First.

Code: Select all

sudo vi /var/lib/pgsql/data/pg_hba.conf

Add the line

Code: Select all

host all all [your IP here]/23 trust

Then this one.

Code: Select all

sudo vi /var/lib/pgsql/data/postgresql.conf

Find configuration line that read as follows:
listen_addresses='localhost'
Change it to

Code: Select all

listen_addresses='*'

Then restart PostgreSQL using the method you prefer.
As the nagios user, run

Code: Select all

sudo /usr/local/nagiosxi/scripts/manage_services.sh restart postgresql

Or as root pick one... (it is good to have alternatives)
/etc/init.d/postgresql restart
service postgresql restart
systemctl restart postgresql

2. Install PostgreSQL ODBC Driver on your workstation
Set it up in the ODBC Control Panel and add the host, user, pass, port, etc.
The host is your current Nagios XI system, user and pass are found in the configuration file below. PG port is 5432
Make sure it tests okay.

Acquire the driver from here - Note newest versions are at the bottom.
http://www.postgresql.org/ftp/odbc/versions/msi/

These are the notes from my research.

First you download ODBC driver, then you installed it. After that go to START->Program->Administrative tools then you select Data Source ODBC then you double click on the same after that you select PostgreSQL 30 then you select configure then you provide proper details such as db name user Id host name password of the same database in this way you will configure your DSN connection.
Then you go on next tab system DSN then you select ADD tab then select postgreSQL_ANSI_64X ODBC after you that you have created PostgreSQL ODBC connection.

or something like that… Use your own judgement. To me it meant in the ODBC Data Source Administer tool, on the User DSN tab, click Add and get the PostgreSQL ANSI to show up. Then click the System DSN tab and add your specific setup that includes the database name, Server IP,Username, Password, Port. Click Test and if it is good, then save it.

I used these resources as a guide to setup the driver
https://odbc.postgresql.org/
http://stackoverflow.com/questions/6796 ... on-windows
Once the ODBC driver tests says Okay, proceed.
If not okay, back to step 1 and verify your work, do not forget the PG restart.

3. Use MySQL Workbench Data Migration tool
http://dba.stackexchange.com/questions/ ... l-to-mysql

More research got me this

Use MySQL Workbench - The following steps are based on MySQL Workbench 6.1
Source & Target
Open MySQL Workbench and start the Migration Wizard Database > Migration Wizard... Set up connection to your source database (PostgreSQL) Setup connection to your target database (MySQL) Select the schemas to migrate
Object Migration
Select the source objects to migrate Review and manual editing proposed migration Target creation options Create schemas Create target results
Data Migration
Data transfer setup Bulk data transfer

- or however it works for you.

4. If this process worked for you then do it for real.
Stop Nagios, database, ndo2db, etc.
Leave PostgreSQL and MariaDB/MySQL running.
Run step 3 again to transfer the data to the new system / Database.
If you run this multiple times against a test system, you will get prompted to delete the old schema and make a new one. That is it drops and creates the nagiosxi db each time. I told it that it was okay to delete the old schema every time I ran it. I had a couple warnings but no errors.

In the Workbench gui, you basically click next again and again until it is finished. I was unable to use the linux command line method. The Workbench did give warm fuzzies though because it tells you exactly what is is doing as it goes.

5. If the data conversion looks like it was successful, then poing Nagios XI to the new location.

To tell Nagios XI to use MySQL instead of pgsql.

Code: Select all

vi /usr/local/nagiosxi/html/config.inc.php

change:

Code: Select all

$cfg['db_info'] = array(
    "nagiosxi" => array(
        "dbtype" => 'pgsql',
        "dbserver" => '',
        "user" => 'nagiosxi',
        "pwd" => 'n@gweb',
        "db" => 'nagiosxi',

to:

Code: Select all

$cfg['db_info'] = array(
    "nagiosxi" => array(
        "dbtype" => 'mysql',
        "dbserver" => '1.2.3.4',
        "user" => 'nagiosxi',
        "pwd" => 'nagiosxi',
        "db" => 'nagiosxi',

Start Nagios, database, ndo2db, etc.

[dwhitfield]

Thanks for posting @SteveBeauchemin!

Are we ready to lock it up?

[SteveBeauchemin]

okay with me to close it.