5 messages in com.mysql.lists.bugsRe: [BUG REPORT]: 3.23.37: Innobase c...| From | Sent On | Attachments |
|---|---|---|
| Frank Schroeder | 16 May 2001 14:04 | |
| Heikki Tuuri | 17 May 2001 06:31 | |
| Michael Widenius | 17 May 2001 09:20 | |
| Frank Schroeder | 07 Jun 2001 11:53 | |
| Heikki Tuuri | 08 Jun 2001 05:18 |
| Subject: | Re: [BUG REPORT]: 3.23.37: Innobase crashes on large table |
|---|---|
| From: | Heikki Tuuri (Heik...@innobase.inet.fi) |
| Date: | 06/08/2001 05:18:23 AM |
| List: | com.mysql.lists.bugs |
Hi!
Thank you for the update. I was actually wondering how you had progressed. In version 3.23.39, which will be released soon, you can specify innodb_flush_method in my.cnf. That may help to speed up disk i/o. You have a read-only application, and then the setting
innodb_flush_method=nosync
is appropriate and may help.
I must admit that the InnoDB index structure based on a clustered index is not the best for your application. You have a kind of inverted list on a sparse numerical matrix. I chose in InnoDB to reserve the same length for an SQL NULL as I reserve for the fixed-length column. It reduces fragmentation in updates, but in your case it wastes space. Maybe I should make the NULL size configurable.
As a mathematician I think that your application is close to being one where a special index structure, hand-written in C, would be justified. The problem is that interfacing it to an SQL interpreter like MySQL is a big job.
Regards,
Heikki http://www.innodb.com
At 08:53 PM 6/7/01 +0200, you wrote:
Just to keep you posted. The fix that Heikki has sent to me worked. I was able to load the table but the space requirements compared to the compressed MyISAM tables are huge. That was one of the problems that I ran out of table space.
The second problem that I did not verify as it took so much time was loading the table using LOAD DATA INFILE from a flat file. This seemed to cause problems after 3 million records really slowing things down. I changed that to load the table with individual INSERT statements generated from mysqldump and then it worked. It took more than 2 days to load the table but it worked.
Michael Widenius wrote:
Hi!
"Frank" == Frank Schroeder <fra...@airflash.com> writes:
Frank> Hi, Frank> I'm trying to load a large table with 7 million records, 50 columns and Frank> 27 indexes into an innodb table. Works fine for MyISAM. Compressed size Frank> of the data file is about 280MB (830MB uncompressed) and 1.5 GB for the Frank> index file. Platform is RedHat Linux 6.2 with kernel 2.2.16-3 and MySQL Frank> is 3.23.37 compiled with egcs-2.91.66 for i686 from the standard MySQL Frank> source RPM. The machine is a dual CPU Pentium machine with 512 MB RAM Frank> and 2 IDE drives (30GB and 40GB).
Frank> For the InnoDB tables I have 6 data files with 1GB on 2 disks and 3 log Frank> files a 50MB. I've also tried it with 5 log files a 1.5GB.
-- Frank Schröder <fra...@airflash.com>
AirFlash Engineering Europe www.airflash.com AirFlash Inc.
<html> <head> </head> <body> Just to keep you posted. The fix that Heikki has sent to me worked. I was able to load the table but the space requirements compared to the compressed MyISAM tables are huge. That was one of the problems that I ran out of table space.<br> <br> The second problem that I did not verify as it took so much time was loading the table using LOAD DATA INFILE from a flat file. This seemed to cause problems after 3 million records really slowing things down. I changed that to load the table with individual INSERT statements generated from mysqldump and then it worked. It took more than 2 days to load the table but it worked.<br> <br> Michael Widenius wrote:<br> <blockquote type="cite"
cite="mid:1510...@narttu.mysql.fi"><pre wrap="">Hi!<br><br></pre>
<blockquote type="cite"> <blockquote type="cite"> <blockquote type="cite"> <blockquote type="cite"> <blockquote type="cite"><pre wrap="">"Frank" == Frank Schroeder
<a class="moz-txt-link-rfc2396E" href="mailto:fra...@airflash.com"><fra...@airflash.com></a> writes:<br></pre>
</blockquote> </blockquote> </blockquote> </blockquote> </blockquote> <pre wrap=""><!----><br>Frank> Hi,<br>Frank> I'm trying
to load a large table with 7 million records, 50 columns and <br>Frank> 27 indexes into an innodb table. Works fine for MyISAM. Compressed size <br>Frank> of the data file is about 280MB (830MB uncompressed) and 1.5 GB for the <br>Frank> index file. Platform is RedHat Linux 6.2 with kernel 2.2.16-3 and MySQL <br>Frank> is 3.23.37 compiled with egcs-2.91.66 for i686 from the standard MySQL <br>Frank> source RPM. The machine is a dual CPU Pentium machine with 512 MB RAM <br>Frank> and 2 IDE drives (30GB and 40GB).<br><br>Frank> For the InnoDB tables I have 6 data files with 1GB on 2 disks and 3 log <br>Frank> files a 50MB. I've also tried it with 5 log files a 1.5GB.<br><br></pre>
</blockquote> <br> <pre class="moz-signature" cols="$mailwrapcol">-- Frank Schröder <a class="moz-txt-link-rfc2396E" href="mailto:fra...@airflash.com"><fra...@airflash.com></a>
AirFlash Engineering Europe <a class="moz-txt-link-abbreviated" href="http://www.airflash.com">www.airflash.com</a> AirFlash Inc. </pre> <br> </body> </html>