50M records each year.. how to handle

--0-188069199-1257154078=:56900
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

I have come across a requirement where I need to store a very large amount =
of data in a table. =0AIn one of our app.. we can have around 50 Million re=
cords each year.. Can any one guide me in choosing a strategy than can hand=
le this load. =0AThanks=0ASN =0A =0A =0ASudhir Nimavat=
Senior software engineer. =0AQuick start global PVT LTD.=0ABaroda - 390007=
=0AGujarat, India Personally I'm always ready to learn, although I do =
not always like being taught =0A Try the new Yahoo! India Homepag=
e. Click here. http://in.yahoo.com/trynew
--0-188069199-1257154078=:56900--

Re: 50M records each year.. how to handle

--00504502962588596c0477708885
Content-Type: text/plain; charset=ISO-8859-1

Hi Sudhir,

I am handling 50M record each day. Your requirement can be easily full
filled using partition and good architecture.

Thanks,
Krishna

On Mon, Nov 2, 2009 at 2:57 PM, || Sudhir Nimavat || <
sudhir_nimavat@yahoo.com> wrote:

> I have come across a requirement where I need to store a very large amount
> of data in a table.
> In one of our app.. we can have around 50 Million records each year.. Can
> any one guide me in choosing a strategy than can handle this load.
>
>
>
>
> Thanks
> SN
>
>
>
>
> Sudhir NimavatSenior software engineer.
> Quick start global PVT LTD.
> Baroda - 390007
> Gujarat, India
>
> Personally I'm always ready to learn, although I do not always like being
> taught
>
>
> Try the new Yahoo! India Homepage. Click here.
> http://in.yahoo.com/trynew

--00504502962588596c0477708885--

Re: 50M records each year.. how to handle

--0-1627344268-1257229443=:29242
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

You mean your app creates 50M records each day !!=0ACan you please elaborat=
e on what type of partitions and storage engine are you using ? Thanks=
=0ASN =0A =0APersonally I'm always ready to learn, although I =
do not always like being taught=0A =0A________________________=
________=0AFrom: Krishna Chandra Prajapati =0ATo: ||=
Sudhir Nimavat || =0ACc: Mysql l.com>=0ASent: Tue, 3 November, 2009 10:41:57 AM=0ASubject: Re: 50M records=
each year.. how to handle Hi Sudhir, I am handling 50M record e=
ach day. Your requirement can be easily full filled using partition and goo=
d architecture. Thanks,=0AKrishna =0AOn Mon, Nov 2, 2009 at 2:57 =
PM, || Sudhir Nimavat || wrote: I have come=
across a requirement where I need to store a very large amount of data in =
a table.=0A>>=0A>In one of our app.. we can have around 50 Million records =
each year.. Can any one guide me in choosing a strategy than can handle thi=
s load.=0A>=0A>=0A>=0A>=0A>>Thanks=0A>>SN=0A>=0A>=0A>=0A>=0A>> Sudhir Nimava=
tSenior software engineer.=0A>>Quick start global PVT LTD.=0A>>Baroda - 390=
007=0A>>Gujarat, India=0A>=0A>>Personally I'm always ready to learn, althou=
gh I do not always like being taught=0A>=0A>=0A>> Try the new Yahoo! I=
ndia Homepage. Click here. http://in.yahoo.com/trynew Now,=
send attachments up to 25MB with Yahoo! India Mail. Learn how. http://in.o=
verview.mail.yahoo.com/photos
--0-1627344268-1257229443=:29242--

Fw: 50M records each year.. how to handle

