High disk usage

--0015174c1038a03d1d049bed5f2f
Content-Type: text/plain; charset=ISO-8859-1

Hello,

We have a database with about 160k tables. This database is causing very
high disk usage.
I'd like to know if there is anything we can do to optimize the database, in
order to reduce disk usage.

With a show global status I see a strange behavior:
| Open_files | 286 |
| Opened_files | 1050743 |

At this time the database has just started (about 10 minutes).

Here is our my.cnf:

[mysqld2]
socket=/var/lib/mysql/mysql2.sock
port = 3306
bind-address = 172.31.235.52
pid-file = /var/lib/mysql/mysql2.pid
datadir = /var/lib/mysql/DB2
language = /usr/share/mysql/english
user=mysql
old_passwords=1

log-slow-queries = /var/log/mysql2-slow.log
innodb_file_per_table
query_cache_size = 128M
join_buffer_size = 3M
tmp_table_size = 256M
max_heap_table_size = 256M
thread_cache_size = 4
table_cache = 6608
innodb_buffer_pool_size = 2G
long_query_time = 10
log-error=/var/log/mysqld2.log

tmpdir = /tmp/tmpdir

Is there anything I can do?

Thank you.

Santiago

--0015174c1038a03d1d049bed5f2f--

Re: High disk usage

--000e0cd1d72445f52b049bedb6cc
Content-Type: text/plain; charset=ISO-8859-1

I'm not sure I made myself clear:
The problem is not disk usage, but CPU time waiting for I/O, which is very
high.

Any ideas?

Santiago Soares
Fone: (41) 8488-0537


On Thu, Feb 10, 2011 at 11:15 AM, Santiago Soares
wrote:

> Hello,
>
> We have a database with about 160k tables. This database is causing very
> high disk usage.
> I'd like to know if there is anything we can do to optimize the database,
> in order to reduce disk usage.
>
> With a show global status I see a strange behavior:
> | Open_files | 286 |
> | Opened_files | 1050743 |
>
> At this time the database has just started (about 10 minutes).
>
> Here is our my.cnf:
>
> [mysqld2]
> socket=/var/lib/mysql/mysql2.sock
> port = 3306
> bind-address = 172.31.235.52
> pid-file = /var/lib/mysql/mysql2.pid
> datadir = /var/lib/mysql/DB2
> language = /usr/share/mysql/english
> user=mysql
> old_passwords=1
>
> log-slow-queries = /var/log/mysql2-slow.log
> innodb_file_per_table
> query_cache_size = 128M
> join_buffer_size = 3M
> tmp_table_size = 256M
> max_heap_table_size = 256M
> thread_cache_size = 4
> table_cache = 6608
> innodb_buffer_pool_size = 2G
> long_query_time = 10
> log-error=/var/log/mysqld2.log
>
> tmpdir = /tmp/tmpdir
>
> Is there anything I can do?
>
> Thank you.
>
> Santiago
>
>
>

--000e0cd1d72445f52b049bedb6cc--

Re: High disk usage

--00248c0ee4aa03ac92049bee49d4
Content-Type: text/plain; charset=ISO-8859-1

Do you queries stay stuck in any particular status? (Writing to net,
Opening/Closing tables, Copying to temp table?)
What kind of disk sub system do you have? What other hardware do you have?
What is the primary engine type?

JW

On Thu, Feb 10, 2011 at 7:39 AM, Santiago Soares
wrote:

> I'm not sure I made myself clear:
> The problem is not disk usage, but CPU time waiting for I/O, which is very
> high.
>
> Any ideas?
>
> Santiago Soares
> Fone: (41) 8488-0537
>
>
> On Thu, Feb 10, 2011 at 11:15 AM, Santiago Soares
> wrote:
>
> > Hello,
> >
> > We have a database with about 160k tables. This database is causing very
> > high disk usage.
> > I'd like to know if there is anything we can do to optimize the database,
> > in order to reduce disk usage.
> >
> > With a show global status I see a strange behavior:
> > | Open_files | 286 |
> > | Opened_files | 1050743 |
> >
> > At this time the database has just started (about 10 minutes).
> >
> > Here is our my.cnf:
> >
> > [mysqld2]
> > socket=/var/lib/mysql/mysql2.sock
> > port = 3306
> > bind-address = 172.31.235.52
> > pid-file = /var/lib/mysql/mysql2.pid
> > datadir = /var/lib/mysql/DB2
> > language = /usr/share/mysql/english
> > user=mysql
> > old_passwords=1
> >
> > log-slow-queries = /var/log/mysql2-slow.log
> > innodb_file_per_table
> > query_cache_size = 128M
> > join_buffer_size = 3M
> > tmp_table_size = 256M
> > max_heap_table_size = 256M
> > thread_cache_size = 4
> > table_cache = 6608
> > innodb_buffer_pool_size = 2G
> > long_query_time = 10
> > log-error=/var/log/mysqld2.log
> >
> > tmpdir = /tmp/tmpdir
> >
> > Is there anything I can do?
> >
> > Thank you.
> >
> > Santiago
> >
> >
> >
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--00248c0ee4aa03ac92049bee49d4--

