High mysql load



  • Hello,

    i have Kopano Core:8.7.80 and Z-Push: 2.3.9 running on an Ubuntu 18.04 Server.
    I see a high mysql load, in the general log off the mysql Server i see alway 3 querrys running:

    DELETE FROM syncedmessages WHERE sync_id=2742 AND change_id>116119
    SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =2742 FOR UPDATE
    UPDATE syncs SET change_id = 116119, sync_time = FROM_UNIXTIME(1573657300) WHERE id = 2742

    Is there a solution to stop that “loop”?

    Best Regards

    Sören



  • Hallo everyone,

    I’m facing then same situation here. Even without incoming connections (inbound-ports on firewall closed) there are permanent write-operations on mysql caused by kopano-server - average writes per hour about 400MB without incoming messages. Unterlying SSD is counting TBW…

    Platform: Debian 9
    Server: Kopano 8.7.80

    Logfile shows some kind of “loop” - normal operation or something faulty?

    Best Regards
    Marcell

    […]
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000029]: “BEGIN;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000029]: “SELECT change_id,sourcekey,sync_type FROM syncs WHERE id=7302 FOR UPDATE;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000029]: “SELECT MAX(id) FROM changes;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000029]: “SELECT m.sourcekey, m.parentsourcekey, c.change_type, c.flags FROM syncedmessages as m LEFT JOIN changes as c ON m.sourcekey=c.sourcekey AND m.parentsourcekey=c.parentsourcekey AND c.id > 1414819 AND c.sourcesync != 7302 WHERE sync_id=7302 AND change_id=1414819;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000029]: “SELECT changes.id, changes.sourcekey, changes.parentsourcekey, changes.change_type, changes.flags, NULL, changes.sourcesync FROM changes LEFT JOIN indexedproperties ON indexedproperties.val_binary = changes.sourcekey AND indexedproperties.tag = 26080 LEFT JOIN hierarchy ON hierarchy.id = indexedproperties.hierarchyid WHERE changes.id > 1414819 AND changes.change_type & 4096 AND changes.sourcesync != 7302 AND (ISNULL(hierarchy.flags) OR hierarchy.flags & 64 = 0) ORDER BY changes.id;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000029]: “DELETE FROM syncedmessages WHERE sync_id=7302 AND change_id>1414819;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000029]: “COMMIT;”

    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000025]: “BEGIN;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000025]: “SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =7302 FOR UPDATE;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000025]: “UPDATE syncs SET change_id = 1414819, sync_time = FROM_UNIXTIME(1578945985) WHERE id = 7302;”
    Mon Jan 13 21:06:25 2020: [ 10006] SQL [00000025]: “COMMIT;”

    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000031]: “BEGIN;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000031]: “SELECT change_id,sourcekey,sync_type FROM syncs WHERE id=7302 FOR UPDATE;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000031]: “SELECT MAX(id) FROM changes;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000031]: “SELECT m.sourcekey, m.parentsourcekey, c.change_type, c.flags FROM syncedmessages as m LEFT JOIN changes as c ON m.sourcekey=c.sourcekey AND m.parentsourcekey=c.parentsourcekey AND c.id > 1414819 AND c.sourcesync != 7302 WHERE sync_id=7302 AND change_id=1414819;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000031]: “SELECT changes.id, changes.sourcekey, changes.parentsourcekey, changes.change_type, changes.flags, NULL, changes.sourcesync FROM changes LEFT JOIN indexedproperties ON indexedproperties.val_binary = changes.sourcekey AND indexedproperties.tag = 26080 LEFT JOIN hierarchy ON hierarchy.id = indexedproperties.hierarchyid WHERE changes.id > 1414819 AND changes.change_type & 4096 AND changes.sourcesync != 7302 AND (ISNULL(hierarchy.flags) OR hierarchy.flags & 64 = 0) ORDER BY changes.id;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000031]: “DELETE FROM syncedmessages WHERE sync_id=7302 AND change_id>1414819;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000031]: “COMMIT;”

    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000028]: “BEGIN;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000028]: “SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =7302 FOR UPDATE;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000028]: “UPDATE syncs SET change_id = 1414819, sync_time = FROM_UNIXTIME(1578945990) WHERE id = 7302;”
    Mon Jan 13 21:06:30 2020: [ 10006] SQL [00000028]: “COMMIT;”

    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000026]: “BEGIN;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000026]: “SELECT change_id,sourcekey,sync_type FROM syncs WHERE id=7302 FOR UPDATE;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000026]: “SELECT MAX(id) FROM changes;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000026]: “SELECT m.sourcekey, m.parentsourcekey, c.change_type, c.flags FROM syncedmessages as m LEFT JOIN changes as c ON m.sourcekey=c.sourcekey AND m.parentsourcekey=c.parentsourcekey AND c.id > 1414819 AND c.sourcesync != 7302 WHERE sync_id=7302 AND change_id=1414819;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000026]: “SELECT changes.id, changes.sourcekey, changes.parentsourcekey, changes.change_type, changes.flags, NULL, changes.sourcesync FROM changes LEFT JOIN indexedproperties ON indexedproperties.val_binary = changes.sourcekey AND indexedproperties.tag = 26080 LEFT JOIN hierarchy ON hierarchy.id = indexedproperties.hierarchyid WHERE changes.id > 1414819 AND changes.change_type & 4096 AND changes.sourcesync != 7302 AND (ISNULL(hierarchy.flags) OR hierarchy.flags & 64 = 0) ORDER BY changes.id;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000026]: “DELETE FROM syncedmessages WHERE sync_id=7302 AND change_id>1414819;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000026]: “COMMIT;”

    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000030]: “BEGIN;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000030]: “SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =7302 FOR UPDATE;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000030]: “UPDATE syncs SET change_id = 1414819, sync_time = FROM_UNIXTIME(1578945995) WHERE id = 7302;”
    Mon Jan 13 21:06:35 2020: [ 10006] SQL [00000030]: “COMMIT;”
    […]



  • Hello,
    just registered for this entry to comment.

    Exactly the same problem here.
    Any suggestions?
    Is there a safe way to purge the tables syncedmessages / syncs so the sync can be retriggered?
    Searched for an kopano-admin command, but none found.
    best regards
    Thorsten


Log in to reply