Thread-save MySQL queries from FUSE

[Sven Utcke-2]

Hi,

what is the recommended way to make queries to MySQL (from FUSE, of
course) in a thread-save way?  Preferably I would like to call
mysql_init() and mysql_real_connect() exactly once for each thread and
then use the same thread-local MYSQL *mysql throughout all functions
in that thread.  But, alas, I have no clue how to achieve that, so
that at the moment each function opens and closes it's own connection,
which is quite some overhead...

Any pointers?

Thanks

Sven
--
  __ _  _ __  __ __
 / _` || '  \ \ \ /
 \__, ||_|_|_|/_\_\  http://kogs-www.informatik.uni-hamburg.de/~utcke/Private/
 |___/     Key fingerprint =  6F F8 55 1C F9 E3 A8 F7  09 DF F7 2C 25 0C 54 53

------------------------------------------------------------------------------
_______________________________________________
fuse-devel mailing list
fuse-devel@...
https://lists.sourceforge.net/lists/listinfo/fuse-devel

[Miklos Szeredi]

On Fri, 19 Dec 2008, Sven Utcke wrote:
> Hi,
>
> what is the recommended way to make queries to MySQL (from FUSE, of
> course) in a thread-save way?  Preferably I would like to call
> mysql_init() and mysql_real_connect() exactly once for each thread and
> then use the same thread-local MYSQL *mysql throughout all functions
> in that thread.  But, alas, I have no clue how to achieve that, so
> that at the moment each function opens and closes it's own connection,
> which is quite some overhead...

You can store the mysql pointer in a thread-specific data.  See the
manual page for following functions:

  pthread_key_create()
  pthread_key_destroy()
  pthread_setspecific()
  pthread_getspecific()

Miklos

------------------------------------------------------------------------------
_______________________________________________
fuse-devel mailing list
fuse-devel@...
https://lists.sourceforge.net/lists/listinfo/fuse-devel

[Sven Utcke-2]

Hello Miklos,

> On Fri, 19 Dec 2008, Sven Utcke wrote:
> > what is the recommended way to make queries to MySQL (from FUSE, of
> > course) in a thread-save way?  Preferably I would like to call
> > mysql_init() and mysql_real_connect() exactly once for each thread and
> > then use the same thread-local MYSQL *mysql throughout all functions
> > in that thread.  But, alas, I have no clue how to achieve that, so
> > that at the moment each function opens and closes it's own connection,
> > which is quite some overhead...
>
> You can store the mysql pointer in a thread-specific data.  See the
> manual page for following functions:
>
>   pthread_key_create()
>   pthread_key_destroy()
>   pthread_setspecific()
>   pthread_getspecific()

I would like to call mysql_close() on thread-termination --- how would
I do that?  Alternatively: anybody knows what happens if I do not call
mysql_close?  Would mysql keep the connection open indefinitely?

Sven
--
    _  ___  ___  ___
 __| |/ __|| __|/ __|                             The dCache File System
/ _` | (__ | _| \__ \              An archive file-system for PB of data
\__,_|\___||_|  |___/                    http://www.desy.de/~utcke/Data/

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
fuse-devel mailing list
fuse-devel@...
https://lists.sourceforge.net/lists/listinfo/fuse-devel

[Francois Barre-2]

Hello Sven,

2009/11/9 Sven Utcke
<utcke+fuse@...<utcke%2Bfuse@...>
>

> Hello Miklos,
>
>
> > On Fri, 19 Dec 2008, Sven Utcke wrote:
> > > what is the recommended way to make queries to MySQL (from FUSE, of
> > > course) in a thread-save way?  Preferably I would like to call
> > > mysql_init() and mysql_real_connect() exactly once for each thread and
> > > then use the same thread-local MYSQL *mysql throughout all functions
> > > in that thread.  But, alas, I have no clue how to achieve that, so
> > > that at the moment each function opens and closes it's own connection,
> > > which is quite some overhead...
> >
> > You can store the mysql pointer in a thread-specific data.  See the
> > manual page for following functions:
> >
> >   pthread_key_create()
> >   pthread_key_destroy()
> >   pthread_setspecific()
> >   pthread_getspecific()
>
> I would like to call mysql_close() on thread-termination --- how would
> I do that?  Alternatively: anybody knows what happens if I do not call
> mysql_close?  Would mysql keep the connection open indefinitely?
>
>

When you call pthread_key_create(), you must provide a function pointer
(void (*destr_function) (void*)) which is called by the pthread library when
the thread terminates.
The void* argument of this function pointer is filled by the pointer
provided by the last call to pthread_setspecific().
Example :

// Declarations
pthread_key_t pthread_key_mysql;
void destr_function_mysql(void* arg);

// Code to be launched before fuse_main() :
pthread_key_create ( &pthread_key_mysql, &destr_function_mysql );

// Then, for each thread, retrieve the associated object
MYSQL* getMysqlPerThread ()
{
MYSQL* mysql = (MYSQL*) pthread_getspecific ( &pthread_key_mysql );
if ( ! mysql )
{
  mysql = /* Your way to allocate a MYSQL object */;
  mysql_init ( mysql );
  pthread_setspecific ( &pthread_key_mysql, mysql );
}
return mysql;
}

// Then, last but not least, you key destructor function
void destr_function_mysql(void* arg)
{
  MYSQL* mysql = (MYSQL*) arg;
  if ( mysql )
  {
     mysql_close(mysql);
  }
}

Hope that helps !

Regards,

François.

> Sven
> --
>    _  ___  ___  ___
>  __| |/ __|| __|/ __|                             The dCache File System
> / _` | (__ | _| \__ \              An archive file-system for PB of data
> \__,_|\___||_|  |___/                    http://www.desy.de/~utcke/Data/<http://www.desy.de/%7Eutcke/Data/>
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus
> on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> fuse-devel mailing list
> fuse-devel@...
> https://lists.sourceforge.net/lists/listinfo/fuse-devel
>

--
François Barre
Chef de Projet GED
Tel : [phone]

Arondor
22, Rue de la Pépinière
75 008 Paris
http://www.arondor.com
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
fuse-devel mailing list
fuse-devel@...
https://lists.sourceforge.net/lists/listinfo/fuse-devel

[Sven Utcke-3]

Hello Francois,

> > I would like to call mysql_close() on thread-termination --- how would
> > I do that?  Alternatively: anybody knows what happens if I do not call
> > mysql_close?  Would mysql keep the connection open indefinitely?

> When you call pthread_key_create(), you must provide a function pointer
> (void (*destr_function) (void*)) which is called by the pthread library when
> the thread terminates.
> The void* argument of this function pointer is filled by the pointer
> provided by the last call to pthread_setspecific().
> Example :
[...]
> Hope that helps !

Well, it certainly looks like just the thing!

Thank you very much!

Sven
--
    _  ___  ___  ___
 __| |/ __|| __|/ __|                             The dCache File System
/ _` | (__ | _| \__ \              An archive file-system for PB of data
\__,_|\___||_|  |___/                    http://www.desy.de/~utcke/Data/

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
fuse-devel mailing list
fuse-devel@...
https://lists.sourceforge.net/lists/listinfo/fuse-devel

[Sven Utcke-3]

[thread-local connection to mysql]

> > Hope that helps !
>
> Well, it certainly looks like just the thing!

Although, surprisingly, it's just as slow to open one connection per
thread and use it many times than to open it for each and every
query.  Strange world.  I really wonder why (exactly) my program is so
slow over NFS --- surely has something to do with many getattr() and
open(), but what exactly should I change?  Anybody knows how exactly
to get useful information out of oprofile?  I know my program spends
60% in some library calls, but which, and from where was the call
made?

Sven
--
    _  ___  ___  ___
 __| |/ __|| __|/ __|                             The dCache File System
/ _` | (__ | _| \__ \              An archive file-system for PB of data
\__,_|\___||_|  |___/                    http://www.desy.de/~utcke/Data/

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
fuse-devel mailing list
fuse-devel@...
https://lists.sourceforge.net/lists/listinfo/fuse-devel

[Arun Ramachandran]

Hello Sven,

 Sometime back I hit the same/similar issue and the getattr / open calls and
the associated context switching costs I hit were the stumbling problem.
Unfortunately, I had no easy solution ...

-arun


-----Original Message-----
From: Sven Utcke [mailto:utcke@...]
Sent: Monday, November 09, 2009 3:19 PM
To: fuse-devel@...
Subject: Re: [fuse-devel] Thread-save MySQL queries from FUSE

[thread-local connection to mysql]

> > Hope that helps !
>
> Well, it certainly looks like just the thing!

Although, surprisingly, it's just as slow to open one connection per
thread and use it many times than to open it for each and every
query.  Strange world.  I really wonder why (exactly) my program is so
slow over NFS --- surely has something to do with many getattr() and
open(), but what exactly should I change?  Anybody knows how exactly
to get useful information out of oprofile?  I know my program spends
60% in some library calls, but which, and from where was the call
made?

Sven
--
    _  ___  ___  ___
 __| |/ __|| __|/ __|                             The dCache File System
/ _` | (__ | _| \__ \              An archive file-system for PB of data
\__,_|\___||_|  |___/                    http://www.desy.de/~utcke/Data/

----------------------------------------------------------------------------
--
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus
on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
fuse-devel mailing list
fuse-devel@...
https://lists.sourceforge.net/lists/listinfo/fuse-devel


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
fuse-devel mailing list
fuse-devel@...
https://lists.sourceforge.net/lists/listinfo/fuse-devel