Re: High disk usage

--0015174c100003d253049beecf33
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Feb 10, 2011 at 2:15 PM, Santiago Soares
wrote:

> With a show global status I see a strange behavior:
> | Open_files | 286 |
> | Opened_files | 1050743 |
>
> At this time the database has just started (about 10 minutes).
>

That's quite a lot for ten minutes, yes. Increasing the open table cache,
query cache and/or index cache might help, depending on data size, table
types (I'd assume mostly InnoDB based off your pool size), et cetera.

Basically it's a matter of finding out why you open and close so many files,
as that's likely to be one of the causes of the I/O waits.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0015174c100003d253049beecf33--

Re: High disk usage

--0015174c36ae7016e8049bf20ec1
Content-Type: text/plain; charset=ISO-8859-1

Hello, thanks for your answers.
All of my tables are MyISAM.
I found out that the application runs a lot of 'show tables' commands.
These commands take a lot of time to run, due to the high number of tables
(~160k).
Is there anything I can do to make 'show tables' run faster?

Santiago Soares
Fone: (41) 8488-0537


On Thu, Feb 10, 2011 at 12:58 PM, Johan De Meersman wrote:

> On Thu, Feb 10, 2011 at 2:15 PM, Santiago Soares > > wrote:
>
>> With a show global status I see a strange behavior:
>> | Open_files | 286 |
>> | Opened_files | 1050743 |
>>
>> At this time the database has just started (about 10 minutes).
>>
>
> That's quite a lot for ten minutes, yes. Increasing the open table cache,
> query cache and/or index cache might help, depending on data size, table
> types (I'd assume mostly InnoDB based off your pool size), et cetera.
>
> Basically it's a matter of finding out why you open and close so many
> files, as that's likely to be one of the causes of the I/O waits.
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--0015174c36ae7016e8049bf20ec1--

Re: High disk usage

--0015174c3e16a9c25b049bf210a7
Content-Type: text/plain; charset=ISO-8859-1

By the way, the 'show tables' commands get stuck in the 'checking
permissions' state.

Santiago Soares
Fone: (41) 8488-0537


On Thu, Feb 10, 2011 at 4:48 PM, Santiago Soares
wrote:

> Hello, thanks for your answers.
> All of my tables are MyISAM.
> I found out that the application runs a lot of 'show tables' commands.
> These commands take a lot of time to run, due to the high number of tables
> (~160k).
> Is there anything I can do to make 'show tables' run faster?
>
>
> Santiago Soares
> Fone: (41) 8488-0537
>
>
> On Thu, Feb 10, 2011 at 12:58 PM, Johan De Meersman wrote:
>
>> On Thu, Feb 10, 2011 at 2:15 PM, Santiago Soares <
>> santiagosoares@gmail.com> wrote:
>>
>>> With a show global status I see a strange behavior:
>>> | Open_files | 286 |
>>> | Opened_files | 1050743 |
>>>
>>> At this time the database has just started (about 10 minutes).
>>>
>>
>> That's quite a lot for ten minutes, yes. Increasing the open table cache,
>> query cache and/or index cache might help, depending on data size, table
>> types (I'd assume mostly InnoDB based off your pool size), et cetera.
>>
>> Basically it's a matter of finding out why you open and close so many
>> files, as that's likely to be one of the causes of the I/O waits.
>>
>> --
>> Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>
>
>

--0015174c3e16a9c25b049bf210a7--

Re: High disk usage

FWIW, I've found MySQL information_schema related features to be quite =
useless when you're dealing with that many tables. Not only do they =
take long, they also activate a lot of locks (or so it seems). So I =
created a script that runs every minute on each db server that basically =
does an "ls" in the data directory, writes a text file and then loads =
that file into a table. Then you need to use SELECT in that table to =
find out which tables there are.

YMMV.


Elizabeth Mattijsen
==================== =====3D=
==============
On Feb 10, 2011, at 7:48 PM, Santiago Soares wrote:
> Hello, thanks for your answers.
> All of my tables are MyISAM.
> I found out that the application runs a lot of 'show tables' commands.
> These commands take a lot of time to run, due to the high number of =
tables
> (~160k).
> Is there anything I can do to make 'show tables' run faster?
>=20
> Santiago Soares
> Fone: (41) 8488-0537
>=20
>=20
> On Thu, Feb 10, 2011 at 12:58 PM, Johan De Meersman =
wrote:
>=20
>> On Thu, Feb 10, 2011 at 2:15 PM, Santiago Soares =
>>> wrote:
>>=20
>>> With a show global status I see a strange behavior:
>>> | Open_files | 286 |
>>> | Opened_files | 1050743 |
>>>=20
>>> At this time the database has just started (about 10 minutes).
>>>=20
>>=20
>> That's quite a lot for ten minutes, yes. Increasing the open table =
cache,
>> query cache and/or index cache might help, depending on data size, =
table
>> types (I'd assume mostly InnoDB based off your pool size), et cetera.
>>=20
>> Basically it's a matter of finding out why you open and close so many
>> files, as that's likely to be one of the causes of the I/O waits.
>>=20
>> --
>> Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: High disk usage

--0015174c36ae56222f049bf34154
Content-Type: text/plain; charset=ISO-8859-1

Thank you for your answers.
They are all in the same schema, the file system is ext3.
I cannot modify the app so it uses a table of table names, instead of show
tables.
I'll try a renice on the mysql process, so it doesn't kill the server.

Santiago Soares
Fone: (41) 8488-0537


On Thu, Feb 10, 2011 at 5:20 PM, Singer X.J. Wang wrote:

> Also, are they in one schema? And what's your file system?
>
> Singer
>
>
> On Thu, Feb 10, 2011 at 14:19, Singer X.J. Wang wrote:
>
>> Okay, I'm going to chime in here and make a suggestion to re-design your
>> schema. You're looking at almost 500,000 files in your case if not even
>> more. I'd suggest bumping up the open table limit in MySQL and open file
>> limit in the OS as a first step, but its a stop gap.
>>
>>
>>
>> On Thu, Feb 10, 2011 at 14:14, Elizabeth Mattijsen wrote:
>>
>>> FWIW, I've found MySQL information_schema related features to be quite
>>> useless when you're dealing with that many tables. Not only do they take
>>> long, they also activate a lot of locks (or so it seems). So I created a
>>> script that runs every minute on each db server that basically does an "ls"
>>> in the data directory, writes a text file and then loads that file into a
>>> table. Then you need to use SELECT in that table to find out which tables
>>> there are.
>>>
>>> YMMV.
>>>
>>>
>>> Elizabeth Mattijsen
>>> =======================================
>>> On Feb 10, 2011, at 7:48 PM, Santiago Soares wrote:
>>> > Hello, thanks for your answers.
>>> > All of my tables are MyISAM.
>>> > I found out that the application runs a lot of 'show tables' commands.
>>> > These commands take a lot of time to run, due to the high number of
>>> tables
>>> > (~160k).
>>> > Is there anything I can do to make 'show tables' run faster?
>>> >
>>> > Santiago Soares
>>> > Fone: (41) 8488-0537
>>> >
>>> >
>>> > On Thu, Feb 10, 2011 at 12:58 PM, Johan De Meersman <
>>> vegivamp@tuxera.be>wrote:
>>> >
>>> >> On Thu, Feb 10, 2011 at 2:15 PM, Santiago Soares <
>>> santiagosoares@gmail.com
>>> >>> wrote:
>>> >>
>>> >>> With a show global status I see a strange behavior:
>>> >>> | Open_files | 286 |
>>> >>> | Opened_files | 1050743 |
>>> >>>
>>> >>> At this time the database has just started (about 10 minutes).
>>> >>>
>>> >>
>>> >> That's quite a lot for ten minutes, yes. Increasing the open table
>>> cache,
>>> >> query cache and/or index cache might help, depending on data size,
>>> table
>>> >> types (I'd assume mostly InnoDB based off your pool size), et cetera.
>>> >>
>>> >> Basically it's a matter of finding out why you open and close so many
>>> >> files, as that's likely to be one of the causes of the I/O waits.
>>> >>
>>> >> --
>>> >> Bier met grenadyn
>>> >> Is als mosterd by den wyn
>>> >> Sy die't drinkt, is eene kwezel
>>> >> Hy die't drinkt, is ras een ezel
>>> >>
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=wang@singerwang.com
>>>
>>>
>>
> --
> The best compliment you could give Pythian for our service is a referral.
>
>
>

--0015174c36ae56222f049bf34154--