questiions for data type and limits of MySQL
Hi, all I have 2 questions: 1) Is there any limit on the number of tables I can create in MySQL and how large I can hold in a database? 2) Does MYSQL support to save binary data file in the table. I can't just save paths to the files. They files reside in another machine. Thanks for reply. X.Chen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: questiions for data type and limits of MySQL
MySQL is generally limited by the OS you are running it on. If you start having thousands of table, the OS is probably going to get bogged down in trying to keep track of all the file handles needed for all those tables and indexes. The practical limit to the number of tables is far lower than the theoretical maximum. So I wouldn't design your database with unlimited tables in mind. A database can hold multiple terabytes of data, but again you would run into limits of the OS, like maximum file size. Using InnoDB you would be able to split the tables into multiple files to work around OS limits on the maximum file size. But again, there are only so many files an OS can keep open and manage before you run into performance problems. Keep in mind that you don't have to keep everything on one machine. Yes, you can store binary data in MySQL. On Sep 20, 2005, at 9:23 AM, [EMAIL PROTECTED] wrote: Hi, all I have 2 questions: 1) Is there any limit on the number of tables I can create in MySQL and how large I can hold in a database? 2) Does MYSQL support to save binary data file in the table. I can't just save paths to the files. They files reside in another machine. Thanks for reply. X.Chen -- 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]
File size limits with mysql 4.1
I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File size limits with mysql 4.1
You must be getting an error code when inserting now. If that is related to index file size (that's what I had) . You can do ALTER TABLE tablename MAX_ROWS=big_num On Mon, 2004-07-26 at 11:48, Michael Dykman wrote: I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? -- - michael dykman - [EMAIL PROTECTED] -- Aman Raheja Linux+ Certified [EMAIL PROTECTED] Brainbench Certified Linux (General) Admin www.TechQuotes.comBrainbench Certified Linux (RedHat 9) Admin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File size limits with mysql 4.1
At 12:48 -0400 7/26/04, Michael Dykman wrote: I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? Are you using MyISAM tables? If so, you probably want to specify MAX_ROWS and/or AVG_ROW_LENGTH table options when you create the tables so that larger internal row pointers get used: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html For existing tables, you can use ALTER TABLE to change the option values. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File size limits with mysql 4.1
thank you for the suggestion, I will give that a try. I thought it suspicious that the table stopped receiving data at 2 bytes under the natural 4G limit (8 byte int) which was standard under 3.22. As I said, I am using a development release and I have found 1 or 2 other regression errors along the way. On Mon, 2004-07-26 at 14:19, Paul DuBois wrote: At 12:48 -0400 7/26/04, Michael Dykman wrote: I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? Are you using MyISAM tables? If so, you probably want to specify MAX_ROWS and/or AVG_ROW_LENGTH table options when you create the tables so that larger internal row pointers get used: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html For existing tables, you can use ALTER TABLE to change the option values. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: File size limits with mysql 4.1
I apologize for my skepticism of 15 minutes ago. I finally _read_ http://dev.mysql.com/doc/mysql/en/Table_size.html carefully, and indeed your suggestion is dead on. thank you again. On Mon, 2004-07-26 at 14:19, Paul DuBois wrote: At 12:48 -0400 7/26/04, Michael Dykman wrote: I am using a development build of 4.1.3 (the last 4.1.3 release I think; mysql-4.1.3-beta-nightly-20040628) so I suppose I have this coming, but here goes: As I am running on RH Enterprise Server 3 with a Pentium Xeon (32-bit) According to the documentation, for a 32 bit processor, I should be able to grow data files to 16G on a 32 bit system, assuming the OS supports it. I am using the ext3 file system which should support at least 2TB. However, I had all insertions to one table grind suddenly to a halt when the data grew to 4294967292 bytes (2^32-2). Has anyone else encountered this or have any practical advice on how to transcend this limitation? Are you using MyISAM tables? If so, you probably want to specify MAX_ROWS and/or AVG_ROW_LENGTH table options when you create the tables so that larger internal row pointers get used: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html For existing tables, you can use ALTER TABLE to change the option values. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limits of mySQL
I'm not a suitable person to compare MySQL against other databases but MySQL will cope with this size of database if the tables are properly indexed and your queries optomised. Here our main database has over 90 tables and several of our tablse hold about the amount of data your'll acrue in year one (with many more rows). None of our tables have that many fields the most complex table has 50 fields but I don't think 100 rows will effect MySQL performance significantly. Several of our tables have more that 10k per row and work just fine. Do you have any idea how well MySQL scales with tables containing some 10th of million rows of data? Cheers, //Anders - thinking of porting - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Limits of mySQL
On Wed, May 01, 2002 at 08:02:20AM +0200, Svensson, B.A.T. (HKG) wrote: Do you have any idea how well MySQL scales with tables containing some 10th of million rows of data? How about 280 million? We've got one that big. It performs very well--because we have it properly indexed and don't run queries that result full table scans. Other have had similar success. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 82 days, processed 2,135,765,322 queries (299/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Limits of mySQL
On Sat, 27 Apr 2002, Sam Minnee wrote: I've been asked to put together a very large (well, it's large to me) database, and while mySQL is great for my current uses, I haven't had experience with stuff of this scale. The database will have about 88 tables, with up to 100 fields per table. There is a _lot_ of interlinking among the tables, and each transaction will have about 10k of data. By the end of the first year, almost 500,000 transactions will be in the database. Unfortunately, I can't be more specific, as another party is designing the database specification, which I don't have a copy of yet. Now, if I were to use mySQL I would want to use the transactional version. I haven't had any experience with this, how does its performance and reliability compare (obviously the transactions are a + to its reliability). My question is: Will mySQL be able to handle this amount / complexity of data well, and how much better would, say, Oracle or even MS SQL Server 2000 be? What about PostgreSQL? PostgreSQLs relationships, constraints, views, and stored procedures would be beneficial, but not at the cost of of suitable performance. It would be much appreciated if someone with more experience developing databases of this scale could give me some advice on the pros and cons of each platform. I'm not a suitable person to compare MySQL against other databases but MySQL will cope with this size of database if the tables are properly indexed and your queries optomised. Here our main database has over 90 tables and several of our tablse hold about the amount of data your'll acrue in year one (with many more rows). None of our tables have that many fields the most complex table has 50 fields but I don't think 100 rows will effect MySQL performance significantly. Several of our tables have more that 10k per row and work just fine. It sounds as though your tables will be skirting close to the 4GB file limit on most standard Unixes so be sure to enable large file support (or use a raw partition for innodb). We're only using innodb on small projects so far but it seems to perform well although we have had some issues with it's interaction with the PHP scripting languages persitent connections. HTH Nigel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Limits of mySQL
On Sat, Apr 27, 2002 at 05:15:10PM +1200, Sam Minnee wrote: I've been asked to put together a very large (well, it's large to me) database, and while mySQL is great for my current uses, I haven't had experience with stuff of this scale. The database will have about 88 tables, with up to 100 fields per table. There is a _lot_ of interlinking among the tables, and each transaction will have about 10k of data. By the end of the first year, almost 500,000 transactions will be in the database. Unfortunately, I can't be more specific, as another party is designing the database specification, which I don't have a copy of yet. No red flags so far. Now, if I were to use mySQL I would want to use the transactional version. I haven't had any experience with this, how does its performance and reliability compare (obviously the transactions are a + to its reliability). It's still as fast and reliable as non-transactional MySQL. My question is: Will mySQL be able to handle this amount / complexity of data well, and how much better would, say, Oracle or even MS SQL Server 2000 be? MySQL will cope just fine. It'll probably be faster than the alternatives. What about PostgreSQL? PostgreSQLs relationships, constraints, views, and stored procedures would be beneficial, but not at the cost of of suitable performance. InnoDB provides referential integrity constraints (relationships), so that's a non-issue. As for views and stored procedures, it's up to you. If you need 'em, try PostgreSQL. MySQL won't have them for a while yet. All the databases you mentioned will work for you app. It comes down to finding the one that has all the features you need at the lowest price. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 79 days, processed 2,065,226,324 queries (302/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Limits of mySQL
I've been asked to put together a very large (well, it's large to me) database, and while mySQL is great for my current uses, I haven't had experience with stuff of this scale. The database will have about 88 tables, with up to 100 fields per table. There is a _lot_ of interlinking among the tables, and each transaction will have about 10k of data. By the end of the first year, almost 500,000 transactions will be in the database. Unfortunately, I can't be more specific, as another party is designing the database specification, which I don't have a copy of yet. Now, if I were to use mySQL I would want to use the transactional version. I haven't had any experience with this, how does its performance and reliability compare (obviously the transactions are a + to its reliability). My question is: Will mySQL be able to handle this amount / complexity of data well, and how much better would, say, Oracle or even MS SQL Server 2000 be? What about PostgreSQL? PostgreSQLs relationships, constraints, views, and stored procedures would be beneficial, but not at the cost of of suitable performance. It would be much appreciated if someone with more experience developing databases of this scale could give me some advice on the pros and cons of each platform. Thanks, Sam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: There are no DB defined space limits on MySQL Databases ?
On Tue, Feb 20, 2001 at 09:33:40AM +0800, yindu wrote: thank you. i know mysql stored its tables in files,And i have looked through the mysql handbook.But i hadn't found information about the file systerm of mysql,i think a table is a file,then all tables in the same database should be stored in the same file_content.But where can i find them? I'm assuming you've installed MySQL under some kind of UNIX. If so: MySQL does not have it's own 'filesystem'; it stores everything in several large files in the UNIX filesystem. MySQL (by default) keeps it's databases in /usr/local/var. This is the 'datadir' parameter to the server. You can see what yours is set to: % echo 'show variables' | mysql This will dump out a list of all of the configuration variables your server is using. Look for 'datadir'. Within that directory, there will be several sub-directories (yours will be different than mine): % ls -l /usr/local/var drwx-- 2 root wheel 512 Apr 26 2000 customers drwx-- 2 root wheel 512 Apr 26 2000 mysql drwx-- 2 root wheel 512 Apr 26 2000 test Each one of these is a database. My MySQL server has three databases, 'customer', 'mysql' and 'test'. 'mysql' is the database that the server itself uses to keep track of valid users, permissions, and the like. 'customer' is one I use for a project. That directory contains several files: % ls /usr/local/val/customer customer.ISD customer.ISM customer.frm site.ISD site.ISM site.frm My database 'customer' has two tables: 'customer' and 'site'. MySQL stores the information about any one table in three files. For example, my customer table has three files: customer.ISDthis is the data file customer.ISMthis is the index file customer.frm(I forget what this is, sorry) Where can i find local informations? I don't know what you mean; sorry. :/ now i create a database named yd for my database user.If i want to know how much space have been used,what can i do ? The 'space used' by a database is the amount of diskspace taken up by the subdirectory, including all of its files. One way is with the 'du' command: % du -k /usr/local/var/customers 20843 The '-k' flag means "count in 1024 byte blocks". Therefore, I'm using 20843 * 1024 = 21343232 bytes. (About two megabytes.) Hope this helps... -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: There are no DB defined space limits on MySQL Databases ?
On Fri, Feb 16, 2001 at 10:46:40AM -0800, Fox Mulder wrote: --- Brian Reichert [EMAIL PROTECTED] wrote: On Sat, Feb 17, 2001 at 03:23:49PM +0800, yindu wrote: There are no DB defined space limits on MySQL Databases ?It's dangerours .How to control the database space on my database user? Filesystem quotas. But, to define filesystem quota, I will need to change de Owner of the file to the name of the respecive user right? Example: if the table belong to user max, I'll: chown max.users *.ISM Right?? Well, let me make sure I undertand what you mean. If _all_ of the database tables are owned by the same user (ie. the user the mysqld runs as), then you could arrange that the database tables live on their own filesystem. If the database tables have to co-exist if other files owned by other users, then yes, you'd have to employ per-user quotas. As you how your OS imposed those limits, you'd have to do research. But yes, at the very least, those database table files would have to be owned by the user you're trying to constrain. If that user is different than the UID that mysqld runs under, then you'd have to assure that mysqld has read/write access to the file. As I write this, I realize I don't know if/how quotas will be imposed when mysqld's UID write to a file owned by otheruser. I suspect that mysqld's own limits will be enforced, not otheruser's. Look into: - putting the worrisome files onto their own filesystem. - resource limits (RLIMIT_FSIZE in setrlimit(2), for example. My OS (FreeBSD) has a command-line tool [limits(1)] for contraining processes. - filesystem quotas. My OS lets me have both user and group quotas. YMMV. []'s Fox W. Mulder __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: There are no DB defined space limits on MySQL Databases ?
On Sat, Feb 17, 2001 at 03:23:49PM +0800, yindu wrote: There are no DB defined space limits on MySQL Databases ?It's dangerours .How to control the database space on my database user? Filesystem quotas. -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: There are no DB defined space limits on MySQL Databases ?
--- Brian Reichert [EMAIL PROTECTED] wrote: On Sat, Feb 17, 2001 at 03:23:49PM +0800, yindu wrote: There are no DB defined space limits on MySQL Databases ?It's dangerours .How to control the database space on my database user? Filesystem quotas. But, to define filesystem quota, I will need to change de Owner of the file to the name of the respecive user right? Example: if the table belong to user max, I'll: chown max.users *.ISM Right?? []'s Fox W. Mulder __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
There are no DB defined space limits on MySQL Databases ?
There are no DB defined space limits on MySQL Databases ?It's dangerours .How to control the database space on my database user?
