Typically sym_data_event should get purged each night. This is controlled by a combindation of the purge job running (it's typically scheduled for midnight, see job.purge.outgoing.cron) and the purge.retention.minutes (which is set to 24 hours by default). So the first I recomend is to make sure the purge job ran successfully.
Another thing to check if is you have any nodes that have been offline for a long time. The purge job will only purges data that has replicated successfully (where the batch status = 'OK'). So any offline nodes will cause data to queue up in sym_outgoing_batch, sym_data_event, and sym_data.
You could check how old some of this data is with a query along these lines:
select * from sym_data_event order by create_time ASC;
You could get a little more detail on the older events like this:
select * from sym_outgoing_batch b JOIN sym_data_event e ON e.batch_id = b.batch_id order by b.create_time ASC;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
2017-07-12 15:54:49,410 INFO [startup] [SymmetricWebServer] [main] About to start SymmetricDS web server on host:port 0.0.0.0:31415
2017-07-12 15:54:49,800 INFO [startup] [/] [main] Initializing Spring root WebApplicationContext
2017-07-12 15:54:49,847 INFO [server] [AbstractSymmetricEngine] [symmetric-engine-startup-0] Initializing connection to database
2017-07-12 15:54:50,362 INFO [server] [JdbcDatabasePlatformFactory] [symmetric-engine-startup-0] Detected database 'MySQL', version '5', protocol 'mysql'
2017-07-12 15:54:50,377 INFO [server] [JdbcDatabasePlatformFactory] [symmetric-engine-startup-0] The IDatabasePlatform being used is org.jumpmind.db.platform.mysql.MySqlDatabasePlatform
2017-07-12 15:54:50,533 INFO [server] [MySqlSymmetricDialect] [symmetric-engine-startup-0] The DbDialect being used is org.jumpmind.symmetric.db.mysql.MySqlSymmetricDialect
2017-07-12 15:54:50,580 INFO [server] [ExtensionService] [symmetric-engine-startup-0] Found 0 extension points from the database that will be registered
2017-07-12 15:54:50,580 INFO [server] [StagingManager] [symmetric-engine-startup-0] The staging directory was initialized at the following location: tmp\server
2017-07-12 15:54:50,643 INFO [server] [ClusterService] [symmetric-engine-startup-0] This node picked a server id of SERVIDOR
2017-07-12 15:54:51,064 INFO [server] [ExtensionService] [symmetric-engine-startup-0] Found 0 extension points from the database that will be registered
2017-07-12 15:54:51,064 INFO [server] [ClientExtensionService] [symmetric-engine-startup-0] Found 7 extension points from spring that will be registered
2017-07-12 15:54:51,079 INFO [server] [AbstractSymmetricEngine] [symmetric-engine-startup-0] Initializing SymmetricDS database
2017-07-12 15:54:51,079 INFO [server] [MySqlSymmetricDialect] [symmetric-engine-startup-0] Checking if SymmetricDS tables need created or altered
2017-07-12 15:54:51,126 INFO [startup] [/] [main] Initializing Spring FrameworkServlet 'rest'
2017-07-12 15:54:51,251 INFO [startup] [SymmetricWebServer] [main] Starting JMX HTTP console on port 31416
2017-07-12 15:54:51,267 INFO [startup] [SymmetricWebServer] [main] Joining the web server main thread
2017-07-12 15:54:51,781 INFO [server] [SymmetricServlet] [qtp7815330-17] Requests for engine server are being rejected while it is starting
2017-07-12 15:54:51,906 INFO [server] [AbstractSymmetricEngine] [symmetric-engine-startup-0] Done initializing SymmetricDS database
2017-07-12 15:54:51,906 INFO [server] [AbstractSymmetricEngine] [symmetric-engine-startup-0] SymmetricDS database version : 3.8.27
2017-07-12 15:54:51,906 INFO [server] [AbstractSymmetricEngine] [symmetric-engine-startup-0] SymmetricDS software version : 3.8.27
2017-07-12 15:54:51,937 INFO [server] [AbstractSymmetricEngine] [symmetric-engine-startup-0] Starting registered node [group=server, id=server, externalId=server]
2017-07-12 15:54:51,937 INFO [server] [TriggerRouterService] [symmetric-engine-startup-0] Synchronizing triggers
2017-07-12 15:54:55,463 INFO [server] [TriggerRouterService] [symmetric-engine-startup-0] Done synchronizing triggers
2017-07-12 15:54:55,463 INFO [server] [RouterJob] [symmetric-engine-startup-0] Starting job.routing on periodic schedule: every 10000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,479 INFO [server] [PushJob] [symmetric-engine-startup-0] Starting job.push on periodic schedule: every 60000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,479 INFO [server] [PullJob] [symmetric-engine-startup-0] Starting job.pull on periodic schedule: every 60000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,479 INFO [server] [JobManager] [symmetric-engine-startup-0] Job job.offline.push not configured for auto start
2017-07-12 15:54:55,479 INFO [server] [JobManager] [symmetric-engine-startup-0] Job job.offline.pull not configured for auto start
2017-07-12 15:54:55,479 INFO [server] [OutgoingPurgeJob] [symmetric-engine-startup-0] Starting job.purge.outgoing with cron expression: 0 0 0 * * *
2017-07-12 15:54:55,479 INFO [server] [IncomingPurgeJob] [symmetric-engine-startup-0] Starting job.purge.incoming with cron expression: 0 0 0 * * *
2017-07-12 15:54:55,479 INFO [server] [StatisticFlushJob] [symmetric-engine-startup-0] Starting job.stat.flush with cron expression: 0 0/5 * * * *
2017-07-12 15:54:55,479 INFO [server] [SyncTriggersJob] [symmetric-engine-startup-0] Starting job.synctriggers with cron expression: 0 0 0 * * *
2017-07-12 15:54:55,479 INFO [server] [HeartbeatJob] [symmetric-engine-startup-0] Starting job.heartbeat on periodic schedule: every 900000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,494 INFO [server] [WatchdogJob] [symmetric-engine-startup-0] Starting job.watchdog on periodic schedule: every 3600000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,494 INFO [server] [StageManagementJob] [symmetric-engine-startup-0] Starting job.stage.management with cron expression: 0 0 * * * *
2017-07-12 15:54:55,494 INFO [server] [JobManager] [symmetric-engine-startup-0] Job job.refresh.cache not configured for auto start
2017-07-12 15:54:55,494 INFO [server] [FileSyncTrackerJob] [symmetric-engine-startup-0] Starting job.file.sync.tracker with cron expression: 0 0/5 * * * *
2017-07-12 15:54:55,494 INFO [server] [FileSyncPullJob] [symmetric-engine-startup-0] Starting job.file.sync.pull on periodic schedule: every 60000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,494 INFO [server] [FileSyncPushJob] [symmetric-engine-startup-0] Starting job.file.sync.push on periodic schedule: every 60000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,494 INFO [server] [InitialLoadExtractorJob] [symmetric-engine-startup-0] Starting job.initial.load.extract on periodic schedule: every 10000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,494 INFO [server] [MonitorJob] [symmetric-engine-startup-0] Starting job.monitor on periodic schedule: every 60000ms with the first run at Wed Jul 12 15:55:05 BRT 2017
2017-07-12 15:54:55,494 INFO [server] [JobManager] [symmetric-engine-startup-0] Job job.report.status not configured for auto start
2017-07-12 15:54:55,494 INFO [server] [SyncConfigJob] [symmetric-engine-startup-0] Starting job.sync.config with cron expression: 0 0/10 1 * * *
2017-07-12 15:54:55,494 INFO [server] [AbstractSymmetricEngine] [symmetric-engine-startup-0] Started SymmetricDS
2017-07-12 15:54:55,494 INFO [server] [AbstractSymmetricEngine] [symmetric-engine-startup-0] SymmetricDS: type=server, name=server, version=3.8.27, groupId=server, externalId=server, databaseName=MySQL, databaseVersion=5.6, driverName=MySQL Connector Java, driverVersion=mysql-connector-java-5.1.30 ( Revision: alexander.soklakov@oracle.com-20140310090514-8xt1yoht5ksg2e7c )
2017-07-12 15:55:05,338 INFO [server] [DataGapFastDetector] [server-job-5] Full gap analysis is running
2017-07-12 15:55:05,353 INFO [server] [DataGapFastDetector] [server-job-5] Querying data in gaps from database took 15 ms
2017-07-12 15:55:05,353 INFO [server] [DataGapFastDetector] [server-job-5] Full gap analysis is done after 15 ms
2017-07-12 15:55:15,400 INFO [server] [RouterService] [server-job-14] The 'heartbeat' channel is NOT in common batch mode
2017-07-12 15:55:15,727 INFO [server] [RouterService] [server-job-14] Routed 5 data events in 358 ms
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Régis,
If you happen to have clients that come online rarely, the server will collect all modifictions for them in sym_outgoing_batch and sym_data_event for long time, consuming lot of space. Instead you can set up your system to set offline clients automatically to disabled after certain ammount of inactivity, and not collect changes for them (also delete the data collected for them until now). When they next come online they will re-register and an initial load will be sent for them. If you want to go this way, basically you just have to set the offline.node.detection.period.minutes parameter to some other than 0 value.
Last edit: krivoz 2017-07-03
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks, but on my research the long amount of event is releted to status 'OK', on server sym_data_event has 125 milion of register with status = 'OK', then i have to delete this before and update for newst version of symmetricds.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Guys,
I'd like to take this question with you guys, my structure is that:
Server: 1
Clients: 121 (all nodes registered in server sending information client to server)
Is it normal has sym_data_event table with 6,8GB on server while my others tables has 1,8GB on total?
If not, how can avoid that?
SymmetricDS version: 3.7.17
Server engine
Client engine
Typically sym_data_event should get purged each night. This is controlled by a combindation of the purge job running (it's typically scheduled for midnight, see job.purge.outgoing.cron) and the purge.retention.minutes (which is set to 24 hours by default). So the first I recomend is to make sure the purge job ran successfully.
Another thing to check if is you have any nodes that have been offline for a long time. The purge job will only purges data that has replicated successfully (where the batch status = 'OK'). So any offline nodes will cause data to queue up in sym_outgoing_batch, sym_data_event, and sym_data.
You could check how old some of this data is with a query along these lines:
select * from sym_data_event order by create_time ASC;
You could get a little more detail on the older events like this:
select * from sym_outgoing_batch b JOIN sym_data_event e ON e.batch_id = b.batch_id order by b.create_time ASC;
Hi Mark,
job.purge.outgoing.cron is executing normally cause sym_outoing_batch table are purging.
I think purge.retention.minutes is not working cause sym_data_event are not purging.
Do you have any idea?
Thanks!
Régis
Last edit: R Debona 2017-06-29
Maybe you are being effected by this?
https://www.symmetricds.org/issues/view.php?id=3174
Hello!
I did the update and the problem continues.
My log is like this:
Hi Régis,
If you happen to have clients that come online rarely, the server will collect all modifictions for them in sym_outgoing_batch and sym_data_event for long time, consuming lot of space. Instead you can set up your system to set offline clients automatically to disabled after certain ammount of inactivity, and not collect changes for them (also delete the data collected for them until now). When they next come online they will re-register and an initial load will be sent for them. If you want to go this way, basically you just have to set the offline.node.detection.period.minutes parameter to some other than 0 value.
Last edit: krivoz 2017-07-03
Thanks, but on my research the long amount of event is releted to status 'OK', on server sym_data_event has 125 milion of register with status = 'OK', then i have to delete this before and update for newst version of symmetricds.
Maybe the purge job is not running? Do you see errors in the log file?
No appear for data_event, just apper purge job to incoming and outgoing batch table.
Any solution to this problem? I'm also finding this problem...
Last edit: Igor Lopes De Agostin 2017-07-12
This might be related to this issue fix in 3.8.27.
https://www.symmetricds.org/issues/view.php?id=3174
I have refined the test here and now it seems to be working, thank you.