--0-768873173-1257400939=:68437
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Ok, Now the problem is.. currently we are using mysql 4.1 and it is n=
ot possible to upgrade mysql at this time.. as far as I know mysql 4.1 does=
not support partitioning.. So the only solution would be to use manual par=
titioning and handle it through code.. Can any one tell me.. how this =
requirement may be satisfied with my sql 4.1.. Any ideas, inputs ?=0A=
=0AThanks=0ASN =0A =0A Personally I'm always ready to l=
earn, although I do not always like being taught=0A ----- Forw=
arded Message ----=0AFrom: Rob =0ATo: || Sudhir =
Nimavat || =0ASent: Mon, 2 November, 2009 3:18:05=
PM=0ASubject: Re: 50M records each year.. how to handle Hi , Yes=
pretty much, depending on the type of access to the records of=0Acourse. B=
ut 50 million sounds like historical semi static data. It will=0Afunction p=
retty much like a sort of raid. Having more storage devices to=0Aseek throu=
gh all the data. (see your other mail), partitioning support is fine. =
See the example there, CREATE TABLE ti (id INT, amount DECIMAL(7,2), t=
r_date DATE)=0A ENGINE=3DINNODB=0A PARTITION BY HASH( MONTH(tr_date) =
)=0A PARTITIONS 6; =0AThe partitioning take place based on the tr_d=
ate colum, based on month,=0Aand chops it up in 6 partitions. There ar=
e some quirks with partitioning, like it does not automatically=0Apartition=
the data yet, which i think is strange, and something that you=0Awant (spe=
cially based on time). But if performance is a big issue at the=0Amoment th=
en the manual partitioning is something worth it. Kind Regards =
=0AOn 11/02/2009 10:35 AM, || Sudhir Nimavat || wrote:=0A> Thanks Rob for q=
uick response..=0A>=0A> Will that give decent performance ?=0A>=0A> Note: T=
here would be more read operations and slow write.. The table=0A> is very s=
imple with few columns only..=0A>=0A>=0A> =0A> =0A> =0A> =0A> *Sudhir N=
imavat*=0A> *Senior software engineer. **=0A> Quick start global PVT LTD.=
=0A> Baroda - 390007*=0A> *Gujarat, India=0A>=0A> Personally I'm always rea=
dy to learn, although I do not always like=0A> being taught=0A> *=0A> =0A>=
=0A>=0A> ------------------------------------------------------------ ------=
------=0A> *From:* Rob =0A> *To:* || Sudhir Nima=
vat || =0A> *Sent:* Mon, 2 November, 2009 3:01:39=
PM=0A> *Subject:* Re: 50M records each year.. how to handle=0A>=0A> On 11/=
02/2009 10:27 AM, || Sudhir Nimavat || wrote:=0A> > I have come across a re=
quirement where I need to store a very large=0A> amount of data in a table.=
=0A> > In one of our app.. we can have around 50 Million records each=0A> y=
ear.. Can any one guide me in choosing a strategy than can handle=0A> this =
load.=0A> >=0A> >=0A> >=0A> >=0A> > Thanks=0A> > SN=0A> >=0A> > =0A> > =0A>=
> =0A> > Sudhir NimavatSenior software engineer.=0A> > Quick start global=
PVT LTD.=0A> > Baroda - 390007=0A> > Gujarat, India=0A> >=0A> > Personally=
I'm always ready to learn, although I do not always like=0A> being taught=
=0A> >=0A> >=0A> > Try the new Yahoo! India Homepage. Click here.=0A> =
http://in.yahoo.com/trynew=0A> > =0A> Consider vertical partitioning with s=
eperate storage devices=0A>=0A>=0A>=0A> -----------------------------------=
-------------------------------------=0A> Add whatever you love to the Yaho=
o! India homepage. Try now!=0A> tp://in.yahoo.com/trynew>=0A> -- =0A> This message has been scanned for vir=
uses and=0A> dangerous content by *MailScanner* o/>, and is=0A> believed to be clean. =0A The INTERNET now has a=
personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
--0-768873173-1257400939=:68437--

Re: Fw: 50M records each year.. how to handle

I did some quick math, and it comes down to 1.5 records per second on
average. Even with peak loads, this is doable without the annoying
manual partitioning, I think. Just make sure your storage is optimal,
think Raid10. If possible, use INSERT DELAYED. If you do updates and
deletes, too, use InnoDB, if just select and insert, use MyISAM - or
if you can do with really slow selects, use the Archive engine.

It depends a lot on your planned use, really, but the above should help.

