MYSQL big query - Printable Version

MYSQL big query - 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 big query (/showthread.php?tid=564378)

[Solved] MYSQL big query [Solved] - qmnty - 21.02.2015

What is the maximum value of the query in MYSQL ?

Whether the server will be heavy if the query is issued more than 2,000 strings ?

Where is better script?

much query have little string or little query but have big string?

For example my saving playerdata script.

Edited:
This is my saving data player script
http://pastebin.com/SWPZYE54

Re: MYSQL big query - Sithis - 21.02.2015

The more you query, the heavier the load on the server will be. What are you trying to do?

Re: MYSQL big query - qmnty - 21.02.2015

I want to save my PlayerData.

In my script.
The query too big, there are 2000 string query.

But it always makes my server shutting down over time.

Where is better way?

big query or much query?

Re: MYSQL big query - Extremo - 21.02.2015

This is usually why things like database normalisation have been applied and used in computer science.

Here are a few interesting reads on that subject in case you are interested. They should also answer your questions.

Introduction to Normalization

These are the rules applied to the database usually:

Rules of Normalization

Stackoverflow, simply because without it this would be an incomplete answer:

StackOverflow Topic

Another tutorial that might suit your needs:

Database normalization in four quick and easy steps

What this should do is split up your queries and probably even make sure you don't have to save some data that rarely changes because it's part of a different system etcetera. This should give you a general idea on how to optimize your queries and databases, but I am sure you can also find other elegant solutions.

Re: MYSQL big query - qmnty - 21.02.2015

What do you mean split up?

is that mean make much query?

i have already using normalization on my DataBase.

but still make my server shutting down

Re: MYSQL big query - Extremo - 21.02.2015

If you have arleady used normalization on your database, then structurally speaking each system should have their independent tables with the respected primary and foreign keys.

In that case, the option I see here is to update the database according to changes. More often than not, a lot of the data you update everytime is not required to be updated because they haven't been altered. I don't even know how you could possibly have so much data to update that your database/server crashes, but assuming this is the case, my option seems to be the solution I can think of.

Also, while on that subject, I'd advise you to port to a plugin that supports multiple threads, so that your server is unaffected when the database requires a long time to process the queries.

I hope this helps.

PS: Have you considered you might have another issue?

Re: MYSQL big query - qmnty - 21.02.2015

I have already insert my script there.

If you've seen?

So, what the solution for my problem?

I get this from mysql_stat

pawn Код:

[05:59:13] Count: 1701
[05:59:13] Uptime: 1194559  Threads: 1  Questions: 108112  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:13] Count: 821
[05:59:13] Uptime: 1194559  Threads: 1  Questions: 108113  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:13] Count: 1639
[05:59:13] Uptime: 1194559  Threads: 1  Questions: 108114  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:13] Count: 1112
[05:59:14] Uptime: 1194560  Threads: 1  Questions: 108115  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:14] Count: 1126
[05:59:14] Uptime: 1194560  Threads: 1  Questions: 108116  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:14] Count: 1122
[05:59:14] Uptime: 1194560  Threads: 1  Questions: 108117  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:14] Count: 1081
[05:59:15] Uptime: 1194561  Threads: 1  Questions: 108118  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:15] Count: 835
[05:59:15] Uptime: 1194561  Threads: 1  Questions: 108119  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:15] Count: 683
[05:59:15] Uptime: 1194561  Threads: 1  Questions: 108120  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90
[05:59:15] Count: 873
[05:59:15] Uptime: 1194561  Threads: 1  Questions: 108121  Slow queries: 0  Opens: 38  Flush tables: 1  Open tables: 18  Queries per second avg: 0.90

Is that normal?

Re: MYSQL big query - qmnty - 22.02.2015

Is my Saving Script normal?

I always got this debug error from crashdetect.

pawn Код:

