[OFBiz] Users - Exporting large tables with webtools

[Brett]

Has anyone used the webtools export utilities to export tables with
millions of rows?  I have used it on smaller tables, but when I try to
export on a large table I get an out of memory error.  I believe the
webtools utility tries to read the entire table in memory before
writing it to the file.

We need this functionality to do a major migration from Oracle to
postgres.  If the functionality doesn't exist we will implement it
ourself and contribute it back if anyone is interested.  If you have
any suggestions please post them to the group.

Thanks,


Brett
 
_______________________________________________
Users mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/users

[David E Jones-2]

It's been a while since I've done any major work with these and I know some changes have been made since then, but it should not be reading everything into memory nor should it be creating the output XML in memory. The code that I wrote way back when used the EntityListIterator to pull from the database, and streamed small chunks of text for each entity and field out to a file as it iterates over the ELI.

If it's not doing that then some investigation would be necessary to find out why...

-David


Brett Palmer wrote:

> Has anyone used the webtools export utilities to export tables with
> millions of rows?  I have used it on smaller tables, but when I try to
> export on a large table I get an out of memory error.  I believe the
> webtools utility tries to read the entire table in memory before
> writing it to the file.
>
> We need this functionality to do a major migration from Oracle to
> postgres.  If the functionality doesn't exist we will implement it
> ourself and contribute it back if anyone is interested.  If you have
> any suggestions please post them to the group.
>
> Thanks,
>
>
> Brett
>  
> _______________________________________________
> Users mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/users

[Brett]

I'll dive in a little deeper and see where the error is coming from.
I did notice that the behavior seems to be different if I select a
single table for export versus all the tables.  Maybe they are calling
different services.

Brett

On 7/7/06, David E. Jones <[hidden email]> wrote:

>
> It's been a while since I've done any major work with these and I know some changes have been made since then, but it should not be reading everything into memory nor should it be creating the output XML in memory. The code that I wrote way back when used the EntityListIterator to pull from the database, and streamed small chunks of text for each entity and field out to a file as it iterates over the ELI.
>
> If it's not doing that then some investigation would be necessary to find out why...
>
> -David
>
>
> Brett Palmer wrote:
> > Has anyone used the webtools export utilities to export tables with
> > millions of rows?  I have used it on smaller tables, but when I try to
> > export on a large table I get an out of memory error.  I believe the
> > webtools utility tries to read the entire table in memory before
> > writing it to the file.
> >
> > We need this functionality to do a major migration from Oracle to
> > postgres.  If the functionality doesn't exist we will implement it
> > ourself and contribute it back if anyone is interested.  If you have
> > any suggestions please post them to the group.
> >
> > Thanks,
> >
> >
> > Brett
> >
> > _______________________________________________
> > Users mailing list
> > [hidden email]
> > http://lists.ofbiz.org/mailman/listinfo/users
>
>
>

[BJ Freeman]

Note: for some reason I did not get the original message.
this happens from time to time, ever since we moved mailing list.

I export daily to back up data. I have millions of rows in Hits and
visits.  On table exports gigs of data in text form.

I have not had any problems.
I am using somewhere around 7600 svn with MS-SQL db.


David E. Jones sent the following on 7/7/2006 11:49 AM:

>
> It's been a while since I've done any major work with these and I know
> some changes have been made since then, but it should not be reading
> everything into memory nor should it be creating the output XML in
> memory. The code that I wrote way back when used the EntityListIterator
> to pull from the database, and streamed small chunks of text for each
> entity and field out to a file as it iterates over the ELI.
>
> If it's not doing that then some investigation would be necessary to
> find out why...
>
> -David
>
>
> Brett Palmer wrote:
>> Has anyone used the webtools export utilities to export tables with
>> millions of rows?  I have used it on smaller tables, but when I try to
>> export on a large table I get an out of memory error.  I believe the
>> webtools utility tries to read the entire table in memory before
>> writing it to the file.
>>
>> We need this functionality to do a major migration from Oracle to
>> postgres.  If the functionality doesn't exist we will implement it
>> ourself and contribute it back if anyone is interested.  If you have
>> any suggestions please post them to the group.
>>
>> Thanks,
>>
>>
>> Brett
>>  
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> http://lists.ofbiz.org/mailman/listinfo/users

[Brett]

BJ,

Do you run this as an automated service or do you manually run the
export through webtools?

Brett

On 7/7/06, BJ Freeman <[hidden email]> wrote:

