Re: Size limitation of user variable?

[Baron Schwartz]

Right, my (implied) point was that you have AT LEAST one limitation --
your max_packet_size.  And I showed the way the original author can
investigate and figure out the rest himself :)

On Wed, Feb 11, 2009 at 4:28 AM, Johan De Meersman  wrote:
> What you just tested, on the other hand, was the limit of your maxpacket :-)
> Up that to something unlikely and try again :-)
>
> On Tue, Feb 10, 2009 at 9:24 PM, Baron Schwartz  wrote:
>>
>> On Tue, Feb 10, 2009 at 1:29 PM, Cantwell, Bryan
>>  wrote:
>> > I am trying to put the result of a function that returns MEDIUMTEXT into
>> > a user variable in my procedure. I haven't attempted to push the limits
>> > of the MEDIUMTEXT size, but wonder if the user variable can even handle
>> > this?
>>
>>
>> The REPEAT() function helps here:
>>
>> mysql> set @var := repeat('a', 1024 * 1024);
>> Query OK, 0 rows affected (0.05 sec)
>>
>> mysql> select length(@var);
>> +--+
>> | length(@var) |
>> +--+
>> |  1048576 |
>> +--+
>> 1 row in set (0.01 sec)
>>
>> So it accepts a mebibyte, let's see if we can notch that up :)
>>
>> mysql> set @var := repeat('a', 1024 * 1024 * 1024);
>> Query OK, 0 rows affected, 1 warning (0.00 sec)
>>
>> mysql> show warnings;
>>
>> +-+--+--+
>> | Level   | Code | Message
>> |
>>
>> +-+--+--+
>> | Warning | 1301 | Result of repeat() was larger than
>> max_allowed_packet (16777216) - truncated |
>>
>> +-+--+--+
>> 1 row in set (0.00 sec)
>>
>> --
>> Baron Schwartz, Director of Consulting, Percona Inc.
>> Our Blog: http://www.mysqlperformanceblog.com/
>> Our Services: http://www.percona.com/services.html
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>>
>
>
>
> --
> Celsius is based on water temperature.
> Fahrenheit is based on alcohol temperature.
> Ergo, Fahrenheit is better than Celsius. QED.
>



-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Size limitation of user variable?

[Johan De Meersman]

What you just tested, on the other hand, was the limit of your maxpacket :-)
Up that to something unlikely and try again :-)

On Tue, Feb 10, 2009 at 9:24 PM, Baron Schwartz  wrote:

> On Tue, Feb 10, 2009 at 1:29 PM, Cantwell, Bryan
>  wrote:
> > I am trying to put the result of a function that returns MEDIUMTEXT into
> > a user variable in my procedure. I haven't attempted to push the limits
> > of the MEDIUMTEXT size, but wonder if the user variable can even handle
> > this?
>
>
> The REPEAT() function helps here:
>
> mysql> set @var := repeat('a', 1024 * 1024);
> Query OK, 0 rows affected (0.05 sec)
>
> mysql> select length(@var);
> +--+
> | length(@var) |
> +--+
> |  1048576 |
> +--+
> 1 row in set (0.01 sec)
>
> So it accepts a mebibyte, let's see if we can notch that up :)
>
> mysql> set @var := repeat('a', 1024 * 1024 * 1024);
> Query OK, 0 rows affected, 1 warning (0.00 sec)
>
> mysql> show warnings;
>
> +-+--+--+
> | Level   | Code | Message
> |
>
> +-+--+--+
> | Warning | 1301 | Result of repeat() was larger than
> max_allowed_packet (16777216) - truncated |
>
> +-+--+--+
> 1 row in set (0.00 sec)
>
> --
> Baron Schwartz, Director of Consulting, Percona Inc.
> Our Blog: http://www.mysqlperformanceblog.com/
> Our Services: http://www.percona.com/services.html
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
>
>


-- 
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.

Re: Size limitation of user variable?

[Baron Schwartz]

On Tue, Feb 10, 2009 at 1:29 PM, Cantwell, Bryan
 wrote:
> I am trying to put the result of a function that returns MEDIUMTEXT into
> a user variable in my procedure. I haven't attempted to push the limits
> of the MEDIUMTEXT size, but wonder if the user variable can even handle
> this?


The REPEAT() function helps here:

mysql> set @var := repeat('a', 1024 * 1024);
Query OK, 0 rows affected (0.05 sec)

mysql> select length(@var);
+--+
| length(@var) |
+--+
|  1048576 |
+--+
1 row in set (0.01 sec)

So it accepts a mebibyte, let's see if we can notch that up :)

mysql> set @var := repeat('a', 1024 * 1024 * 1024);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-+--+--+
| Level   | Code | Message
 |
+-+--+--+
| Warning | 1301 | Result of repeat() was larger than
max_allowed_packet (16777216) - truncated |
+-+--+--+
1 row in set (0.00 sec)

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: size limitation

[Pascal THIVENT]

Hi,

For our benchmarks, we use for the MySQL Server a Sun Enterprise 3500 with 4
UltraSPARC processors, 6 Go of RAM, 3 disks of 40 Go...
MySQL runs under Solaris 7.


> -Message d'origine-
> De: Jeremy D . Zawodny [mailto:[EMAIL PROTECTED]]
> Date: vendredi 23 mars 2001 19:11
> À: Pascal THIVENT
> Cc: [EMAIL PROTECTED]
> Objet: Re: size limitation
> 
> 
> On Fri, Mar 23, 2001 at 11:33:42AM +0100, Pascal THIVENT wrote:
> >  
> > 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).
> 
> What sort of hardware are you planning to use?
> -- 
> Jeremy D. Zawodny, <[EMAIL PROTECTED]>
> Technical Yahoo - Yahoo Finance
> Desk: (408) 328-7878Fax: (408) 530-5454
> Cell: (408) 439-9951
> 

-
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

RE: size limitation

[Sinisa Milivojevic]

Pascal THIVENT writes:
 > 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.


Hi!

Consider using smallint instead of int for second and third column.

This will make entire row size 8 bytes. That is for data only, not
counting index space, which is more complicated.

You make no update in order to be quick. But may be with updates your
table could last longer. Much longer.


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team



-
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

RE: size limitation

[Pascal THIVENT]

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

Re: size limitation

[Jeremy D . Zawodny]

On Sat, Mar 24, 2001 at 05:30:52PM +0200, Heikki Tuuri wrote:
>
> 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?

I don't know of anyone doing that yet... But I'm sure they're out
there somewhere.

Jeremy
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878Fax: (408) 530-5454
Cell: (408) 439-9951

-
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

Re: size limitation

[Heikki Tuuri]

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 
>Mail :   [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

Re: size limitation

[Jeremy D . Zawodny]

On Fri, Mar 23, 2001 at 11:33:42AM +0100, Pascal THIVENT wrote:
>  
> 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).

What sort of hardware are you planning to use?
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878Fax: (408) 530-5454
Cell: (408) 439-9951

-
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