[14:50:16] [debug] #0 native mysql_query () [b7441b50] from mysql.so
[14:50:16] [debug] #1 00088034 in public SavingData (0x00000023) from script.amx
[14:50:16] [debug] #2 000e2374 in public Audio_OnPlayerDisconnect (0x00000023, 0x00000001) from script.amx
[14:50:16] [debug] #3 native CallLocalFunction () [080dce40] from scriptserver
[14:50:16] [debug] #4 0000b928 in public SSCANF_OnPlayerDisconnect (0x00000023, 0x00000001) from script.amx
[14:50:16] [debug] #5 000059b8 in public Itter_OnPlayerDisconnect (0x00000023, 0x00000001) from script.amx
[14:50:16] [debug] #6 native CallLocalFunction () [080dce40] from scriptserver
[14:50:16] [debug] #7 00001834 in public Streamer_OnPlayerDisconnect (0x00000023, 0x00000001) from script.amx
[14:50:16] [debug] #8 000006f0 in public OnPlayerDisconnect (0x00000023, 0x00000001) from script.amx

In this case.
function SavingData is my Saving Script.

And my server shutting down.

This last debug from Debug log before server shutting down.

pawn Код:

[19:37:07] CMySQLHandler::Query(UPDATE playerdata SET Skills='0|0|0|0|0|0', Wea$...bla...bla.... - - An error has occured. (Error ID: 2013, Lost connection to MySQL server during query)
[19:37:07] >> mysql_real_escape_string( Connection handle: 1 )

Re: MYSQL big query - qmnty - 22.02.2015

the "bla..bla..." i mean that Debug log not completely.

This is complete log from that.

pawn Код:

CMySQLHandler::Query(UPDATE playerdata SET Skills='0|0|0|0|0|0', WeaponSkill='0|4|74|0', LastDuty='22|2|2015', DutyTime='0|0|0', WorkShop='1', WSid='1', KTime='29|2|2015', KTPID='1', CheckCash='0', Checks='0', GunLic='0', Gun='0|3|0|0|0|31|0|0|0|0|0|0', GunAmmo='0|10|0|0|0|465|0|0|0|0|0|0', DrugsTime='0', MechTime='0', Insurance='0', PayDay='6', Dice='0', Spraycan='0', Rope='0', Cigars='0', Sprunk='0', Tutorial='1', Tutor='0', OnDuty='1', Married='0', MarriedTo='None', IP='127.0.0.1', WantedLevel='0', NewMuted='0', NewMutedTotal='0', AdMuted='0', AdMutedTotal='0', HelpMute='0', SpawnPos='1193.648315|-1300.986206|13.386955|190.591552', ReportMuted='0', ReportMutedTotal='0', ReportMutedTime='0', VIPMuted='0', VIPMutedTime='0', GiftTime='-5', AdvisorDutyHours='0', AcceptedHelp='0', AcceptReport='273' WHERE NormalName='Kora_Sama') - An error has occured. (Error ID: 2013, Lost connection to MySQL server during query)
[19:37:07] >> mysql_real_escape_string( Connection handle: 1 )

That's completely log.

Re: MYSQL big query - Extremo - 22.02.2015

Quote:

Originally Posted by Extremo

This is usually why things like database normalisation have been applied and used in computer science.

Here are a few interesting reads on that subject in case you are interested. They should also answer your questions.

Introduction to Normalization

These are the rules applied to the database usually:

Rules of Normalization

Stackoverflow, simply because without it this would be an incomplete answer:

StackOverflow Topic

Another tutorial that might suit your needs:

Database normalization in four quick and easy steps

What this should do is split up your queries and probably even make sure you don't have to save some data that rarely changes because it's part of a different system etcetera. This should give you a general idea on how to optimize your queries and databases, but I am sure you can also find other elegant solutions.

That's how you normalise. I still don't get how you lose connection when you use localhost. It should avoid the whole TCP/UDP connection and use a direct socket. Maybe someone else has a clue?