| *** grantc has joined #ingres | 01:53 | |
| *** grantc has quit IRC | 02:48 | |
| *** grantc has joined #ingres | 03:18 | |
| *** rossand has joined #ingres | 04:04 | |
| *** ChanServ sets mode: +o rossand | 04:04 | |
| *** atrofast has joined #ingres | 04:04 | |
| *** cthibert has joined #ingres | 04:11 | |
| *** mull has joined #ingres | 05:43 | |
| *** Meyer080 has joined #ingres | 06:13 | |
| Meyer080 | hi, does ingres have a checkpoint command like SQL Server, that flushes committed txn to the disk? | 06:16 |
|---|---|---|
| *** Dejan has joined #ingres | 06:50 | |
| grantc | hi Meyer080 - if you wish to commit a txn then the "commit" statement is used. If you wish to make sure it's on disk you can force a "consistency point" | 07:02 |
| grantc | to do that you issue the command "set trace point DM1305" | 07:02 |
| Meyer080 | grantc, it means that 'consistency points' occur automatically in regular intervals? | 07:05 |
| grantc | yes | 07:05 |
| grantc | i'm trying to remember the maths for how it's worked out | 07:06 |
| grantc | If i remember correctly it's either for a % of the log file or size of data | 07:07 |
| Meyer080 | ok grantc. How regular are they? one can set up their interval somehow? perhaps per database? | 07:07 |
| grantc | it's per installation | 07:10 |
| grantc | are you using linux/unix? | 07:11 |
| Meyer080 | grantc, linux | 07:13 |
| grantc | iisetres -v ii.`iipmhost`.rcp.log.cp_interval_mb X - set the consistency point to X MB | 07:17 |
| grantc | make sure ingres is down before | 07:17 |
| Meyer080 | grantc: great | 07:17 |
| grantc | or - iisetres -v ii.`iipmhost`.rcp.log.cp_interval X - set the CP to X% of the transaction log file | 07:18 |
| Dejan | i actually think the default one is fine | 07:18 |
| grantc | the former is a wrapper for the latter | 07:18 |
| grantc | Dejan: it depends | 07:18 |
| Dejan | it should not be extra big | 07:18 |
| Dejan | and also not extra small | 07:18 |
| grantc | 12% of 2G is a lot of data | 07:18 |
| grantc | imagine an 8GB log file etc.. :) | 07:19 |
| *** cthibert has left #ingres | 07:19 | |
| grantc | Meyer080: you're welcome | 07:20 |
| Meyer080 | I understand one adjusts the interval thinking about how long the recovery interval will be | 07:20 |
| grantc | or thinking about how much data is sat in your log file before the disks go boom | 07:21 |
| *** cthibert has joined #ingres | 07:21 | |
| Dejan | not just that, consistency point triggers some expensive processes | 07:21 |
| Dejan | you definitely should not have it to be a small value | 07:21 |
| Dejan | but grantc has right | 07:21 |
| grantc | Dejan, i'd disagree | 07:21 |
| Dejan | it should not be a large one too | 07:21 |
| Dejan | you do not want CPs every minute | 07:22 |
| grantc | setting it to 1MB would be foolish but it's very much a site specific parameter based on usage and hardware | 07:22 |
| grantc | What happens if you're writting 100MB/s of data ... you want all that data in your trx log file? | 07:23 |
| Dejan | default value is 40Mb | 07:24 |
| Dejan | i think that is pretty fine | 07:24 |
| grantc | it depends on the size of your trx log file as it's a percentage of that | 07:24 |
| Dejan | so it is not the actual size in Mb ? | 07:25 |
| grantc | if you setup a 2GB log file 12% = 240MB | 07:25 |
| Dejan | why the hack the name is "cp_interval_mb" ? | 07:25 |
| grantc | the cp_interval_mb is used with the size of transaction of log file to calc the % | 07:25 |
| grantc | it's a derived param | 07:25 |
| Dejan | ahammmmm | 07:26 |
| grantc | ii.$.rcp.log.cp_interval:((ii.$.rcp.log.cp_interval_mb * 1024) / ii.$.rcp.file.kbytes) * 100, | 07:26 |
| grantc | see grep cp_interval $II_SYSTEM/ingres/files/dbms.crs | 07:27 |
| Dejan | so percentage is actually a derived parameter | 07:27 |
| Dejan | not the cp_interval_mb ? | 07:28 |
| grantc | if you set _mb it derives the % value, if you set the % cp_interval_mb is not updated | 07:28 |
| grantc | i hope that reads ok | 07:28 |
| Dejan | yeah, i get it | 07:28 |
| grantc | "iisetres -v param value" shows the derived changes | 07:29 |
| grantc | http://ingres.pastebin.com/waJkjqU7 | 07:32 |
| Meyer080 | grantc, in case of a server failure, when ingres restarts, the rollforward/rollback of transactions occur automatically or one needs to execute a command or pass a flag to the dbms process? | 07:34 |
| grantc | Meyer080: from memory, the recovery server, dmfrcp, rolls back open transactions to the last CP | 07:36 |
| grantc | see http://docs.ingres.com/ingres/9.3/database-administrator-guide/2373-logging-system | 07:37 |
| Meyer080 | grantc: what about txn that were committed and not flushed to the disk? in other dbms's this is called rollforwarding | 07:38 |
| Meyer080 | grantc: thx | 07:38 |
| grantc | assuming the transactions are in the journal files (not sure what they are called in SQL Server) the you can recover the DB from the last backup/checkpoint (ckpdb command) using rollforwarddb +j ( +j = apply the journal files) | 07:40 |
| *** Alex| has quit IRC | 07:40 | |
| grantc | http://docs.ingres.com/ingres/9.3/database-administrator-guide/2380-backup-by-checkpoints | 07:40 |
| Dejan | grantc, did you try incremental rollforward ? | 07:41 |
| Dejan | i never tried it, i must do it one day when i have time | 07:42 |
| grantc | i looked at it a while ago - i've not got my head around how it works just yet | 07:42 |
| grantc | i was looking at it to maintain an offsite backup of a live system | 07:43 |
| grantc | the next time i'm in the UK i'm going to have a word with mike flower as he knows how it works | 07:43 |
| Dejan | yeah, he wrote that presentation... | 07:44 |
| Dejan | but i wish i have the original PP file | 07:44 |
| Dejan | because lots of slides are missing | 07:44 |
| Dejan | this is good stuff, because i can use this to make an online, standby database | 07:45 |
| Dejan | it is going to miss few transactions | 07:45 |
| Dejan | but the moment i get next JNL file, i just "apply" it | 07:45 |
| Dejan | and i get those new transactions | 07:46 |
| Dejan | etc | 07:46 |
| grantc | the next jnl file has to be complete | 07:47 |
| grantc | i.e. not open | 07:47 |
| Dejan | sure | 07:47 |
| Dejan | it is some nice form of simple replication | 07:48 |
| grantc | yeah - that's what i was looking at it for | 07:55 |
| Meyer080 | grantc: so, if you have journaled database, and in order to miss the fewest number of transactions, once the ingres server restarts after a failure one must run rollforwarddb +j. Is that correct? | 08:04 |
| grantc | if you have a corrupt database then that command will recover all transactions from the last backup which is held in a journal file | 08:06 |
| grantc | It's been a while since I've done DBA stuff, unless Dejan or pboro have any ideas I'd suggest you ask at http://community.ingres.com/forum | 08:08 |
| grantc | alternatively use the USENET group comp.databases.ingres | 08:08 |
| Meyer080 | understood. thx for your time grantc | 08:09 |
| Dejan | Meyer080, i think recovery is going to do it for you | 08:10 |
| Meyer080 | Dejan: how can one be sure? | 08:11 |
| Dejan | if you want to be sure, yes, do rollforwarddb +1 | 08:11 |
| Dejan | +j pardon | 08:11 |
| Dejan | you can see the rcp log | 08:11 |
| Dejan | after you restart it is going to start recovery process, and there you will be able to see what it did | 08:12 |
| Meyer080 | that command will ensure I will have everything back up to the last consistency point, right? | 08:12 |
| Meyer080 | dejan: understood | 08:13 |
| Dejan | yes | 08:13 |
| Dejan | iircp.log | 08:13 |
| Meyer080 | Dejan: terrific, thank you. | 08:14 |
| Dejan | I am not a superduper Ingres DBA either | 08:15 |
| Dejan | it is not my job, however, I do lots of DBA stuff here | 08:15 |
| Dejan | because they do not want to hire a new person for it | 08:16 |
| Dejan | and i must admit, it is refreshing sometimes to do DBA work | 08:16 |
| Dejan | and forget about boresome software development | 08:16 |
| Dejan | :D | 08:16 |
| *** Meyer080 has quit IRC | 08:52 | |
| *** Alex| has joined #ingres | 09:10 | |
| *** ChanServ sets mode: +o Alex| | 09:10 | |
| *** Alex| has quit IRC | 09:45 | |
| *** grantc has quit IRC | 11:08 | |
| *** Mud has quit IRC | 11:56 | |
| *** Alex| has joined #ingres | 13:06 | |
| *** ChanServ sets mode: +o Alex| | 13:06 | |
| *** Alex| has quit IRC | 13:19 | |
| *** cthibert has left #ingres | 13:20 | |
| *** mull has quit IRC | 14:21 | |
| *** mull has joined #ingres | 15:09 | |
| *** mull has quit IRC | 18:11 | |
| *** rossand has quit IRC | 18:14 | |
| *** atrofast has quit IRC | 18:17 | |
| *** cytrinox has quit IRC | 19:24 | |
| *** cytrinox has joined #ingres | 19:26 | |
| *** Alex| has joined #ingres | 22:06 | |
| *** ChanServ sets mode: +o Alex| | 22:06 | |
| *** Alex| has quit IRC | 22:49 | |
| *** Alex| has joined #ingres | 23:02 | |
| *** ChanServ sets mode: +o Alex| | 23:02 | |
| *** Mud has joined #ingres | 23:19 | |
| *** Alex| has quit IRC | 23:29 | |
| *** Alex| has joined #ingres | 23:29 | |
| *** ChanServ sets mode: +o Alex| | 23:29 | |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!