Multiple threads connection

Hello there,
My question is in connection with this thread:
http://groups.google.com/group/comp....06a92abcf8feeb.

The program/server accepts about 1600 concurrent clients, receives a
message and sends a response to each client. The message received
will be parsed and inserted into a database, and has an infinite loop
querying the database until there is an update, then retrieves the
data from the database then send it back. I'm using a thread per
client approach. In connecting to the MySQL database, i opened 10
connections. I have a function that checks if this connection is
currently being used and flag it. When having more than 300 clients,
my dbase connections keeps encountering MySQL server has gone away, or
Commands out of sync.

The final setup will have 10 instances of the program with 160 clients
connecting. So basically i will have 100 db connections still. But
the same problem still occur when i use 2 instances with 180 clients
each. I posted this question with comp.programming.threads and most
analysis said that i have no problem with my socket programming, and
my issues is with my database connection. I alway find the error
during the waiting period, ie. in the infinite loop to check if there
is an update in the table (if it is deleted). Using valgrind i have
these problems:

Invalid write of size 4
==30927== at 0x403EA49: free_root (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x4051B51: mysql_free_result (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x804ADE8: ProcessText (server7.c:518)
==30927== by 0x8049374: ThreadMain (server7.c:97)
==30927== by 0x4085DAB: start_thread (in /lib/tls/
libpthread-0.60.so)
==30927== by 0x416B9E9: clone (in /lib/tls/libc-2.3.2.so)
==30927== Address 0x21D2B2DC is 28 bytes inside a block of size 88
free'd
==30927== at 0x401B3FB: free (vg_replace_malloc.c:233)
==30927== by 0x403BEB4: my_no_flags_free (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x4051B27: mysql_free_result (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x804ADE8: ProcessText (server7.c:518)
==30927== by 0x8049374: ThreadMain (server7.c:97)
==30927== by 0x4085DAB: start_thread (in /lib/tls/
libpthread-0.60.so)
==30927== by 0x416B9E9: clone (in /lib/tls/libc-2.3.2.so)

==30927== Invalid read of size 4
==30927== at 0x4051B18: mysql_free_result (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x804ADE8: ProcessText (server7.c:518)
==30927== by 0x8049374: ThreadMain (server7.c:97)
==30927== by 0x4085DAB: start_thread (in /lib/tls/
libpthread-0.60.so)
==30927== by 0x416B9E9: clone (in /lib/tls/libc-2.3.2.so)
==30927== Address 0x21D2B304 is 68 bytes inside a block of size 88
free'd
==30927== at 0x401B3FB: free (vg_replace_malloc.c:233)
==30927== by 0x403BEB4: my_no_flags_free (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x4051B27: mysql_free_result (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x804ADE8: ProcessText (server7.c:518)
==30927== by 0x8049374: ThreadMain (server7.c:97)
==30927== by 0x4085DAB: start_thread (in /lib/tls/
libpthread-0.60.so)
==30927== by 0x416B9E9: clone (in /lib/tls/libc-2.3.2.so)

==30927== Invalid free() / delete / delete[]
==30927== at 0x401B3FB: free (vg_replace_malloc.c:233)
==30927== by 0x403BEB4: my_no_flags_free (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x4051B27: mysql_free_result (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x804ADE8: ProcessText (server7.c:518)
==30927== by 0x8049374: ThreadMain (server7.c:97)
==30927== by 0x4085DAB: start_thread (in /lib/tls/
libpthread-0.60.so)
==30927== by 0x416B9E9: clone (in /lib/tls/libc-2.3.2.so)
==30927== Address 0x21D2B2C0 is 0 bytes inside a block of size 88
free'd
==30927== at 0x401B3FB: free (vg_replace_malloc.c:233)
==30927== by 0x403BEB4: my_no_flags_free (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x4051B27: mysql_free_result (in /u01/mysql/lib/mysql/
libmysqlclient.so.14.0.0)
==30927== by 0x804ADE8: ProcessText (server7.c:518)
==30927== by 0x8049374: ThreadMain (server7.c:97)
==30927== by 0x4085DAB: start_thread (in /lib/tls/
libpthread-0.60.so)
==30927== by 0x416B9E9: clone (in /lib/tls/libc-2.3.2.so)

Line 518 is the line with the mysql_free_result() part in:

queryString[strlen(queryString)]='\0';
for (;;)
{
pthread_mutex_lock(&mutex2);
freeIdx2=GetFreeConnection(); // get free database connector
selectdb(&myconn[freeIdx2], queryString, strlen(queryString),
&res_set);
pthread_mutex_unlock(&mutex2);

if (!res_set)
{
perror("Query error");
}
else if ( (numrow=mysql_num_rows(res_set)) == 0)
{
if ( strcmp(cpdata.actionID,"04") == 0) // MML COMM 04
{
if (function1(sendBuffer, insert_id));
}
else
{
if (function2(sendBuffer, insert_id));
}
break;
}
else
sleep(1); // sleep 1 second
mysql_free_result(res_set);
}


Here are the some of the code found in my database connection library
//// Start

typedef struct
{
MYSQL *conn;
int flg;

} M_DB_CONN;

typedef struct
{
char dbname[16];
char dbuser[16];
char dbpasswd[16];
char dbhostip[16];
char authsvr[16];
unsigned int dbport;
} CONFIGDATA;

CONFIGDATA cfg;

/* Check for free database connector */
int GetFreeConnection(void)
{

static int freex = 0;
int y=0;
while (1)
{
if(myconn[freex].flg==0)
{
myconn[freex].flg=1;
y=freex++;
if (freex >= NUMDBCONNECTION) freex=0;
return y;
}
else
freex++;
if (freex >= NUMDBCONNECTION) freex=0;
}
}

/* Open NUMDBCONNECTION number of database connection */
int connectdb( CONFIGDATA cfg, char *socketname, unsigned int flags )
{
int i;
for (i=0; i<NUMDBCONNECTION; i++)
{
myconn[i].conn = mysql_init(NULL); // verlin conn will
be an array [10]
if (myconn[i].conn == NULL)
{
printf("can't connect");
abort();
return PORTAL_FAILURE;
}

#if MYSQL_VERSION_ID > 32199 /* this lets us set the
port number */
if (mysql_real_connect (myconn[i].conn, cfg.dbhostip,
cfg.dbuser, cfg.dbpasswd, cfg.dbname, cfg.dbport, socketname, flags)
== NULL)
{
syslog(LOG_ERR, "mysql_real_connect failed:
\nError %u (%s)\n", mysql_errno (myconn[i].conn), mysql_error
(myconn[i].conn));
#else
if (mysql_connect (myconn[i].conn, cfg.dbhostip,
cfg.dbuser, cfg.dbpasswd) == NULL)
{
syslog(LOG_ERR, "mysql_connect failed:\nError
%u (%s)\n", mysql_errno (myconn[i].conn), mysql_error
(myconn[i].conn));
#endif
mysql_close(myconn[i].conn);
return PORTAL_FAILURE;
}

myconn[i].flg=0;

}
return PORTAL_SUCCESS;
}

/* Process the query */
void processdb(M_DB_CONN *con, CONFIGDATA cfg, char *query, unsigned
int len)
{
if (mysql_real_query(con->conn, query, len)) {
//#if USE_SYSLOG
//syslog(LOG_ERR, "Cant Connect, Query: %s, Error:
%s: ", query, mysql_error(conn));
//#else
printf("%s: %s\n", query, mysql_error(con->conn) );
perror("processdb error");
//#endif
}
con->flg=0;
//else {
// #if USE_SYSLOG
// syslog(LOG_INFO, "Processing Query: %s", query);
// #endif
// }
}

/* Return resultset */
int selectdb(M_DB_CONN *con, char *query, unsigned int len, MYSQL_RES
**res1)
{
if ( !(mysql_real_query(con->conn, query, len)) )
{
*res1 = mysql_store_result(con->conn);
con->flg=0;
return TRUE;
}
con->flg=0;
printf("%s: %s\n", query, mysql_error(con->conn) );
perror("selectdb error ");
return FALSE;
}

//// End

Pls take note, i do not not have problems encounter when i have 1
instance of the program with 300 clients connected. What would you
suggest? I'm using MySQL version 4.1.11

Sonny wrote:
> Hello there,
> My question is in connection with this thread:
> http://groups.google.com/group/comp....06a92abcf8feeb.
>
> The program/server accepts about 1600 concurrent clients, receives a
> message and sends a response to each client. The message received
> will be parsed and inserted into a database, and has an infinite loop
> querying the database until there is an update, then retrieves the
> data from the database then send it back. I'm using a thread per
> client approach. In connecting to the MySQL database, i opened 10
> connections. I have a function that checks if this connection is
> currently being used and flag it. When having more than 300 clients,
> my dbase connections keeps encountering MySQL server has gone away, or
> Commands out of sync.
>
> The final setup will have 10 instances of the program with 160 clients
> connecting. So basically i will have 100 db connections still. But
> the same problem still occur when i use 2 instances with 180 clients
> each. I posted this question with comp.programming.threads and most
> analysis said that i have no problem with my socket programming, and
> my issues is with my database connection. I alway find the error
> during the waiting period, ie. in the infinite loop to check if there
> is an update in the table (if it is deleted). Using valgrind i have
> these problems:
>

So you have 1600 infinite loops polling the db? Assuming that each
thread is polling a specific field (associated with that thread) that's
still alot of work for the db to do.

Suggestions:

1) Make sure your MySQL is tuned for your needs
2) Switch to AJAX w/sessions (this way your message/response system
works without tying up the db server. In which case you may not need 10
db servers in the end.

Norm

Sonny <smaniaol@gmail.com> wrote:
>
> The program/server accepts about 1600 concurrent clients, receives a
> message and sends a response to each client. The message received
> will be parsed and inserted into a database, and has an infinite loop
> querying the database until there is an update, then retrieves the
> data from the database then send it back. I'm using a thread per
> client approach. In connecting to the MySQL database, i opened 10
> connections. I have a function that checks if this connection is
> currently being used and flag it. When having more than 300 clients,
> my dbase connections keeps encountering MySQL server has gone away, or
> Commands out of sync.

Are you sharing MySQL connections between threads? This is not
recommended. Have you read the manual?

http://dev.mysql.com/doc/refman/5.0/...d-clients.html

But I suggest to stay away from your current solution. Either
have each thread open it's own connection on demand (and probably
implement an upper bound of the number of concurrently opened
connections) or implement a limited number of service threads
that talk to MySQL exclusively.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On 3 Set, 20:46, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> Sonny <smani...@gmail.com> wrote:
>
> > The program/server accepts about 1600 concurrent clients, receives a
> > message and sends a response to each client. The message received
> > will be parsed and inserted into a database, and has an infinite loop
> > querying the database until there is an update, then retrieves the
> > data from the database then send it back. I'm using a thread per
> > client approach. In connecting to the MySQL database, i opened 10
> > connections. I have a function that checks if this connection is
> > currently being used and flag it. When having more than 300 clients,
> > my dbase connections keeps encountering MySQL server has gone away, or
> > Commands out of sync.
>
> Are you sharing MySQL connections between threads? This is not
> recommended. Have you read the manual?
>
> http://dev.mysql.com/doc/refman/5.0/...d-clients.html
>
> But I suggest to stay away from your current solution. Either
> have each thread open it's own connection on demand (and probably
> implement an upper bound of the number of concurrently opened
> connections) or implement a limited number of service threads
> that talk to MySQL exclusively.
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

Thanks for the responses sirs, I will look into that. I put mutexes
for each db connections. Somehow it worked now. But were gonna change
the model soon, so this problem is not relevant now. Anyway, about
how many open database connections can MySQL have concurrently?

Hi Sonny,

Sonny <smaniaol@gmail.com> wrote:
> On 3 Set, 20:46, Axel Schwenke <axel.schwe...@gmx.de> wrote:

<cut>

> Thanks for the responses sirs,

No need for pluralis majestatis <g>

> how many open database connections can MySQL have concurrently?

There are only practical limits. Each open connection eats at least
one file descriptor for the TCP socket and some memory (~ 2MB with
defaults). So if you have big hardware and push operating system
limits, you can have several thousand open connections. But there's
not much use of that. I've seen at most ~1500 concurrent connections
being used on live systems. Normally you stay well below 1000.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On Sep 5, 1:39 am, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> Hi Sonny,
>
> Sonny <smani...@gmail.com> wrote:
> > On 3 Set, 20:46, Axel Schwenke <axel.schwe...@gmx.de> wrote:
>
> <cut>
>
> > Thanks for the responses sirs,
>
> No need for pluralis majestatis <g>
>
> > how many open database connections can MySQL have concurrently?
>
> There are only practical limits. Each open connection eats at least
> one file descriptor for the TCP socket and some memory (~ 2MB with
> defaults). So if you have big hardware and push operating system
> limits, you can have several thousand open connections. But there's
> not much use of that. I've seen at most ~1500 concurrent connections
> being used on live systems. Normally you stay well below 1000.
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

I see, thanks a lot for the info