Project

General

Profile

High DB usage causing excessive binlog growth

Sean Drummond
Added almost 5 years ago

Hey guys as we have been testing we noticed a significant increase in DB usage more specifically in DB updates. This has not been an issue in dev because we do not keep binlogs. However in prod we keep a few days worth of binlogs incase of recovery for coverage between the backup completing and potential downtime. We only have about 200 users online and the binlog is filling up every 15 minutes or so and they are about 1.1G each.

Is there a caching mechanism or would we have to turn binlogs off completely?

Thanks,

Sean


Replies (13)

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam almost 5 years ago

We usually setup DB in master-slave mode to have a DB backup in case the master DB fails.

As for the DB usage we did not notice the problem. What kind of DB do you use? Are you able to track down what kind of DB activity is filling up binlogs? Unless you have chat history archive in use, there should be very little activity writing to DB.

Added by Sean Drummond almost 5 years ago

We have our DBs running in a master backup mode where they will auto failover. We also take full backups once an hour. Without binlog running I can get 600K users online with >2k queries per sec with no issues. The problem is with storing all the updates via binlogs and I also want to make sure we are doing nothing wrong.

We are using mysql-server-5.1.69-1.el6_4.x86_64

We do not have chat history on

The actual DB is only about 100Mb

I have attached an excerpt from the binlogs along with our current init properties

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam almost 5 years ago

Tigase should not write to DB very frequently during normal operation. The excerpt from the binlog you attached shows only one update query and this is for VHostManager component which updates vhost configuration. This should not happen often, this update should only be executed when you add/remove or update vhost configuration and this is usually very infrequent operation. Even in your case, I can see you use many vhosts on your system, it should still not cause problems.

However, from your init.properties configuration I can see that you have a custom WebVHostManager component plus other custom plugins and code. Maybe your WebVHostManager is not properly optimized and writes to database too frequently when it is not really needed? The same applies to other custom code. Make sure it does not access, and writes in particular, database when it is not necessary.

Added by Subir Jolly almost 5 years ago

Here's our VHost Repository code. We Just override yours and return true for every vhost.

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam almost 5 years ago

As I said, updating VHost data in DB should not be frequent event. Your binlog excerpt contains only a single query and I do not know how often it happens and what are other queries. You have to look at the whole binlog to find out what put the main load on the DB server, then we might be able to help you with investigating why this happens.

Added by Subir Jolly almost 5 years ago

Artur,

We have run into a new issue with our VHostRepository because it writes to the DB too often.

For catchup: We cannot use the standard VHost system because we have an unknown list of VHosts and therefore no way to prepopulate the list.

Our initial modifications were to make VHost repository return true for all VHosts and just disable s2s. In order for this to work each VHost we get we immediately add the item to the VHost repository and return true.

This is causing us to write FAR too much to the DB because external systems ping our ip then get added constantly.

We attempted to simply stop storing vhosts in the DB and just return true for all but this broke our authentication.

Now we are exploring the following alternatives:

Option 1

Modify ConfigRepository in some way so that it can modify the cached values without modifying the DB.

Option 2

Modify CallbackHandlerFactory to pass through the domain given in authentication rather than the VHost name (issue here is if there is no VHost in the DB the callbackhandler factory will pass through the full server name rather than the domain given by user).

Base Problem:

When the VHost is not in the DB. the CallbackHandlerFactory calls setDomain(server_name) which makes it so all of our authentication attempts think the realm/domain being authenticated against is the server name. We need some way for our Authentication CallbackHandler to see the domain passed in via the user's login credentials rather than the current VHost.

If you guys have any ideas on how to get this information in the auth callbackhandler OR another way to approach this please let us know.

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam almost 5 years ago

Subir Jolly wrote:

Artur,

We have run into a new issue with our VHostRepository because it writes to the DB too often.

For catchup: We cannot use the standard VHost system because we have an unknown list of VHosts and therefore no way to prepopulate the list.

Our initial modifications were to make VHost repository return true for all VHosts and just disable s2s. In order for this to work each VHost we get we immediately add the item to the VHost repository and return true.

During my visit in your office we talked about this. It looks your approach causes routing problems within a Tigase clustered configuration so you have to be careful about this.

This is causing us to write FAR too much to the DB because external systems ping our ip then get added constantly.

We attempted to simply stop storing vhosts in the DB and just return true for all but this broke our authentication.

Now we are exploring the following alternatives:

