Re: Altering database size to add more space
On Fri, Jun 25, 2010 at 7:11 AM, Prabhat Kumar aim.prab...@gmail.comwrote: In case MyISAM it will grow up to space on your data drive or the Max size of file limited by OS.. Not entirely correct. There is some kind of limit to a MyISAM file that has to do with pointer size - I've encountered it several years ago. You shouldn't be encountering it, in most circumstances, but that's what the max_data_length column in *show table status* is about. Before 5.0.6, the default max datafile size was 4G, but that's been upped to 256T now. If you're really running in to this have a look at the various advanced options for create/alter table, like avg_row_length and max_rows; as well as the variable myisam_data_pointer_size. Now what Sarkis is running into, is more of a logical error: data_free does not tell you how much free space there is *for data*, but how much free space there is *in the existing datafile*. That is, it really tells you how much space in your file has become free by deleting rows et al. This also explains why it's always 0 for InnoDB tables :-) -- 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: Altering database size to add more space
I think you're confusing table size with data base size. The original post grouped by schema so it appears the question concerns database size. I don't believe mysql imposes any limits on that. Is there a limit on the number of tables you can have in a schema imposed by mysql? On Fri, Jun 25, 2010 at 4:13 AM, Johan De Meersman vegiv...@tuxera.bewrote: On Fri, Jun 25, 2010 at 7:11 AM, Prabhat Kumar aim.prab...@gmail.comwrote: In case MyISAM it will grow up to space on your data drive or the Max size of file limited by OS.. Not entirely correct. There is some kind of limit to a MyISAM file that has to do with pointer size - I've encountered it several years ago. You shouldn't be encountering it, in most circumstances, but that's what the max_data_length column in *show table status* is about. Before 5.0.6, the default max datafile size was 4G, but that's been upped to 256T now. If you're really running in to this have a look at the various advanced options for create/alter table, like avg_row_length and max_rows; as well as the variable myisam_data_pointer_size. Now what Sarkis is running into, is more of a logical error: data_free does not tell you how much free space there is *for data*, but how much free space there is *in the existing datafile*. That is, it really tells you how much space in your file has become free by deleting rows et al. This also explains why it's always 0 for InnoDB tables :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Altering database size to add more space
On Fri, 25 Jun 2010 06:31:11 -0500, Jim Lyons jlyons4...@gmail.com wrote: I think you're confusing table size with data base size. The original post grouped by schema so it appears the question concerns database size. I don't believe mysql imposes any limits on that. Is there a limit on the number of tables you can have in a schema imposed by mysql? Not by MySQL. On some file systems, there's a practical limit of ~10k tables/database. At that point, looking up directory entries can cause slowdowns. / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Altering database size to add more space
I feel like I am missing something, because I am not able to find the answer to this simple question. How can I increase the size of a database? I am using the following query to check the available space and notice that it is time to increase. SELECT table_schema AS 'Db Name', Round( Sum( data_length + index_length ) / 1024 / 1024, 3) AS 'Db Size (MB)', Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)' FROM information_schema.tables GROUP BY table_schema ; Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Altering database size to add more space
what is the innodb file size that u have specified in my.cnf. If the last file is autoextend, that this will grow to the size of the disk space avaliable. regards anandkl On Thu, Jun 24, 2010 at 7:43 PM, Sarkis Karayan skara...@gmail.com wrote: I feel like I am missing something, because I am not able to find the answer to this simple question. How can I increase the size of a database? I am using the following query to check the available space and notice that it is time to increase. SELECT table_schema AS 'Db Name', Round( Sum( data_length + index_length ) / 1024 / 1024, 3) AS 'Db Size (MB)', Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)' FROM information_schema.tables GROUP BY table_schema ; Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: Altering database size to add more space
What do you mean time to increase? What tells you that? A database's size is determined by the amount of available diskspace. If you need more than the filesystem that it is currently on has, then you can either move the entire schema (which is synonymous to database) to another filesystem and symlink it. You can also store individual tables and table files on other file systems and symlink those. Either way, you have the total collection of disk space available to you. This assumes a Linux OS. If your innodb tables are being restricted, you need to see if you have a max size defined for the table space and if that is what you're bumping into. On Thu, Jun 24, 2010 at 9:13 AM, Sarkis Karayan skara...@gmail.com wrote: I feel like I am missing something, because I am not able to find the answer to this simple question. How can I increase the size of a database? I am using the following query to check the available space and notice that it is time to increase. SELECT table_schema AS 'Db Name', Round( Sum( data_length + index_length ) / 1024 / 1024, 3) AS 'Db Size (MB)', Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)' FROM information_schema.tables GROUP BY table_schema ; Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Altering database size to add more space
There is 2 way to check databases size : A. OS level, you can do *#du -hs *of data dir , it will show current usages of you database size at File system level. B. You can also check on Database level check details herehttp://adminlinux.blogspot.com/2009/12/mysql-tips-calculate-database-and-table.html How can I increase the size of a database? Its depend upon the how have you configured my.cnf and the type of engine you are using. In case MyISAM it will grow up to space on your data drive or the Max size of file limited by OS.. and In case of Innodb it will also grow up to space on you drive same as for MyISAM, since its default configuration is *autoextend* innodb_data_file_path=ibdata1:50M;ibdata2:50M:*autoextend* Check details herehttp://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html --Prabhat On Thu, Jun 24, 2010 at 9:00 PM, Jim Lyons jlyons4...@gmail.com wrote: What do you mean time to increase? What tells you that? A database's size is determined by the amount of available diskspace. If you need more than the filesystem that it is currently on has, then you can either move the entire schema (which is synonymous to database) to another filesystem and symlink it. You can also store individual tables and table files on other file systems and symlink those. Either way, you have the total collection of disk space available to you. This assumes a Linux OS. If your innodb tables are being restricted, you need to see if you have a max size defined for the table space and if that is what you're bumping into. On Thu, Jun 24, 2010 at 9:13 AM, Sarkis Karayan skara...@gmail.com wrote: I feel like I am missing something, because I am not able to find the answer to this simple question. How can I increase the size of a database? I am using the following query to check the available space and notice that it is time to increase. SELECT table_schema AS 'Db Name', Round( Sum( data_length + index_length ) / 1024 / 1024, 3) AS 'Db Size (MB)', Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)' FROM information_schema.tables GROUP BY table_schema ; Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
