mysql_pquery vs mysql_tquery
#1

As of now, I use mysql_tquery everywhere unless I need to do a dozen queries at once (for example, loading player data).

My intuition is that a large number of queries which are executed one after another and the results from all the queries are needed to continue further would perform better with pquery as each query get executed parallelly. However, I see several flaws in that logic. Out of the dozen queries, some of them are expensive and some of them relatively take no time. Moreover, I don't account for other effects such as SQL server's cache when queries are executed simultaneously.

I don't know when it is appropriate to use mysql_pquery. Can someone enlighten me?
Reply
#2

tquery is added to a queue which gets executed in a thread separated from the SAMP server, while pquery is sent immediately. pquery can be frantically dangerous for use if you depend on results from a previous query. (e.g race collision). You should only use pquery for insert or update queries, which have no callbacks attached.
Reply
#3

Quote:
Originally Posted by PaulDinam
Посмотреть сообщение
tquery is added to a queue which gets executed in a thread separated from the SAMP server, while pquery is sent immediately. pquery can be frantically dangerous for use if you depend on results from a previous query. (e.g race collision). You should only use pquery for insert or update queries, which have no callbacks attached.
The order at which player's data is loaded does not matter. I have over a dozen queries to load information from the database (calls a callback). I believe that by using pquery, I can reduce the total time taken to load the player's data.
Reply
#4

If you have HUGE amount of queries in you script for samp server then you are doing something wrong in first place.

And to the question, like wiki told you, if you dont know whats better for you (pquery or tquery), use tquery.

pquery is better with parallel requests, more parallel requests and bigger connection pool give you more performance in contrast to tquery.
Reply
#5

Using tquery you have one extra thread and all queries get pushed onto a queue in that one thread. So the queries get executed in the order they are received. With pquery on the other hand your query gets pushed onto a queue in any of the available threads so queries may not necessarily execute in the order they are received. The amount of threads that are available is the number you specified for "poolsize" in mysql_connect.
Reply
#6

Quote:
Originally Posted by Vince
Посмотреть сообщение
Using tquery you have one extra thread and all queries get pushed onto a queue in that one thread. So the queries get executed in the order they are received. With pquery on the other hand your query gets pushed onto a queue in any of the available threads so queries may not necessarily execute in the order they are received. The amount of threads that are available is the number you specified for "poolsize" in mysql_connect.
Yes but at what kind of situations should pquery be used over tquery?

The wiki says pquery could be slower sometimes. I am not able to understand at what situations pquery performs slower than tquery.

My question is when to use which.
Reply
#7

tquery for when you have long and complex queries which shouldn't interfere with the server's main thread.
Reply
#8

I'd just use mysql_tquery for long queries and mysql_pquery to load the data
http://imgur.com/a/MSAxX
Reply
#9

Quote:
Originally Posted by PaulDinam
Посмотреть сообщение
tquery is added to a queue which gets executed in a thread separated from the SAMP server, while pquery is sent immediately. pquery can be frantically dangerous for use if you depend on results from a previous query. (e.g race collision). You should only use pquery for insert or update queries, which have no callbacks attached.
Can somone confirm this for me quickly, should I use pquery to for example insert a row for a table.
Reply
#10

Quote:
Originally Posted by Vince
Посмотреть сообщение
Using tquery you have one extra thread and all queries get pushed onto a queue in that one thread. So the queries get executed in the order they are received. With pquery on the other hand your query gets pushed onto a queue in any of the available threads so queries may not necessarily execute in the order they are received. The amount of threads that are available is the number you specified for "poolsize" in mysql_connect.
This.

Use mysql_pquery to fetch a lot of data at the same time, like at player login. Imagine if you did a fresh server restart and suddenly dozens of users are logging in simultaneously. You'll probably see a performance improvement there if comparing to mysql_tquery.
For everything else you really should be using mysql_tquery, because of various performance-related reasons (mainly locks).
I did a small comparison between pquery and tquery, and got some nice results. (running MariaDB with InnoDB; script I've used + logs here; pool size = 4; table has 300k entries)
I've basically sent one big update query which was working without using the primary index, and three insert queries after that, each one creating three entries.
insert query time with tquery: steady ~4ms
insert query time with pquery: ranging from 4 - 10ms
More than doubled insert query execution time in the worst case. Moreover, you can speed up the tquery inserts by using a transaction, which takes down execution speed from ~12ms (3*4ms) to ~6ms. Transactions don't work with mysql_pquery, so there's nothing you could improve there.

That's a very synthetic example of course. You can use mysql_pquery and generally don't notice anything if you're running on good hardware. I've run that example on a PC with an SSD and a powerful CPU. 4 milliseconds more or less don't really mean anything. But if you really care for performance, use the advice I've given at the beginning: mysql_pquery to load a lot of data concurrently, mysql_tquery for everything else.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)