Re: Space is filling up
Harald Fuchs wrote: In article [EMAIL PROTECTED], gerald_clark [EMAIL PROTECTED] writes: Asif Iqbal wrote: Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. My databases (mysql,rt3,test) are taking only about 2k. No. The ibdata* files ARE your database. You need more disk space, and you need it now. Not necessarily. ibdata files can grow if they are declared with autoextend, but they don't shrink, even if you're deleting all rows. And without disk space, he can't dump and reload unless he dumps to tape. Better verify that tape dump before deleting those innodb files! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
Asif Iqbal wrote: Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. My databases (mysql,rt3,test) are taking only about 2k. No. The ibdata* files ARE your database. You need more disk space, and you need it now. I am worry about the ib*log and ibdata* files. Mostly the ibdata files which are taking 5 gig. I am not sure if I can clean those up or rotate or something else to reclaim the disk space. (root)@webrt:/usr/local/mysql/data# /usr/local/bin/ls -lh total 5.2G -rw-rw1 mysqlmysql 25K Jul 4 2003 ib_arch_log_00 -rw-rw1 mysqlmysql2.5K Aug 27 2003 ib_arch_log_02 -rw-rw1 mysqlmysql2.5K Aug 28 2003 ib_arch_log_04 -rw-rw1 mysqlmysql100M Jul 8 20:38 ib_logfile0 -rw-rw1 mysqlmysql100M Jun 29 20:23 ib_logfile1 -rw-rw1 mysqlmysql1.9G Jul 8 20:37 ibdata1 -rw-rw1 mysqlmysql3.1G Jul 8 20:33 ibdata2 -rw-r--r--1 mysqlmysql1.6K Sep 15 2003 my.cnf drwxr-x---2 mysqlmysql 512 Jul 4 2003 mysql drwx--2 mysqlmysql 512 Jul 30 2003 newdb drwx--2 mysqlmysql1.5K Jun 30 23:10 rt3 drwxr-x---2 mysqlmysql 512 Jul 28 2003 test -rw-rw1 mysqlother 67K Nov 21 2003 webrt.err -rw-rw1 mysqlmysql 3 Nov 21 2003 webrt.pid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
In article [EMAIL PROTECTED], gerald_clark [EMAIL PROTECTED] writes: Asif Iqbal wrote: Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. My databases (mysql,rt3,test) are taking only about 2k. No. The ibdata* files ARE your database. You need more disk space, and you need it now. Not necessarily. ibdata files can grow if they are declared with autoextend, but they don't shrink, even if you're deleting all rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Space is filling up
Probably not. At this point, I'd say his options are limited to either deleting data to free up some space or getting a bigger hard drive. Unless his situation is like mine and he only needs to keep the data for a certain period of time, the bigger hard drive is definitely the way to go. And since hard drives are so cheap now, I'd be tempted to go that route even if he can age out data. I'm seeing ads for 200G drives for $90.00. Jack -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 5:06 PM To: 'Asif Iqbal' Cc: [EMAIL PROTECTED] Subject: Re: Space is filling up Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. But does he even have enough disk space to optimize tables? Jack -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 2:07 PM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: Space is filling up gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. My databases (mysql,rt3,test) are taking only about 2k. I am worry about the ib*log and ibdata* files. Mostly the ibdata files which are taking 5 gig. I am not sure if I can clean those up or rotate or something else to reclaim the disk space. (root)@webrt:/usr/local/mysql/data# /usr/local/bin/ls -lh total 5.2G -rw-rw1 mysqlmysql 25K Jul 4 2003 ib_arch_log_00 -rw-rw1 mysqlmysql2.5K Aug 27 2003 ib_arch_log_02 -rw-rw1 mysqlmysql2.5K Aug 28 2003 ib_arch_log_04 -rw-rw1 mysqlmysql100M Jul 8 20:38 ib_logfile0 -rw-rw1 mysqlmysql100M Jun 29 20:23 ib_logfile1 -rw-rw1 mysqlmysql1.9G Jul 8 20:37 ibdata1 -rw-rw1 mysqlmysql3.1G Jul 8 20:33 ibdata2 -rw-r--r--1 mysqlmysql1.6K Sep 15 2003 my.cnf drwxr-x---2 mysqlmysql 512 Jul 4 2003 mysql drwx--2 mysqlmysql 512 Jul 30 2003 newdb drwx--2 mysqlmysql1.5K Jun 30 23:10 rt3 drwxr-x---2 mysqlmysql 512 Jul 28 2003 test -rw-rw1 mysqlother 67K Nov 21 2003 webrt.err -rw-rw1 mysqlmysql 3 Nov 21 2003 webrt.pid Thanks for all the help Jack -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 2:07 PM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: Space is filling up gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Space is filling up
Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
What about getting a bigger drive? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Space is filling up
If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. Jack -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 2:07 PM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: Space is filling up gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
--- Asif Iqbal [EMAIL PROTECTED] wrote: gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? If any of your data can be considered history data that is never modified, you could create compressed myISAM tables for that data, removing it from the inno tablespaces. This could give you a little breathing room until you can get a bigger drive. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Space is filling up
Jack Coxen wrote: If you database contains time-based data you could age out old records. I only need to keep data for 6 months so I run a nightly script to delete any records more than 6 months old. And before anyone asks...yes, I also run another script to ANALYZE/OPTIMIZE my tables. But does he even have enough disk space to optimize tables? Jack -Original Message- From: Asif Iqbal [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 2:07 PM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: Space is filling up gerald_clark wrote: What about getting a bigger drive? I guess that would be my only option eh? Asif Iqbal wrote: Hi All My mysql data dir is filled up to 95% and only 345M left. Here is the dir looks like 1.9Gibdata1 3.1Gibdata2 3.0Kib_arch_log_02 3.0Kib_arch_log_04 25K ib_arch_log_00 65M rt3 67K webrt.err 70K mysql 101Mib_logfile0 101Mib_logfile1 Here is my my.cnf file looks like [..related portion..] innodb_data_home_dir = /usr/local/mysql/data/ innodb_data_file_path = ibdata1:1882M;ibdata2:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data/ innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 100M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Any help to clean the dir would be greatly appreciated Thanks -- Asif Iqbal PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu There's no place like 127.0.0.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]