Re: [Bacula-users] MySQL (Innodb) - table is full ???
Hi All, My bacula database (with MyISAM tables) is currently 5.3 GB in size after only 10 months of use. Last weekend my File table filled up, which was easily fixed by doing the following as recommended at http://www.bacula.org/dev-manual/Catalog_Maintenance.html#SECTION00244 : ALTER TABLE File MAX_ROWS=281474976710656; But, the above command made me wonder if I will fill the File table again in the future. It also made me consider migrating my tables from MyISAM to InnoDB. Do you think the migration is worth the hassle? I should mention that I do AutoPrune my normal backups, but I must keep my archival backups indefinitely. These archival backups total over 2 TB per month. Btw, with the rate at which my users generate data it is conceivable that the normal and archival backups will continue to grow in size. Fyi, my autochanger is stackable, which means that I can just buy another unit and have 38 new slots (and possible 2 more drives) instantly available within the same storage resource. I mention this to denote that I am only worried about the limitations of my *database* storage not tape storage. Any thoughts? Regards, Mike Drew Bentley wrote: On 8/17/07, Alan Brown [EMAIL PROTECTED] wrote: On Fri, 17 Aug 2007, Drew Bentley wrote: Yeah, autoextend for InnoDB seems to have bitten you. I usually never do this and have monitors to tell me if it's reaching a certain threshold, as you're probably not even using all of the InnoDB space allocated, as it's not particularly nice in giving back space that was once used, at least in my experience. Is there any way to see how much it's actually using? Not that I'm aware of, only show table status and or innodb status will print out the usage. If you perform a full dump and reinsert, you're always going to gain usage in space. -drew - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] MySQL (Innodb) - table is full ???
On Thu, 8 Nov 2007, Mike Seda wrote: Hi All, My bacula database (with MyISAM tables) is currently 5.3 GB in size after only 10 months of use. Last weekend my File table filled up, which was easily fixed by doing the following as recommended at http://www.bacula.org/dev-manual/Catalog_Maintenance.html#SECTION00244 : ALTER TABLE File MAX_ROWS=281474976710656; Right. You now have room in your table for data on 281.5 trillion files. should mention that I do AutoPrune my normal backups, but I must keep my archival backups indefinitely. These archival backups total over 2 TB per month. How many files are in each of your archival backups? At 100 million files per backup, you've got room in your DB for 2.8 million backup sessions. Depending on which version of MySQL you've installed and the filesystem the database is stored on, it's possible that you'll eventually run into an issue with physical size of the database files, but you're not going to run out of rows in the table any time soon. -- D - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] MySQL (Innodb) - table is full ???
On Fri, 17 Aug 2007, Alan Brown wrote: innodb_data_file_path = ibdata1:26000M;ibdata2:2000M:autoextend (As I understand it, this should grow the tablespace as needed) Doh! FilesystemSize Used Avail Use% Mounted on /dev/mapper/VolGroupMSSLAY01-LogVolSQL 57G 54G 0 100% /var/lib/mysql That'd definitly explain things... - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] MySQL (Innodb) - table is full ???
On Thu, 16 Aug 2007, Drew Bentley wrote: You'll either need to add more InnoDB by adding more to your /etc/my.cnf file or convert the tables to use MyISAM. Usually depends on what MySQL will use as default or if someone else created the InnoDB space but you can easily add more by editing the innodb_data_file_path in the my.cnf Here's what I have: innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:26000M;ibdata2:2000M:autoextend (As I understand it, this should grow the tablespace as needed) innodb_data_home_dir = /var/lib/mysql innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 512M innodb_log_files_in_group = 2 innodb_log_group_home_dir = /var/lib/mysql/log innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 # ls -l /var/lib/mysql total 55755736 drwx-- 2 mysql mysql4096 Jan 3 2007 bacula -rw-rw 1 mysql mysql 27262976000 Aug 16 16:35 ibdata1 -rw-rw 1 mysql mysql 29775060992 Aug 16 16:35 ibdata2 drwxr-xr-x 2 root root 4096 Dec 15 2006 log drwx-- 2 mysql mysql 16384 Jun 1 2006 lost+found drwx-- 2 mysql mysql4096 Jul 9 2006 mysql lrwxrwxrwx 1 root root 26 Dec 19 2006 mysql.sock - /var/run/mysqld/mysql.sock drwxr-xr-x 3 mysql mysql4096 Jun 29 2006 new drwx-- 2 mysql mysql4096 Jun 29 2006 test A typical my.cnf file for the InnoDB stuff looks like this: # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:15000M; innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=4 set-variable = innodb_log_file_size=128M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=8M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 If you wanted to add space, simply edit this line: innodb_data_file_path = ibdata1:15000M; And make it look like this: innodb_data_file_path = ibdata1:15000M; ibdata2:15000M; That would add 15000 more MB to my existing space. Add what you need. -Drew - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] MySQL (Innodb) - table is full ???
On 8/17/07, Alan Brown [EMAIL PROTECTED] wrote: On Fri, 17 Aug 2007, Alan Brown wrote: innodb_data_file_path = ibdata1:26000M;ibdata2:2000M:autoextend (As I understand it, this should grow the tablespace as needed) Doh! FilesystemSize Used Avail Use% Mounted on /dev/mapper/VolGroupMSSLAY01-LogVolSQL 57G 54G 0 100% /var/lib/mysql That'd definitly explain things... Yeah, autoextend for InnoDB seems to have bitten you. I usually never do this and have monitors to tell me if it's reaching a certain threshold, as you're probably not even using all of the InnoDB space allocated, as it's not particularly nice in giving back space that was once used, at least in my experience. -drew - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] MySQL (Innodb) - table is full ???
On Fri, 17 Aug 2007, Drew Bentley wrote: Yeah, autoextend for InnoDB seems to have bitten you. I usually never do this and have monitors to tell me if it's reaching a certain threshold, as you're probably not even using all of the InnoDB space allocated, as it's not particularly nice in giving back space that was once used, at least in my experience. Is there any way to see how much it's actually using? - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] MySQL (Innodb) - table is full ???
On 8/17/07, Alan Brown [EMAIL PROTECTED] wrote: On Fri, 17 Aug 2007, Drew Bentley wrote: Yeah, autoextend for InnoDB seems to have bitten you. I usually never do this and have monitors to tell me if it's reaching a certain threshold, as you're probably not even using all of the InnoDB space allocated, as it's not particularly nice in giving back space that was once used, at least in my experience. Is there any way to see how much it's actually using? Not that I'm aware of, only show table status and or innodb status will print out the usage. If you perform a full dump and reinsert, you're always going to gain usage in space. -drew - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] MySQL (Innodb) - table is full ???
(Have been running mysql and dbchecks last couple of days...) On Tue, 14 Aug 2007, Drew Bentley wrote: I've just encountered this message on my system. I can understand it on MyISAM, but I thought InnoDB had no upper limits. Does anyone have experience and recovery pointers? Perhaps InnoDB was or is specified in your my.cnf file. It is, although MyISAM is default. All the bacula tables are Inno. What does show innodb status give you? Find out what tables or databases are using it to dig a little deeper. I've attached the output, but don't really know enough about Inno internals to make much comment Status \n=\n070816 14:35:00 INNODB MONITOR OUTPUT\n=\nPer second averages calculated from the last 35 seconds\n--\nSEMAPHORES\n--\nOS WAIT ARRAY INFO: reservation count 201367, signal count 201363\nMutex spin waits 4963249, rounds 5871077, OS waits 40562\nRW-shared spins 173736, OS waits 83550; RW-excl spins 131546, OS waits 69710\n\nTRANSACTIONS\n\nTrx id counter 0 502848895\nPurge done for trx's n:o 0 502848895 undo n:o 0 0\nHistory list length 2\nTotal number of lock structs in row lock hash table 0\nLIST OF TRANSACTIONS FOR EACH SESSION:\n---TRANSACTION 0 0, not started, process no 8092, OS thread id 1147570528\nMySQL thread id 6, query id 593072 localhost root\nshow innodb status\n\nFILE I/O\n\nI/O thread 0 state: waiting for i/o request (insert buffer thread)\nI/O thread 1 state: waiting for i/o request (log thread)\nI/O thread 2 state: waiting for i/o request (read thread)\nI/O thread 3 state: waiting for i/o request (write thread)\nPending normal aio reads: 0, aio writes: 0,\n ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0\nPending flushes (fsync) log: 0; buffer pool: 0\n1638489 OS file reads, 599527 OS file writes, 957 OS fsyncs\n0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s\n-\nINSERT BUFFER AND ADAPTIVE HASH INDEX\n-\nIbuf for space 0: size 1, free list len 15230, seg size 15232, is empty\nIbuf for space 0: size 1, free list len 15230, seg size 15232,\n0 inserts, 0 merged recs, 0 merges\nHash table size 4425293, used cells 3918006, node heap has 14127 buffer(s)\n0.00 hash searches/s, 0.00 non-hash searches/s\n---\nLOG\n---\nLog sequence number 39 3867858362\nLog flushed up to 39 3867858362\nLast checkpoint at 39 3867858362\n0 pending log writes, 0 pending chkp writes\n593279 log i/o's done, 0.00 log i/o's/second\n--\nBUFFER POOL AND MEMORY\n--\nTotal memory allocated 2358002660; in additional pool allocated 11091712\nBuffer pool size 131072\nFree buffers 1\nDatabase pages 116944\nModified db pages 0\nPending reads 0\nPending writes: LRU 0, flush list 0, single page 0\nPages read 8093129, created 4896, written 20771\n0.00 reads/s, 0.00 creates/s, 0.00 writes/s\nNo buffer pool page gets since the last printout\n--\nROW OPERATIONS\n--\n0 queries inside InnoDB, 0 queries in queue\nMain thread process no. 8092, id 1147169120, state: waiting for server activity\nNumber of rows inserted 0, updated 0, deleted 592918, read 1333829373\n0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s\n\nEND OF INNODB MONITOR OUTPUT\n\n - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] MySQL (Innodb) - table is full ???
On 8/16/07, Alan Brown [EMAIL PROTECTED] wrote: (Have been running mysql and dbchecks last couple of days...) On Tue, 14 Aug 2007, Drew Bentley wrote: I've just encountered this message on my system. I can understand it on MyISAM, but I thought InnoDB had no upper limits. Does anyone have experience and recovery pointers? Perhaps InnoDB was or is specified in your my.cnf file. It is, although MyISAM is default. All the bacula tables are Inno. What does show innodb status give you? Find out what tables or databases are using it to dig a little deeper. I've attached the output, but don't really know enough about Inno internals to make much comment You'll either need to add more InnoDB by adding more to your /etc/my.cnf file or convert the tables to use MyISAM. Usually depends on what MySQL will use as default or if someone else created the InnoDB space but you can easily add more by editing the innodb_data_file_path in the my.cnf A typical my.cnf file for the InnoDB stuff looks like this: # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:15000M; innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=4 set-variable = innodb_log_file_size=128M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=8M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 If you wanted to add space, simply edit this line: innodb_data_file_path = ibdata1:15000M; And make it look like this: innodb_data_file_path = ibdata1:15000M; ibdata2:15000M; That would add 15000 more MB to my existing space. Add what you need. -Drew - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
