SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 05/08/2020, 04:20 PM   #1
Fairuz
Huge Clucker
 
Fairuz's Avatar
 
Join Date: Sep 2015
Location: Indonesia
Posts: 468
Reputation: 89
Default Increasing MySQL load performance

I just updated to MySQL R41 and the loading time is so crap somehow.
PHP Code:
thread OnEntrancesLoad( )
{
    new
        
rows= -1label32 ],
        
loadingTick GetTickCount( )
    ;

    
cache_get_row_countrows );

    if ( 
rows )
    {
        while( ++
rows )
        {
            
// Save label
            
cache_get_value_namei"LABEL"label );

            
// Create entrance
            
new FloatXFloatYFloatZFloateXFloateYFloateZintIDiWorldbool:iCustombool:iVIPiMapIcon,
            
ID;
            
cache_get_value_floati"X");
            
cache_get_value_floati"Y");
            
cache_get_value_floati"Z");
            
cache_get_value_floati"EX"eX );
            
cache_get_value_floati"EY"eY );
            
cache_get_value_floati"EZ"eZ );
            
cache_get_value_inti"INTERIOR"intID );
            
cache_get_value_inti"WORLD"iWorld );
            
cache_get_value_booli"CUSTOM"iCustom );
            
cache_get_value_booli"VIP_ONLY"iVIP );
            
cache_get_value_inti"MAP_ICON"iMapIcon );
            
cache_get_value_inti"ID"ID );
            
CreateEntrancelabel,
                
XYZeXeYeZintIDiWorldiCustomiVIPiMapIcon,
                .
savedId ID
            
);
        }
    }
    
printf"[ENTRANCES]: %d entrances have been loaded. (Tick: %dms)"Iter_Countentrances ), GetTickCount( ) - loadingTick );
    return 
1;