On 11/5/09, || Sudhir Nimavat || wrote:
> Ok,
>
> Now the problem is.. currently we are using mysql 4.1 and it is not possible
> to upgrade mysql at this time.. as far as I know mysql 4.1 does not support
> partitioning.. So the only solution would be to use manual partitioning and
> handle it through code..
>
> Can any one tell me.. how this requirement may be satisfied with my sql
> 4.1..
>
> Any ideas, inputs ?
>
> Thanks
> SN
>
>
>
>
>
>
>
> Personally I'm always ready to learn, although I do not always like being
> taught
>
>
>
>
> ----- Forwarded Message ----
> From: Rob
> To: || Sudhir Nimavat ||
> Sent: Mon, 2 November, 2009 3:18:05 PM
> Subject: Re: 50M records each year.. how to handle
>
> Hi ,
>
> Yes pretty much, depending on the type of access to the records of
> course. But 50 million sounds like historical semi static data. It will
> function pretty much like a sort of raid. Having more storage devices to
> seek through all the data.
>
> (see your other mail), partitioning support is fine. See the example there,
>
> CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE)
> ENGINE=INNODB
> PARTITION BY HASH( MONTH(tr_date) )
> PARTITIONS 6;
>
>
> The partitioning take place based on the tr_date colum, based on month,
> and chops it up in 6 partitions.
>
> There are some quirks with partitioning, like it does not automatically
> partition the data yet, which i think is strange, and something that you
> want (specially based on time). But if performance is a big issue at the
> moment then the manual partitioning is something worth it.
>
> Kind Regards
>
>
> On 11/02/2009 10:35 AM, || Sudhir Nimavat || wrote:
>> Thanks Rob for quick response..
>>
>> Will that give decent performance ?
>>
>> Note: There would be more read operations and slow write.. The table
>> is very simple with few columns only..
>>
>>
>>
>>
>>
>>
>> *Sudhir Nimavat*
>> *Senior software engineer. **
>> Quick start global PVT LTD.
>> Baroda - 390007*
>> *Gujarat, India
>>
>> Personally I'm always ready to learn, although I do not always like
>> being taught
>> *
>>
>>
>>
>> ------------------------------------------------------------ ------------
>> *From:* Rob
>> *To:* || Sudhir Nimavat ||
>> *Sent:* Mon, 2 November, 2009 3:01:39 PM
>> *Subject:* Re: 50M records each year.. how to handle
>>
>> On 11/02/2009 10:27 AM, || Sudhir Nimavat || wrote:
>> > I have come across a requirement where I need to store a very large
>> amount of data in a table.
>> > In one of our app.. we can have around 50 Million records each
>> year.. Can any one guide me in choosing a strategy than can handle
>> this load.
>> >
>> >
>> >
>> >
>> > Thanks
>> > SN
>> >
>> >
>> >
>> >
>> > Sudhir NimavatSenior software engineer.
>> > Quick start global PVT LTD.
>> > Baroda - 390007
>> > Gujarat, India
>> >
>> > Personally I'm always ready to learn, although I do not always like
>> being taught
>> >
>> >
>> > Try the new Yahoo! India Homepage. Click here.
>> http://in.yahoo.com/trynew
>> >
>> Consider vertical partitioning with seperate storage devices
>>
>>
>>
>> ------------------------------------------------------------ ------------
>> Add whatever you love to the Yahoo! India homepage. Try now!
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by *MailScanner* , and is
>> believed to be clean.
>
>
> The INTERNET now has a personality. YOURS! See your Yahoo! Homepage.
> http://in.yahoo.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Fw: 50M records each year.. how to handle

