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 https://github.com/brouznouf/fivem-mysql-async/issues/179#issuecomment-1119497488, or unsubscribe https://github.com/notifications/unsubscribe-auth/AUX6XMZPB2BX6C3UN6VLKXDVIT32DANCNFSM474YE6UA . You are receiving this because you were mentioned.Message ID: @.***>