Re: Help with large database import

Hi,

   I have the similar kind of issue in MySQL 5.0.45.

ERROR 1114 (HY000) at line 1702: The table 'qp_weightage_1' is full

I am trying the installation of mysql cluster for the first time. I am trying to import a db of size 900MB in to the cluster. The server has got 8GB RAM. I have not configured any of the parameters like data memory, index memory... etc.
I have the following doubts:

1. How can i load a huge table. What are the variables to be tuned? what are the parameters to be considered for the import to be successful?
2. Is there any link where i can find more data on tuning the variables.

Please help. Thanks in advance.

Regards,
Pradeep Chandru.

On 3/2/07, Anders D. Hansen <anders@dyekjaer.dk> wrote:
>
> On Mar 1, 2007, at 22:15 , Charles Aylward wrote:
> >
> > On 3/1/07 12:47 PM, "Anders D. Hansen" <anders@dyekjaer.dk> wrote:
> >
> >> On Mar 1, 2007, at 19:42 , Charles Aylward wrote:
> >>
> >>>
> >>> Greetings,
> >>>
> >>> If the dump of your database is ~400MB, then, you will need
> >>> approximately
> >>> that that DataMemory (rule of thumb would be _at least_ this
> >>> much). This
> >>> variable defines the space for the raw storage of the table
> >>> contents. Of
> >>> course, you would probably want to add a little on top of this if
> >>> you intend
> >>> to add to this database in any way. Also, depending on how key-
> >>> heavy your
> >>> schemas are, you might need to increase your IndexMemory as well.
> >>
> >> Alright, but I was hoping to rely on "Disk Data Storage" so that I
> >> would not need to add more memory to the machines.
> >> When I'm using tablespaces it should not require more memory or am I
> >> missing something completely?
> >
> > This assumption is correct (barring relatively small increases
> > required for
> > various overhead of course). Sorry I overlooked that this was your
> > intent
> > from the beginning.
> >
> > When you created the logfile group(s) and tablespace, did you allow
> > for
> > enough space (at least 400MB)? It's a bit misleading that the
> > variable is
> > called "INITIAL_SIZE" for the datafile. Once the file is created,
> > it's size
> > cannot be changed. You can however add more datafiles later using
> > alter
> > tablespace. Since you are doing what sounds like an initial
> > install, I'd
> > say start fresh and recreate your tablespace with a datafile (or
> > two) large
> > enough to hold you for quite a while into the future (especially
> > since disk
> > space is cheap and you probably have plenty). I'd start with at
> > least 2G.
> >
> >> I was hoping to import a couple of databases to the cluster and
> >> these will definitely grow to more than 400MB...
> >> Will this not work when using tablespaces?
> >
> > This definitely will work. There is some size declaration that is too
> > small. I'd imagine it's when you created the table space. Like I
> > said, the
> > "INITIAL" part is confusing. Either start over and create a new
> > tablespace
> > or use alter tablespace to add more datafiles to the existing
> > tablespace.
> >
> > Cheers,
> > Charles
> >
> > P.S. Remember that only nonindexed columns are stored on disk, so
> > you will
> > still need enough memory to house your indexed columns.
>
> Come to think about it, this database has alot of indexes...
> I forgot about that - it must be the source of the problem.
>
> Thank you very much for your time!
>
> Kind Regards,
> Anders
> >
> >>
> >>>
> >>> Another quick note, unless you intend to be making massive
> >>> amounts of
> >>> updates to this data once you are up and running (say, over 1000
> >>> updates per
> >>> second), you will probably not want to have your
> >>> TimeBetweenLocalCheckpoints
> >>> set to 6 (which means continuous). This creates unnecessary
> >>> overhead/busy
> >>> work on a infrequently updated database. Even on exceptionally busy
> >>> clusters I have not needed to set this below 10.
> >>
> >> I will definitely change this to 10 then.
> >>
> >> Thank you.
> >>
> >> ~Anders
> >>
> >>>
> >>> Cheers,
> >>> Charles
> >>>
> >>>
> >>>
> >>> On 3/1/07 7:35 AM, "Anders D. Hansen" <anders@dyekjaer.dk> wrote:
> >>>
> >>>> Greetings,
> >>>>
> >>>> I have successfully installed and configured two servers each with
> >>>> 1GB of Memory.
> >>>> They are running the latest 5.1.16 version om MySQL server on
> >>>> FreeBSD.
> >>>>
> >>>> My config look like this:
> >>>>
> >>>> [NDBD DEFAULT]
> >>>> NoOfReplicas=2
> >>>> NoOfFragmentLogFiles=32
> >>>> TimeBetweenLocalCheckpoints=6
> >>>> DataMemory=250M
> >>>> IndexMemory=50M
> >>>> [MYSQLD DEFAULT]
> >>>> [NDB_MGMD DEFAULT]
> >>>> [TCP DEFAULT]
> >>>> [NDB_MGMD]
> >>>> HostName=<IP>
> >>>> # Storage Engines
> >>>> [NDBD]
> >>>> HostName=<IP>
> >>>> DataDir= /data/mysql-cluster
> >>>> [NDBD]
> >>>> HostName=<IP>
> >>>> DataDir=/usr/local/mysql-cluster
> >>>> [MYSQLD]
> >>>> [MYSQLD]
> >>>>
> >>>> My problem occurs when I try to import a mysqldump database file
> >>>> which on the filesystem is about 400MB.
> >>>> At this point I know I need to use Cluster Disk Data Storage - but
> >>>> how is this used during import?
> >>>>
> >>>> First I create the logfile group and tablespace...
> >>>> I then modify the dump file by adding the following to the "create
> >>>> table" statements:
> >>>> TABLESPACE <tablespace> STORAGE DISK
> >>>> ENGINE=NDB;
> >>>>
> >>>> But at some point I get a "Table <table> is full" error...
> >>>>
> >>>> I have also tried to first import the dump files as it is without
> >>>> changing engine and adding tablespace.
> >>>> Then executing commands like these on every table:
> >>>> alter table <table> TABLESPACE <tablespace> STORAGE DISK
> >>>> ENGINE=NDB;
> >>>>
> >>>> But again I receive a "Table <table> is full" error.
> >>>>
> >>>> How can I import this database or is it just not possible?
> >>>>
> >>>> Thank you very much!
> >>>>
> >>>> ~Anders
> >>>>
> >>>
> >
>
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:
> http://lists.mysql.com/cluster?unsub=chandru.dba@gmail.com
>
>
Received on Mon Oct 15 03:56:56 2007