Posted by peter
General query logging can be very handy in MySQL on profuction server for various debugging needs. Unfortunately you can’t switch it on and off without restarting server until MySQL 5.0.
What can you do in MySQL 5.0 and below ?
Use Our Patch - With this patch you can get all queries logged as slow queries (with times) and as you can change long-query-time online you can effectively enable and disable debug logging live. Note however this is not 100% equivalent for general query log - for example connects or queries with syntax errors will not be logged.
Enable logging to /dev/null You can enable queries to say “all_queries” log and symlink that to /dev/null. So when you will need to enable queries you can symlink it to something else and run “flush logs” so logs are reopened and written to the file in question. When you have debug info you can just switch it back. Using /dev/null as a target allows to eliminate a lot of log writing overhead and save disk space which can be consumed very fast otherwise. Of course it does not remove all logging overhead - but this should not be major for most applications.
Posted by
peter @ 3:19 am ::
tips ::
Posted by Vadim
If you like to compile MySQL from sources by yourself, for different needs, like debugging, testing etc, you probably can face this issue.
What I usually do to fast compile and test is
SQL:
-
./configure --prefix=/dir/to/mysql
-
make
-
make install
and then, for example, load the dump of InnoDB from previous version:
mysql testdatabase < dump.sql
I bet you will not notice all your tables now is MyISAM. Why?
SQL:
-
Welcome TO the MySQL monitor. Commands end WITH ; OR \g.
-
Your MySQL connection id IS 1
-
Server version: 5.1.21-beta-log Source distribution
-
-
Type 'help;' OR '\h' FOR help. Type '\c' TO clear the buffer.
-
-
mysql> SHOW engines;
-
+------------+---------+-----------------------------------------------------------+--------------+----+------------+
-
| Engine | Support | Comment | Transactions | XA | Savepoints |
-
+------------+---------+-----------------------------------------------------------+--------------+----+------------+
-
| CSV | YES | CSV storage engine | NO | NO | NO |
-
| MRG_MYISAM | YES | Collection of identical MyISAM TABLES | NO | NO | NO |
-
| MEMORY | YES | Hash based, stored IN memory, useful FOR TEMPORARY TABLES | NO | NO | NO |
-
| MyISAM | DEFAULT | DEFAULT engine AS of MySQL 3.23 WITH great performance | NO | NO | NO |
-
+------------+---------+-----------------------------------------------------------+--------------+----+------------+
-
4 rows IN SET (0.00 sec)
By default InnoDB is not compiled as storage engines.
Perhaps it is related to new pluginable architecture and all engines are equal to be not included by default.
It is not too hard to fix, you just need to use --with-plugins=innodb (or max, or max-no-ndb, which includes set of more engines)
SQL:
-
./configure ---prefix=/dir/to/mysql --with-plugins=innodb
-
make
-
make install
But what I would want to see is BIG Warning or even Error that InnoDB table can't be created instead of calm converting to MyISAM
del.icio.us
digg