------_=_NextPart_001_01C6DB58.ED12F388
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have a design concern with MySql++, I have multithreaded software that
can have 1000 threads simultaneously connecting to a Mysql server

=20

At first I made one connection per thread but it quickly becomes a
performance problem, the system was taking to long to connect.

=20

Then I switched to one connection for each server/database shared
amongst the threads.

=20

Now I receive `MySQL server has gone away ` reading the online doc I now
read a contradictory advice to use many connections, anyone as a
scalable solution to share?

* You can also encounter this error with applications that fork
child processes, all of which try to use the same connection to the
MySQL server. This can be avoided by using a separate connection for
each child process.=20

=20

=20

=20

=20


------_=_NextPart_001_01C6DB58.ED12F388--

Ok after all the performance look's great I think my problem was
mysql_ping() after reading the description again I found that we must
use this only when there was a significant idle period and not every
query like I was doing.

I guess the normal behaviour is to wait after an error and then
reconnect if possible?

Julien



-----Original Message-----
From: Julien Chavanton=20
Sent: September 18, 2006 3:31 PM
To: plusplus (AT) lists (DOT) mysql.com
Subject: Connection sharing vs lost of query per connections

I have a design concern with MySql++, I have multithreaded software that
can have 1000 threads simultaneously connecting to a Mysql server

=20

At first I made one connection per thread but it quickly becomes a
performance problem, the system was taking to long to connect.

=20

Then I switched to one connection for each server/database shared
amongst the threads.

=20

Now I receive `MySQL server has gone away ` reading the online doc I now
read a contradictory advice to use many connections, anyone as a
scalable solution to share?

* You can also encounter this error with applications that fork
child processes, all of which try to use the same connection to the
MySQL server. This can be avoided by using a separate connection for
each child process.=20

=20

=20

=20

=20


--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsu...ie.nctu.edu.tw

Julien Chavanton wrote:
Yes. While a ping is the least expensive round-trip call you can have
to the database, it's still expensive in terms of local CPU time.

There's a MySQL connection option to make the library try to reconnect
automatically. This was covered just days ago. Search the list.

You should still write your code with an awareness that a connection
could fail, however. Networks break, servers die.

--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsu...ie.nctu.edu.tw

I understand the only work around is to make "pooling" meaning the
software will dynamically create and share a certain amount of
connections amongst the threads as it will be required to avoid (mutex
lock) an to minimize the number of connections.

Since I have since it is a performance issue to have too many
connections.

Are my presumptions correct or do I miss something?



-----Original Message-----
From: Julien Chavanton=20
Sent: September 20, 2006 10:28 AM
To: 'plusplus (AT) lists (DOT) mysql.com'
Subject: RE: Connection sharing vs lost of query per connections


I was thinking the Mysql++ api was thread safe because the database
system is using lock's himself and because of other opionion I have read
about connection pooling.

Reading the documentation it is written that when sharing a connection
between threads we have to lock the connection between
Mysql_query() and mysql_store_result() witch mean we have to wait after
the full query completion and data transfer, it is then clear that
sharing connection is not interesting since one slow query could
interfere with another quick query (that may normally not be affected by
database locks).

I have to conclude that Mysql++ is not efficient with threading while
sharing connection, I hope I misunderstand something?

Or is there another way around?



-----Original Message-----
From: Warren Young [mailto:mysqlpp (AT) etr-usa (DOT) com]=20
Sent: September 19, 2006 11:59 AM
To: MySQL++ Mailing List
Subject: Re: Connection sharing vs lost of query per connections

Julien Chavanton wrote:
Yes. While a ping is the least expensive round-trip call you can have=20
to the database, it's still expensive in terms of local CPU time.

There's a MySQL connection option to make the library try to reconnect=20
automatically. This was covered just days ago. Search the list.

You should still write your code with an awareness that a connection=20
could fail, however. Networks break, servers die.