--0-1971826480-1257419048=:99568
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi Johan Thanks, This is really helpful response... As I said.. t=
here would be more read operations and slow write=0Aoperation.. I may use A=
pplication level caching of data to reduce=0Aselect queries..=0AI am totall=
y against of using manual partitioning as it would create=0Alots of problem=
s like primary keys, joins, clusters and replication=0Aetc.. =0Ait would be=
difficult to migrate to another DB or upgrade mysql in=0Afuture.. In futur=
e if we plan to upgrade mysql or use oracle than we=0Awont be able to use b=
uilt in partitioning support without modifying the=0Aapplication code becau=
se application knows about and handles partitions However my biggest c=
oncern is, Will mysql 4.1 be able to handle such a big table? around 50M ea=
ch year so threre can be 250M records after five years.. I guess, tabl=
e structure would be some thing like below.. - message_folders=0A-- i=
d=0A-- user_id (FK)=0A-- name varchar2=0A-- created_at timestap - Conv=
ersation=0A-- id=0A-- folder_id (FK) =0A- messages=0A-- id=0A-- author=
_id (FK)=0A-- Recipant (FK)=0A-- Subject varchar2=0A-- Body varchar2=0A-- a=
ttchment_name=0A-- author_deleted boolean=0A- Recipant_deleted boolean=0A-=
- date_sent timestamp - Conversation_messages=0A-- conversation_id=0A-=
- message_id - unique Any suggestion on Table structure or any thing i=
s welcome.. Thanks again=0ASN=0A =0A =0A =0ASudhir Nimavat=
Senior software engineer. =0AQuick start global PVT LTD.=0ABaroda - 390007=
=0AGujarat, India Personally I'm always ready to learn, although I do =
not always like being taught=0A =0A___________________________=
_____=0AFrom: Johan De Meersman =0ATo: || Sudhir Nimava=
t || =0ACc: Mysql =0ASent:=
Thu, 5 November, 2009 4:14:30 PM=0ASubject: Re: Fw: 50M records each year.=
.. how to handle I did some quick math, and it comes down to 1.5 record=
s per second on=0Aaverage. Even with peak loads, this is doable without the=
annoying=0Amanual partitioning, I think. Just make sure your storage is op=
timal,=0Athink Raid10. If possible, use INSERT DELAYED. If you do updates =
and=0Adeletes, too, use InnoDB, if just select and insert, use MyISAM - or=
=0Aif you can do with really slow selects, use the Archive engine. It =
depends a lot on your planned use, really, but the above should help. =
On 11/5/09, || Sudhir Nimavat || wrote:=0A> Ok,=
=0A>=0A> Now the problem is.. currently we are using mysql 4.1 and it is no=
t possible=0A> to upgrade mysql at this time.. as far as I know mysql 4.1 d=
oes not support=0A> partitioning.. So the only solution would be to use man=
ual partitioning and=0A> handle it through code..=0A>=0A> Can any one tell =
me.. how this requirement may be satisfied with my sql=0A> 4.1..=0A>=0A> An=
y ideas, inputs ?=0A>=0A> Thanks=0A> SN=0A>=0A>=0A>=0A>=0A>=0A>=0A>=0A> Per=
sonally I'm always ready to learn, although I do not always like being=0A> =
taught=0A>=0A>=0A>=0A>=0A> ----- Forwarded Message ----=0A> From: Rob n@connectedserver.com>=0A> To: || Sudhir Nimavat || om>=0A> Sent: Mon, 2 November, 2009 3:18:05 PM=0A> Subject: Re: 50M records=
each year.. how to handle=0A>=0A> Hi ,=0A>=0A> Yes pretty much, depending =
on the type of access to the records of=0A> course. But 50 million sounds l=
ike historical semi static data. It will=0A> function pretty much like a so=
rt of raid. Having more storage devices to=0A> seek through all the data.=
=0A>=0A> (see your other mail), partitioning support is fine. See the examp=
le there,=0A>=0A> CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DAT=
E)=0A> ENGINE=3DINNODB=0A> PARTITION BY HASH( MONTH(tr_date) )=0A> =
PARTITIONS 6;=0A>=0A>=0A> The partitioning take place based on the tr_d=
ate colum, based on month,=0A> and chops it up in 6 partitions.=0A>=0A> The=
re are some quirks with partitioning, like it does not automatically=0A> pa=
rtition the data yet, which i think is strange, and something that you=0A> =
want (specially based on time). But if performance is a big issue at the=0A=
> moment then the manual partitioning is something worth it.=0A>=0A> Kind R=
egards=0A>=0A>=0A> On 11/02/2009 10:35 AM, || Sudhir Nimavat || wrote:=0A>>=
Thanks Rob for quick response..=0A>>=0A>> Will that give decent performanc=
e ?=0A>>=0A>> Note: There would be more read operations and slow write.. Th=
e table=0A>> is very simple with few columns only..=0A>>=0A>>=0A>>=0A>>=0A>=
>=0A>>=0A>> *Sudhir Nimavat*=0A>> *Senior software engineer. **=0A>> Quick =
start global PVT LTD.=0A>> Baroda - 390007*=0A>> *Gujarat, India=0A>>=0A>> =
Personally I'm always ready to learn, although I do not always like=0A>> be=
ing taught=0A>> *=0A>>=0A>>=0A>>=0A>> -------------------------------------=
-----------------------------------=0A>> *From:* Rob ..com>=0A>> *To:* || Sudhir Nimavat || =0A>> *Sent=
:* Mon, 2 November, 2009 3:01:39 PM=0A>> *Subject:* Re: 50M records each ye=
ar.. how to handle=0A>>=0A>> On 11/02/2009 10:27 AM, || Sudhir Nimavat || w=
rote:=0A>> > I have come across a requirement where I need to store a very =
large=0A>> amount of data in a table.=0A>> > In one of our app.. we can hav=
e around 50 Million records each=0A>> year.. Can any one guide me in choosi=
ng a strategy than can handle=0A>> this load.=0A>> >=0A>> >=0A>> >=0A>> >=
=0A>> > Thanks=0A>> > SN=0A>> >=0A>> >=0A>> >=0A>> >=0A>> > Sudhir NimavatS=
enior software engineer.=0A>> > Quick start global PVT LTD.=0A>> > Baroda -=
390007=0A>> > Gujarat, India=0A>> >=0A>> > Personally I'm always ready to =
learn, although I do not always like=0A>> being taught=0A>> >=0A>> >=0A>> >=
Try the new Yahoo! India Homepage. Click here.=0A>> http://in.yahoo.c=
om/trynew=0A>> >=0A>> Consider vertical partitioning with seperate storage =
devices=0A>>=0A>>=0A>>=0A>> -----------------------------------------------=
-------------------------=0A>> Add whatever you love to the Yahoo! India ho=
mepage. Try now!=0A>> hoo.com/trynew>=0A>> --=0A>> This message has been scanned for viruses and=
=0A>> dangerous content by *MailScanner* , an=
d is=0A>> believed to be clean.=0A>=0A>=0A> The INTERNET now has a pe=
rsonality. YOURS! See your Yahoo! Homepage.=0A> http://in.yahoo.com/ -=
- =0AMySQL General Mailing List=0AFor list archives: http://lists.mysql.com=
/mysql=0ATo unsubscribe: http://lists.mysql.com/mysql?unsub=3Dsudhir543-=
nimavat@yahoo.com =0A From cricket scores to your friends. Try th=
e Yahoo! India Homepage! http://in.yahoo.com/trynew
--0-1971826480-1257419048=:99568--

