List:General Discussion« Previous MessageNext Message »
From:BAUMEISTER Alexandre Date:July 5 2001 7:31am
Subject:Re[3]: Proposition: Attach a comment to each connection
View as plain text  
Bonjour Werner,

  My  web  site  as a lot of hits (really!), it's load balanced on the
  web side on more than 30 servers.

  I've to save database load as much as possible.

  The  problem  with your implementation is that it needs at least one
  more  insert query for each page or even more if there are more than
  one query on the page.

  Web  pages  (PHP)  are not the only things doing queries on my Mysql
  servers.  There  are  a  lot  of  cronjobs  in  Perl  or C which are
  recording  data, doing some maintenance and that from many machines.
  Implementing  recording  a  "query  log"  like the one you described
  would generate really to much queries and would ask a lot of work.

  What  I  though  was  to had an information in Mysql on it's clients
  connection.

  For example, we could add one more parameter to mysql_real_connect :

  MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char
  *user,  const char *passwd, const char *db, unsigned int port, const
  char *unix_socket, unsigned int client_flag, char *comment)

  Which could be use like :

  mysql_real_connect(&mysql,"myserver.com","me","mypass","mydb",3306,null,0,"From page
xxxx.phtml on server X");

  Mysql  would  keep in memory, with other data about that connection,
  the comment "From page xxxx.phtml on server X".

  Then "mysqladmin process" would return :

+------+-------+-----------------+------+---------+------+-------+---------+-------------------------+------------------+
| Id   | User  | Host            | db   | Command | Time | State | Comment                
          | Info             |
+------+-------+-----------------+------+---------+------+-------+-----------------------------------+------------------+
| 1405 | me    | xxx.xxx.xxx.xxx | mydb | Query   | 3    |       | From page xxxx.phtml on
server X  | SELECT FORM ...  |
| 5973 | root  | localhost       |      | Query   | 0    |       |                        
          | show processlist |
+------+-------+-----------------+------+---------+------+-------+-----------------------------------+------------------+

  This would be really helpful, in my opinion not to hard to implement
  in  Mysql  and  easy to use from the client. This would not use much
  memory  (only  a string for each connection which number is limited)
  and would not ask more work to Mysql and the server.

  Regards,
  Alex.

WS> How about tracking your own record until then? Create a table and
WS> insert a record for each connection.

WS> If you would use a class to connect like db_mysql.inc from
WS> PHPLIB, it would be a snap to implement. From php, you know all
WS> sorts of variables to identify your general setting.

WS> I assume that the query itself will identify the job quite
WS> easily, so you use that in a text field to track your query.

WS> The Link-ID would identify your connection, which would be a
WS> permanent connection most probably. I think that this is what you
WS> call process, right?

WS> If you don't need all that information, just drop some of that.
WS> The table will grow pretty big very fast, if you record all
WS> connections, but so what? Any tracking record is of that kind,
WS> and Apache does well recording all hits, so this should't be much
WS> of a problem.

WS> You might as well normalize your table to get the most out of it.

WS> If you invest a little more work or don't work with a class, you
WS> would introduce, say two parameters, file name and line number,
WS> and go through all your code and supplement the queries with this
WS> information (and wirte a wrapper function for it, in case of no
WS> class, to issue the query and record the data).

WS> It is a one time work. Then you would have no problem finding out
WS> where any query comes from.

WS> This is exactly the kind of information you might be interested
WS> in, which MySQL cannot provide by itself, AFAICS.

WS> Am I naive or is this a viable proposition?


WS> Sie schrieben am Mittwoch, 4. Juli 2001, 23:59:44:

>> Bonjour,

>> I sent this post a few days ago but it didn't get a lot of contributions.
>> Wouldn't it be an interresting feature ?

>>> > >   When  you  have  a  big  web  site  with  a  lot of pages,
> programs,
>>> > >   cronjobs,  applications  ...  connecting  to  Mysql,  it's
> sometimes
>>> > >   difficult  to  find  from where a given query/connection comes
> from.
>>> > >   And it's sometimes urgent to find the origin of a disturbing
> query.
>>> > >
>>> > >   So,  I  think  it  would  be great to be able to give a comment
> when
>>> > >   connecting  to  Mysql.  "mysqladmin  processlist"  could  show 
> this
>>> > >   comment and this may be really helpful in some situations !
>>> > >
>>> > >   As this already been discussed ?
>>> > >

>> Regards,
>> Alex.

Thread
size of a tablekarel pitra2 Jul
  • Proposition: Attach a comment to each connectionBAUMEISTER Alexandre2 Jul
Re: Proposition: Attach a comment to each connectionD'Arcy Rittich2 Jul
Re: Proposition: Attach a comment to each connectionSiomara Pantarotto2 Jul
  • Re: Proposition: Attach a comment to each connectionD'Arcy Rittich2 Jul
Re: Proposition: Attach a comment to each connectionAlexandre BAUMEISTER4 Jul
  • Re[2]: Proposition: Attach a comment to each connectionWerner Stuerenburg5 Jul
    • Re[3]: Proposition: Attach a comment to each connectionWerner Stuerenburg5 Jul
      • different subdir for databaseJaime Teng5 Jul
        • Re: different subdir for databaseJeremy Zawodny5 Jul
          • RE: different subdir for databaseChris Bolt5 Jul
          • Re: different subdir for databaseJan Muszynski5 Jul
        • Re: different subdir for databaseMiguel Angel Sol√≥rzano5 Jul
      • Re[4]: Proposition: Attach a comment to each connectionBAUMEISTER Alexandre5 Jul
    • Re[3]: Proposition: Attach a comment to each connectionBAUMEISTER Alexandre5 Jul
  • Re: Re[4]: Proposition: Attach a comment to each connectionAnsgar Becker5 Jul
    • Re[6]: Proposition: Attach a comment to each connectionBAUMEISTER Alexandre5 Jul
  • Re: Proposition: Attach a comment to each connectionJustin Farnsworth5 Jul
    • Re[2]: Proposition: Attach a comment to each connectionBAUMEISTER Alexandre5 Jul
  • Re: Proposition: Attach a comment to each connectionJustin Farnsworth5 Jul
RE: Proposition: Attach a comment to each connectionScott Hess5 Jul
  • Re[2]: Proposition: Attach a comment to each connectionBAUMEISTER Alexandre5 Jul
Re[2]: Proposition: Attach a comment to each connectionBAUMEISTER Alexandre5 Jul
Re: Proposition: Attach a comment to each connectionJustin Farnsworth5 Jul
Re[5]: Proposition: Attach a comment to each connectionWerner Stuerenburg6 Jul
Re[5]: Proposition: Attach a comment to each connectionWerner Stuerenburg6 Jul