I have good news and bad news on this one.
GOOD NEWS
You could use the general log as a table you can query
Step 01) Add this to /etc/my.cnf
[mysqld]
log
log-output=TABLE
Step 02) service mysql restart
OK mysqld is not recording every query in the table mysql.general_log
. Problem: look at the initial layout of mysql.general_log
:
mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
Table: general_log
Create Table: CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.09 sec)
What good is a the general log as a CSV table
Step 03) Make mysql.general_log
a MyISAM table and index it
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;
Now it looks like this:
mysql> show create table general_log\G
*************************** 1. row ***************************
Table: general_log
Create Table: CREATE TABLE `general_log` (
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`thread_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`command_type` varchar(64) NOT NULL,
`argument` mediumtext NOT NULL,
KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)
mysql>
THe user and host values are appended together in the user_host fields.
How do you rotate out the general log?
Here is an example of how to blank out mysql.general_log
:
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
DROP TABLE mysql.general_log;
ALTER TABLE mysql.general_log_new RENAME mysql.general_log;
SET GLOBAL general_log = @old_log_state;
Here is an example of how to keep the last 3 days of entries:
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
CREATE TABLE mysql.general_log_new LIKE mysql.general_log;
INSERT INTO mysql.general_log_new
SELECT * FROM mysql.general_log WHERE event_time > NOW() - INTERVAL 3 DAY;
DROP TABLE mysql.general_log;
ALTER TABLE mysql.general_log_new RENAME mysql.general_log;
SET GLOBAL general_log = @old_log_state;
BAD NEWS
Anything you have collected in the text file version of the general log will not come for the ride. You can collect new entries going forward.
You should think about partitioning the table for a big reason.
All indexes you have on a giant table, even just one index, can generated a lot of CPU load and disk I/O just to perform index maintenance when executing INSERTs, UPDATEs, and DELETEs.
I wrote an earlier post back on October 7, 2011 on why Table Partitioning would be a big help. Here is one excerpt from my past post:
Partitioning of data should serve to group data that are logically and
cohesively in the same class. Performance of searching each partition
need not be the main consideration as long as the data is correctly
grouped. Once you have achieved the logical partitioning, then
concentrate on search time. If you are just separating data by id
only, it is possible that many rows of data may never be accessed for
reads or writes. Now, that should be a major consideration: Locate all
ids most frequently accessed and partition by that. All less
frequently accessed ids should reside in one big archive table that is
still accessible by index lookup for that 'once in a blue moon' query.
You can read my entire post later on this.
To cut right to the chase, you need to research and find out what data is rarely used in your 10GB table. That data should be placed in an archive table that is readily accessible should you need adhoc queries for a historical nature. Migrating that archival from the 10GB, followed by OPTIMIZE TABLE
on the 10GB table, can result in a Working Set that is faster to run SELECTs, INSERTs, UPDATEs, and DELETEs. Even DDL would go faster on a 2GB Working Set than a 10GB table.
UPDATE 2012-02-24 16:19 EDT
Two points to consider
- From your comment, it sounds like normalization is what you may need.
- You may need to migrate out everything over 90 days old into an archive table but still access archive and working set at the same time. If your data is all MyISAM, I recommend using the MERGE storage engine. First, you create the MERGE table map once that unites a working set MyISAM table and an archive MyISAM table. You would keep data less than 91 days in one MyISAM table and rollover any data over 90 days old into the archive. You would query the MERGE table map only.
Here are two posts I made on how to use it:
Here is an additional post I made on tables with a lot of columns
Too many columns in MySQL
Best Answer
I prefer the slowlog. But I also like to crank
long_query_time
down to 1.0 or less.Use
pt-query-digest
to analyze the log. This can be done without copying it to another machine.(Check the arguments to that tool to see if it can limit the output to a given time period.)