Re: How big is too big?
Misao schrieb: Our production databases here are really growing and getting to be rather big. The question on our minds is; when is a database or table just too big? We have a few 20-30GB-InnoDB-Tables (growing) without any problems (mysql 4.1.5gamma). The limits of mysql are somewhere in the terabyte-area I think, there is information on that in the manual. I assume your problem would probably be hardware/performance at some point. The machine that we are running that big database on is a dual-Opteron, 8gigs of RAM, 750GB RAID 1+0 SATA-Hotswap. no problems so far... nice piece of hardware ;) I have 2 or 3 tables that the MySQL Administrator can't even get a size on. It reports it as 0Bytes, but the little picture bar shows that these tables take up almost 1/3 of the database size. I think these tables could be as big as 8GB, but we have quite a few above 1GB. Seems like a problem of MySQL Administrator. Check if you use the newest version, else change your frontend or make a bug-report. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How big is too big?
The maximum single file size of a system is as follows, *Operating System* *File-size Limit* Linux 2.2-Intel 32-bit 2GB (LFS: 4GB) Linux 2.4 (using ext3 filesystem) 4TB Solaris 9/1016TB NetWare w/NSS filesystem8TB win32 w/ FAT/FAT32 2GB/4GB win32 w/ NTFS 2TB (possibly larger) MacOS X w/ HFS+ 2TB (cut and pasted from mysql.com per table size) and heres a nice tutorial on how to figure out your database size via php http://www.webmasterworld.com/forum88/2069.htm if you dont do php, its basicaly a script that says, SHOW TABLE STATUS; and then adds up the data_lenght index_lenght of each table within a database. Regards, Marcus Joyce Jan Kirchhoff wrote: Misao schrieb: Our production databases here are really growing and getting to be rather big. The question on our minds is; when is a database or table just too big? We have a few 20-30GB-InnoDB-Tables (growing) without any problems (mysql 4.1.5gamma). The limits of mysql are somewhere in the terabyte-area I think, there is information on that in the manual. I assume your problem would probably be hardware/performance at some point. The machine that we are running that big database on is a dual-Opteron, 8gigs of RAM, 750GB RAID 1+0 SATA-Hotswap. no problems so far... nice piece of hardware ;) I have 2 or 3 tables that the MySQL Administrator can't even get a size on. It reports it as 0Bytes, but the little picture bar shows that these tables take up almost 1/3 of the database size. I think these tables could be as big as 8GB, but we have quite a few above 1GB. Seems like a problem of MySQL Administrator. Check if you use the newest version, else change your frontend or make a bug-report. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How big is too big?
[snip] ...big... [/snip] We are running OpenBSD servers utilizing a dual Xeon processor architecture with a 1TB RAID. We have a database using MyISAM tables...here are some stats from phpMyAdmin on one database on this server -- 20 table(s) Sum 347,092,085 --149.6 GB Two tablea in this database -- tblClass10 128,310,773 MyISAM 40.3 GB tblClass11 205,420,404 MyISAM 97.4 GB We do experience delays in performing some complex queries, but even in these cases MySQL consumes less than 50% of server resources during processing. We have a set of complex queries that takes approximately 30-45 minutes when run each week, including UPDATES. Big is only determined by 2 things in my experience, physical resources and the ability to manage the database well. There are several companies (Yahoo comes to mind IIRC) that have MySQL databases far larger and more complex than the ones I use each day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How big is too big?
The problem isn't really if the database fits into RAM, it's if your index does, if your searches use the index. Databases can easily outgrow even the maximum RAM you could install on even a high end machine. So while it's ideal to fit your database in RAM, it's not always feasible. Your first big hit will be from an index not being able to be cached in RAM, then you require disk access for all your searches on that index. That said, you will most likely hit the limits of your database design before you hit the limit of MySQL. If your database data doesn't change much, just gets added to, you should look at dimensional database design as opposed to traditional relational design. This starts getting you into the realm of datawarehousing, which has different design rules. Two books that may be helpful are: High Performance MySQL from O'Reillly Designing Effective Database Systems from Addison Wesley The first book would be helpful for scaling MySQL, the second will be helpful for creating a scalable design. On Jan 27, 2005, at 7:08 PM, Misao wrote: Our database server has 4Gigs of ram on it, and we have a hard time of figuring out the true InnoDB settings on how to use that ram up. I ended up just increasing the ram used until it just wouldn't run anymore, then backed up and used that. We plan on adding another 4Gigs of ram, total of 8GB. Tweaking the InnoDB stuff doesn't seem as easy as the MyISAM side. So as it stands, we have 4GB, and one table that easily exceeds that, almost 8GB in size. Even after we bump the server up to 8GB, that means this table will barely fit. Does that mean we need to start cleaning out that table, or adding new ram? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How big is too big?
Our production databases here are really growing and getting to be rather big. The question on our minds is; when is a database or table just too big? Our InnoDB datafile was 116GB last I checked, and I know we've got a few 20GB+ databases on there, but my real concern is over the table sizes. I have 2 or 3 tables that the MySQL Administrator can't even get a size on. It reports it as 0Bytes, but the little picture bar shows that these tables take up almost 1/3 of the database size. I think these tables could be as big as 8GB, but we have quite a few above 1GB. Does anyone know the point that the MySQL Administrator can't report on table sizes? Also, does anyone know any of the magic numbers where things get difficult for databases and tables and even the main server when it gets too big? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How big is too big?
Does anyone know the point that the MySQL Administrator can't report on table sizes? Misao: I do not use MySQL Adminstrator, so I do not know at what point it is unable to correctly report the size of a table. But if there exists a table that it cannot correctly report the size of, it is a bug in the MySQL Administrator, and should be reported at http://bugs.mysql.com You can use SHOW TABLE STATUS LIKE 'table_name' to get the info about any given table. Also, does anyone know any of the magic numbers where things get difficult for databases and tables and even the main server when it gets too big? There is no one magic number, but I define a couple of threshholds that could be of some guidance: * when the table does not fit into RAM anymore * when the most frequently accessed part of the table does not fit into RAM anymore However, the actual cut-off values for when the trouble starts is largely application dependent. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How big is too big?
Our database server has 4Gigs of ram on it, and we have a hard time of figuring out the true InnoDB settings on how to use that ram up. I ended up just increasing the ram used until it just wouldn't run anymore, then backed up and used that. We plan on adding another 4Gigs of ram, total of 8GB. Tweaking the InnoDB stuff doesn't seem as easy as the MyISAM side. So as it stands, we have 4GB, and one table that easily exceeds that, almost 8GB in size. Even after we bump the server up to 8GB, that means this table will barely fit. Does that mean we need to start cleaning out that table, or adding new ram? -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Thursday, January 27, 2005 6:52 PM To: Misao Cc: mysql@lists.mysql.com Subject: Re: How big is too big? Does anyone know the point that the MySQL Administrator can't report on table sizes? Misao: I do not use MySQL Adminstrator, so I do not know at what point it is unable to correctly report the size of a table. But if there exists a table that it cannot correctly report the size of, it is a bug in the MySQL Administrator, and should be reported at http://bugs.mysql.com You can use SHOW TABLE STATUS LIKE 'table_name' to get the info about any given table. Also, does anyone know any of the magic numbers where things get difficult for databases and tables and even the main server when it gets too big? There is no one magic number, but I define a couple of threshholds that could be of some guidance: * when the table does not fit into RAM anymore * when the most frequently accessed part of the table does not fit into RAM anymore However, the actual cut-off values for when the trouble starts is largely application dependent. -- Sasha Pachev AskSasha Linux Consulting http://www.asksasha.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
