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=gcdmg-mysql-2@m.gmane.org

Re: Altering database size to add more space

--001636e0a80c8240450489c829d2
Content-Type: text/plain; charset=ISO-8859-1

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 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=anandkl@gmail.com
>
>

--001636e0a80c8240450489c829d2--

Re: Altering database size to add more space

--0016e6541d883b47dc0489c8569d
Content-Type: text/plain; charset=ISO-8859-1

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 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=jlyons4435@gmail.com
>
>


--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e6541d883b47dc0489c8569d--

Re: Altering database size to add more space

--0016363b7dd0665b610489d3cdd4
Content-Type: text/plain; charset=ISO-8859-1

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
here

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 here

--Prabhat


On Thu, Jun 24, 2010 at 9:00 PM, Jim Lyons 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
> 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=jlyons4435@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

--0016363b7dd0665b610489d3cdd4--

Re: Altering database size to add more space

--00163692034dc5f5b10489d72e24
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Jun 25, 2010 at 7:11 AM, Prabhat Kumar wrote:

> 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

--00163692034dc5f5b10489d72e24--

Re: Altering database size to add more space

--001636416b29ac74240489d91b82
Content-Type: text/plain; charset=ISO-8859-1

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 wrote:

> On Fri, Jun 25, 2010 at 7:11 AM, Prabhat Kumar wrote:
>
>> 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

--001636416b29ac74240489d91b82--

Re: Altering database size to add more space

On Fri, 25 Jun 2010 06:31:11 -0500, Jim Lyons

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=gcdmg-mysql-2@m.gmane.org