PHP Code:
[23:15:47] [ENTRANCES]: 209 entrances have been loaded. (Tick4265ms
How do I improve this?
__________________
Los Santos based Cops and Robbers with many features.
178.128.82.211:7777

Last edited by Fairuz; 06/08/2020 at 12:54 AM.
Fairuz is offline   Reply With Quote
Old 05/08/2020, 06:06 PM   #2
RoboN1X
Huge Clucker
 
RoboN1X's Avatar
 
Join Date: Feb 2011
Location: Indonesia
Posts: 453
Reputation: 145
Default Re: Increasing MySQL load performance

try to comment out your CreateEntrance code and retest

Suggestion:
get cache using field indexes instead of name, if you sure have a fixed field order
select and store only fields you need, dont select *
Try use normal pawn callback
Try using latest version of mysql
__________________
Need Help? Use Search, WIKI, and FAQ
RoboN1X is offline   Reply With Quote
Old 06/08/2020, 12:58 AM   #3
Fairuz
Huge Clucker
 
Fairuz's Avatar
 
Join Date: Sep 2015
Location: Indonesia
Posts: 468
Reputation: 89
Default Re: Increasing MySQL load performance

Quote:
Originally Posted by RoboN1X View Post
try to comment out your CreateEntrance code and retest

Suggestion:
get cache using field indexes instead of name, if you sure have a fixed field order
select and store only fields you need, dont select *
Try use normal pawn callback
Try using latest version of mysql
1. Does that really affect it that much?
2. I will try
3. What do you mean by normal pawn callback?
4. It is the latest. It was a typo
__________________
Los Santos based Cops and Robbers with many features.
178.128.82.211:7777
Fairuz is offline   Reply With Quote
Old 06/08/2020, 05:41 PM   #4
Fairuz
Huge Clucker
 
Fairuz's Avatar
 
Join Date: Sep 2015
Location: Indonesia
Posts: 468
Reputation: 89
Default Re: Increasing MySQL load performance

Anyone?
__________________
Los Santos based Cops and Robbers with many features.
178.128.82.211:7777
Fairuz is offline   Reply With Quote
Old 09/08/2020, 02:29 PM   #5
Fairuz
Huge Clucker
 
Fairuz's Avatar
 
Join Date: Sep 2015
Location: Indonesia
Posts: 468
Reputation: 89
Default Re: Increasing MySQL load performance

Bump. I'm still struggling..
__________________
Los Santos based Cops and Robbers with many features.
178.128.82.211:7777
Fairuz is offline   Reply With Quote
Old 09/08/2020, 04:11 PM   #6
DobbysGamertag
Gangsta
 
DobbysGamertag's Avatar
 
Join Date: Jan 2013
Location: England
Posts: 808
Reputation: 100
Default Re: Increasing MySQL load performance

Have you tried it without CreateEntrance?

I'm loading 355 zones with the same code, and being passed to a function and im averaging ~20ms.
__________________


San Andreas Trucking

A re-imagining of trucking on SA:MP/OpenMultiplayer.
Coming Soon
DobbysGamertag is offline   Reply With Quote
Old 09/08/2020, 07:58 PM   #7
Kwarde
High-roller
 
Kwarde's Avatar
 
Join Date: Nov 2009
Location: The Netherlands
Posts: 2,863
Reputation: 1693
Default Re: Increasing MySQL load performance

Quote:
Originally Posted by DobbysGamertag View Post
Have you tried it without CreateEntrance?

I'm loading 355 zones with the same code, and being passed to a function and im averaging ~20ms.
Why the hell would you do that? All the function does is load data from the database into local variables, and then CreateEntrance() is called with the loaded information.
If you remove CreateEntrance() it loads data from all entrances to do nothing with it. Then you're better off removing the entire OnEntrancesLoad() function.

To achieve best performance you're best off running the server on a GNU/Linux system. Make sure that the MySQL host (I can recommend MariaDB) is on the same system as the samp server.

As for script optimalisations; Long loading times can be a pain in the arse but making sure they're unthreaded queries is a good idea. It doesn't speed up things but it makes sure that everything is loaded before releasing the server (so to speak) --When using threaded queries users can join while not everything is loaded yet.
Otherwise I don't see much space for improvements here. Maybe indeed this suggestion from RoboN1X:
Quote:
get cache using field indexes instead of name, if you sure have a fixed field order
Personally I'm not a fan of that. If you alter a table in the future and a column gets removed, or a column gets created between other columns, you've to alter all of your code. (So, when using field indexes, you should always create new columns as last).

Also I use cache_get_value_name(_{int/float/bool}) myself. But that can not affect runtime execution time because cache_get_value(_{int/float_bool}) is a macro ("overload macros for cache_get_value natives") which corrects itself to proper usage by the pre-processor.
__________________
When the opportunity presents itself to flip-a da table, uh, you flip-a da table.

Discord: Kwarde#8009
Kwarde is offline   Reply With Quote
Old 11/08/2020, 03:34 AM   #8
Fairuz
Huge Clucker
 
Fairuz's Avatar
 
Join Date: Sep 2015
Location: Indonesia
Posts: 468
Reputation: 89
Default Re: Increasing MySQL load performance

This is because of mysql_log when using ALL. I figured it out yesterday, I assumed that this is because of how the logging system has changed in MySQL R40, because it doesn't affect really much in R39.
__________________
Los Santos based Cops and Robbers with many features.
178.128.82.211:7777
Fairuz is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mysql performance MRM Scripting Help 0 30/01/2018 10:17 AM
mysql table doesn't load/save, or load take very long time Inuro Scripting Help 5 22/08/2017 09:21 PM
[Making Logs]MySQL vs Text Files for Performance Yashas Scripting Help 4 14/02/2016 01:10 PM
How to load stuff from mysql using a loop, to load alot of stuff at once Ox1gEN Scripting Help 21 13/10/2014 06:29 PM
[HELP] Increasing MySQL efficiency. yanir3 Scripting Help 1 06/01/2014 12:30 PM


All times are GMT. The time now is 12:58 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.