Growing database & Performance

--0-762438831-1246029944=:90012
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I have a website where my database is continuously growing. And I started b=
eing worried about performance.
I have a couple of questions, and I would appreciate it very much if you ca=
n elaborate on them.

- I have about 70 tables in the same database.=A0 some of them are becoming=
very large (over 1 million record), and I guess in a couple of months some=
of them will be double in size, like the mailbox table. Would it be benefi=
cial to divide this database tables across different databases where each d=
atabase holds some tables?=20

- I was looking at MySQL table partition, and I would like to try it. I am =
not sure though what is the best way to do it, for example in my mailbox ta=
ble, I have "senderId" and "receiverId" as keys and I query inbox and outbo=
x using these keys.. If I want to paritition the table, what is the partion=
by method I should use? 

- do you think dividing my mailbox table into separate tables for inbox and=
outbox like: mailbox_inbox, mailbox_outbox, and mailbox_messages would be =
beneficial?


- I am also guessing that my mailbox table will be holding 10s of=0Amillion=
s of records in a year or two period.. and I am not sure about=0Athe best w=
ay to habdle such a table when it grows very much.. any ideas=0Aon how to p=
lan for such a senario?=20
I can imagine the best way would be to create many tables each holding a po=
rtion of the mailbox table while using MySQL partition on each of them... I=
am wondering though about the best way to map "senderId" and "receiverId" =
to the correct table

thank you
=0A
--0-762438831-1246029944=:90012--

Re: Growing database & Performance

At 10:25 AM 6/26/2009, you wrote:
>I have a website where my database is continuously growing. And I started
>being worried about performance.
>I have a couple of questions, and I would appreciate it very much if you
>can elaborate on them.
>
>- I have about 70 tables in the same database. some of them are becoming
>very large (over 1 million record), and I guess in a couple of months some
>of them will be double in size, like the mailbox table. Would it be
>beneficial to divide this database tables across different databases where
>each database holds some tables?
>
>- I was looking at MySQL table partition, and I would like to try it. I am
>not sure though what is the best way to do it, for example in my mailbox
>table, I have "senderId" and "receiverId" as keys and I query inbox and
>outbox using these keys.. If I want to paritition the table, what is the
>partion by method I should use?
>
>- do you think dividing my mailbox table into separate tables for inbox
>and outbox like: mailbox_inbox, mailbox_outbox, and mailbox_messages would
>be beneficial?
>
>
>- I am also guessing that my mailbox table will be holding 10s of millions
>of records in a year or two period.. and I am not sure about the best way
>to habdle such a table when it grows very much.. any ideas on how to plan
>for such a senario?
>I can imagine the best way would be to create many tables each holding a
>portion of the mailbox table while using MySQL partition on each of
>them... I am wondering though about the best way to map "senderId" and
>"receiverId" to the correct table
>
>thank you
>

Are you using MyISAM or InnoDb?

Mike


--
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: Growing database & Performance

1 million records is not a lot at all if you have it indexed right.
I've worked with systems where tables grew by 30 million records per
month. Although we only kept about 300 million records at any one
time.

It's not really the size or record count you need to worry about, but
the activity. You may end up with locking issues if you are using
MyISAM. Although if you are probably not doing a lot of deletes and
updates, just mainly inserts so it may not be able.
InnoDB won't have locking issues, but could have performance issues if
don't have enough memory.

You can use partitions or merge tables to split your data, but
splitting your tables into different databases isn't really going to
help you.

Splitting your mail into inbox and outbox may be helpful since you
inbox would probably only ever have records added to it. Whereas the
outbox would have a lot of activity (inserts and deletes).

Brent Baisley

On Fri, Jun 26, 2009 at 11:25 AM, fa so wrote:
> I have a website where my database is continuously growing. And I started=
being worried about performance.
> I have a couple of questions, and I would appreciate it very much if you =
can elaborate on them.
>
> - I have about 70 tables in the same database.=A0 some of them are becomi=
ng very large (over 1 million record), and I guess in a couple of months so=
me of them will be double in size, like the mailbox table. Would it be bene=
ficial to divide this database tables across different databases where each=
database holds some tables?
>
> - I was looking at MySQL table partition, and I would like to try it. I a=
m not sure though what is the best way to do it, for example in my mailbox =
table, I have "senderId" and "receiverId" as keys and I query inbox and out=
box using these keys.. If I want to paritition the table, what is the parti=
on by method I should use?
>
> - do you think dividing my mailbox table into separate tables for inbox a=
nd outbox like: mailbox_inbox, mailbox_outbox, and mailbox_messages would b=
e beneficial?
>
>
> - I am also guessing that my mailbox table will be holding 10s of
> millions of records in a year or two period.. and I am not sure about
> the best way to habdle such a table when it grows very much.. any ideas
> on how to plan for such a senario?
> I can imagine the best way would be to create many tables each holding a =
portion of the mailbox table while using MySQL partition on each of them...=
I am wondering though about the best way to map "senderId" and "receiverId=
" to the correct table
>
> thank you
>
>
>
>

