as my DB is growing fast i will have to split it into more databases. my Oracle friend said that he can do it on Oracle easly and then just say:
x = database1_part1 + database1_part2
and then he is using x and Oracle knows how to handle it.
any such thing for MySQL? if not: what should i do before i reach 2GB?
thanks!
It depends on what table types you are using and how your data is distributed between the various tables. It is totally different on Oracle but it is certainly possible to do.
If this is really going to be a problem then I would suggest moving to InnoDB tables as the tablesize is unlimited. You can create table-spaces that span multiple files and if you want, multiple physical devices.
What OS are you using and what version and binary of MySQL. From what I remember, apart from a few exceptions, you can have unlimited table sizes with MyISAM since v.3.23.
Hope this helps 
wow
so youre saying i can tell MySQL that one part of the DB is on server1 and another on server2?
and when i make a SELECT query it will look into both DBs as they were one database?
what about if one server dies? he says that the DB is not accessible?
With InnoDB you can spilt the data-files into parts wich exist on different HARDDISKS, you cannot split the data and put two parts on different machines.
Splitting the datafiles into smaller parts has only one purpose: You can put the parts on different disks, which means you can read the data at higher speed.
There is no point in splitting the data into two databases, because you still have to query all the rows, the total number of rows does not change, so neither does the speed!
If anything, the split database will be slower because you now have yto query two databases instead of just one.
i dont understand this...
let me ask you this way: my table will be growing up constantly. lets assume i have 10GB hard disk and my table will go up to 50GB. can i split the database/table to 5 machines, each holding 10GB of the database?
did you want to say that i CAN split the database but i CANT split a table?
thank you!
No. You can split the db/tables across multiple hard disks. It sounds like you should be using a bigger hard-disk(s), not trying to split the db.
I suppose you could split the db across multiple servers using clusters. Can do it with Oracle easily but never done it with MySQL.
Try posting the same question on Devshed's MySQL forum. Loads of gurus there.
hi,
you say your database is growing really, really fast and you want it to scale better. How is the performance of the database when it is a couple GB's?
Also approxamilty how hard is the database getting hit?
Here are some ideas that i have which might help you. If all the data does not need to be live you might want to create a script which dumps older records and haves then somewhere.
You could manually select tables and move then yo differient servers but this would be a pain. If you don't mind me asking what is this application for and why is it growing so fast?
thanks
- justin
no i dont mind of course not.
it is a search engine with Affiliates and Advertisers and it must log lots of things. for each click: date/time, id, unique click, ip, keywords, search id, advertiser id, affiliate id.... and 20 fields more.
+ i must globally save all the searches, dates, keywords, etc.
believe me - a lot of data. and i cant archive anything... all the data must be available.
There are more things you can do to speed up a query than just reducing the number of records.
What is your table structure, and what kind of queries do you run against it?
i know that there are things that can be done.. But if the database is 10GB and growing and you run out of HD space.. I think you are fighting a loosing battle.
Ofcourse I agree with that 
But nowadays one doesn't run out of diskspace at 10GB, more like 100GB. Both mysql and PostgreSQL boast that they can handle at least 60GB in a single database. Speed is more often an issue than storage capacity.
I'd like to know the data structure because it may be possible to reduce the row-size, and therefore the database size.
yeah. i agree with that. it would be nice to see if the database has some level of normalization.
no, it not the point that you save 5% of the space now. even 60GB doesnt help. okay, ill ask this way now so you will know what i am asking:
largest disks on the market are 10GB disks, one disk per PC. and my database is 9GB and growing. in next month it will go up to 22GB.
how do i split the database to more parts so i can "put" it on 3 PC's and still do "SELECT * FROM <table>" so that MySQL will know to perform this select over 3 places [3 parts which represents 1 DB].
it is possible - yes or no and how?
thats what i need...
thanks
hi,
if you have ATA drives then the drive size is NOT 10GB.. you can get drives that are 120GB as of recent. But you can also go and get a RAID card that will allow you to chain these disks into an array so that you can have 4 x 120 GB drives and depending on the RAID level yield as much as 480GB.
You can do that same with SCSI but i don't think the drive size's are the same.
I think that using a RAID will save you tons of money rather then buying a cluster of PC's and using mysql. But if you really want to use a PC and split the database to a cluster of computer i think you can do this with a new oracle product.
please forget disks and sizes. i was not asking because i didnt know we have 120GB disks today. i was asking because I NEED TO KNOW HOW TO SPLIT THE DB TO MORE PC's.
and split the database to a cluster of computer i think you can do this with a new oracle product.
what about MySQL?
" I NEED TO KNOW HOW TO SPLIT THE DB TO MORE PC's. "
No you don't, you are asking the wrong question here.
YOu defineately do NOT want to split the data over multiple servers.
The only thing you want is to increase the diskspace and decrease the amount of used diskspace.
Splitting over more PC's will certainly NOT make your queries run any faster. The ONLY thing that makes your queries run faster is optimizing the database-model and reducing the amount of recorddata.
With a modern IDE controller you can already create RAID arrays using 180GB (yes, 180G
disks. Add 4 of those to your server and you have 720GB of diskspace.
If the queries run too slowly there are only a few things that help: optimize the datamodel, reduce the amount of data, increase the CPU capacity.
YOu defineately do NOT want to split the data over multiple servers.
yes this is exactly what i want.
i didnt say my queries are too slow and i want to speed them up this way - you didnt read my questions. i was only asking how to SPLIT the whole thing not how to speed it up.
so my questions is still open - split, split, split. i know about optimization, indexes, big and fast disks, etc.
but i want to split the DB!
I did read your question, let me remind you of what you asked:
"as my DB is growing fast i will have to split it into more databases."
"if not: what should i do before i reach 2GB?"
So you want to split it because you think that will somehow solve your problem of low diskspace.
And what we are telling you is that you have no problem at all. With modern hardware you can expand well into the terabytes without splitting the database.
Splitting a database is never a solution. Especially not if you still need to query the entire database.
Oracle can cluster, but only to improve performance. More CPU's = more speed. Oracle doesn't cluster to add diskspace, that is just a fortunate side effect of adding more servers.
we are getting closer :-)
1) so youre saying there is never a need to split the DB because of its size [lets forget performance now]?
2) how do "they" handle really large databases which wont fit on "modern" hardware? if you cant afford something that looks-like-a-fridge monster with 100xxxxx terabytes disks, what should you do? how does Yahoo! do such things? - i understand everything if they keep whole DB on one "fridge" and use load balancers but somehow i thought they might split the whole thing ~ okay, maybe a stupid idea for someone like Yahoo! who can easily buy such monster computers...
i appreciate all the answers - thats the only way we can learn something.
"1) so youre saying there is never a need to split the DB because of its size [lets forget performance now]? "
Only if you are running into the limit of what your database/OS can handle.
There used to be a 2GB filesize limit for linux, but that has changed a lot over the past years. And even if there was still a 2GB limit, InnoDB lets you create tablespaces which mysql will use to store it's data in. All those spaces can be <2GN, but combined under mysql you can have for example 100*2GB = 200GB.
"how do "they" handle really large databases which wont fit on "modern" hardware? "
No offence, but regarding your recent remark on 10GB being the largest disk, I think you may want to take a short tour of modern hardware, just to get up to speed with sizes and prices of the new 'good stuff'.
Modern hardware includes daisy-chainable fiber-optic SCSI arrays that can range well into the hunderds of terabytes, yet the OS just sees one disk.
If you have cash for an industial size database, you'll also have cash for an industrial size server.
" if you cant afford something that looks-like-a-fridge monster with 100xxxxx terabytes disks, what should you do? "
That's a bit of a nasty question because if you need more space you'll have to get it. If not one-way, then another way. The simplest and best way is to just add disks, and with modern prices and equipment this is nolonger a real problem.
Ofcourse, as I stated before, if you are consuming a lot of space, it is always worth it to get a second opinion about your database model. There's always some little thing you forgot about that is eating away at your space.
"somehow i thought they might split the whole thing"
They might, but only on a functionality level.
For exampl, if you are loggin stats for ten big cusomers that put in sh*tloads of records, it may be worth it to split that up into ten tables, so the number of records per table stays as low as it can be. But that only works if you never want to query all the data at the same time, which I believe you do want to do in your case.