Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite


We are building a service witch require a huge database. We are actually planning on getting more than 100 millions rows in our biggest table.

So, what are the best options to store all this data in our database and afterward access to this data (search in the database) ?

No need to say that it must be fast.

Thanks.

share|improve this question
Perhaps add an example row and query? – Andomar Jun 24 '12 at 16:09
4  
100 million rows is not considered "huge" these days. – a_horse_with_no_name Jun 24 '12 at 16:20
At least he does not say one million. Dude, the last project I was working on was "hugh". 21.000gb hugh database. With one table addin around 80 million rows DAILY - and keeping some YEARS of daily data in that table. 100 millions is not tiny, is not necessarily small, but saying that is hugh is an insult to the people dealing with large databases today. This is not 1995 or so. THEN my first large db had 10gb (on a server with 128mb). – TomTom Jun 24 '12 at 16:27
@TomTom and you did use mySQL for that DB? – Nils Jun 24 '12 at 21:06
No, that was an Oracle ExaData cluster - tsort of an appliance, comes with hardware. THe price is - ah - "ferrari level", and we had 3 of those clusters (development, production, backup). – TomTom Jun 24 '12 at 22:39
show 3 more comments

migrated from serverfault.com Jun 24 '12 at 16:05

2 Answers

up vote 3 down vote

100 million rows in a table does not make a database "huge" by today's standards

This is also not MySql related in most ways - because you leave pretty fat the server software level and talk hardware.

The standard answer to this is that it all depends on your workload profile. Is it transactional, reporting, OLAP?

You mentioned that your largest table is 100 million rows. How does this compare to the other tables? Are they much smaller, or about the same size?

You may want:

  • As much RAM as you can. It is nice that servers these days should be able to load this size table in memory, isn't it?

  • As much disk performance as you can get.

This is where special hardware gets into the game fast. The last project I did in this area had 3x768gb flash disks with write back cache and 2x96gb with 48 cores each. However, this was an Oracle RAC and probably way beyond what MySQL could handle.

Anyhow, your best bet would be to go SSD for the IO side, memory for the buffer side, and with something in the 100 million row range you should be able to keep a significant part of that in memory. Using a 64gb server and 48gb buffer for that table I get 480 bytes per row (table, index). That wont be exact, but it gives you an idea that you can easily (64gb servers are quite cheap) fit the data in memory.

Here is a hint: Databases are not large when the fit in memory. They are large when you can not fit them into decent server hardware.

share|improve this answer
Ok. My bad for the huge. Database isn't really my field. It's so far the biggest database i had to deal with. The others tables are pretty small. Only couple hundreds row and almost nothing inside. There is only this big table. Who is going to grow daily as well. Probably 20 000 row a day. My knowledge is actually pretty bad about DB, so sadly i don't understand this line: "Sadly, it all depends. Is it transactional or reporting / data warehouse?" – bl0b Jun 24 '12 at 17:04
Transactional / tons of updates id different than analytical (tons of groupings for reporting). One is normally disc limited, the other normally more ram / Processor limited. – TomTom Jun 24 '12 at 17:12
up vote 1 down vote

Since the speed of any query against this database will depend very much on what search values you put into the query, one option could be creating a simple setup with a mysql database, then generating some testdata which are similar to the real data, creating a few typical queries and also the heaviest queries you can imagine and just try it out. you need this anyway of you need to decide between different database systems down the road.

then follow up any suggestions in from mysql tools about indexing etc.

i guess in your situation i would actually wanted proof that a simple mysql database did NOT solve the problem before starting to look at non free options.

there are also other free options, like Postgres.

both of these would normally have no problems with 20000 rows added every day.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.