Re: Fw: 50M records each year.. how to handle

Sudhir N wrote:
> Hi Johan
>
> Thanks, This is really helpful response...
>
> As I said.. there would be more read operations and slow write
> operation.. I may use Application level caching of data to reduce
> select queries..
> I am totally against of using manual partitioning as it would create
> lots of problems like primary keys, joins, clusters and replication
> etc..
> it would be difficult to migrate to another DB or upgrade mysql in
> future.. In future if we plan to upgrade mysql or use oracle than we
> wont be able to use built in partitioning support without modifying the
> application code because application knows about and handles partitions
>
> However my biggest concern is, Will mysql 4.1 be able to handle such a big table? around 50M each year so threre can be 250M records after five years..
>
What forbids you to upgrade to a newer version right now? If there is a
cost for you to add another machine i think the amount of time you are
investing now in finding a solution other than upgrading (that you
mention you will do anyway in the future) just grows.
And if you cannot afford another mashine for some reason you could
always install multiple versions of MySQL on the same box and slowly
migrate existing data to the new version.
And if your DBA if the decider he clearly is not doing his job in
assisting you.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Fw: 50M records each year.. how to handle

--0-2021836092-1257423067=:74736
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

> What forbids you to upgrade to a newer version right now? Bec=
ause I am not the person who take this decision.. I can suggest but its not=
in my hand to decide.. I understand that Its taking more time thn what It =
would take to upgrade.. But this is how things work.. Thanks=0ASN=0A=
=0A =0A =0APersonally I'm always ready to learn, although I do not =
always like being taught=0A =0A_______________________________=
_=0AFrom: Jay Ess =0ACc: Mysql >=0ASent: Thu, 5 November, 2009 4:58:29 PM=0ASubject: Re: Fw: 50M records e=
ach year.. how to handle Sudhir N wrote:=0A> Hi Johan=0A> =0A> Thanks,=
This is really helpful response...=0A> =0A> As I said.. there would be mor=
e read operations and slow write=0A> operation.. I may use Application leve=
l caching of data to reduce=0A> select queries..=0A> I am totally against o=
f using manual partitioning as it would create=0A> lots of problems like pr=
imary keys, joins, clusters and replication=0A> etc.. it would be difficult=
to migrate to another DB or upgrade mysql in=0A> future.. In future if we =
plan to upgrade mysql or use oracle than we=0A> wont be able to use built i=
n partitioning support without modifying the=0A> application code because a=
pplication knows about and handles partitions=0A> =0A> However my biggest c=
oncern is, Will mysql 4.1 be able to handle such a big table? around 50M ea=
ch year so threre can be 250M records after five years..=0A> =0AWhat forbi=
ds you to upgrade to a newer version right now? If there is a cost for you =
to add another machine i think the amount of time you are investing now in =
finding a solution other than upgrading (that you mention you will do anywa=
y in the future) just grows.=0AAnd if you cannot afford another mashine for=
some reason you could always install multiple versions of MySQL on the sam=
e box and slowly migrate existing data to the new version.=0AAnd if your DB=
A if the decider he clearly is not doing his job in assisting you. =0A=
=0A-- MySQL General Mailing List=0AFor list archives: http://lists.mysql.co=
m/mysql=0ATo unsubscribe: http://lists.mysql.com/mysql?unsub=3Dsudhir543=
-nimavat@yahoo.com =0A Yahoo! India has a new look. Take a sneak =
peek http://in.yahoo.com/trynew
--0-2021836092-1257423067=:74736--

