| *** rossand has quit IRC | 01:43 | |
| *** Alex|off is now known as Alex| | 06:46 | |
| *** perftuning has quit IRC | 07:26 | |
| *** grantc_offline is now known as grantc | 08:30 | |
| *** elPiola has quit IRC | 08:41 | |
| grantc | lo Alex| | 09:19 |
|---|---|---|
| *** elPiola has joined #ingres | 09:52 | |
| Alex| | hi grantc - lunch with Jo now. Am back later | 11:40 |
| grantc | ok - say hi for me | 11:40 |
| troal01 | grantc: The modify to reorganize didn't work... I still rant out of tids/pages :( | 11:54 |
| troal01 | Is there something I'm missing? | 11:54 |
| *** troal01 has quit IRC | 12:15 | |
| *** troal01 has joined #ingres | 12:15 | |
| troal01 | Could tid be made a 64 bit integer? | 13:04 |
| troal01 | I mean theoretically | 13:04 |
| elPiola | anything is possible in theory | 13:04 |
| troal01 | I'm assuming that's a major change though? | 13:07 |
| elPiola | yes, it's <b><i><u>*M.A.J.O.R*</u></i></b> change | 13:08 |
| * grantc feels that elPiola has understated the amount of work needed | 13:09 | |
| troal01 | Haha | 13:10 |
| elPiola | grantc is probably right, I just couldn't think of other means of highlighting | 13:10 |
| troal01 | Hey grantc did you see my earlier comment re reorganize? | 13:10 |
| grantc | err no | 13:17 |
| grantc | i have been having some firewall fun | 13:18 |
| troal01 | Heh, gotta love firewalls | 13:18 |
| troal01 | I did the modify tab to reorganize with location = (loc1, loc2, loc3, loc4) but it still runs out of pages | 13:19 |
| grantc | this one was causing ssh sessions ( and consequently scp/rsync) to stall | 13:19 |
| grantc | what page size are you using? | 13:20 |
| grantc | and what is the row-width | 13:21 |
| grantc | i need to prepare some lunch - today's menu consists of albondigas en salsa de tomate con patatas fritas... | 13:22 |
| troal01 | 16k pages, row size of 40 bytes | 13:24 |
| troal01 | I could squeeze in more rows if I increased it to 32k pages but I would be wasting a lot of space | 13:24 |
| troal01 | And it still wouldn't be enough | 13:25 |
| troal01 | I run out of pages after only about 60% of the db has bee nloaded | 13:25 |
| *** rossand has joined #ingres | 14:28 | |
| *** ChanServ sets mode: +o rossand | 14:28 | |
| grantc | troal01, how many rows are you getting per page? | 15:23 |
| troal01 | grantc: Around 340 rows/page | 15:24 |
| grantc | you can increase the fillfactor | 15:25 |
| grantc | since 100% usage is 400 rows more or less | 15:25 |
| grantc | i cannot remember the defaults | 15:25 |
| troal01 | But I'm still going to run out of pages | 15:26 |
| grantc | what storage structure are you using? | 15:27 |
| troal01 | HEAP at the moment | 15:27 |
| troal01 | Once I get everything loaded I'm going to change that | 15:27 |
| grantc | how many rows are you going to insert? | 15:27 |
| troal01 | Good question, I don't have the total #rows | 15:29 |
| grantc | you said earlier "<troal01> I run out of pages after only about 60% of the db has bee nloaded" | 15:30 |
| grantc | so you must have a rough idea? | 15:30 |
| troal01 | I currently have it split up in four binary files which I want to load | 15:30 |
| *** DarylM has joined #ingres | 15:30 | |
| troal01 | They're each ~50GBs | 15:30 |
| troal01 | Containing three tables, row widths are around 40 for each | 15:30 |
| troal01 | I've tried to fiddle with page size before and I run out of pages | 15:32 |
| troal01 | So I need a way to exceed the max number of pages for one table | 15:32 |
| grantc | not possible | 15:32 |
| troal01 | Or a workaround | 15:32 |
| troal01 | What about through partitioning? | 15:32 |
| grantc | create more locations | 15:33 |
| grantc | you can have up to 256 IIRC per database | 15:33 |
| troal01 | But I already modified this table modify tab to reorganize with location = (loc1, loc2, loc3, loc4) etc and it still runs out of pges | 15:33 |
| grantc | that's the same as locations but with conditions | 15:33 |
| grantc | i.e. data that matches this "set" goes in this/these locations | 15:33 |
| grantc | i guess you need more locations... if you know how many rows you are inserting you can work out the number of pages it will use (340 rows per page) | 15:35 |
| grantc | then div the number of pages by 2^23 et voila | 15:35 |
| grantc | for a back of a fag packet calculation.. | 15:36 |
| troal01 | SHould I have to reorganize it after every copy statement? | 15:36 |
| grantc | no you will need to re-org it before | 15:37 |
| grantc | or issue the create table with the correct number of locations | 15:37 |
| troal01 | Okay so I did reorg it before... are you sure having several locations will automatically enable me to go above the 2^23 page limit? | 15:59 |
| grantc | that is my understanding since you cannot have any more than 2^23 pages per location, unless someone else here knows any better? | 16:00 |
| grantc | see http://community.ingres.com/forum/dba-forum/1203-trying-grow-file-beyond-what-tid-can-handle.html | 16:00 |
| troal01 | I came across that post, Paul recommends fiddling with page size which in that case might make al ot of sense but it still doesn't change the fact there is an upper limit in the end | 16:10 |
| grantc | perhaps my idea of partitioned vs location is wrong... | 16:11 |
| grantc | also i had assumed 2^23 pages per location... | 16:12 |
| DarylM | You are correct | 16:13 |
| grantc | its per location or per table? | 16:14 |
| DarylM | location (physical file really, that is why partitions work also) | 16:14 |
| DarylM | (unless I haven't had enough coffee yet today....) | 16:15 |
| grantc | reading what maspa05 wrote in the forum it would indicate otherwise | 16:15 |
| *** Alex| is now known as Alex|off | 16:16 | |
| DarylM | wait, brain freeze - | 16:16 |
| DarylM | yes, I remember now. locations were used to get around the old 2GB file size limit | 16:17 |
| DarylM | system wide max of 2^23 pages | 16:17 |
| troal01 | Okay so what if you really really need more pages? | 16:21 |
| troal01 | create two tables? | 16:21 |
| grantc | use table partitions | 16:22 |
| grantc | see the create table docs | 16:22 |
| DarylM | But at 32K pages we are talking about 274,877,906,944 bytes | 16:22 |
| troal01 | Because of row width I can fit 512 rows on a 32K page, but only take up about 20kb, 20kb/page * 8388595max pages = ~170GBs | 16:25 |
| troal01 | Hm | 16:26 |
| troal01 | Maybe that will work, but I'll waste a lot of disk space | 16:26 |
| troal01 | And my HDD is only 500 gbs :P | 16:26 |
| *** Alex|off is now known as Alex| | 16:27 | |
| DarylM | There is overhead in the records. It is not strictly rowwidth*#rows | 16:27 |
| DarylM | but yes, wasted space | 16:27 |
| troal01 | I'll look into partitioning then, can this be used to work around the max pages issue then? | 16:28 |
| DarylM | The claim is yes, but I have not personally tried or tested that | 16:29 |
| * DarylM thinks the term "bleeding edge" comes to mind.... | 16:30 | |
| *** perftuning has joined #ingres | 16:31 | |
| troal01 | Hehe, I'll let you know how it goes, thanks | 16:32 |
| DarylM | With that much data, you might find partitions will address a number of other issues that will eventually come up | 16:33 |
| troal01 | Can you map each partition to a particular location? I mean is that what this does? modify main to reconstruct with partition = (hash on id 4 partitions (part1, part2, part3, part4)), location = (data_1, data_2, data_3, data_4) | 17:09 |
| *** Alex| is now known as Alex|off | 17:09 | |
| *** Alex|off is now known as Alex| | 19:00 | |
| *** troal01 has left #ingres | 19:42 | |
| *** troal01 has joined #ingres | 19:50 | |
| troal01 | -= THIS MESSAGE NOT LOGGED =- | 19:50 |
| pboro | -= THIS MESSAGE NOT LOGGED =- | 19:51 |
| troal01 | Hey where will the logs be posted? | 19:51 |
| grantc | :/ | 19:52 |
| pboro | NSA, Interpol, Europol, you name it... wait a sec, wrong chan | 19:52 |
| grantc | troal01, not sure yet | 19:52 |
| troal01 | Hehe | 19:52 |
| grantc | i have a program that will convert them to html | 19:52 |
| grantc | as well as media wiki | 19:52 |
| pboro | grantc, going to do any editorial work on them before posting them? | 19:52 |
| grantc | the former can be scripted so I don't have to do diddly squat | 19:53 |
| grantc | how's that for an answer pboro | 19:53 |
| pboro | okay :) | 19:53 |
| troal01 | Heh, never do anything manually that you can script! | 19:53 |
| grantc | i don't intend on doing any editing unless i really have to | 19:54 |
| pboro | I'm still trying to script my life management system v0.1 :/ | 19:54 |
| grantc | I would like to have them posted to the wiki but I am not sure what the options are for that | 19:54 |
| troal01 | I wouldn't mind scripting taking-out-garbage.sh so I don't have to do it anymore :P | 20:00 |
| grantc | @config supybot.plugins.ChannelLogger.filenameTimestamp %Y-%m-%d | 20:04 |
| ii_log | grantc: The operation succeeded. | 20:04 |
| grantc | http://croker.net/~ii_log/irc/logs/%23ingres/ | 20:08 |
| grantc | not sure i like the colourisation | 20:10 |
| grantc | but that is a minor issue | 20:10 |
| *** Gerhard has joined #ingres | 20:44 | |
| *** Alex| is now known as Alex|off | 20:57 | |
| Gerhard | Hi all | 22:04 |
| *** rossand has quit IRC | 22:20 | |
| *** Gerhard has quit IRC | 22:21 | |
| *** DarylM has quit IRC | 23:45 | |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!