--=20
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsu...ecom (DOT) com


--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsu...ie.nctu.edu.tw

I am confused because it seem "sharing" the connection between one
hundred threads does not seem to produce any "sync error" as long as
each thread as it's own "MYSQL_RES *res_ptr" they can simultaneously use
the connection, if this is true, I found this solution perfect since I
only want to minimize the amount of connections I do not want to
minimise the amount of MYSQL_RES structure in memory.

I have made lots of stress test with 100 threads sharing one connection
and there was no problem.

Maybe what the manual mean was that threads can not share the same
MYSQL_RES structure without using mutex or maybe I am really lucky while
doing my stress tests?




-----Original Message-----
From: Julien Chavanton=20
Sent: September 20, 2006 10:50 AM
To: Julien Chavanton; 'plusplus (AT) lists (DOT) mysql.com'
Subject: RE: Connection sharing vs lost of query per connections

I understand the only work around is to make "pooling" meaning the
software will dynamically create and share a certain amount of
connections amongst the threads as it will be required to avoid (mutex
lock) an to minimize the number of connections.

Since I have since it is a performance issue to have too many
connections.

Are my presumptions correct or do I miss something?



-----Original Message-----
From: Julien Chavanton=20
Sent: September 20, 2006 10:28 AM
To: 'plusplus (AT) lists (DOT) mysql.com'
Subject: RE: Connection sharing vs lost of query per connections


I was thinking the Mysql++ api was thread safe because the database
system is using lock's himself and because of other opionion I have read
about connection pooling.

Reading the documentation it is written that when sharing a connection
between threads we have to lock the connection between
Mysql_query() and mysql_store_result() witch mean we have to wait after
the full query completion and data transfer, it is then clear that
sharing connection is not interesting since one slow query could
interfere with another quick query (that may normally not be affected by
database locks).

I have to conclude that Mysql++ is not efficient with threading while
sharing connection, I hope I misunderstand something?

Or is there another way around?



-----Original Message-----
From: Warren Young [mailto:mysqlpp (AT) etr-usa (DOT) com]=20
Sent: September 19, 2006 11:59 AM
To: MySQL++ Mailing List
Subject: Re: Connection sharing vs lost of query per connections

Julien Chavanton wrote:
Yes. While a ping is the least expensive round-trip call you can have=20
to the database, it's still expensive in terms of local CPU time.

There's a MySQL connection option to make the library try to reconnect=20
automatically. This was covered just days ago. Search the list.

You should still write your code with an awareness that a connection=20
could fail, however. Networks break, servers die.

--=20
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsu...ecom (DOT) com


--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsu...ie.nctu.edu.tw

I was thinking the Mysql++ api was thread safe because the database
system is using lock's himself and because of other opionion I have read
about connection pooling.

Reading the documentation it is written that when sharing a connection
between threads we have to lock the connection between
Mysql_query() and mysql_store_result() witch mean we have to wait after
the full query completion and data transfer, it is then clear that
sharing connection is not interesting since one slow query could
interfere with another quick query (that may normally not be affected by
database locks).

I have to conclude that Mysql++ is not efficient with threading while
sharing connection, I hope I misunderstand something?

Or is there another way around?



-----Original Message-----
From: Warren Young [mailto:mysqlpp (AT) etr-usa (DOT) com]=20
Sent: September 19, 2006 11:59 AM
To: MySQL++ Mailing List
Subject: Re: Connection sharing vs lost of query per connections

Julien Chavanton wrote:
Yes. While a ping is the least expensive round-trip call you can have=20
to the database, it's still expensive in terms of local CPU time.

There's a MySQL connection option to make the library try to reconnect=20
automatically. This was covered just days ago. Search the list.

You should still write your code with an awareness that a connection=20
could fail, however. Networks break, servers die.

--=20
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsu...ecom (DOT) com


--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsu...ie.nctu.edu.tw

