MySQL Async High Tick, High Usage and Causing High Delays on Server

[iamrapgod]

Describe the bug
A clear and concise description of what the bug is.

• Server is starting to get delays opening up some database related resources. (Scoreboard, Inventory, storing and getting vehicles etc.)

To Reproduce
Please provide a brief script that causes the error.

• if more than 10 players tries to open scoreboard (which requires data from database) it cause some warnings and delays of opening up
• if more than 30 players, players begin to experience high hitch and delays
• when es_extended auto save/sync to database for more than 30 players it cause mysql-async to tick up to 50 CPU msec+

Expected behavior
A clear and concise description of what you expected to happen.

• should not be more than 2 seconds opening up database related functions (scoreboard, inventory)
• minimal delays and low mysql tick

Screenshots / Logs
If applicable, add screenshots and Logs to help explain your problem.

• Tracing it from ETW tracer it shows 5 instance of mysql async tick for 450+ms
• Resource Monitor showing mysql-async over 7ms CPU msec (sometimes it cranks up to 100+) from time to time (everytime there will be database related execution)
• Server logs shows an example of hitch warnings and Query warnings:
  [ script:mysql-async] [MariaDB:10.4.20-MariaDB-log] [WARNING] [esx_skin] [500ms] SELECT skin FROM users WHERE identifier = ? : ["steam:11000013624b544"]
  [ citizen-server-impl] server thread hitch warning: timer interval of 521 milliseconds

Software:

• OS: [e.g. Windows 10]
  Windows Server 2019
• Version of the resource [e.g. 22]
  mysql-async 3.3.2
• Database Version [e.g. MariaDB 10.4 or MySQL 8.0]
• MariaDB 10.4
  Additional context
  Add any other context about the problem here.
• tried optimizing SQL codes (instead of using SELECT *, tried to be more specified)
• optimized MySQL config see below:

max_connections=505
max_user_connections=500
max_allowed_packet=1G
query_cache_size=0
innodb_buffer_pool_size=26G
innodb_file_per_table=ON
thread_cache_size=32
innodb_buffer_pool_instances=6
key_buffer_size=9G

• I have 6 cores w/ 12 threads and 32GB of RAM

[iamrapgod]

SYNERGY-T71NBUL.log

• Please see my Query Logs I got from SQL logs

[iamrapgod]

fxserver_1625584243.log

• Please see my CFX Server logs

[bocado47]

any fix with this?

[tranminhduc123]

I also have the same error how to fix it?

[iamrapgod]

Apparently, es_extended is causing it to happen. There's a bug from the call outs of the identifier whenever it tries to save something using the identifier of the player. I upgraded my es_extended to a different version and it fixed the issue. You guys can try it. I am using a stand alone es_extended

[ChennaiWarriorYT]

@iamrapgod Can you share the es_extended version git link you're using? Thnx in advance

[owtxmody]

Apparently, es_extended is causing it to happen. There's a bug from the call outs of the identifier whenever it tries to save something using the identifier of the player. I upgraded my es_extended to a different version and it fixed the issue. You guys can try it. I am using a stand alone es_extended

Where Ur es_extended ??

[iamrapgod]

Can you share your es_extended ?

…

On Fri, 6 May 2022 at 19:01, owtxmody ***@***.***> wrote: Apparently, es_extended is causing it to happen. There's a bug from the call outs of the identifier whenever it tries to save something using the identifier of the player. I upgraded my es_extended to a different version and it fixed the issue. You guys can try it. I am using a stand alone es_extended Where Ur es_extended ?? — Reply to this email directly, view it on GitHub <#179 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AUX6XMZPB2BX6C3UN6VLKXDVIT32DANCNFSM474YE6UA> . You are receiving this because you were mentioned.Message ID: ***@***.***>