MySQL slow response after a period of run-time

MySQL slow response after a period of run-time - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL slow response after a period of run-time (/showthread.php?tid=540488)

MySQL slow response after a period of run-time - yanir3 - 05.10.2014

If I keep my server up for a few hours, the MYSQL starts to lag. When a player connects to the server, it takes more than 10 seconds for the server to fetch this single query:

PHP код:



mysql_format(sql, szQuery, sizeof(szQuery), "SELECT `username` FROM `newrp_users` WHERE `username` = '%s' AND `status` = 4", GetName(playerid));

        mysql_pquery(sql, szQuery, "OnPlayerDataLoaded", "d", playerid);

When usually, it takes less than a second. Does anyone know what could be the cause?
This fixes itself after a gamemode restart.

Additional info:
Server is Linux, I'm using a static version of the plugin and latest version (R39-2).

Re: MySQL slow response after a period of run-time - Vince - 05.10.2014

What is the server's resource usage when the game starts to lag? Also mind giving the output of (run it in a client like phpMyAdmin):

PHP код:



EXPLAIN SELECT `username` FROM `newrp_users` WHERE `username` = 'RandomName' AND `status` = 4

Put an index on username if you haven't done so already.

Re: MySQL slow response after a period of run-time - yanir3 - 05.10.2014

Quote:

Originally Posted by Vince

What is the server's resource usage when the game starts to lag? Also mind giving the output of (run it in a client like phpMyAdmin):

PHP код:



EXPLAIN SELECT `username` FROM `newrp_users` WHERE `username` = 'RandomName' AND `status` = 4

Put an index on username if you haven't done so already.

I have never used indexes on MySQL, mind explaining their purpose and contribution to the code?
Note that, I select by username only once, after that I use ID to save data.

Here's the output though.