> Note: for some reason I did not get the original message.
> this happens from time to time, ever since we moved mailing list.
>
> I export daily to back up data. I have millions of rows in Hits and
> visits.  On table exports gigs of data in text form.
>
> I have not had any problems.
> I am using somewhere around 7600 svn with MS-SQL db.
>
>
> David E. Jones sent the following on 7/7/2006 11:49 AM:
> >
> > It's been a while since I've done any major work with these and I know
> > some changes have been made since then, but it should not be reading
> > everything into memory nor should it be creating the output XML in
> > memory. The code that I wrote way back when used the EntityListIterator
> > to pull from the database, and streamed small chunks of text for each
> > entity and field out to a file as it iterates over the ELI.
> >
> > If it's not doing that then some investigation would be necessary to
> > find out why...
> >
> > -David
> >
> >
> > Brett Palmer wrote:
> >> Has anyone used the webtools export utilities to export tables with
> >> millions of rows?  I have used it on smaller tables, but when I try to
> >> export on a large table I get an out of memory error.  I believe the
> >> webtools utility tries to read the entire table in memory before
> >> writing it to the file.
> >>
> >> We need this functionality to do a major migration from Oracle to
> >> postgres.  If the functionality doesn't exist we will implement it
> >> ourself and contribute it back if anyone is interested.  If you have
> >> any suggestions please post them to the group.
> >>
> >> Thanks,
> >>
> >>
> >> Brett
> >>
> >> _______________________________________________
> >> Users mailing list
> >> [hidden email]
> >> http://lists.ofbiz.org/mailman/listinfo/users
>

[David E Jones-2]

Just a thought: sometimes JDBC drivers will read in all rows from a database into memory, ie they don't support the database cursor.

You say you're coming from Oracle? I'm not aware of any such issues, but I guess it would depend on the JDBC driver you're using, and the JDBC driver version of course.

The pre-8 series Postgres drivers had this problem, but the newer ones support the database cursor just fine.

-David


BJ Freeman wrote:

> Note: for some reason I did not get the original message.
> this happens from time to time, ever since we moved mailing list.
>
> I export daily to back up data. I have millions of rows in Hits and
> visits.  On table exports gigs of data in text form.
>
> I have not had any problems.
> I am using somewhere around 7600 svn with MS-SQL db.
>
>
> David E. Jones sent the following on 7/7/2006 11:49 AM:
>>
>> It's been a while since I've done any major work with these and I know
>> some changes have been made since then, but it should not be reading
>> everything into memory nor should it be creating the output XML in
>> memory. The code that I wrote way back when used the
>> EntityListIterator to pull from the database, and streamed small
>> chunks of text for each entity and field out to a file as it iterates
>> over the ELI.
>>
>> If it's not doing that then some investigation would be necessary to
>> find out why...
>>
>> -David
>>
>>
>> Brett Palmer wrote:
>>> Has anyone used the webtools export utilities to export tables with
>>> millions of rows?  I have used it on smaller tables, but when I try to
>>> export on a large table I get an out of memory error.  I believe the
>>> webtools utility tries to read the entire table in memory before
>>> writing it to the file.
>>>
>>> We need this functionality to do a major migration from Oracle to
>>> postgres.  If the functionality doesn't exist we will implement it
>>> ourself and contribute it back if anyone is interested.  If you have
>>> any suggestions please post them to the group.
>>>
>>> Thanks,
>>>
>>>
>>> Brett
>>>  
>>> _______________________________________________
>>> Users mailing list
>>> [hidden email]
>>> http://lists.ofbiz.org/mailman/listinfo/users

[BJ Freeman]

as a service model from the export all of the web tools.

Brett Palmer sent the following on 7/7/2006 12:36 PM:

> BJ,
>
> Do you run this as an automated service or do you manually run the
> export through webtools?
>
> Brett
>
> On 7/7/06, BJ Freeman <[hidden email]> wrote:
>> Note: for some reason I did not get the original message.
>> this happens from time to time, ever since we moved mailing list.
>>
>> I export daily to back up data. I have millions of rows in Hits and
>> visits.  On table exports gigs of data in text form.
>>
>> I have not had any problems.
>> I am using somewhere around 7600 svn with MS-SQL db.
>>
>>
>> David E. Jones sent the following on 7/7/2006 11:49 AM:
>> >
>> > It's been a while since I've done any major work with these and I know
>> > some changes have been made since then, but it should not be reading
>> > everything into memory nor should it be creating the output XML in
>> > memory. The code that I wrote way back when used the EntityListIterator
>> > to pull from the database, and streamed small chunks of text for each
>> > entity and field out to a file as it iterates over the ELI.
>> >
>> > If it's not doing that then some investigation would be necessary to
>> > find out why...
>> >
>> > -David
>> >
>> >
>> > Brett Palmer wrote:
>> >> Has anyone used the webtools export utilities to export tables with
>> >> millions of rows?  I have used it on smaller tables, but when I try to
>> >> export on a large table I get an out of memory error.  I believe the
>> >> webtools utility tries to read the entire table in memory before
>> >> writing it to the file.
>> >>
>> >> We need this functionality to do a major migration from Oracle to
>> >> postgres.  If the functionality doesn't exist we will implement it
>> >> ourself and contribute it back if anyone is interested.  If you have
>> >> any suggestions please post them to the group.
>> >>
>> >> Thanks,
>> >>
>> >>
>> >> Brett
>> >>
>> >> _______________________________________________
>> >> Users mailing list
>> >> [hidden email]
>> >> http://lists.ofbiz.org/mailman/listinfo/users
>>
>