Plan icon indicating copy to clipboard operation
Plan copied to clipboard
verified publisher icon plan-player-analytics

Reduce amount of extension metadata transactions

Open AuroraLS3 opened this issue 3 years ago • 0 comments

I would like to be able to..

Improve write performance by reducing queue size from extension metadata storage transactions.

Is your feature request related to a problem? Please describe.

The amount of writes shows up as deadlocks since the metadata table is accessed frequently

458edd978b - Last occurred: 2022-11-27 Occurrences: 6
---- Context 5 ----
Plan v5.5 build 1969
Velocity 3.1.2-SNAPSHOT (git-213f3f88-b183)
Server v3.1.2-SNAPSHOT (git-213f3f88-b183)

Transaction: class com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction
DB State: OPEN - fatal: false
Error code: 1213
UPDATE plan_extension_providers SET text=?,description=?,priority=?,condition_name=?,icon_id=?,tab_id=(SELECT id FROM plan_extension_tabs WHERE name=? AND plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) LIMIT 1),show_in_players_table=?,hidden=?,provided_condition=?,format_type=?,player_name=? WHERE plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) AND name=?
Deadlock

---- Context 4 ----
Plan v5.5 build 1969
Velocity 3.1.2-SNAPSHOT (git-213f3f88-b183)
Server v3.1.2-SNAPSHOT (git-213f3f88-b183)

Transaction: class com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction
DB State: OPEN - fatal: false
Error code: 1213
UPDATE plan_extension_providers SET text=?,description=?,priority=?,condition_name=?,icon_id=?,tab_id=(SELECT id FROM plan_extension_tabs WHERE name=? AND plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) LIMIT 1),show_in_players_table=?,hidden=?,provided_condition=?,format_type=?,player_name=? WHERE plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) AND name=?
Deadlock

---- Context 3 ----
Plan v5.5 build 1969
Velocity 3.1.2-SNAPSHOT (git-213f3f88-b183)
Server v3.1.2-SNAPSHOT (git-213f3f88-b183)

Transaction: class com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction
DB State: OPEN - fatal: false
Error code: 1213
UPDATE plan_extension_providers SET text=?,description=?,priority=?,condition_name=?,icon_id=?,tab_id=(SELECT id FROM plan_extension_tabs WHERE name=? AND plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) LIMIT 1),show_in_players_table=?,hidden=?,provided_condition=?,format_type=?,player_name=? WHERE plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) AND name=?
Deadlock

---- Context 2 ----
Plan v5.5 build 1969
Velocity 3.1.2-SNAPSHOT (git-213f3f88-b183)
Server v3.1.2-SNAPSHOT (git-213f3f88-b183)

Transaction: class com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction
DB State: OPEN - fatal: false
Error code: 1213
UPDATE plan_extension_providers SET text=?,description=?,priority=?,condition_name=?,icon_id=?,tab_id=(SELECT id FROM plan_extension_tabs WHERE name=? AND plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) LIMIT 1),show_in_players_table=?,hidden=?,provided_condition=?,format_type=?,player_name=? WHERE plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) AND name=?
Deadlock

---- Context 1 ----
Plan v5.5 build 1969
Velocity 3.1.2-SNAPSHOT (git-213f3f88-b183)
Server v3.1.2-SNAPSHOT (git-213f3f88-b183)

Transaction: class com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction
DB State: OPEN - fatal: false
Error code: 1213
UPDATE plan_extension_providers SET text=?,description=?,priority=?,condition_name=?,icon_id=?,tab_id=(SELECT id FROM plan_extension_tabs WHERE name=? AND plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) LIMIT 1),show_in_players_table=?,hidden=?,provided_condition=?,format_type=?,player_name=? WHERE plugin_id=(SELECT id FROM plan_extension_plugins WHERE name=? AND server_uuid=? LIMIT 1) AND name=?
Deadlock

---- Stacktrace ----
java.util.concurrent.CompletionException: com.djrapitops.plan.exceptions.database.DBOpException: SQL Failure: Deadlock found when trying to get lock; try restarting transaction
   java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:315)
   java.base/java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:320)
   java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1770)
   java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
   java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
   java.base/java.lang.Thread.run(Thread.java:833)
Caused by:
com.djrapitops.plan.exceptions.database.DBOpException: SQL Failure: Deadlock found when trying to get lock; try restarting transaction
   com.djrapitops.plan.exceptions.database.DBOpException.forCause(DBOpException.java:152)
   com.djrapitops.plan.storage.database.transactions.ExecStatement.execute(ExecStatement.java:57)
   com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction.lambda$storeProvider$0(StoreProviderTransaction.java:69)
   com.djrapitops.plan.storage.database.transactions.Transaction.execute(Transaction.java:214)
   com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction.performOperations(StoreProviderTransaction.java:64)
   com.djrapitops.plan.storage.database.transactions.Transaction.executeTransaction(Transaction.java:89)
   com.djrapitops.plan.storage.database.SQLDB.lambda$executeTransaction$3(SQLDB.java:346)
   java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
   java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
   java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
   java.base/java.lang.Thread.run(Thread.java:833)
Caused by:
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
   com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
   com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
   com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
   com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
   com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
   com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
   com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
   com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
   plan.com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
   plan.com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
   com.djrapitops.plan.storage.database.transactions.ExecStatement.callExecute(ExecStatement.java:70)
   com.djrapitops.plan.storage.database.transactions.ExecStatement.execute(ExecStatement.java:64)
   com.djrapitops.plan.storage.database.transactions.ExecStatement.execute(ExecStatement.java:55)
   com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction.lambda$storeProvider$0(StoreProviderTransaction.java:69)
   com.djrapitops.plan.storage.database.transactions.Transaction.execute(Transaction.java:214)
   com.djrapitops.plan.extension.implementation.storage.transactions.providers.StoreProviderTransaction.performOperations(StoreProviderTransaction.java:64)
   com.djrapitops.plan.storage.database.transactions.Transaction.executeTransaction(Transaction.java:89)
   com.djrapitops.plan.storage.database.SQLDB.lambda$executeTransaction$3(SQLDB.java:346)
   java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
   java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
   java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
... (17 lines left)

Solution:

  • Keep a cache of extension metadata and only update if it has changed (Since it can be dynamic it can't be stored once).
    • No need to keep all data, it is enough to create a checksum for each plugin-method

AuroraLS3 avatar Nov 27 '22 14:11 AuroraLS3