High mysql load

soeren76

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

marpap

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;”
[…]

tjantzen

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

chackl

I have the same issuse since more than one year - thinking that it might get fixed in future
I just thought it would be no problem - but it will get you a critical high write-load over time so that cache SSDs rapidly will use there lifetime / TBW.

current system: Univention 4.4-8 errata983
WebApp: 5.1.0.0+167.1
Kopano Core: 8.7.20
No Z-Push (It was installed - but i uninstalled it via Appcenter)

I see this issue on 2 different installations that are not linked. They are eaven not updated at the same time.

I did a query log coming to this result:

/usr/sbin/mysqld, Version: 10.1.48-MariaDB-0+deb9u2 (Debian 9.13). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
210527 15:20:27       14 Query    BEGIN
           14 Query    SELECT change_id,sourcekey,sync_type FROM syncs WHERE id=1 FOR UPDATE
           14 Query    SELECT MAX(id) FROM changes
           14 Query    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 > 240040 AND c.sourcesync != 1 WHERE sync_id=1 AND change_id=240040
           14 Query    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 > 240040  AND changes.change_type & 4096  AND changes.sourcesync != 1 AND changes.change_type & 15 != 3 AND (ISNULL(hierarchy.flags) OR hierarchy.flags & 64 = 0)  ORDER BY changes.id
           14 Query    DELETE FROM syncedmessages WHERE sync_id=1 AND change_id>240040
           14 Query    COMMIT
           12 Query    BEGIN
           12 Query    SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =1 FOR UPDATE
           12 Query    UPDATE syncs SET change_id = 240040, sync_time = FROM_UNIXTIME(1622121627) WHERE id = 1
           12 Query    COMMIT
210527 15:20:32       13 Query    BEGIN
           13 Query    SELECT change_id,sourcekey,sync_type FROM syncs WHERE id=1 FOR UPDATE
           13 Query    SELECT MAX(id) FROM changes
           13 Query    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 > 240040 AND c.sourcesync != 1 WHERE sync_id=1 AND change_id=240040
           13 Query    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 > 240040  AND changes.change_type & 4096  AND changes.sourcesync != 1 AND changes.change_type & 15 != 3 AND (ISNULL(hierarchy.flags) OR hierarchy.flags & 64 = 0)  ORDER BY changes.id
           13 Query    DELETE FROM syncedmessages WHERE sync_id=1 AND change_id>240040
           13 Query    COMMIT
           10 Query    BEGIN
           10 Query    SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =1 FOR UPDATE
           10 Query    UPDATE syncs SET change_id = 240040, sync_time = FROM_UNIXTIME(1622121632) WHERE id = 1
           10 Query    COMMIT
210527 15:20:37        9 Query    BEGIN
            9 Query    SELECT change_id,sourcekey,sync_type FROM syncs WHERE id=1 FOR UPDATE
            9 Query    SELECT MAX(id) FROM changes
            9 Query    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 > 240040 AND c.sourcesync != 1 WHERE sync_id=1 AND change_id=240040
            9 Query    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 > 240040  AND changes.change_type & 4096  AND changes.sourcesync != 1 AND changes.change_type & 15 != 3 AND (ISNULL(hierarchy.flags) OR hierarchy.flags & 64 = 0)  ORDER BY changes.id
            9 Query    DELETE FROM syncedmessages WHERE sync_id=1 AND change_id>240040
            9 Query    COMMIT
           15 Query    BEGIN
           15 Query    SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =1 FOR UPDATE
           15 Query    UPDATE syncs SET change_id = 240040, sync_time = FROM_UNIXTIME(1622121637) WHERE id = 1
           15 Query    COMMIT
210527 15:20:42        8 Query    BEGIN
            8 Query    SELECT change_id,sourcekey,sync_type FROM syncs WHERE id=1 FOR UPDATE
            8 Query    SELECT MAX(id) FROM changes
            8 Query    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 > 240040 AND c.sourcesync != 1 WHERE sync_id=1 AND change_id=240040
            8 Query    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 > 240040  AND changes.change_type & 4096  AND changes.sourcesync != 1 AND changes.change_type & 15 != 3 AND (ISNULL(hierarchy.flags) OR hierarchy.flags & 64 = 0)  ORDER BY changes.id
            8 Query    DELETE FROM syncedmessages WHERE sync_id=1 AND change_id>240040
            8 Query    COMMIT
           11 Query    BEGIN
           11 Query    SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =1 FOR UPDATE
           11 Query    UPDATE syncs SET change_id = 240040, sync_time = FROM_UNIXTIME(1622121642) WHERE id = 1
           11 Query    COMMIT
210527 15:20:47       10 Query    BEGIN
           10 Query    SELECT change_id,sourcekey,sync_type FROM syncs WHERE id=1 FOR UPDATE
           10 Query    SELECT MAX(id) FROM changes
           10 Query    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 > 240040 AND c.sourcesync != 1 WHERE sync_id=1 AND change_id=240040
           10 Query    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 > 240040  AND changes.change_type & 4096  AND changes.sourcesync != 1 AND changes.change_type & 15 != 3 AND (ISNULL(hierarchy.flags) OR hierarchy.flags & 64 = 0)  ORDER BY changes.id
           10 Query    DELETE FROM syncedmessages WHERE sync_id=1 AND change_id>240040
           10 Query    COMMIT
            9 Query    BEGIN
            9 Query    SELECT sourcekey, change_id, sync_type FROM syncs WHERE id =1 FOR UPDATE
            9 Query    UPDATE syncs SET change_id = 240040, sync_time = FROM_UNIXTIME(1622121647) WHERE id = 1
            9 Query    COMMIT

There is a query-block that is executed every 5 seconds with an UPDATE statement - which i fear causes the write-load.
Is there any way to get around this? Like setting the 5 seconds to 1 minute? I do not know what this thin is doing - so maybe it can be disabled?

Thank you,
Kind regards, C.Hackl

chackl

This post is deleted!

cpzengel

Same Problem here!!!
How to handle?