Hi,


I have setup a MYSQL database to log web traffic traffic visits.

The daily records added to the DB is about 100K, the database is increasing size very rapidly and thus i wanted to ask how many records a MySQL Db can hold...
FYI, each record has no more than 100 Characters. It increases in size about 5MB per day.

And yes, theres nothing to worry about the host, as i am the host myself. This MySQL DB is running on one of my VPS's.

Any comments would be highly appreciated.

By referencing the documentation at http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html


There is a limit of 232 (~4.295E+09) rows in a MyISAM table. If you build MySQL with the --with-big-tables option, the row limitation is increased to (232)2 (1.844E+19) rows. See Section 2.4.14.2, “Typical configure Options”. Binary distributions for Unix and Linux are built with this option.

When creating the table, i used the default settings in PhpMyAdmin.. the table is MyISAM for sure.. but how do i lookup if its a --with-big-tables ??

Is there any reason why you can't follow the link I gave you and look on the MySQL site?

At either build option, you're likely to hit your VPS disk or CPU limit before MySQL will run out of steam.

Growing at 100K records a day is quite acceptable. That's 36 mio rows per year. We have several tables with >300 million rows, and even mySQL 3 can handle it just fine (albeit we use fixed-length rows). With proper index it's also very manageable. Examples:

Example #1 -- many small rows:
Server: mySQL 3.23.58
Hardware: Quad Intel(R) Xeon(TM) CPU 2.80GHz
RAM: 4 GB
Storage: 300 GB RAID 5

Type: MyISAM, Fixed
Rows: 333,076,571
Avg Length: 30 bytes
Data length: 9,992,297,130 bytes
Max data length: 128,849,018,879 bytes
Index: 5,250,556,928 bytes

mysql> select * from table where gene_id_a=5555;
...
467 rows in set (0.42 sec)

This particular server has very little writing and >99% reading; data is loaded at once and stays.


Example #2 -- fewer large rows:
Server: mySQL 5.0.24a-standard
Hardware: Dual, Dual Core AMD Opteron(tm) Processor 285
RAM: 8 GB
Storage: 6.4 TB RAID 5 (16 disks)

Type: MyISAM, Dynamic
Rows: 27,561,214
Avg Length: 9404 bytes
Data length: 258,014,518,796 bytes
Max data length: 281,474,976,710,655 bytes
Index: 283,924,480 bytes

mysql> select * from table where varid=8559454;
...
1 row in set (0.11 sec)

This server services 128 computation nodes and is hammered with >600 queries/second. Data in this table only grows by about 500,000 rows (4.5 GB) per day. The database is currently about 1.2 TB in size.

Perhaps these examples convince you that a decent (not not necessarily super monster) machine can handle the load. There is no need for a special version of mySQL either. However, a VPS won't cut it. Not for long.

Hey m8, thanks for the very satisfying answer.

I am pleased to see MySQL's handling power...
I do plan to remove stats that are over 1 month old (dorment). I think this will keep the DB to a limit ...

Thanks again..
Sahel.