Is my mysql.general_log table getting too big?
I just recently upgraded to MySQL 5.1.6 in order to take advantage of the ability to save the general log to a table -> i.e. mysql.general_log. Once i did this I was immediately surprised how many queries are actually hitting our system. I have about 40,000 rows in this general log table from the first hour. I haven't found it written on the MySQL docs about if there is a general log table size limit.
Is there a problem to letting this general log grow at this rate?
If there is a size problem, how to deal with it?
Are there some accepted practices how to deal with a size problem if there is one?
Should I make an event to purge the table and save the data to a file every so often?
Thanks a lot for the help!
mysql logging database-performance
asked Feb 18 '12 at 16:41
jeffery_the_windjeffery_the_wind
5,9131875124
add a comment |
I just recently upgraded to MySQL 5.1.6 in order to take advantage of the ability to save the general log to a table -> i.e. mysql.general_log. Once i did this I was immediately surprised how many queries are actually hitting our system. I have about 40,000 rows in this general log table from the first hour. I haven't found it written on the MySQL docs about if there is a general log table size limit.
Is there a problem to letting this general log grow at this rate?
If there is a size problem, how to deal with it?
Are there some accepted practices how to deal with a size problem if there is one?
Should I make an event to purge the table and save the data to a file every so often?
Thanks a lot for the help!
mysql logging database-performance
asked Feb 18 '12 at 16:41
jeffery_the_windjeffery_the_wind
5,9131875124
One thing you should be aware of, is that running general query log in production environment is not recommended. The performance impact can be pretty high, especially in high concurrency situations.
– Mchl
Feb 18 '12 at 16:55
add a comment |
I just recently upgraded to MySQL 5.1.6 in order to take advantage of the ability to save the general log to a table -> i.e. mysql.general_log. Once i did this I was immediately surprised how many queries are actually hitting our system. I have about 40,000 rows in this general log table from the first hour. I haven't found it written on the MySQL docs about if there is a general log table size limit.
Is there a problem to letting this general log grow at this rate?
If there is a size problem, how to deal with it?
Are there some accepted practices how to deal with a size problem if there is one?
Should I make an event to purge the table and save the data to a file every so often?
Thanks a lot for the help!
mysql logging database-performance
asked Feb 18 '12 at 16:41
jeffery_the_windjeffery_the_wind
5,9131875124
I just recently upgraded to MySQL 5.1.6 in order to take advantage of the ability to save the general log to a table -> i.e. mysql.general_log. Once i did this I was immediately surprised how many queries are actually hitting our system. I have about 40,000 rows in this general log table from the first hour. I haven't found it written on the MySQL docs about if there is a general log table size limit.
Is there a problem to letting this general log grow at this rate?
If there is a size problem, how to deal with it?
Are there some accepted practices how to deal with a size problem if there is one?
Should I make an event to purge the table and save the data to a file every so often?
Thanks a lot for the help!
mysql logging database-performance
mysql logging database-performance
asked Feb 18 '12 at 16:41
jeffery_the_windjeffery_the_wind
5,9131875124
asked Feb 18 '12 at 16:41
jeffery_the_windjeffery_the_wind
5,9131875124
asked Feb 18 '12 at 16:41
jeffery_the_windjeffery_the_wind
5,9131875124
asked Feb 18 '12 at 16:41
jeffery_the_windjeffery_the_wind
5,9131875124
asked Feb 18 '12 at 16:41
jeffery_the_windjeffery_the_wind
5,9131875124
5,9131875124
One thing you should be aware of, is that running general query log in production environment is not recommended. The performance impact can be pretty high, especially in high concurrency situations.
– Mchl
Feb 18 '12 at 16:55
add a comment |
One thing you should be aware of, is that running general query log in production environment is not recommended. The performance impact can be pretty high, especially in high concurrency situations.
– Mchl
Feb 18 '12 at 16:55
One thing you should be aware of, is that running general query log in production environment is not recommended. The performance impact can be pretty high, especially in high concurrency situations.
– Mchl
Feb 18 '12 at 16:55
One thing you should be aware of, is that running general query log in production environment is not recommended. The performance impact can be pretty high, especially in high concurrency situations.
– Mchl
Feb 18 '12 at 16:55
add a comment |
4 Answers
4
active
oldest
votes
The general_log table by default uses the CSV engine, which is literally just a full-blown CSV file on your drive, but can be accessed via SQL. This means its size limit is the size limit of files on your file system.
answered Feb 18 '12 at 16:51
Marc BMarc B
314k31320421
add a comment |
I do something like this for my log file. I'm only interested in keeping the last 24 hours, but you could tweak the event to create archive tables, etc. It won't log for the few seconds it takes the event to run, but I don't mind.
CREATE EVENT `prune_general_log` ON SCHEDULE
EVERY 1 DAY STARTS '2013-10-18'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'This will trim the general_log table to contain only the past 24 hours of logs.'
DO BEGIN
SET GLOBAL general_log = 'OFF';
RENAME TABLE mysql.general_log TO mysql.general_log2;
DELETE FROM mysql.general_log2 WHERE event_time <= NOW()-INTERVAL 24 HOUR;
OPTIMIZE TABLE general_log2;
RENAME TABLE mysql.general_log2 TO mysql.general_log;
SET GLOBAL general_log = 'ON';
END
answered Oct 18 '13 at 21:16
GraemeDGraemeD
8112
Love this answer. Thank you
– Routhinator
Jun 23 '16 at 20:04
add a comment |
You should use some utility like mysql-log-rotate http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html for rotating log file.
answered Feb 18 '12 at 16:49
Vivek GoelVivek Goel
9,7251984160
Unfortunately I am running on a Windows server, it says in the documentation this is included on Linux (Red Hat) installations.
– jeffery_the_wind
Feb 20 '12 at 12:55
add a comment |
Not sure if this is best practice but this was my solution:
DATE=$(date +"%Y%m%d%H%M")
mv general_log.CSV general_log.${DATE}.csv # move the log table file
sudo -u mysql -g mysql touch general_log.CSV # create a new log table file with correct owner and group
mysql -u root -e "FLUSH TABLE mysql.general_log" # flush the log table
answered Nov 23 '18 at 19:33
BlamBlam
169311
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f9343001%2fis-my-mysql-general-log-table-getting-too-big%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
The general_log table by default uses the CSV engine, which is literally just a full-blown CSV file on your drive, but can be accessed via SQL. This means its size limit is the size limit of files on your file system.
answered Feb 18 '12 at 16:51
Marc BMarc B
314k31320421
add a comment |
The general_log table by default uses the CSV engine, which is literally just a full-blown CSV file on your drive, but can be accessed via SQL. This means its size limit is the size limit of files on your file system.
answered Feb 18 '12 at 16:51
Marc BMarc B
314k31320421
add a comment |
The general_log table by default uses the CSV engine, which is literally just a full-blown CSV file on your drive, but can be accessed via SQL. This means its size limit is the size limit of files on your file system.
answered Feb 18 '12 at 16:51
Marc BMarc B
314k31320421
The general_log table by default uses the CSV engine, which is literally just a full-blown CSV file on your drive, but can be accessed via SQL. This means its size limit is the size limit of files on your file system.
answered Feb 18 '12 at 16:51
Marc BMarc B
314k31320421
answered Feb 18 '12 at 16:51
Marc BMarc B
314k31320421
answered Feb 18 '12 at 16:51
Marc BMarc B
314k31320421
answered Feb 18 '12 at 16:51
Marc BMarc B
314k31320421
314k31320421
add a comment |
add a comment |
I do something like this for my log file. I'm only interested in keeping the last 24 hours, but you could tweak the event to create archive tables, etc. It won't log for the few seconds it takes the event to run, but I don't mind.
CREATE EVENT `prune_general_log` ON SCHEDULE
EVERY 1 DAY STARTS '2013-10-18'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'This will trim the general_log table to contain only the past 24 hours of logs.'
DO BEGIN
SET GLOBAL general_log = 'OFF';
RENAME TABLE mysql.general_log TO mysql.general_log2;
DELETE FROM mysql.general_log2 WHERE event_time <= NOW()-INTERVAL 24 HOUR;
OPTIMIZE TABLE general_log2;
RENAME TABLE mysql.general_log2 TO mysql.general_log;
SET GLOBAL general_log = 'ON';
END
answered Oct 18 '13 at 21:16
GraemeDGraemeD
8112
Love this answer. Thank you
– Routhinator
Jun 23 '16 at 20:04
add a comment |
I do something like this for my log file. I'm only interested in keeping the last 24 hours, but you could tweak the event to create archive tables, etc. It won't log for the few seconds it takes the event to run, but I don't mind.
CREATE EVENT `prune_general_log` ON SCHEDULE
EVERY 1 DAY STARTS '2013-10-18'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'This will trim the general_log table to contain only the past 24 hours of logs.'
DO BEGIN
SET GLOBAL general_log = 'OFF';
RENAME TABLE mysql.general_log TO mysql.general_log2;
DELETE FROM mysql.general_log2 WHERE event_time <= NOW()-INTERVAL 24 HOUR;
OPTIMIZE TABLE general_log2;
RENAME TABLE mysql.general_log2 TO mysql.general_log;
SET GLOBAL general_log = 'ON';
END
answered Oct 18 '13 at 21:16
GraemeDGraemeD
8112
Love this answer. Thank you
– Routhinator
Jun 23 '16 at 20:04
add a comment |
I do something like this for my log file. I'm only interested in keeping the last 24 hours, but you could tweak the event to create archive tables, etc. It won't log for the few seconds it takes the event to run, but I don't mind.
CREATE EVENT `prune_general_log` ON SCHEDULE
EVERY 1 DAY STARTS '2013-10-18'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'This will trim the general_log table to contain only the past 24 hours of logs.'
DO BEGIN
SET GLOBAL general_log = 'OFF';
RENAME TABLE mysql.general_log TO mysql.general_log2;
DELETE FROM mysql.general_log2 WHERE event_time <= NOW()-INTERVAL 24 HOUR;
OPTIMIZE TABLE general_log2;
RENAME TABLE mysql.general_log2 TO mysql.general_log;
SET GLOBAL general_log = 'ON';
END
answered Oct 18 '13 at 21:16
GraemeDGraemeD
8112
I do something like this for my log file. I'm only interested in keeping the last 24 hours, but you could tweak the event to create archive tables, etc. It won't log for the few seconds it takes the event to run, but I don't mind.
CREATE EVENT `prune_general_log` ON SCHEDULE
EVERY 1 DAY STARTS '2013-10-18'
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'This will trim the general_log table to contain only the past 24 hours of logs.'
DO BEGIN
SET GLOBAL general_log = 'OFF';
RENAME TABLE mysql.general_log TO mysql.general_log2;
DELETE FROM mysql.general_log2 WHERE event_time <= NOW()-INTERVAL 24 HOUR;
OPTIMIZE TABLE general_log2;
RENAME TABLE mysql.general_log2 TO mysql.general_log;
SET GLOBAL general_log = 'ON';
END
answered Oct 18 '13 at 21:16
GraemeDGraemeD
8112
answered Oct 18 '13 at 21:16
GraemeDGraemeD
8112
answered Oct 18 '13 at 21:16
GraemeDGraemeD
8112
answered Oct 18 '13 at 21:16
GraemeDGraemeD
8112
8112
Love this answer. Thank you
– Routhinator
Jun 23 '16 at 20:04
add a comment |
Love this answer. Thank you
– Routhinator
Jun 23 '16 at 20:04
Love this answer. Thank you
– Routhinator
Jun 23 '16 at 20:04
Love this answer. Thank you
– Routhinator
Jun 23 '16 at 20:04
add a comment |
You should use some utility like mysql-log-rotate http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html for rotating log file.
answered Feb 18 '12 at 16:49
Vivek GoelVivek Goel
9,7251984160
Unfortunately I am running on a Windows server, it says in the documentation this is included on Linux (Red Hat) installations.
– jeffery_the_wind
Feb 20 '12 at 12:55
add a comment |
You should use some utility like mysql-log-rotate http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html for rotating log file.
answered Feb 18 '12 at 16:49
Vivek GoelVivek Goel
9,7251984160
Unfortunately I am running on a Windows server, it says in the documentation this is included on Linux (Red Hat) installations.
– jeffery_the_wind
Feb 20 '12 at 12:55
add a comment |
You should use some utility like mysql-log-rotate http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html for rotating log file.
answered Feb 18 '12 at 16:49
Vivek GoelVivek Goel
9,7251984160
You should use some utility like mysql-log-rotate http://dev.mysql.com/doc/refman/5.0/en/log-file-maintenance.html for rotating log file.
answered Feb 18 '12 at 16:49
Vivek GoelVivek Goel
9,7251984160
answered Feb 18 '12 at 16:49
Vivek GoelVivek Goel
9,7251984160
answered Feb 18 '12 at 16:49
Vivek GoelVivek Goel
9,7251984160
answered Feb 18 '12 at 16:49
Vivek GoelVivek Goel
9,7251984160
9,7251984160
Unfortunately I am running on a Windows server, it says in the documentation this is included on Linux (Red Hat) installations.
– jeffery_the_wind
Feb 20 '12 at 12:55
add a comment |
Unfortunately I am running on a Windows server, it says in the documentation this is included on Linux (Red Hat) installations.
– jeffery_the_wind
Feb 20 '12 at 12:55
Unfortunately I am running on a Windows server, it says in the documentation this is included on Linux (Red Hat) installations.
– jeffery_the_wind
Feb 20 '12 at 12:55
Unfortunately I am running on a Windows server, it says in the documentation this is included on Linux (Red Hat) installations.
– jeffery_the_wind
Feb 20 '12 at 12:55
add a comment |
Not sure if this is best practice but this was my solution:
DATE=$(date +"%Y%m%d%H%M")
mv general_log.CSV general_log.${DATE}.csv # move the log table file
sudo -u mysql -g mysql touch general_log.CSV # create a new log table file with correct owner and group
mysql -u root -e "FLUSH TABLE mysql.general_log" # flush the log table
answered Nov 23 '18 at 19:33
BlamBlam
169311
add a comment |
Not sure if this is best practice but this was my solution:
DATE=$(date +"%Y%m%d%H%M")
mv general_log.CSV general_log.${DATE}.csv # move the log table file
sudo -u mysql -g mysql touch general_log.CSV # create a new log table file with correct owner and group
mysql -u root -e "FLUSH TABLE mysql.general_log" # flush the log table
answered Nov 23 '18 at 19:33
BlamBlam
169311
add a comment |
Not sure if this is best practice but this was my solution:
DATE=$(date +"%Y%m%d%H%M")
mv general_log.CSV general_log.${DATE}.csv # move the log table file
sudo -u mysql -g mysql touch general_log.CSV # create a new log table file with correct owner and group
mysql -u root -e "FLUSH TABLE mysql.general_log" # flush the log table
answered Nov 23 '18 at 19:33
BlamBlam
169311
Not sure if this is best practice but this was my solution:
DATE=$(date +"%Y%m%d%H%M")
mv general_log.CSV general_log.${DATE}.csv # move the log table file
sudo -u mysql -g mysql touch general_log.CSV # create a new log table file with correct owner and group
mysql -u root -e "FLUSH TABLE mysql.general_log" # flush the log table
answered Nov 23 '18 at 19:33
BlamBlam
169311
answered Nov 23 '18 at 19:33
BlamBlam
169311
answered Nov 23 '18 at 19:33
BlamBlam
169311
answered Nov 23 '18 at 19:33
BlamBlam
169311
169311
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f9343001%2fis-my-mysql-general-log-table-getting-too-big%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
One thing you should be aware of, is that running general query log in production environment is not recommended. The performance impact can be pretty high, especially in high concurrency situations.
– Mchl
Feb 18 '12 at 16:55