How big is too big for a MySQL table?

Question

I was finally convinced to put my smaller tables into one large one, but exactly how big is too big for a MySQL table?

I have a table with 18 fields. Some are TEXT, some are short VARCHAR(16), others longer VARCHAR(100).

Right now we get about 200,000 rows a day, which would be 6 million+ a month. How big is too big? Does it matter how many fields you have, or just rows?

Answer 1

There's not a great general solution to the question "How big is too big" - such concerns are frequently dependent on what you're doing with your data and what your performance considerations are.

There are some fundamental limits on table sizes. You can't have more than 1000 columns. Your records can't be bigger than 8k each. These limits change depending on database engine. (The ones here are for InnoDB.)

It sounds like you've merged several different data sets into one table. You probably have some fields that tell you what data set this record pertains to, along with some data fields, and some timestamp information. That's not a very wide record (unless you're logging, say, all the input parameters of each request.) Your main problem will be with selectivity. Indexing this table in a meaningful way will be a challenge. If your common fields can be selective enough that you can use them to get to the records you want without consulting the table, that will be a huge plus. (Cf. table scan)

For that many records per day (basically, two a second all day, and I'm presuming you have a peak-load period where it's much higher), you'll also want to make sure that you specifically look at optimizations on improving insertion speed. As a general rule, more indexes = slower insertions. If you can, consider archiving off outdated records to another table entirely. In prior workplaces, we've used an archival strategy of Last Month, Prior Three Months, Prior Six Months, each in separate tables. Another idea is to delete older records. Many environments simply don't need information beyond a certain date. Hanging on to logging records from three months ago is often overly expensive.

Finally, don't neglect the physical storage of your table. The thinner your records are, the less physical IO needs to occur to read (or for that matter, to insert) a record. You can store your indexes on a separate physical hard drive. If there's a lot of redundant data in your records storing the table compressed might actually be a speed increase. If you have a little cash to burn, consider the value of a good RAID array for striping your data.

So, to answer your basic question: it's a lot of records, but with a careful eye towards tuning, it won't be a problem.