very large HEAP-tables in 4.1.3

[Jan Kirchhoff]

I was just wondering if anybody has been using very large HEAP-tables 
and if there are ways to have mysql use the memory more efficient:
(I have no experience with all heap-tables but using them as temporary 
tables...)

I just started testing with 2 heap-tables on a development-system (p4 
3.2ghz, 2GB RAM) to get an idea of what's possible and what's not:

I think mysql uses way too much memory (overhead) to store my data. I've 
fed the database with realtime-data for 1 hour now and I need at least 
30 times as much in the tables. (200 times would be perfect - of course 
on some other machine with more memory)
Right now "top" tells me that mysql is using around 10% of the memory.

I already increased  max_heap_table_size and I will have to increase it 
much more, but right now it seems that I would need 6GB of RAM to get my 
minimum amount of data in those tables. Which means I'd need a 
64bit-system. But can mysql deal with 6GB-HEAP-tables???

So has anybody tried something like this, yet? We are currently using 
our own, self-written databases for this, but we are thinking about 
either using a sql-database in order to be able to make joins to other 
tables or adding features to our own software (64bit-support, sql-like 
interface etc.). If it works with mysql, we'd probably prefer that since 
its much less work for us and easier to handle. It doesn't matter if 
mysql uses a little more memory, but right now it seems like mysql is 
wasting way too much memory :(

thanks for any help!
Jan
| max_heap_table_size             | 
499999744                                                                    
|


mysql> show table status\G
*************************** 1. row ***************************
          Name: geldbrief
        Engine: HEAP
       Version: 9
    Row_format: Fixed
          Rows: 2449755
Avg_row_length: 61
   Data_length: 157468096
Max_data_length: 609999634
  Index_length: 19690688
     Data_free: 0
Auto_increment: NULL
   Create_time: NULL
   Update_time: NULL
    Check_time: NULL
     Collation: latin1_swedish_ci
      Checksum: NULL
Create_options: max_rows=20000000
       Comment:
*************************** 2. row ***************************
          Name: umsaetze
        Engine: HEAP
       Version: 9
    Row_format: Fixed
          Rows: 236425
Avg_row_length: 45
   Data_length: 11402880
Max_data_length: 535713975
  Index_length: 1942648
     Data_free: 0
Auto_increment: NULL
   Create_time: NULL
   Update_time: NULL
    Check_time: NULL
     Collation: latin1_swedish_ci
      Checksum: NULL
Create_options: max_rows=20000000
       Comment:
2 rows in set (0.00 sec)
CREATE TABLE `geldbrief` (
 `symbol` char(12) NOT NULL default '',
 `quelle` int(10) unsigned NOT NULL default '0',
 `kurszeit` datetime NOT NULL default '0000-00-00 00:00:00',
 `ticknumber` int(10) unsigned NOT NULL default '0',
 `bid` double(16,4) default NULL,
 `bidsize` double(16,4) default NULL,
 `ask` double(16,4) default NULL,
 `asksize` double(16,4) default NULL,
 PRIMARY KEY  (`symbol`,`quelle`,`kurszeit`,`ticknumber`)
) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=20000000
CREATE TABLE `umsaetze` (
 `symbol` char(12) NOT NULL default '',
 `quelle` int(10) unsigned NOT NULL default '0',
 `kurszeit` datetime NOT NULL default '0000-00-00 00:00:00',
 `ticknumber` int(10) unsigned NOT NULL default '0',
 `kurs` double(16,4) default NULL,
 `umsatz` double(16,4) default NULL,
 PRIMARY KEY  (`symbol`,`quelle`,`kurszeit`,`ticknumber`)
) ENGINE=HEAP DEFAULT CHARSET=latin1 MAX_ROWS=20000000
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]