Guys, I really2 need help.. Are there anyone out there with suggestions or anything at all that might help? Please help.
Thanx. Sent via BlackBerry Storm from Maxis -----Original Message----- From: [email protected] Date: Mon, 07 Dec 2009 12:00:01 To: <[email protected]> Subject: DBmail Digest, Vol 69, Issue 3 Send DBmail mailing list submissions to [email protected] To subscribe or unsubscribe via the World Wide Web, visit http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail or, via email, send a message with subject or body 'help' to [email protected] You can reach the person managing the list at [email protected] When replying, please edit your Subject line so it is more specific than "Re: Contents of DBmail digest..." Today's Topics: 1. Optimizing Dbmail Database (Daniel Mejia) 2. Re: Optimizing Dbmail Database (Daniel Mejia) 3. Re: Optimizing Dbmail Database (Josh Marshall) 4. Re: Optimizing Dbmail Database (Daniel Mejia) ---------------------------------------------------------------------- Message: 1 Date: Sun, 6 Dec 2009 20:13:46 -0800 (PST) From: Daniel Mejia <[email protected]> Subject: [Dbmail] Optimizing Dbmail Database To: [email protected] Message-ID: <[email protected]> Content-Type: text/plain; charset=us-ascii I was thinking to run the OPTIMIZE TABLE command in the dbmail database, bcoz right now the database size of my organization dbmail database is currently around 750 GB. Another reason was that my dbmail-util -ay command (which runs on 2am and 6am everyday) keeps giving me this error: [code] Dec 07 03:00:37 mailadmin.mpob.g lt-dbmail-util[29046]: Error:[sql] dbmysql.c,db_query(+290): [Lost connection to MySQL server during query] [SELECT MIN(messageblk_idnr),MAX(is_header) FROM dbmail_messageblks GROUP BY physmessage_id HAVING MAX(is_header)=0] Dec 07 03:00:37 mailadmin.mpob.g lt-dbmail-util[29046]: Error:[db] db.c,db_icheck_isheader(+1788): could not access messageblks table Failed. An error occured. Please check log. Maintenance done. Errors were found but not fixed due to failures. Please check the logs for further details, turning up the trace level as needed. [/code] I am running dbmail 2.2.10, mysql 5.0.45. My question is: is this a good idea? (to run the OPTIMIZE TABLE command). If so, why. Thanx a lot guys. -- View this message in context: http://old.nabble.com/Optimizing-Dbmail-Database-tp26672088p26672088.html Sent from the dbmail users mailing list archive at Nabble.com. ------------------------------ Message: 2 Date: Sun, 6 Dec 2009 20:21:13 -0800 (PST) From: Daniel Mejia <[email protected]> Subject: Re: [Dbmail] Optimizing Dbmail Database To: [email protected] Message-ID: <[email protected]> Content-Type: text/plain; charset=us-ascii This is my mysql config file (my.cnf) [code] [mysqld] datadir=/sql/mysql innodb_data_home_dir = /sql/ibdata innodb_data_file_path = ibdata1:2000M:autoextend socket=/var/lib/mysql/mysql.sock user=mysql innodb_buffer_pool_size=5G innodb_additional_mem_pool_size=20M innodb_log_file_size=1G innodb_log_buffer_size=8M innodb_log_group_home_dir = /iblogs innodb_file_per_table innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-external-locking query_cache_type = 0 max_connections = 110 thread_cache_size = 110 sort_buffer_size = 1048576 read_buffer_size = 1048576 max_connections=1000 [/code] -- View this message in context: http://old.nabble.com/Optimizing-Dbmail-Database-tp26672088p26672316.html Sent from the dbmail users mailing list archive at Nabble.com. ------------------------------ Message: 3 Date: Mon, 07 Dec 2009 14:59:14 +1000 From: Josh Marshall <[email protected]> Subject: Re: [Dbmail] Optimizing Dbmail Database To: DBMail mailinglist <[email protected]> Message-ID: <[email protected]> Content-Type: text/plain On Sun, 2009-12-06 at 20:13 -0800, Daniel Mejia wrote: > I was thinking to run the OPTIMIZE TABLE command in the dbmail database, bcoz > right now the database size of my organization dbmail database is currently > around 750 GB. Is there much free space in that table? If not, optimize table wont free any space and probably wont improve access speed etc. Remember that you need as much free space to optimise as the size of the table. I have a mysql server that has 20Gb free in the innodb but only 5Gb free on the disk. Since the messageblk table is 80Gb I can't reclaim that space, but it doesn't make a difference as the innodb engine reuses the space fairly well. Note also that to optimise a table means to read it and write it out with the table locked. A 750Gb database will take a loooong time and you wont be able to write to it for that time. Josh. ------------------------------ Message: 4 Date: Sun, 6 Dec 2009 21:23:38 -0800 (PST) From: Daniel Mejia <[email protected]> Subject: Re: [Dbmail] Optimizing Dbmail Database To: [email protected] Message-ID: <[email protected]> Content-Type: text/plain; charset=us-ascii > Is there much free space in that table? If not, optimize table wont free > any space and probably wont improve access speed etc. with the SHOW TABLE STATUS command, i can see that the free space is not adequate. i have around 2 TB of free space on the hard disk, but im not so sure about the table free space. > Note also that to optimise a table means to read it and write it out > with the table locked. A 750Gb database will take a loooong time and you > wont be able to write to it for that time. we are willing to shut down the email server for that maintenance if it gives the intended result, which is to free up as much space as possible and at least improve a tiny bit of access speed if possible. if OPTIMIZE TABLE is not the ideal solution, what would u recommend me to do? the error that pops up in the dbmail-util -ay command really bugs us. -- View this message in context: http://old.nabble.com/Optimizing-Dbmail-Database-tp26672088p26672745.html Sent from the dbmail users mailing list archive at Nabble.com. ------------------------------ _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail End of DBmail Digest, Vol 69, Issue 3 ************************************* _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