h3. Option 1

Modify ConfigRepository in some way so that it can modify the cached values without modifying the DB.

h3. Option 2

Modify CallbackHandlerFactory to pass through the domain given in authentication rather than the VHost name (issue here is if there is no VHost in the DB the callbackhandler factory will pass through the full server name rather than the domain given by user).

Base Problem:

When the VHost is not in the DB. the CallbackHandlerFactory calls setDomain(server_name) which makes it so all of our authentication attempts think the realm/domain being authenticated against is the server name. We need some way for our Authentication CallbackHandler to see the domain passed in via the user's login credentials rather than the current VHost.

If you guys have any ideas on how to get this information in the auth callbackhandler OR another way to approach this please let us know.

To be honest I do not understand what you are saying. Please note, when a client connect to XMPP server it must provide the server vhost (domain for the XMPP service) in stream open element or in the first body element for Bosh clients. This is long before authentication even happens. So the XMPP server knows for which domain the user attempts to connect and later authenticate.

Added by John Catron almost 5 years ago

Okay so ignoring the previous.

Our bin logs are being flooded because of large numbers of writes of the same information to the db.

UPDATE tig_pairs SET pval = NAME_CONST('_tval',_utf8'' COLLATE 'utf8_general_ci') WHERE nid = NAME_CONST('_nid',2) AND uid = NAME_CONST('_uid',2) AND pkey = NAME_CONST('_tkey',_utf8'vhosts-lists' COLLATE 'utf8_general_ci')

^ Each post is essentially that except the xml section has about 500 domains listed in it in full xml. Those same 500 domains keep getting updated in the table with no information change. Sometimes there is a new domain (501 or 502 etc.) but THOSE are expected as they are different. Something, however, is causing it to write the list even when there are no changes. Due to our EXTREMELY large domain count this is flooding our mysql logs with data causing over a gig of data to be generated every few minutes.

Added by Subir Jolly almost 5 years ago

I also noticed these in mysqld logs:

CALL TigGetUserDBUid('vhost-manager')
select nid as nid1 from tig_nodes where (uid = 2) AND (parent_nid is null) AND (node = 'root')
CALL TigUpdatePairs(2, 2, 'vhosts-lists', '<vhost hostname="" ......DATA CHOPPED OFF BY ME.........."><comps/><other/></vhost>')

Apparently TigUpdatePairs() calls update query which we are seeing in bin logs.

These calls happen every few seconds even when the server is idle. Is there a way to override the functionality in Tigase which calls TigUpdatePairs() stored procedure?

We would like to have one of the following:

  • We want the update to happen only when a new vhost enters tigase system, OR

  • We want the update to happen periodically but not this frequently.

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam almost 5 years ago

Both calls are related to VHostManager which stores vhost data when it was updated. We talked about this before. The VHostManager should not write to DB if nothing was modified. It does, however, read from DB periodically. This is to handle a case in a cluster mode when vhost list was modified on one cluster node, so the other nodes can quickly pickup the change. But this is only for reading and should not trigger binlog updates.

Added by John Catron almost 5 years ago

Artur:

To make sure we weren't causing this we did the following:

Created new Tigase instance with vanilla tigase.

Created new Tigase db for the new instance.

Inserted a row into the Tigase db that has 600+ vhost in it.

We then ran the Vanilla Tigase. This new instance had 0 traffic to it. nothing happening and no custom code.

It filled up a bin log with 1.1G of data in about 18 minutes due to posting MANY updates to the tables posting lists of the virtualhosts we had added to the DB.

Tigase is running updates on the tig_pairs db with lists of vhosts almost constantly and when the list is 500+ domains this amounts to an enormous amount of data. In our main system we are filling 1GB of data every 6 minutes (because 3 servers are all doing this same thing that the test server was doing).

I'm sure if you guys insert a row into tig_pairs with 500+ vhosts in it you will see the same enormous amount of 'UPDATE' queries to the tables.

Avatar?id=6023&size=32x32

Added by Artur Hefczyc TigaseTeam almost 5 years ago

I have committed a code change which should fix the problem: d8fe7ea6e0e018715b3a391f9c142f7607571938

I am not sure if it is included in our last nightly build so please check it out from source repository.

Added by Subir Jolly almost 5 years ago

The fixes seem to be holding up. We are still running them overnight to see if the fixes actually worked and fixed our main DB issue.

Thank you so much, Artur and team.

    (1-13/13)