Re: InnoDB Tablespace
On Mon, Aug 2, 2010 at 8:35 PM, Johnny Withers joh...@pixelated.net wrote: Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? I seem to recall - but am on holiday, and thus way too lazy to actually check - that the free refers to the amount of free space in the files (so should go up when you delete records, and so on), not amount of total disk space you are still allowed to fill. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB Tablespace
About the above - it is saying 6144 KB so it is 6.1 GB. Are you sure? I would think 6144KB = 6.144 MB, or 6144 * 1000 = 6,144,000 bytes. I think since InnoDB, by default, extends the table space by 8MB increments, this is reporting the free space in this increment. How can I tell total remaining space so I can adjust and/or add new table space before it runs out of space next time? I have another server with a different config line, however, the last innodb file specified is also max 16G and when i run show table status on it, it reports 3983360 kB free, which i would assume is 3.9 GB? Could this be because it's filling up space in one of the files before the last auto-extending file, which these files are fixed sizes? Both servers are 5.0.77-log. One server is Cent OS, the other is RHEL. I'm very confused here. JW On Mon, Aug 2, 2010 at 1:53 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Hey john, Yes you can add it but safe to keep auto-extend at the end and monitor the disk space as well. Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? About the above - it is saying 6144 KB so it is 6.1 GB. On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers joh...@pixelated.netwrote: I recently ran out of table space on a production server that had the following configuration line: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G Before I changed this line and restarted the server, I ran SHOW TABLE STATUS LIKE 'table' on one of the databases and the comment filed said: InnoDB Free: 3NNN kB (I don't remember the exact number, but know it started with 3 and had 4 digits. I modified the configuration line above to: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? Also, If I wanted to add another file to this file_path variable, can I just add it to the end like so: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; *ibdata7:16G* Or will that cause MySQL to complain the file size isn't correct the next time it starts? Thanks for any help! -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: InnoDB Tablespace
Hi Johnny, Sorry about that - i just overlooked and the simple way to calculate the sizes is to query the information_schema table called tables for data and index sizes. On Tue, Aug 3, 2010 at 8:55 PM, Johnny Withers joh...@pixelated.net wrote: About the above - it is saying 6144 KB so it is 6.1 GB. Are you sure? I would think 6144KB = 6.144 MB, or 6144 * 1000 = 6,144,000 bytes. I think since InnoDB, by default, extends the table space by 8MB increments, this is reporting the free space in this increment. How can I tell total remaining space so I can adjust and/or add new table space before it runs out of space next time? I have another server with a different config line, however, the last innodb file specified is also max 16G and when i run show table status on it, it reports 3983360 kB free, which i would assume is 3.9 GB? Could this be because it's filling up space in one of the files before the last auto-extending file, which these files are fixed sizes? Both servers are 5.0.77-log. One server is Cent OS, the other is RHEL. I'm very confused here. JW On Mon, Aug 2, 2010 at 1:53 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Hey john, Yes you can add it but safe to keep auto-extend at the end and monitor the disk space as well. Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? About the above - it is saying 6144 KB so it is 6.1 GB. On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers joh...@pixelated.netwrote: I recently ran out of table space on a production server that had the following configuration line: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G Before I changed this line and restarted the server, I ran SHOW TABLE STATUS LIKE 'table' on one of the databases and the comment filed said: InnoDB Free: 3NNN kB (I don't remember the exact number, but know it started with 3 and had 4 digits. I modified the configuration line above to: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? Also, If I wanted to add another file to this file_path variable, can I just add it to the end like so: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; *ibdata7:16G* Or will that cause MySQL to complain the file size isn't correct the next time it starts? Thanks for any help! -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA
InnoDB Tablespace
I recently ran out of table space on a production server that had the following configuration line: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G Before I changed this line and restarted the server, I ran SHOW TABLE STATUS LIKE 'table' on one of the databases and the comment filed said: InnoDB Free: 3NNN kB (I don't remember the exact number, but know it started with 3 and had 4 digits. I modified the configuration line above to: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? Also, If I wanted to add another file to this file_path variable, can I just add it to the end like so: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; *ibdata7:16G* Or will that cause MySQL to complain the file size isn't correct the next time it starts? Thanks for any help! -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: InnoDB Tablespace
Hey john, Yes you can add it but safe to keep auto-extend at the end and monitor the disk space as well. Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? About the above - it is saying 6144 KB so it is 6.1 GB. On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers joh...@pixelated.netwrote: I recently ran out of table space on a production server that had the following configuration line: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G Before I changed this line and restarted the server, I ran SHOW TABLE STATUS LIKE 'table' on one of the databases and the comment filed said: InnoDB Free: 3NNN kB (I don't remember the exact number, but know it started with 3 and had 4 digits. I modified the configuration line above to: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G Now when i run the same show table status command, the comment field says: InnoDB free: 6144 kB Is that telling me that I only have 6MB of storage left even though I increased the table space by 8GB? Also, If I wanted to add another file to this file_path variable, can I just add it to the end like so: innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G; *ibdata7:16G* Or will that cause MySQL to complain the file size isn't correct the next time it starts? Thanks for any help! -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Thanks Suresh Kuna MySQL DBA
Re: WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files.
This is because you didn't copy innodb ibdata and ib_log files togeter. Or you forgot to stop mysqld when you remove its ib_log files. On Sat, Feb 7, 2009 at 7:21 AM, my sql mysql.g...@gmail.com wrote: WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. GOAL: Trying to restore mysql backup on different host using InnoDB backup that copes the backed up files to a files sever where I pulle them down to the new host I place all the MySQL datafiles and InnoDB log files in the same directory and all paths in the my.cnf files are pointing here upon the startup of the mysqld and the following crach recover that the Innodb do I get tons of this errors [see below] why? I do have the two InnoDB log files - so why does it complain that my DB 'may' be corrupt - I don't like this message. Event tried with seting innodb_force_recovery = 4 but I still get the error upon startup - why? It doesn't look like a clean startup to me 090206 14:56:34 InnoDB: Error: page 27060 log sequence number 4 1755884236 InnoDB: is in the future! Current system log sequence number 4 1682795020. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files.
WHY do I see this error when restoring my backup db : InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. GOAL: Trying to restore mysql backup on different host using InnoDB backup that copes the backed up files to a files sever where I pulle them down to the new host I place all the MySQL datafiles and InnoDB log files in the same directory and all paths in the my.cnf files are pointing here upon the startup of the mysqld and the following crach recover that the Innodb do I get tons of this errors [see below] why? I do have the two InnoDB log files - so why does it complain that my DB 'may' be corrupt - I don't like this message. Event tried with seting innodb_force_recovery = 4 but I still get the error upon startup - why? It doesn't look like a clean startup to me 090206 14:56:34 InnoDB: Error: page 27060 log sequence number 4 1755884236 InnoDB: is in the future! Current system log sequence number 4 1682795020. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files.
Innodb tablespace
Hi all, Are there any reasons why one would NOT use separate ibd files for each table (--innodb_file_per_table). It seems logical to me to separate what does not belong together logically (different databases), but I as the shared tablespace is the default I wonder if it has nay advantages I am not aware of Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb tablespace
Hi All, If you specify one file per table, these files would be created under the database directory of that particular database . So, the benifit with respect to IO is negative. To have these files placed in different file system to get IO benifit, you need to use symbolic links. Please correct me if i am wrong. regards anandkl On 6/15/07, Olaf Stein [EMAIL PROTECTED] wrote: Hi all, Are there any reasons why one would NOT use separate ibd files for each table (--innodb_file_per_table). It seems logical to me to separate what does not belong together logically (different databases), but I as the shared tablespace is the default I wonder if it has nay advantages I am not aware of Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb tablespace
Hi all, Are there any reasons why one would NOT use separate ibd files for each table Fragmentation for one. A single file can re-use empty space from deleted rows for any added rows. A single file can only re-use space from that one file. Therefore the sum table size will be larger with many files. Depending on how much data you regularly delete. (Fragmentation also occurs when row sizes are increased.) The file system is also an issue. Lots of files require a good file system, like Reiserfs, and not FAT32. A single file is just as efficient with any file system. But I don't believe there are any IO difference. The same number of file handles are used, whether they all access one file or 1000 files. Personally I like a single file, it's easier to administer and HotBackup works. But, if any IonnDB developers read this mailing list, we really need to be able to break this file using defined table space, as with Oracle. Then have as many/few files as we like Ben Olaf Stein wrote: Hi all, Are there any reasons why one would NOT use separate ibd files for each table (--innodb_file_per_table). It seems logical to me to separate what does not belong together logically (different databases), but I as the shared tablespace is the default I wonder if it has nay advantages I am not aware of Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb tablespace
Olaf Stein wrote: Hi all, Are there any reasons why one would NOT use separate ibd files for each table (--innodb_file_per_table). It seems logical to me to separate what does not belong together logically (different databases), but I as the shared tablespace is the default I wonder if it has nay advantages I am not aware of Fragmentation for one. A single file can re-use empty space from deleted data for any added tables and rows. A single file can only re-use space from that one file. Therefore the sum table size will be larger with many files. Depending on how much data you regularly delete. (Fragmentation also occurs when row sizes are increased.) There are file system problems as well. Many files rely on a good file system, like Reiserfs, and not, say, Fat32. Many files also result in a heavier hit on a journaling file system. Important if you are using a lot of files, like many hundreds. But I don't believe there is any IO difference. The same number of file handles are used, whether they all access one file or 1000 files... Also worth noting that an external single file is still used with file-per-table, which I suspect is used for referential constraint storage. Therefore a complete division by database is not possible, all databases still use this one file. Personally I like a single file, it's easier to administer and HotBackup works. But, if any IonnDB developers read this mailing list, we really need to be able to break this file using defined table space, as with Oracle. Then have as many/few files as we like. Ben Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb tablespace
In the last episode (Jun 15), Ben Clewett said: Are there any reasons why one would NOT use separate ibd files for each table Fragmentation for one. A single file can re-use empty space from deleted rows for any added rows. A single file can only re-use space from that one file. Therefore the sum table size will be larger with many files. Depending on how much data you regularly delete. I would claim that the file-per-table method reduces fragmentation and overall tablepsace size as compared to the tablespace method: OPTIMIZE'ing a file-per-table table recreates that .ibd file and removes all the empty space. To do that with the single tablespace setup, you would have to dump all tables, delete your tablespace file, and reload. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
pre-create innodb tablespace
Hi, Can I pre-create innodb tablespace using something like dd (or any other better tool)? I have a server that is getting low on innodb table space and I want to add 15GB or so, but I want to minimize downtime. The server is a bit slow and I estimate it will take around 10-20 minutes or so. I was hoping to 'dd' the additional table files, add the configuration directives to my.cnf and restart the server. This would mean my downtime would be whatever time it takes to restart the server. Thanks.
Re: pre-create innodb tablespace
Gary Richardson [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Can I pre-create innodb tablespace using something like dd (or any other better tool)? I'm afraid it can't be done that way, because fresh datafile is not simply an empty file filled with zeros. You should be able though to expand the tablespace by specifying autoextend attribute for the last datafile listed in innodb_data_file_path. This will, of course, not expand the tablespace immediately. It will grow in time as the new data is stored and according to the database parameters. Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The best way to configure innodb tablespace
Hi I'm running MySQL 4.0.25 on a Linux machine dual xeon 3.0GB Ghz(32 bit) with 6 GB RAM the data is connected to a SAN storage I wander what is the best tablespace configuration (except RAW) of configuraing? my configuration is as follows: innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G;ibdata5:10G;ibdata6:10G innodb_buffer_pool_size=2G innodb_log_file_size=500M (*3 files) innodb_log_buffer_size=8M the data within the tablespaces is approx. 42GB so far this server is quite load .. my question is : is it better to create 1 chunck of 60GB or is there better configuration of tablespaces? Thanks in advance Yariv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb TableSpace Free and Total Size
Using SQL : SHOW TABLE STATUS, we could see Innodb free size from inno db table's comment. Is there any SQL to know total tablespace size allocated? -- Regards, Ady Wicaksono HP: +628562208680 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Tablespace per Schema?
Hello everyone, Is it possible to configure MySQL so that it uses one InnoDB tablespace file per database schema, instead of mixing all data into the shared tablespace? I'd like to keep my applications data seperate, and having one file per application (schema) should make data migrations easier. Regards, Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Tablespace per Schema?
From: Andreas Schildbach Is it possible to configure MySQL so that it uses one InnoDB tablespace file per database schema, instead of mixing all data into the shared tablespace? No, but you can use a file per table: http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html It still uses a shared tablespace for certain definitions, but on the page above you can read how to move tablespace files in and out of InnoDB. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to re-use InnoDB tablespace
Hi, everyone, I created two databases DB_A and DB_B whose tables were all in InnoDB. DB_A has data of approximately 500 G, DB_B has data of about 200G. Later I dropped the entire DB_B because it served only purpose of testing. I noticed that the size of the table files ibdata1 and ibdata2 didn't not change (ibdata2 is auto extended). Now ibdata1 is 2G and ibdata2 is 793 G. It said InnoDB free: 201787392 kB when I did show table status. The server is dedicated to the MySQL server and the disk partition now has only 2G available. Since I will soon add another 5 G of data to DB_A, I am afraid that there won't be enough space. I would greatly appreciate if someone can answer some questions so I can have an idea of what kind of situation I am facing. My questions are: 1. I assume that the space of InnoDB free: 201787392 kB was resulted from the dropping of DB_B. Will this chunk be re-used when new data is inserted? 2. If the answer for Question 1 is no, I believe I unfortunately have to follow the instruction on http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html to decrease the size the tablespace. Since I have a huge database, when I import the dump files, I am expecting to insert several billions of rows. Will each insertion into an InnoDB table be followed by an automatic commit? If yes, it will take probably weeks to import the data back. Is there a way to quickly import data back? The data includes blob, Load data from a text file won't work. 3. If I would do a replication now with an off-line backup, will the entire ibdata2 of 793 G be copied to the slave? Again will the free InnoDB table space be re-used on the slave? Thank you very much in advance for your help! Regards, Zhe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to re-use InnoDB tablespace
[snip] I assume that the space of InnoDB free: 201787392 kB was resulted from the dropping of DB_B. Will this chunk be re-used when new data is inserted? [/snip] InnoDB tablespace will not shrink when data is removed. The space the you have from the dropped database will be reused. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: actual size of a innodb tablespace
Thank for your answer. I did a show table status and this is convenient for what I want. But I was surprised that two of the 26 tables give a row number different each time I process a show table status. The size remains the same. In a 3 times show table status I got 699-634-731 rows. A select from the table give 677 rows. Here is how the table is created. What can explain that? Johanne Duhaime CREATE TABLE `Alias` ( `oid` bigint(20) NOT NULL auto_increment, `aliasId` varchar(100) NOT NULL default '', `proteinOid` bigint(20) NOT NULL default '0', `aliasGroupOid` bigint(20) NOT NULL default '0', `description` varchar(255) default NULL, `sourceDatabase` varchar(100) default NULL, `determined` tinyint(4) NOT NULL default '0', `sequence` text, PRIMARY KEY (`oid`), UNIQUE KEY `aliasId_2` (`aliasId`), KEY `aliasId` (`aliasId`), KEY `proteinOid` (`proteinOid`), KEY `aliasGroupOid` (`aliasGroupOid`), CONSTRAINT `Alias_ibfk_1` FOREIGN KEY (`proteinOid`) REFERENCES `Protein` (`oi d`) ON UPDATE CASCADE, CONSTRAINT `Alias_ibfk_2` FOREIGN KEY (`aliasGroupOid`) REFERENCES `AliasGroup ` (`oid`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -Message d'origine- De : Jeff Mathis [mailto:[EMAIL PROTECTED] Envoyé : 14 janvier 2005 16:15 À : Duhaime Johanne Cc : mysql@lists.mysql.com Objet : Re: actual size of a innodb tablespace if you issue a show table status command from the mysql prompt, you'll get an estimate of how much free space exists in the files. Duhaime Johanne wrote: Hello Context: innodb per table. Is it possible to know the actual size of my innodb file? What part of the initial size (10M: autoextend) is actually used? I would like to have an idea of the data space progression? Per database (I have 3). Thank you in advance Johanne Duhaime IRCM courrier: [EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: actual size of a innodb tablespace
I think I can summarize what has been explained before: 1) InnoDB does not always know exactly how many rows are in the database from the point of view of each user. This is a side effect of the row-level locking system. Each user has the potential to see a different set of rows depending on the table status and what is going on at the moment. 2) To compensate for this inexact number, SHOW TABLE STATUS performs 10 random dives into the table tree and the estimate you see is the average of those 10 dives. This saves time because you don't have to physically determine each and every row that is visible to the user. 3) Count(*) will perform that physical count but it takes much longer because it MUST perform a table scan in order to determine which row is visible to the user (the # of committed rows not part of another user's transaction plus the # of rows added as part of the current user's transaction). Shawn Green Database Administrator Unimin Corporation - Spruce Pine Duhaime Johanne [EMAIL PROTECTED] wrote on 01/17/2005 01:10:45 PM: Thank for your answer. I did a show table status and this is convenient for what I want. But I was surprised that two of the 26 tables give a row number different each time I process a show table status. The size remains the same. In a 3 times show table status I got 699-634-731 rows. A select from the table give 677 rows. Here is how the table is created. What can explain that? Johanne Duhaime CREATE TABLE `Alias` ( `oid` bigint(20) NOT NULL auto_increment, `aliasId` varchar(100) NOT NULL default '', `proteinOid` bigint(20) NOT NULL default '0', `aliasGroupOid` bigint(20) NOT NULL default '0', `description` varchar(255) default NULL, `sourceDatabase` varchar(100) default NULL, `determined` tinyint(4) NOT NULL default '0', `sequence` text, PRIMARY KEY (`oid`), UNIQUE KEY `aliasId_2` (`aliasId`), KEY `aliasId` (`aliasId`), KEY `proteinOid` (`proteinOid`), KEY `aliasGroupOid` (`aliasGroupOid`), CONSTRAINT `Alias_ibfk_1` FOREIGN KEY (`proteinOid`) REFERENCES `Protein` (`oi d`) ON UPDATE CASCADE, CONSTRAINT `Alias_ibfk_2` FOREIGN KEY (`aliasGroupOid`) REFERENCES `AliasGroup ` (`oid`) ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -Message d'origine- De : Jeff Mathis [mailto:[EMAIL PROTECTED] Envoyé : 14 janvier 2005 16:15 À : Duhaime Johanne Cc : mysql@lists.mysql.com Objet : Re: actual size of a innodb tablespace if you issue a show table status command from the mysql prompt, you'll get an estimate of how much free space exists in the files. Duhaime Johanne wrote: Hello Context: innodb per table. Is it possible to know the actual size of my innodb file? What part of the initial size (10M: autoextend) is actually used? I would like to have an idea of the data space progression? Per database (I have 3). Thank you in advance Johanne Duhaime IRCM courrier: [EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
actual size of a innodb tablespace
Hello Context: innodb per table. Is it possible to know the actual size of my innodb file? What part of the initial size (10M: autoextend) is actually used? I would like to have an idea of the data space progression? Per database (I have 3). Thank you in advance Johanne Duhaime IRCM courrier: [EMAIL PROTECTED]
Re: actual size of a innodb tablespace
if you issue a show table status command from the mysql prompt, you'll get an estimate of how much free space exists in the files. Duhaime Johanne wrote: Hello Context: innodb per table. Is it possible to know the actual size of my innodb file? What part of the initial size (10M: autoextend) is actually used? I would like to have an idea of the data space progression? Per database (I have 3). Thank you in advance Johanne Duhaime IRCM courrier: [EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB tablespace Question.
Hello! I have what seems to be a trivial question, but have not been able to find a definite answer and your help would be greatly appreciated. Question: When creating InnoDB table spaces, are there any advantages to using multi table spaces for each table or is it better to create a few large table spaces for all tables? If the latter, then is it best to create a very large table space, say 30G, (my OS supports LFS) rather then using the auto extend feature for table spaces in InnoDB? What is the over head of the InnoDB auto extend? Thanks! --Dave J. David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax : 260.459.6271
RE: InnoDB tablespace Question.
Depends on your disk setup. Remember a table space is a virtual filesystem that sits on top of the OS. Having one large file and chopping a contiguous block of the disk out enables better seeks as well as caching if the file doesn't bust the system cache. In your case it will. One file needs to be autoextended else your application will run into errors once the data needs to grow pass the tablespace. Having multiple table spaces on different spindles enable the data to be segmented a bit more getting a few more bits of speed, but at the possible detriment of needed to access both separate data spaces if the data requested spans multiple files. In essence I have found that using multiple table spaces is best used when the disk is starting to fill up and I need to put the data on a different disk. You'll get a constant boost in performance if you put the innodb log files on a different spindle or set of spindles as your data file. -Original Message- From: Dave Juntgen [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 6:30 AM To: [EMAIL PROTECTED] Subject: InnoDB tablespace Question. Hello! I have what seems to be a trivial question, but have not been able to find a definite answer and your help would be greatly appreciated. Question: When creating InnoDB table spaces, are there any advantages to using multi table spaces for each table or is it better to create a few large table spaces for all tables? If the latter, then is it best to create a very large table space, say 30G, (my OS supports LFS) rather then using the auto extend feature for table spaces in InnoDB? What is the over head of the InnoDB auto extend? Thanks! --Dave J. David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax : 260.459.6271 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on InnoDB - Tablespace enough but engine said table full
Heiki Thx... it's work :D Sorry, one liner Heikki Tuuri [EMAIL PROTECTED] wrote: Ady, - Alkuperäinen viesti - Lähettäjä: Ady Wicaksono Vastaanottaja: Ady Wicaksono ; Heikki Tuuri Kopio: Lähetetty: Friday, September 03, 2004 10:41 AM Aihe: Re: Problem on InnoDB - Tablespace enough but engine said table full I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ib data14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/da ta3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9 :1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty (953856 - 877184) / 64 = 1198 MB 1) Stop the mysqld server. 2) Add a new 1198M ibdata file at the end of innodb_data_file_path. 3) When you start mysqld, InnoDB will write that new ibdata file full of zeros, and increment the tablespace size stored in the tablespace header by 1198M. 4) Then stop the mysqld server, remove the extra 1198M ibdata file from innodb_data_file_path and delete that extra ibdata file. 5) Start mysqld again. 6) Voila! Now the tablespace size stored in the tablespace header agrees with the combined size of the files in innodb_data_file_path! Be very careful! From now on always follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html when you want to add a new ibdata file. Thx Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now.
Re: Problem on InnoDB - Tablespace enough but engine said table full
I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ibdata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/data3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:1500M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9:1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty Thx Ady Wicaksono [EMAIL PROTECTED] wrote: May i know, how could i know which of the data files that InnoDB MySQL engine is not used ? Did i i made a mistake when adding table space ? Heikki Tuuri wrote: Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! - Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard.
Re: Problem on InnoDB - Tablespace enough but engine said table full
Ady, - Alkuperäinen viesti - Lähettäjä: Ady Wicaksono [EMAIL PROTECTED] Vastaanottaja: Ady Wicaksono [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Kopio: [EMAIL PROTECTED] Lähetetty: Friday, September 03, 2004 10:41 AM Aihe: Re: Problem on InnoDB - Tablespace enough but engine said table full I try to detect using MC (Midnight Commander) and found that after ;/data4/ibdata25:1802M it won't write anymore... I remove these data file and add /ibdata1/ibdata10:1500M and /data1/ibdata11:1500M I believe, All data below is empty but corrupt :( data file defintion -- #/data4/ibdata25.data3:576M;/data1/ibdata10:1500M; #/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ib data14:1500M;/data2/ibdata15:1500M;/data2/ibdata16 :1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/da ta3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ib data22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; --- data file defintion -- I change my innodb_data_file_path to: innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9 :1500M;/data4/ibdata25:1802M;/data1/ibdata10:1500M ;/data1/ibdata11:1500M It's working since i know i have 877184 pages now, later i simply add something like /data1/ibdata12:1500M but, i still have error when starting, here is InnoDB: Error: tablespace size stored in header is 877184 pages, but InnoDB: the sum of data file sizes is 953856 pages How to fix it ? since i found every data in innodb_data_file_path is not empty (953856 - 877184) / 64 = 1198 MB 1) Stop the mysqld server. 2) Add a new 1198M ibdata file at the end of innodb_data_file_path. 3) When you start mysqld, InnoDB will write that new ibdata file full of zeros, and increment the tablespace size stored in the tablespace header by 1198M. 4) Then stop the mysqld server, remove the extra 1198M ibdata file from innodb_data_file_path and delete that extra ibdata file. 5) Start mysqld again. 6) Voila! Now the tablespace size stored in the tablespace header agrees with the combined size of the files in innodb_data_file_path! Be very careful! From now on always follow the instructions at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html when you want to add a new ibdata file. Thx Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on InnoDB - Tablespace enough but engine said table full
Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem on InnoDB - Tablespace enough but engine said table full
May i know, how could i know which of the data files that InnoDB MySQL engine is not used ? Did i i made a mistake when adding table space ? Heikki Tuuri [EMAIL PROTECTED] wrote: Ady, InnoDB thinks that the tablespace size is 10 706 MB. You have specified 36 782 MB of data files in the my.cnf line :(. Now you should figure out what are the data files that InnoDB is using, and remove the end of the innodb_data_file_path line, as well as the unused ibdata files. Remember that InnoDB uses data files starting from the first, in the order that you list them in innodb_data_file_path. Be very careful! Do not remove your valuable data! After that, you can grow the tablespace as instructed at http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html . I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = \ /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:15 00M;/data0 \ /ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ \ ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M \ ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/dat a1/ibdata1 \ 2:1500M;/data1/ibdata13:1500M;/data2/ibd \ ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500 M;/data2/i \ bdata18:1500M;/data2/ibdata19:1500M;/dat \ a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata2 3:1500M;/d \ ata3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers!
Problem on InnoDB - Tablespace enough but engine said table full
I have MySQL for heavy duty job . here is my InnoDB table space definition innodb_data_file_path = /data0/ibdata1:10M;/data0/ibdata2:10M;/data0/ibdata3:1082M;/data0/ibdata4:1500M;/data0/ibdata5:1500M;/ data0/ibdata6:1500M;/data0/ibdata7:1500M;/data1/ibdata8:1500M;/data1/ibdata9:1500M;/data4/ibdata25:1802M;/data3/ibdata25:1802M ;/data4/ibdata25.data3:576M;/data1/ibdata10:1500M;/data1/ibdata11:1500M;/data1/ibdata12:1500M;/data1/ibdata13:1500M;/data2/ibd ata14:1500M;/data2/ibdata15:1500M;/data2/ibdata16:1500M;/data2/ibdata17:1500M;/data2/ibdata18:1500M;/data2/ibdata19:1500M;/dat a3/ibdata20:1500M;/data3/ibdata21:1500M;/data3/ibdata22:1500M;/data3/ibdata23:1500M;/data3/ibdata24:1500M; What i don't understand is : after ibdata1 - ibdata9 full is go to /data4/ibdata25 ... (1802Mbyte) But it's not continues to write to /data4/ibdata25.data3... /data1/ibdata10 and so on but said table is full when i try to write data to table The question is why ? Secondly What is the meaning of InnoDB: Error: tablespace size stored in header is 685184 pages, but InnoDB: the sum of data file sizes is 2354048 pages Thx - Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage!
RE: InnoDB TableSpace Question
Oracle can shrink tablespaces, you can find how to do it searching Metalink for this article: 1029252.6 How to Resize a Datafile Best regards, Antonio Delgado. -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: martes, 03 de agosto de 2004 22:00 To: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question Oracle cannot shrink datafiles (same idea as InnoDB datafiles) when data is deleted either. David Marc Slemko wrote: On Tue, 3 Aug 2004 12:42:03 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? Some do consider it a tremendous limitation. It all depends on how it is being used. Oh, and one thing I forgot... in newer 4.1 versions, if you set things up so each table has its own file with innodb_file_per_table, then I think if you do an optimize table it will end up shrinking the file for that table since it will recreate it. However that really is just a workaround, and there are a lot of disadvantages to that method ... especially the fact that free space is now per table instead of per tablespace. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. No confidentiality or privilege is waived or lost by any wrong transmission. If you have received this message in error, please immediately destroy it and kindly notify the sender by reply email. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Opinions, conclusions and other information in this message that do not relate to the official business of Ydilo Advanced Voice Solutions, S.A. shall be understood as neither given nor endorsed by it. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
In article [EMAIL PROTECTED], Jeff Mathis [EMAIL PROTECTED] writes: my understanding is that the datafiles are created when the server initializes, and this this is the designed and expected behavior. Most other database products use a similar model. Your scenario cannot happen. You specify how many innodb data files and how large in your config file. when the server starts, it allocates all the space you requested. if the server cannot find the space at startup, you get an error. if during an import the file size is exceeded, you get an error and the import stops. ... except if you use the autoextend clause on your innodb_data_file_path. In this case InnoDB files can grow automatically, but they can't shrink. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
David Griffiths writes: Oracle cannot shrink datafiles (same idea as InnoDB datafiles) when data is deleted either. Actually, Oracle has been able to resize data files since 7.2. It is usually done with an 'alter tablespace ... coalesce' followed by an 'alter tablespace datafile ... resize nM' command. But the resize will choke on a datafile with active extents in that datafile. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB TableSpace Question
Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? Here is a cut/paste: mysql show table status like 'CNRC' \G *** 1. row *** Name: CNRC Type: InnoDB Row_format: Fixed Rows: 18777009 Avg_row_length: 54 Data_length: 1028128768 Max_data_length: NULL Index_length: 1296220160 Data_free: 0 Auto_increment: 34182820 Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: InnoDB free: 7083008 kB 1 row in set (2.06 sec) Thanks very much in advance! -Dave Seltzer - Dave Seltzer Systems Architect TVEyes p: 203.254.3600 x222 f: 203.254.3605 e: [EMAIL PROTECTED] - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
On Tue, 3 Aug 2004 10:07:25 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? No, a restart will not shrink it. Currently the only option I can think of is to do a dump and restore, using mysqldump (since innodb hot backup just copies the data file, it won't be of any use in shrinking it). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB TableSpace Question
Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? -Dave Seltzer -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 12:31 PM To: David Seltzer Cc: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question On Tue, 3 Aug 2004 10:07:25 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? No, a restart will not shrink it. Currently the only option I can think of is to do a dump and restore, using mysqldump (since innodb hot backup just copies the data file, it won't be of any use in shrinking it). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
On Tue, 3 Aug 2004 12:42:03 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? Some do consider it a tremendous limitation. It all depends on how it is being used. Oh, and one thing I forgot... in newer 4.1 versions, if you set things up so each table has its own file with innodb_file_per_table, then I think if you do an optimize table it will end up shrinking the file for that table since it will recreate it. However that really is just a workaround, and there are a lot of disadvantages to that method ... especially the fact that free space is now per table instead of per tablespace. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB TableSpace Question
I agree with David. If there is no present way to recover unused InnoDB tablespace, then we (as a community) seriously need to create a tool to do just that. How have we gone so long without it? I always assumed it was possible (I guess I have been just lucky enough to not need to do it yet) What if, during the course of a major data import, I try something that creates a working table that expands my datafile to fill my available disk space. I might have made a logical error or not. Regardless of why it filled up, without the ability to reclaim that room, an entire server could be royally scr***d (assuming a server that supports a mix of InnoDB and other table types). Please tell me there is something other than a dump-delete-import that can be used to shrink InnoDB tablespaces. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Seltzer [EMAIL PROTECTED] wrote on 08/03/2004 12:42:03 PM: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? -Dave Seltzer -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 12:31 PM To: David Seltzer Cc: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question On Tue, 3 Aug 2004 10:07:25 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? No, a restart will not shrink it. Currently the only option I can think of is to do a dump and restore, using mysqldump (since innodb hot backup just copies the data file, it won't be of any use in shrinking it). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB TableSpace Question
Shawn, This is precisely the situation that I am in So it seems I cannot accomplish what I need while on-line. Does anyone know of a tool/method for doing this offline? -Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 1:09 PM To: David Seltzer Cc: [EMAIL PROTECTED] Subject: RE: InnoDB TableSpace Question I agree with David. If there is no present way to recover unused InnoDB tablespace, then we (as a community) seriously need to create a tool to do just that. How have we gone so long without it? I always assumed it was possible (I guess I have been just lucky enough to not need to do it yet) What if, during the course of a major data import, I try something that creates a working table that expands my datafile to fill my available disk space. I might have made a logical error or not. Regardless of why it filled up, without the ability to reclaim that room, an entire server could be royally scr***d (assuming a server that supports a mix of InnoDB and other table types). Please tell me there is something other than a dump-delete-import that can be used to shrink InnoDB tablespaces. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Seltzer [EMAIL PROTECTED] wrote on 08/03/2004 12:42:03 PM: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? -Dave Seltzer -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 12:31 PM To: David Seltzer Cc: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question On Tue, 3 Aug 2004 10:07:25 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? No, a restart will not shrink it. Currently the only option I can think of is to do a dump and restore, using mysqldump (since innodb hot backup just copies the data file, it won't be of any use in shrinking it). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
my understanding is that the datafiles are created when the server initializes, and this this is the designed and expected behavior. Most other database products use a similar model. Your scenario cannot happen. You specify how many innodb data files and how large in your config file. when the server starts, it allocates all the space you requested. if the server cannot find the space at startup, you get an error. if during an import the file size is exceeded, you get an error and the import stops. you cannot overrun your disk. jeff [EMAIL PROTECTED] wrote: I agree with David. If there is no present way to recover unused InnoDB tablespace, then we (as a community) seriously need to create a tool to do just that. How have we gone so long without it? I always assumed it was possible (I guess I have been just lucky enough to not need to do it yet) What if, during the course of a major data import, I try something that creates a working table that expands my datafile to fill my available disk space. I might have made a logical error or not. Regardless of why it filled up, without the ability to reclaim that room, an entire server could be royally scr***d (assuming a server that supports a mix of InnoDB and other table types). Please tell me there is something other than a dump-delete-import that can be used to shrink InnoDB tablespaces. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Seltzer [EMAIL PROTECTED] wrote on 08/03/2004 12:42:03 PM: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? -Dave Seltzer -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 12:31 PM To: David Seltzer Cc: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question On Tue, 3 Aug 2004 10:07:25 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? No, a restart will not shrink it. Currently the only option I can think of is to do a dump and restore, using mysqldump (since innodb hot backup just copies the data file, it won't be of any use in shrinking it). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-955-1434 Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
Jeff Mathis said: my understanding is that the datafiles are created when the server initializes, and this this is the designed and expected behavior. Most other database products use a similar model. Your scenario cannot happen. You specify how many innodb data files and how large in your config file. when the server starts, it allocates all the space you requested. if the server cannot find the space at startup, you get an error. if during an import the file size is exceeded, you get an error and the import stops. you cannot overrun your disk. jeff [EMAIL PROTECTED] wrote: I agree with David. If there is no present way to recover unused InnoDB tablespace, then we (as a community) seriously need to create a tool to do just that. How have we gone so long without it? I always assumed it was possible (I guess I have been just lucky enough to not need to do it yet) What if, during the course of a major data import, I try something that creates a working table that expands my datafile to fill my available disk space. I might have made a logical error or not. Regardless of why it filled up, without the ability to reclaim that room, an entire server could be royally scr***d (assuming a server that supports a mix of InnoDB and other table types). Please tell me there is something other than a dump-delete-import that can be used to shrink InnoDB tablespaces. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Seltzer [EMAIL PROTECTED] wrote on 08/03/2004 12:42:03 PM: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? -Dave Seltzer -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 12:31 PM To: David Seltzer Cc: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question On Tue, 3 Aug 2004 10:07:25 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? No, a restart will not shrink it. Currently the only option I can think of is to do a dump and restore, using mysqldump (since innodb hot backup just copies the data file, it won't be of any use in shrinking it). A number of products allow the extants to be added onto automatically when the initial assignment is exceeded. I thought one of the newer versions of MySQL did that as well but I don't know if its in a stable release yet. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
On Tue, Aug 03, 2004 at 01:08:58PM -0400, [EMAIL PROTECTED] wrote: I agree with David. If there is no present way to recover unused InnoDB tablespace, then we (as a community) seriously need to create a tool to do just that. How have we gone so long without it? Because it's just not a problem for most folks. What if, during the course of a major data import, I try something that creates a working table that expands my datafile to fill my available disk space. If that's something you're really worried about, you probably shouldn't be using the autoextend feature on your tablespace file(s). Please tell me there is something other than a dump-delete-import that can be used to shrink InnoDB tablespaces. Not that I've heard of... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
Oracle cannot shrink datafiles (same idea as InnoDB datafiles) when data is deleted either. David Marc Slemko wrote: On Tue, 3 Aug 2004 12:42:03 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? Some do consider it a tremendous limitation. It all depends on how it is being used. Oh, and one thing I forgot... in newer 4.1 versions, if you set things up so each table has its own file with innodb_file_per_table, then I think if you do an optimize table it will end up shrinking the file for that table since it will recreate it. However that really is just a workaround, and there are a lot of disadvantages to that method ... especially the fact that free space is now per table instead of per tablespace. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
Actually, Oracle can shrink or grow datafiles: ALTER DATABASE DATAFILE '/usr01/oracle/sid/data001' resize 200M; On Aug 3, 2004, at 15:59, David Griffiths wrote: Oracle cannot shrink datafiles (same idea as InnoDB datafiles) when data is deleted either. David Marc Slemko wrote: On Tue, 3 Aug 2004 12:42:03 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? Some do consider it a tremendous limitation. It all depends on how it is being used. Oh, and one thing I forgot... in newer 4.1 versions, if you set things up so each table has its own file with innodb_file_per_table, then I think if you do an optimize table it will end up shrinking the file for that table since it will recreate it. However that really is just a workaround, and there are a lot of disadvantages to that method ... especially the fact that free space is now per table instead of per tablespace. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB tablespace fragmentation bug in MySQL 4.0.20
Hi, ( hopefully a MySQL developer sees this at some point! ) I am giving InnoDB a good workout before rolling it out onto production systems, and found a bug in the way the tablespace gets fragmented when doing basic add/drop of indexes. Below my sig is a series of SQL commands I used to replicate the problem. Basically, when doing a drop index, add index, drop index, one would expect the tablespace to look more or less how it looked after the first drop index since the add index should just reuse what gets reclaimed during the 1st drop. What I am finding however is that this sequence will perpetually grow the tablespace, both on disk, and according to InnoDB. The really interesting thing about this issue is that the tablespace data file grows on disk at the drop index time, not during the add index. I could not believe it when I saw it at first, but I repeated the procedure and confirmed this aspect of this bug a couple times. Note that I am using the autoextend feature with a basic innodb config of: # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql4/innodb innodb_data_file_path = ibdata1:100M:autoextend innodb_log_group_home_dir = /usr/local/mysql4/innodb innodb_log_arch_dir = /usr/local/mysql4/innodb transaction-isolation = READ-COMMITTED and the innodb files end up looking like this: ]$ ls -allg /usr/local/mysql4/innodb/ total 504352 drwxr-xr-x2 mysql4096 Jun 21 00:50 . drwxr-xr-x 12 root 4096 Jun 21 00:17 .. -rw-rw1 mysql 25088 Mar 22 22:00 ib_arch_log_00 -rw-rw1 mysql2560 Jun 21 00:50 ib_arch_log_02 -rw-rw1 mysql499122176 Jul 1 19:45 ibdata1 -rw-rw1 mysql 8388608 Jul 1 19:45 ib_logfile0 -rw-rw1 mysql 8388608 Jul 1 19:45 ib_logfile1 and just before the last drop index the ibdata1 file looked like: ]$ ls -allg /usr/local/mysql4/innodb/ -rw-rw1 mysql490733568 Jul 1 19:44 ibdata1 Finally, I call this a bug because it seems that if one is doing no more than routine maintenance on tables by adding/dropping indexes, one will eventually run out of disk space regardless of whether one is actually using that disk space! Also, I have a linux 2.4 kernel that this is running on, with mysql compiled with gcc 3.2.2. Thanks, Josh Josh Chamas, Founder| NodeWorks - http://www.nodeworks.com Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com http://www.chamas.com | Apache::ASP - http://www.apache-asp.org mysql alter table clicks drop index idx_test; Query OK, 891450 rows affected (57.83 sec) Records: 891450 Duplicates: 0 Warnings: 0 mysql show table status like 'clicks'; ++++++-+-+--+---++- +-++++ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment| ++++++-+-+--+---++- +-++++ | clicks | InnoDB | Dynamic| 891651 | 95 |85590016 | NULL | 58458112 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 323584 kB | ++++++-+-+--+---++- +-++++ 1 row in set (0.00 sec) mysql alter table clicks add index idx_test (client_id); Query OK, 891450 rows affected (1 min 4.73 sec) Records: 891450 Duplicates: 0 Warnings: 0 mysql show table status like 'clicks'; ++++++-+-+--+---++- +-++++ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment| ++++++-+-+--+---++- +-++++ | clicks | InnoDB | Dynamic| 891651 | 95 |85590016 | NULL | 91602944 | 0 | NULL | NULL | NULL| NULL || InnoDB free: 291840 kB |
Fw: Innodb Tablespace Management
Hi, I need to manage Innodb Tablespace very effectively from my application. To do that I require clear picture of Innodb tablespace file and its organization. My application needs to do the following 1. backup/restore of databases While doing so should calculate the size of source database, and check it against free space available in data directory. How do I calculate this figure for InnoDB databases 2. Free-Disk Space controlled inserts My application uses two databases normal and faults. I have to restrict faults database in disk size. At present if I have huge number of faults, fault database eats away the disk space(ibdata) and normal is not able to insert. I want to restrict disk space occupied by fault database(say, it can take max 5MB), how do I do it? 3. Innodb reaches table full soon. My system has only 50 MB Flash partition, where I have to store data. Innodb logs occupy 10MB and other files (.frm) occupies 10MB more, which leaves 30MB for ibdata file. Transactions return back table full when about 6000 rows are inserted to them. but show table status reports 3072KB free against the table name. Why is this contradiction? SHOW TABLE STATUS FROM Agora LIKE 'objects' | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | | objects | InnoDB | Dynamic| 6054 |955 | 5783552 | NULL |0 | 0 | NULL | NULL| NULL | NULL || InnoDB free: 3072 kB | Any Help will be greatly appreciated. Thanks a lot, Sp.Raja -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDb tablespace file compaction
Hi MySQL , I have made a test database for performance testing and my InnoDb tablespace grew from 1Gig to 8 Gig... After I dropped that test database, the tablespace stayed to the same size. How can I resize it to its original size? = - Benoit St-Jean [EMAIL PROTECTED] Yahoo! Messenger: bstjean http://cactus.swiki.net - - 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
innodb tablespace size
innodb_data_file_path=ibdata1:100M:autoextended:max:200M anyone knows what happen if the above innodb tablespace reach the limit of 200M? Thanks in advance. Natale Babbo sql __ Mio Yahoo!: personalizza Yahoo! come piace a te http://it.yahoo.com/mail_it/foot/?http://it.my.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
Re: innodb tablespace size
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 25 October 2002 14:50, Natale Babbo wrote: innodb_data_file_path=ibdata1:100M:autoextended:max:200M anyone knows what happen if the above innodb tablespace reach the limit of 200M? I would assume you will get an error: table space full :) Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) iD8DBQE9uUFeSVDhKrJykfIRAoPfAJ0UZjmT2BBal5mW/jfVgB+vu09TrQCfZbjv vq9XTdj4P5PJUz9Xfm0d61U= =yqLm -END PGP SIGNATURE- - 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: innodb tablespace size
My guess is that you can not operate the db when space is required and you get error message. Like when trying to alter a table it will tell you that the table is full. This is because MySQL will create a temporary table drop the original and rename the new one. HTH JFernando -Original Message- From: Natale Babbo [mailto:natale_babbo75;yahoo.it] Sent: October 25, 2002 08:51 To: [EMAIL PROTECTED] Subject: innodb tablespace size innodb_data_file_path=ibdata1:100M:autoextended:max:200M anyone knows what happen if the above innodb tablespace reach the limit of 200M? Thanks in advance. Natale Babbo sql __ Mio Yahoo!: personalizza Yahoo! come piace a te http://it.yahoo.com/mail_it/foot/?http://it.my.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 - 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: innodb tablespace size
At 14:50 +0200 10/25/02, Natale Babbo wrote: innodb_data_file_path=ibdata1:100M:autoextended:max:200M anyone knows what happen if the above innodb tablespace reach the limit of 200M? It stops getting bigger. :-) What do you mean by what happen? That is, what are the conditions for which you expect something to happen? If the tablespace fills up and then you run nothing but SELECT queries, nothing unusual will happen. If the tablespace fills up and you try to add more rows to your InnoDB tables, likely you won't be able to. (See http://www.innodb.com/ibman.html#Error_handling for information.) Thanks in advance. Natale Babbo sql - 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
InnoDB tablespace tools
Is there a plan to have any tablespace management tools for innobase? I personaly dont' care if these are via a separate program or through the mysql SQL interface but the following would be very handy. move tables between tablespaces list tables in a tablespace shrink tablespace It is very handy with BDB and MyISAM tables to be able to back them up directly or move them across different machines. If I have a large database server with gigs of InnoDB tablespace and we need to move a single database or table to another server it would require mysqldump and reloading the tables, which can be very slow for large tables. This seems to be the main disadvantage of the table space method. It would be nice if one could move a binary table with the following steps. - shutdown the server - copy a table into a new tablespace by itself - move the portable table space to the new server - expand the tablespace for growing room or copy the table to an existing tablespace I realize these are major things, I'm just curious if there are plans for any of the above sort of tools. - 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