Re: Fw: 50M records each year.. how to handle

Johan De Meersman tuxera.be> writes:

>
> I did some quick math, and it comes down to 1.5 records per second on
> average. Even with peak loads, this is doable without the annoying
> manual partitioning, I think.

A major benefit of partitioning is the ease of maintenance (running
operations like optimize/mysqlcheck).

Coming to the original question -

>
> On 11/5/09, || Sudhir Nimavat || yahoo.com> wrote:
> > Ok,
> >
> > Now the problem is.. currently we are using mysql 4.1 and it is not possible
> > to upgrade mysql at this time.. as far as I know mysql 4.1 does not support
> > partitioning.. So the only solution would be to use manual partitioning and
> > handle it through code..

Look into the "merge table" storage engine
.
Your app will still need to handle to job of creating and loading the
underlying tables, but selects/deletes/updates be transparent. This is
the closest that you can come to partitioning with 4.1, unless you can
rewrite your app to handle sharding.






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Fw: 50M records each year.. how to handle

--0-910796970-1257440020=:62857
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi Raj Shekhar, Thanks for suggestion.. This could be a solution=0AIf =
we can not upgrade thn Merge tables could be a solution.. =0ABut app will n=
eed to create a new table every year and alter merge table definition.. =0A=
However I doubt that merge table would give a decent performance for Select=
operaton.. Thanks =0ASN =0A_______________________=
_________=0AFrom: Raj Shekhar =0ATo: mysql@lists.my=
sql.com=0ASent: Thu, 5 November, 2009 9:41:05 PM=0ASubject: Re: Fw: 50M rec=
ords each year.. how to handle Johan De Meersman tuxera=
..be> writes: > =0A> I did some quick math, and it comes down to 1.5 re=
cords per second on=0A> average. Even with peak loads, this is doable witho=
ut the annoying=0A> manual partitioning, I think. A major benefit of =
partitioning is the ease of maintenance (running=0Aoperations like optimize=
/mysqlcheck). Coming to the original question - > =0A> On 11/5/09=
, || Sudhir Nimavat || yahoo.com> wrote:=0A> > Ok,=0A>=
>=0A> > Now the problem is.. currently we are using mysql 4.1 and it is no=
t possible=0A> > to upgrade mysql at this time.. as far as I know mysql 4.1=
does not support=0A> > partitioning.. So the only solution would be to use=
manual partitioning and=0A> > handle it through code.. Look into the =
"merge table" storage engine=0A e-storage-engine.html>.=0AYour app will still need to handle to job of crea=
ting and loading the=0Aunderlying tables, but selects/deletes/updates be tr=
ansparent. This is=0Athe closest that you can come to partitioning with 4.1=
, unless you can=0Arewrite your app to handle sharding. =
=0A-- =0AMySQL General Mailing List=0AFor list archives: http://lists.mysql=
..com/mysql=0ATo unsubscribe: http://lists.mysql.com/mysql?unsub=3Dsudhir=
543-nimavat@yahoo.com =0A Keep up with people you care about with=
Yahoo! India Mail. Learn how. http://in.overview.mail.yahoo.com/connectmor=
e
--0-910796970-1257440020=:62857--