--
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: Growing database & Performance

At 11:02 AM 6/26/2009, you wrote:
>Hi Mike,
>I am using MyISAM but I want to convert all tables to InnoDB.. but before
>I make the conversion I am trying to have a solid plan on how to handle
>everything

If it were me, I'd would create at least 2 tables, current and
archive. Since 95% of the activity will be with the current table, it will
be small and faster than trying to put all of the data into 1 table. If the
user wants to see the messages from more than a certain # of months (6mo or
1yr), then have a checkbox so he can display or search the archive. Every
month, copy the oldest month to the archive and delete it from the current
table. If you were using MyISAM you could use Merge tables for that and it
would handle this situation quite easily. Of course if you need
transactions, then you're going to have to use InnoDb.

Mike




>--- On Fri, 6/26/09, mos wrote:
>>
>>From: mos
>>Subject: Re: Growing database & Performance
>>To: mysql@lists.mysql.com
>>Date: Friday, June 26, 2009, 8:52 AM
>>
>>At 10:25 AM 6/26/2009, you wrote:
>> > I have a website where my database is continuously growing. And I
>> started being worried about performance.
>> > I have a couple of questions, and I would appreciate it very much if
>> you can elaborate on them.
>> >
>> > - I have about 70 tables in the same database. some of them are
>> becoming very large (over 1 million record), and I guess in a couple of
>> months some of them will be double in size, like the mailbox table.
>> Would it be beneficial to divide this database tables across different
>> databases where each database holds some tables?
>> >
>> > - I was looking at MySQL table partition, and I would like to try it.
>> I am not sure though what is the best way to do it, for example in my
>> mailbox table, I have "senderId" and "receiverId" as keys and I query
>> inbox and outbox using these keys.. If I want to paritition the table,
>> what is the partion by method I should use?
>> >
>> > - do you think dividing my mailbox table into separate tables for
>> inbox and outbox like: mailbox_inbox, mailbox_outbox, and
>> mailbox_messages would be beneficial?
>> >
>> >
>> > - I am also guessing that my mailbox table will be holding 10s of
>> millions of records in a year or two period.. and I am not sure about
>> the best way to habdle such a table when it grows very much.. any ideas
>> on how to plan for such a senario?
>> > I can imagine the best way would be to create many tables each holding
>> a portion of the mailbox table while using MySQL partition on each of
>> them... I am wondering though about the best way to map "senderId" and
>> "receiverId" to the correct table
>> >
>> > thank you
>> >
>>
>>Are you using MyISAM or InnoDb?
>>
>>Mike
>>
>>-- MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To
>>unsubscribe:
>>http://lists.mysql.com/mysql?unsub=faka3o@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: Growing database & Performance

>> Would it be beneficial to divide this database tables=20
>> across different databases where each database holds some tables?

If you are planning to scale to large amounts of database activity in =
the future then yes, this will help very much. If you split your tables =
into several logical databases and ensure there are no cross-database =
joins; in the future you will be able to scale by moving some of the =
logical databases onto separate physical hosts relatively easily.

Of course tuning your SQL/mysql configuration/operating system/indexes =
properly will afford you the best scalability rather than throwing =
hardware at it.

Cheers,

Andrew

-----Original Message-----
From: fa so [mailto:faka3o@yahoo.com]=20
Sent: 26 June 2009 16:26
To: mysql@lists.mysql.com
Subject: Growing database & Performance

I have a website where my database is continuously growing. And I =
started being worried about performance.
I have a couple of questions, and I would appreciate it very much if you =
can elaborate on them.

- I have about 70 tables in the same database.=A0 some of them are =
becoming very large (over 1 million record), and I guess in a couple of =
months some of them will be double in size, like the mailbox table. =
Would it be beneficial to divide this database tables across different =
databases where each database holds some tables?=20

- I was looking at MySQL table partition, and I would like to try it. I =
am not sure though what is the best way to do it, for example in my =
mailbox table, I have "senderId" and "receiverId" as keys and I query =
inbox and outbox using these keys.. If I want to paritition the table, =
what is the partion by method I should use? 

- do you think dividing my mailbox table into separate tables for inbox =
and outbox like: mailbox_inbox, mailbox_outbox, and mailbox_messages =
would be beneficial?


- I am also guessing that my mailbox table will be holding 10s of
millions of records in a year or two period.. and I am not sure about
the best way to habdle such a table when it grows very much.. any ideas
on how to plan for such a senario?=20
I can imagine the best way would be to create many tables each holding a =
portion of the mailbox table while using MySQL partition on each of =
them... I am wondering though about the best way to map "senderId" and =
"receiverId" to the correct table

thank you



=20

--
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