Max Db connections while maintenance

[Kush Rohra]

Hello Team,

I would like to know when does revive store records into rv_data_bkt_m database?

Currently, I am running manual maintenance midnight every hour.

But somehow when I see my logs it records gets insert every afternoon due to which DB connections go high and it affects the delivery of banners.

Also, I have setup time preference as Asia/Calcutta is there any relation to maintenance or adding the records to the database?

Can you please tell me the way out so that database store records at night only.

Thanks

 

Edited May 24, 2018 by Kush Rohra
Wanted to add more points

[andrewatfornax]

The data_bkt_m table is updated in real time as impressions for delivery are logged.

The maintenance process will read from, and delete rows, from that table - but not insert anything.

[Kush Rohra]

Hello Andrew,

Thanks for the reply.

Can you please tell me if we can change the time of data_bkt_m to update? because on my end it gets updated every day in the afternoon from 2-4 PM IST where I get max delivery of traffic which cause the max DB connections and server goes down.

Please let me know the solution so that I can change the time when data_bkt_m gets updated.

Thanks

[andrewatfornax]

No, you can't. Data is inserted into the table in real time as delivery happens (which cannot be changed), and is read from the table every time maintenance runs and the data needs to be summarised. 

This should happen hourly when maintenance runs - it should not be a single, massive update once a day that takes a couple of hours. 

It sounds to me more like the issue is that you are having database issues under delivery load, rather than because of maintenance.

What database are you using? MySQL? Are you using MyISAM tables? What monitoring do you have in place on your server? How many DB connections are permitted? Can you add more? Have you checked if your setup is leaving database connections open that could be closed, and if so, have you tried tuning that?

[Kush Rohra]

Hello Andrew,

Thanks for the reply.

Below are the configurations :

Revive 4.1.1
Apache 2.4.18
MySQL 5.6.37
Hosted: AWS 
The region of the server: Mumbai, India

Yes, I am using ISAM tables and to monitor Aws has its tool to check the DB connections and usage.

And the max connections allowed is 321. No, I haven't changed the default DB connections.

I was just checking the performance once the DB connections go max database does tables are locked.

Can you please help me out with this issue.

Thanks

[andrewatfornax]

Hi,

Please remember that MyISAM tables implement table-level locking.

So, during delivery of banners, only one banner can be logged at a time.

The use of InnoDB may be more appropriate, given that it supports row level locking.

While general database support is somewhat beyond the scope of this forum, please remember to always back up your database before making any changes!

[Kush Rohra]

Hello Andrew,

Thanks for the reply.

Earlier I was running maintenance every hour. But in some instances, when I receive a heavy amount of traffic the DB connections reaches a limit and then table level locking occurs. 

Because as per my assumption if I am running maintenance every hour so it will read data and delete them. so that table gets space to insert new banner delivery records.

Hence I would like to know is there a restriction or limit of the rv_data_bkt_m table? So that if i am receiving the banner delivery more then the size/restriction of table I need to find an alternative way to solve this max DB connection.

Thanks

[andrewatfornax]

On 6/7/2018 at 10:14 PM, Kush Rohra said:

Earlier I was running maintenance every hour.

That's good! It is recommended that you do.

On 6/7/2018 at 10:14 PM, Kush Rohra said:

But in some instances, when I receive a heavy amount of traffic the DB connections reaches a limit and then table level locking occurs. 

That's not quite correct.

If you are using MyISAM tables, then table level locking always occurs. Every single write to the table results in the whole table being locked, so only one write can happen at a time.

What you really mean is that when you get heavy traffic, the table level locking starts to become a problem, and things don't work, because processes start to have to wait too long for their turn to lock the table, and things break.

On 6/7/2018 at 10:14 PM, Kush Rohra said:

Because as per my assumption if I am running maintenance every hour so it will read data and delete them. so that table gets space to insert new banner delivery records.

Hence I would like to know is there a restriction or limit of the rv_data_bkt_m table? So that if i am receiving the banner delivery more then the size/restriction of table I need to find an alternative way to solve this max DB connection.

Running maintenance will delete rows from the data bucket tables - but that's irrelevant. Revive Adserver is quite efficient in how it records impression data, so there's not really any limit to the number of rows it can have (other than what MySQL or PostgreSQL impose).

The real issue with running out of connections to the database is (probably) because connections are staying around for so long, because they have to wait for so long to complete their writes, because you are using MyISAM, which uses table level locking.

The solution is to move to InnoDB tables (or PostgreSQL).

[Kush Rohra]

Thanks a lot for your help here.

Before I convert DB engine to InnoDB do I need to do any changes in tables? or Only DB engine needs to be changed from ISAM to InnoDB.

Let me know your thoughts so that I can proceed further with changing the DB engine.

[Kush Rohra]

Hello Andrew,

Also wanted to ask do I need to create a new DB ? or can I change the DB engine in the existing database which I am currently using?

Thanks

[andrewatfornax]

Before you convert, the most important thing to do would be to:

• Backup the database.
• Test the backup can be used to restore the database.
• Test the conversion process on the restored database.
• Test Revive Adserver on the converted test database.
• Document everything so you can do it quickly when you need to.
• Plan for how to revert if it all goes wrong.

But you know all that already!