My understanding is that you can't share the same connection between threads
without mutexing around the Query and storing the Result. If you don't
mutex this bit, then another thread can perform a Query in between the two
and the first thread gets the second threads result set:

Thread1 Query
Thread2 Query
Thread1 Store Result (stores result to Thread2's Query)

This is because the result set is stored (or at least a reference to it is
stored) within the shared MYSQL structure and is only copied to a separate
space when you call store result. If you need more info, see
http://dev.mysql.com/doc/refman/5.1/...d-clients.html



-----Original Message-----
From: Julien Chavanton [mailto:jc (AT) atlastelecom (DOT) com]
Sent: 20 September 2006 19:24
To: Julien Chavanton; plusplus (AT) lists (DOT) mysql.com
Subject: RE: Connection sharing vs lost of query per connections

I am confused because it seem "sharing" the connection between one hundred
threads does not seem to produce any "sync error" as long as each thread as
it's own "MYSQL_RES *res_ptr" they can simultaneously use the connection, if
this is true, I found this solution perfect since I only want to minimize
the amount of connections I do not want to minimise the amount of MYSQL_RES
structure in memory.

I have made lots of stress test with 100 threads sharing one connection and
there was no problem.

Maybe what the manual mean was that threads can not share the same MYSQL_RES
structure without using mutex or maybe I am really lucky while doing my
stress tests?




-----Original Message-----
From: Julien Chavanton
Sent: September 20, 2006 10:50 AM
To: Julien Chavanton; 'plusplus (AT) lists (DOT) mysql.com'
Subject: RE: Connection sharing vs lost of query per connections

I understand the only work around is to make "pooling" meaning the software
will dynamically create and share a certain amount of connections amongst
the threads as it will be required to avoid (mutex
lock) an to minimize the number of connections.

Since I have since it is a performance issue to have too many connections.

Are my presumptions correct or do I miss something?



-----Original Message-----
From: Julien Chavanton
Sent: September 20, 2006 10:28 AM
To: 'plusplus (AT) lists (DOT) mysql.com'
Subject: RE: Connection sharing vs lost of query per connections


I was thinking the Mysql++ api was thread safe because the database
system is using lock's himself and because of other opionion I have read
about connection pooling.

Reading the documentation it is written that when sharing a connection
between threads we have to lock the connection between
Mysql_query() and mysql_store_result() witch mean we have to wait after
the full query completion and data transfer, it is then clear that
sharing connection is not interesting since one slow query could
interfere with another quick query (that may normally not be affected by
database locks).

I have to conclude that Mysql++ is not efficient with threading while
sharing connection, I hope I misunderstand something?

Or is there another way around?



-----Original Message-----
From: Warren Young [mailto:mysqlpp (AT) etr-usa (DOT) com]
Sent: September 19, 2006 11:59 AM
To: MySQL++ Mailing List
Subject: Re: Connection sharing vs lost of query per connections

Julien Chavanton wrote:
Yes. While a ping is the least expensive round-trip call you can have
to the database, it's still expensive in terms of local CPU time.

There's a MySQL connection option to make the library try to reconnect
automatically. This was covered just days ago. Search the list.

You should still write your code with an awareness that a connection
could fail, however. Networks break, servers die.

--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsu...ecom (DOT) com


--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe:
http://lists.mysql.com/plusplus?unsu...coms (DOT) com



--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsu...ie.nctu.edu.tw

Julien Chavanton wrote:
Not even a little bit.

The only thing we do right now is allow you to link MySQL++ to the
thread-aware versions of your C and C++ standard libraries if you want,
so calls down into those don't blow the program up when you use threads.
MySQL++ itself has no awareness of threads.

We will be extending the current Lock class at some point to add some
thread safety, but right now, it's completely useless for that purpose.

--
MySQL++ Mailing List
For list archives: http://lists.mysql.com/plusplus
To unsubscribe: http://lists.mysql.com/plusplus?unsu...ie.nctu.edu.tw