Hi,
the database I would like to managed is constitued by one table.
This table is made of 3 columns, with no primary key
mysql> CREATE TABLE test (
id1 int(8) unsigned,
id2 int(4) unsigned,
id3 int(4) unsigned
);
mysql> CREATE INDEX idx_id1 ON test (id1);
mysql> CREATE INDEX idx_id2 ON test (id2);
mysql> CREATE INDEX idx_id3 ON test (id3);
We use 3 indexes because our benchmark shown that it's better than an index
on the 3 columns for our application.
mysql> desc test;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| id1 | int(8) unsigned | | MUL | 0 | |
| id2 | int(4) unsigned | | MUL | 0 | |
| id3 | int(4) unsigned | | MUL | 0 | |
+-----------------+-----------------+------+-----+---------+-------+
Here are the request we make on this table :
SELECT COUNT(*) FROM test WHERE id1=? AND id2=?;
SELECT COUNT(*) FROM test WHERE id1=? AND id=2=? AND id3=?;
INSERT INTO test (USER_ID, FLIGHT_ID, FLIGHTOBJECT_ID) values (?,?,?);
The row size is 16 bytes. So we'll get 16 gigabytes of datas per day (it
will be around 10 gigabyte in reality because only 66% of the request need
an insert).
The table is going to grow until we'll drop it (because of storage
limitations). 10 days of lifetime for our datas will be enough :)
We make no update in order to be very quick.
> -----Message d'origine-----
> De: Heikki Tuuri [mailto:[EMAIL PROTECTED]]
> Date: samedi 24 mars 2001 15:31
> À: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Objet: Re: size limitation
>
>
> Pascal,
>
> could you describe what indexes you should have on the data, and
> how you will add to it and delete from it?
>
> >In order to choose a DBMS, i've to check to MySQL is able to
> support our
> >requirements. I've got to manage one database.
> >This database is made of one table.
> >This table will grow very quickly (we evaluate that we'll
> have to make
> >arround 1.000.000.000 inserts per day).
>
> Do you really mean one billion inserts per day? 1000 million inserts?
> If the row size is 100 bytes, you will get new data 100 gigabytes
> per day. If you have a terabyte of disk, you can only fit 10 days'
> data in it.
>
> >I want to be sure that MySQL is able to manage table bigger
> than 1 TeraOctet.
> >Does anyone have already use in similar condition ?
> >I read that innobase table allows to store a table on
> several oracle-like
> >"tablespace" that can be created on several disks. I think
> we can put the
> >indexes on other disk too (using symbolic links).
>
> An Innobase tablespace can span 4 billion database pages, that is,
> 64 terabytes with the default page size of 16 kB.
>
> A single file of a tablespace can currently hold 2 GB or 4 GB
> depending on your OS. You would need some 500 data files in your
> tablespace.
>
> You cannot currently instruct Innobase where to put your data
> and index trees (all go into the same tablespace). But, if your table
> is so huge, both the data and the indexes will lie on several disks,
> since a single disk can store max 80 GB of data today, I think.
>
> If you have 1 billion insertions per day, that is 12 000 inserts per
> second around the clock. The CPU usage of Innobase would allow
> you to do this many insertions. The problem can be the communications
> overhead between your client process and the MySQL server. It might
> use even 100 microseconds per insert. You can reduce this overhead by
> using the multiple INSERT INTO ... VALUES (...), (...), (...)
> insertion syntax of MySQL.
>
> Another problem is the number of disks you need: if you have to make
> 12 000 random insertions per second, you need a disk farm of
> some 200-500 separate disks. The operating system will use some CPU
> for each disk i/o. If you have to read and write 12 000 16 kB pages
> each second, you need a bandwidth of 400 MB/second in your disk
> channel. These numbers are similar to the largest TPC-C benchmarks
> run today. The hardware will be expensive. You can look at
> www.tcp.org, where you find descriptions of the benchmark systems
> and their prices.
>
> >I would like to know what are the requirement to store so
> much records.
> >Does anyome have reference of site that manage a table
> bigger than 1 tera ?
>
> The largest databases in the world are of the order 4 - 40 TB.
> I think many of them use Teradata database machines. Some use
> Oracle.
>
> >Did someone try that ? I hope it's possible.
> >Otherwise, i'll to choose Oracle :( and Oracle is not cheap.
> Regards,--
>
> MySQL/Innobase may be able to do what you need, but we should have
> a real-world test of using a big disk farm. I do not know if any
> MySQL user uses a disk farm. Maybe Jeremy Zawodny knows?
>
> Regards,
>
> Heikki
>
> >Pascal ThiventGroupe SQLIWeb : <http://www.sqli.com/>
http://www.sqli.com
>Mail : <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] : +33 (0)1 55 93 25
39
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
