How big is your… database?
Database, MySQL May 22nd, 2007 - 3,144 viewsI was just looking over some statistics for the back-end MySQL database for this site and another project I’m involved with. I was impressed to discover the server had executed well over 100,000,000 queries since it started 68 days ago (well, 118,565,844 to be exact). That’s over twenty queries per second, for more than two months, without a hitch. Talk about enterprise level.
This reminded me of a conversation I overheard (and interrupted) between the head DBA and the President of a certain Bay Area startup (that shall remain nameless) regarding MySQL. The DBA was trying to convince the President of the company to switch from MySQL to Oracle. Being a proponent of open source solutions, and a long-time MySQL user, I asked the DBA why he wanted to switch. He said it was a scalability issue — the largest tables in their database were quickly approaching one billion rows, and he was concerned that MySQL wouldn’t be able to handle it.
My first reaction was “so what.” It’s not like there is some arbitrary limit on the number of rows in a table. The only table size limits I know of are due to limitations of the underlying operating system. And as far as I know there are no limits on the number of rows in a table, only on the database’s total size on disk. But the DBA was persistent, and claimed he had been told by the CEO of MySQL AB that there were no production MySQL databases with tables over one billion rows. If the CEO said that, then who was I to argue? So I left it at that… But now, from the safety of my own soapbox, I’m wondering: how big is your database?
May 23rd, 2007 at 7:38 am
Mines currently 102,901,564 Queries in 55 days!
May 23rd, 2007 at 11:41 am
The biggest database on the server I mentioned in the article comes in at 346MB on disk. It has 35 tables, the largest of which holds tagging information and, with 1,219,074 rows, takes up 132MB.
So, it’s not huge, but it’s big enough to keep things interesting.
May 24th, 2007 at 10:24 pm
I worked for a company around 6 months ago, the had a PHP based application that clients used to make and send newsletters etc. It was 7.6Gb in size and was growing at around 100mb a week. During the time i worked there, the only problems encountered were to do with bad design of the database itself rather than MySQL issues
May 25th, 2007 at 2:33 am
Why not test? That’s the best way to find out the limits of MySQL on a particular system.
May 25th, 2007 at 12:44 pm
Yea, testing would provide a lot of useful data. But nothing compares to the real thing. If somebody has a production system that exceeds one billion rows there would be no argument.
May 26th, 2007 at 2:58 am
Are you saying that a production system with over 1B rows can’t be duplicated for testing?
I’m pretty sure it can be done. There are companies specialized in testing software (and therefore setting up such environments, including web/sql traffic ect.). So even if it’s not an option to test inhouse it can still be done by others.
May 29th, 2007 at 2:20 am
No. I’m absolutely certain that a system with 1B rows _can_ be duplicated for testing. I’m also certain that a realistic load would be nearly impossible to duplicate. It’s not the data that’s hard to scale for testing, it’s the load… particularly when we’re talking about a completely hypothetical system.
Here’s the bottom line: one production system is worth a thousand tests. I could produce test results all day long, but a single production system would prove my point: MySQL can handle a table that’s larger than 1 billion rows in a production environment.
June 4th, 2007 at 1:08 pm
Running comparsion site on mysql:
I don’t think thats bad at all query are quick considering the amount of records. I have heard that
some large database use Clucene to return results from mysql database file how true this is i don’t know.
Table Type: MyISAM
Rows 1016468
Data Length 420MB
Testing SELECT on FULL TEXT index:
0.033161878585815
August 7th, 2007 at 8:59 pm
my database size is about 12 MB,
August 18th, 2007 at 7:33 pm
There are MySQL databases with well over a billion rows.
According to this article, there are MySQL databases with 20 billion rows.
http://www.sqlsummit.com/Articles/MySQL5.htm
I think that you’d probably need more than a single MySQL server to handle the load though. Depends on what ratio of reads to writes you have.
October 24th, 2007 at 2:35 pm
What a bunch of nonesense. At Compete.com we had tables with > billion rows back in 2001 running MySQL 3.23 on FreeBSD. If I remember correctly our largest database back then approached > 1 Terabyte.
Back in the 3.2.x days this was not easy, it required a lot of horizontal table partitioning (by date) back before this sort of thing was common.
February 20th, 2008 at 4:38 am
Well, I know im a little bit late, but still I’m feeling my data could interest you :D
Just found your blog and like your style… looking forward to your next post. :)
Right at the moment, the server is running 28 days and has done 151,667,235 queries. That are 61 Queries/Second.
The database itself is quite small, about 200 MB.
The server is Webserver as well as Database-Server; 3000+ with 2 GB of ram. Ilding on ~0.3%. Tell the DBA that he should give up his job..
February 25th, 2008 at 4:35 am
Maybe the main concern here is about MySQL Load Balancing / Replication of MySQL Database server. And the most important thing is database design, what kind of application we need to build. We can separate the information onto several tables. And always use index on the most accessed fields.