Re: Fw: 50M records each year.. how to handle

yahoo.com> writes:

> But app will need to create a new table every year and alter merge
> table definition..

Right. ALTER table on a merge table is a fast operation. A MERGE
table need not maintain an index of its own because it uses the
indexes of the individual tables. As a result, MERGE table collections
are very fast to create or remap.


> However I doubt that merge table would give a decent performance for
> Select operaton..

I cannot comment on the above statement without seeing your table
indexes and your select queries. If you use optimal indexes, you will
have fast selects.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Fw: 50M records each year.. how to handle

--0-1269304602-1257442385=:63136
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

As I said on my earlier message... Table structure would be some thin=
g like - message_folders=0A-- id PK=0A-- user_id (FK) - Indexed=0A-- =
name varchar2=0A-- created_at timestap - Conversation=0A-- id PK=0A-- =
folder_id (FK) Indexed =0A- messages=0A-- id PK=0A-- author_id (FK)=
=0A-- Recipant (FK)=0A-- Subject varchar2=0A-- Body varchar2=0A-- attchment=
_name=0A-- author_deleted boolean=0A- Recipant_deleted boolean=0A-- date_s=
ent timestamp - Conversation_messages=0A-- conversation_id Indexed=0A=
-- message_id - unique =0A =0A =0ASudhir NimavatSenior software e=
ngineer. =0AQuick start global PVT LTD.=0ABaroda - 390007=0AGujarat, India=
Personally I'm always ready to learn, although I do not always like b=
eing taught=0A =0A________________________________=0AFrom: Raj=
Shekhar =0ATo: mysql@lists.mysql.com=0ASent: Thu, =
5 November, 2009 10:58:34 PM=0ASubject: Re: Fw: 50M records each year.. how=
to handle yahoo.com> writes: > But app w=
ill need to create a new table every year and alter merge=0A> table definit=
ion.. Right. ALTER table on a merge table is a fast operation. A MER=
GE=0Atable need not maintain an index of its own because it uses the=0Ainde=
xes of the individual tables. As a result, MERGE table collections=0Aare ve=
ry fast to create or remap. =0A> However I doubt that merge table woul=
d give a decent performance for=0A> Select operaton.. I cannot comment=
on the above statement without seeing your table=0Aindexes and your select=
queries. If you use optimal indexes, you will=0Ahave fast selects. =
-- =0AMySQL General Mailing List=0AFor list archives: http://lists.my=
sql.com/mysql=0ATo unsubscribe: http://lists.mysql.com/mysql?unsub=3Dsud=
hir543-nimavat@yahoo.com =0A Now, send attachments up to 25MB wit=
h Yahoo! India Mail. Learn how. http://in.overview.mail.yahoo.com/photos
--0-1269304602-1257442385=:63136--

