More than 500.000.000 rows

Wien

Hello,

I've got a very big issue to sort out with my mySQL DataBase

I will probably have more than 500.000.000 rows very soon

Do you think mySQL can deal well with that volume of data ?

Do you think I need something else than a DB ? like handle my data in many XML files ?

If somebody has already experienced that case I would be gratefull to heard about it

thank you very much

dhodge

Are all the rows being accessed daily? Could a Data Warehouse be built to store some of the older data?

bpat1434

500 million rows?

mySQL Forum::Jay Pipes wrote:
It's very tough to make an assertion on an issue like this where we have little knowledge at all about the underlying application, hardware, queries being run against the database, application requirements, etc. Sure, I have seen MySQL handle millions of inserts in a day (or 1M as your subject suggested). However, the exact load that the database server can take depends on a myriad factors, including hardware setup, the type of storage engines used, the average volume, network latency, other applications running on the server, application level code, the type of queries being run, and the competence of maintainers. I'm not sure if this is the answer you're looking for, but without much more detailed information, it is very difficult to say anything specific...

So it depends upon your system hardware. How much space can the mySQL server have? How much is able to be utilized?

Are you using a temp table to do all your data manipulation?

Those are teh types of questions you need to ask yourself. YOu can also read this forum:
MySQL + NAS(NFS or CIFS) + Huge Table Insert Problem (Long)

I need to insert roughly 15-40 Million rows a day

You need to check that MAXROWS is set large enough to handle the load as well....

MarkR

Obviously you should test the capacity of your database and hardware using synthetic data. That's the only thing I can suggest.

I'm sure MySQL has no inherent limit that stops you from adding new rows once you hit 500 million.

With 500 million rows, you'll need quite a lot of storage, so thing carefully about exactly how many columns you really need.

I've never used a database with quite that many rows in it, but I imagine it would work.

Mark

Plasma

Do you need all of those rows? If not, remove obsolete ones.

Thats way too many to be of any use.

Roger_Ramjet

For InnoDB tables "... The maximum tablespace size is four billion database pages (64T😎. This is also the maximum size for a table."

"With the MyISAM storage engine in MySQL 3.23, the maximum table size was increased to 65536 terabytes (2567 – 1 bytes). With this larger allowed table size, the maximum effective table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits."

Not going to be a problem there then. If your operating system can handle the file-size then mysql can use it.

In effect the maximum number of rows you can store in a table will be the maximum size of the primary key index - provided they do not exceed the total byte capacity of the physical file.

Making such a monster work fast is a whole other problem.