John McCaskey wrote:
I wasnt quite sure about the safety of the error output that occured later
on, but it is true for sure in the case of no errors occuring releasing
sooner is safe. Good optimization.
I have no problems here using it like that, or any errors or core dumps or
connectivity problems, but then again I run those tests local.
Here is another interesting thing though, if multi threading is used, I
got the best results by using 2 connections, I am also using dual CPUs
(i%CONNECTIONS). I guess everyone can draw its own conclusions depending
on his needs but for me, and since I only need to do *few, simple, local*
queries, looks like I can "live" without the multi thread over head for
now. The more complex are the queries, slower the connections and higher
the response time is, (the slower the answer you get in general) then
multi threading could be a winner.
Given your connections are local and you have 2 cpus Id be interested to see
4 connections, 4 threads, 2500 queries per thread. Opening many more threads
than you have cpus is always going to be a bad idea unless there is some
sort of blocking io operation (like waiting for the network, or a disk read)
or something occuring in each thread so that alot of the time spent is just
idle. However, with just 2-4 threads and connections you should be able to
execute the queries in parallell as you actually have the hardware to do so,
while avoiding alot of the thread overhead that you saw with 100. You will
likely still go with single threaded for your situation, but it would be
very interesting to me if we could see the scenario I just outlined posted
to compare to your last posted results.
You must have read my mind! I modified it a bit more to allow just that and
already performed those tests this morning and you are very right. It appears
that adding one more thread per CPU doesn't add significantly much over head
so I would probably go with a total of 2 threads per CPU just to be on the
safe side for the cases you have described. The optimal and very logical,
under normal conditions, is to have one thread per CPU and it does make a lot
of sense.
--------------------- Local database -----------------
Thread Safe ON
DB Connections: 2, Threads: 2, Queries per Thread: 5000, Total Queries: 10000
12.74 real 2.48 user 8.28 sys
Thread Safe ON
DB Connections: 2, Threads: 2, Queries per Thread: 5000, Total Queries: 10000
12.61 real 2.74 user 7.90 sys
Thread Safe ON
DB Connections: 4, Threads: 4, Queries per Thread: 2500, Total Queries: 10000
12.83 real 2.63 user 8.23 sys
Thread Safe ON
DB Connections: 4, Threads: 4, Queries per Thread: 2500, Total Queries: 10000
12.80 real 2.84 user 8.02 sys
Thread Safe ON
DB Connections: 8, Threads: 8, Queries per Thread: 1250, Total Queries: 10000
13.06 real 2.79 user 8.31 sys
Thread Safe ON
DB Connections: 8, Threads: 8, Queries per Thread: 1250, Total Queries: 10000
12.97 real 2.76 user 8.18 sys
Now, watch those multi threaded queries really take off when the same tests
are performed on a remote database with average ping time of 11ms and since
its a remote one, I performed 1000 queries tests but the diffrence is very
obvious! Clearly, multi threading is the winner here.
---------------- Remote database with average ping of 11 ms -----------------
Thread Safe OFF
DB Connections: 1, Total Queries: 1000
32.70 real 0.07 user 0.54 sys
Thread Safe OFF
DB Connections: 1, Total Queries: 1000
33.23 real 0.11 user 0.51 sys
Thread Safe ON
DB Connections: 2, Threads: 2, Queries per Thread: 500, Total Queries: 1000
19.47 real 0.26 user 0.88 sys
Thread Safe ON
DB Connections: 2, Threads: 2, Queries per Thread: 500, Total Queries: 1000
20.52 real 0.28 user 0.86 sys
Thread Safe ON
DB Connections: 4, Threads: 4, Queries per Thread: 250, Total Queries: 1000
13.48 real 0.24 user 0.89 sys
Thread Safe ON
DB Connections: 4, Threads: 4, Queries per Thread: 250, Total Queries: 1000
13.05 real 0.25 user 0.90 sys
Thread Safe ON
DB Connections: 8, Threads: 8, Queries per Thread: 125, Total Queries: 1000
10.39 real 0.28 user 0.85 sys
Thread Safe ON
DB Connections: 8, Threads: 8, Queries per Thread: 125, Total Queries: 1000
10.06 real 0.25 user 0.89 sys
Lefteris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