Re: Fw: 50M records each year.. how to handle

Sudhir,


sudhir543-nimavat@yahoo.com wrote:
>=20
> > What forbids you to upgrade to a newer version right now?
>=20
> Because I am not the person who take this decision.. I can suggest =
but its not in my hand to decide.. I understand that Its taking more =
time thn what It would take to upgrade.. But this is how things work.=
..
>=20

MySQL version 4.1 is past its "end-of-life", you will not get any
updates and bug fixes for it, whatever the severity of the issue may =
be
(including security bugs).

Your question sounded like you are introducing some new application.

Doing this on a base whose service life has expired is not just silly=
,
it is plain wrong and would (if any problems arise) be considered
"grossly negligent".
I don't know what your local legal rules are, but in several parts of
the world a company doing this would put themselves at great risks.

I can only advise you (and your decision powers) *extremely* stongly =
to
use a version which is released for production purposes and in active
support.
Currently, this would be MySQL 5.0 and 5.1, but 5.0 will also soon re=
ach
its end-of-life, so the next upgrade would be imminent.

You (and your decision makers) should do the right thing for your
organization and start this using 5.1!


Regards,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
(+49 30) 417 01 487
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Fw: 50M records each year.. how to handle

--0-1553350797-1257444776=:42553
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Joerg, I know that.. I am not developing a new system but adding a =
new module to an existing application which is based on 4.1. And as I said =
earlier.. its not only me who can take decision of upgrading... otherwise =
I would prefer to upgrade instead of seeking a solution that isnt really a =
solution.. Thanks=0ASN =0A =0A =0APersonally I'm always =
ready to learn, although I do not always like being taught=0A =
=0A________________________________=0AFrom: Joerg Bruehe COM>=0ATo: sudhir543-nimavat@yahoo.com=0ACc: Mysql =
=0ASent: Thu, 5 November, 2009 11:39:11 PM=0ASubject: Re: Fw: 50M records e=
ach year.. how to handle Sudhir, =0Asudhir543-nimavat@yahoo.com w=
rote:=0A> =0A> > What forbids you to upgrade to a newer version right now?=
=0A> =0A> Because I am not the person who take this decision.. I can sugges=
t but its not in my hand to decide.. I understand that Its taking more time=
thn what It would take to upgrade.. But this is how things work..=0A> =0A=
=0AMySQL version 4.1 is past its "end-of-life", you will not get any=0Aupda=
tes and bug fixes for it, whatever the severity of the issue may be=0A(incl=
uding security bugs). Your question sounded like you are introducing s=
ome new application. Doing this on a base whose service life has expir=
ed is not just silly,=0Ait is plain wrong and would (if any problems arise)=
be considered=0A"grossly negligent".=0AI don't know what your local legal =
rules are, but in several parts of=0Athe world a company doing this would p=
ut themselves at great risks. I can only advise you (and your decision=
powers) *extremely* stongly to=0Ause a version which is released for produ=
ction purposes and in active=0Asupport.=0ACurrently, this would be MySQL 5.=
0 and 5.1, but 5.0 will also soon reach=0Aits end-of-life, so the next upgr=
ade would be imminent. You (and your decision makers) should do the ri=
ght thing for your=0Aorganization and start this using 5.1! =0ARegards=
,=0AJörg -- =0AJoerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.CO=
M=0A (+49 30) 417 01 487=0ASun Microsystems GmbH, Komturstr=
aße 18a, D-12099 Berlin=0AGeschaeftsfuehrer: Thomas Schroeder, Wolfgang=
Engels, Wolf Frenkel=0AVorsitzender des Aufsichtsrates: Martin Haering =
Muenchen: HRB161028 =0A The INTERNET now has a personality. YOUR=
S! See your Yahoo! Homepage. http://in.yahoo.com/
--0-1553350797-1257444776=:42553--