I am having trouble finding the meaning of "opens: xxx" when I issue a
"status" command at MySQL command line. It does not seem to be the
same thing as Variable_Names "Open_files", or "Open_tables", etc, as I
am not getting the same values.

I have a J2EE app that is slowing down after a day or so, after I
moved it from MySQL 3.23.5x to 5.0.45. I noticed when the problem
happens I see a parameter of [or as high as] opens:549 from my status
command. If I shutdown/start MySQL, the problem goes away, and opens:
parameter starts off again in the single digits....but starts
gradually growing again.

Does this mean I have 549 live open connections via my connector/j
driver from my app? I haven't changed my app at all.....and I suppose
I need to find out what "opens:xxx" means, but am not finding it. All
docs that I Google or Dubois' MySQL book refer to all the other
"Open_xxxx" parameters.

Thanks for any ,
Rob

>I am having trouble finding the meaning of "opens: xxx" when I issue a
>"status" command at MySQL command line. It does not seem to be the
>same thing as Variable_Names "Open_files", or "Open_tables", etc, as I
>am not getting the same values.

There is a difference between "I have opened XXX tables since the
server restarted" and "I have YYY tables open right now". Similarly,
there's a difference between "my server has answered 2.7 million
questions since it was last restarted" (about 1 a second for a
month) vs. "my server is processing 2.7 million questions right
now" (which is ridiculous).

>I have a J2EE app that is slowing down after a day or so, after I
>moved it from MySQL 3.23.5x to 5.0.45. I noticed when the problem
>happens I see a parameter of [or as high as] opens:549 from my status
>command. If I shutdown/start MySQL, the problem goes away, and opens:
>parameter starts off again in the single digits....but starts
>gradually growing again.

I would expect the number of opens to never decrease except when
you restart the server. The same goes for the number of questions.

As the server stays up longer, it tends to get bigger, either because
it is fully utilizing the memory it is supposed to use (various
caches fill up, etc) or because it is leaking memory. If MySQL and
J2EE is running on the same server, perhaps they are fighting over
available memory. Or perhaps the total memory to be used by MySQL
is just too much for the available memory on the server (so tune
the parameters lower, or get more memory). The number of opens may
roughly correspond to server uptime.

>Does this mean I have 549 live open connections via my connector/j
>driver from my app?

No, 549 connections since startup. This is not inconsistent with a
maximum of 10 simultaneous connections.

>I haven't changed my app at all.....and I suppose
>I need to find out what "opens:xxx" means, but am not finding it. All
>docs that I Google or Dubois' MySQL book refer to all the other
>"Open_xxxx" parameters.

rob_mazur <mazurr@gmail.com> wrote:

> I am having trouble finding the meaning of "opens: xxx" when I issue a
> "status" command at MySQL command line. It does not seem to be the
> same thing as Variable_Names "Open_files", or "Open_tables", etc, as I
> am not getting the same values.

This variable corresponds to the global (!) Opened_tables counter.
This number usually increases after a server restart until it reaches
a value around table_cache, then grows only slowly. If this number
increases fast, it could mean your table_cache is too small:

http://dev.mysql.com/doc/refman/5.0/...ld_table_cache

> I have a J2EE app that is slowing down after a day or so, after I
> moved it from MySQL 3.23.5x to 5.0.45. I noticed when the problem
> happens I see a parameter of [or as high as] opens:549 from my status
> command. If I shutdown/start MySQL, the problem goes away, and opens:
> parameter starts off again in the single digits....but starts
> gradually growing again.

I doubt there's correlation between this number and a general slowness
of MySQL. Of course restarting MySQL will reset this counter. But OTOH
549 is a rather low number (after a few days uptime). Problem could be
anything; not enough information to go on.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Thanks guys for the responses. Hmmm? Well, I suppose my problem
could be somewhere else. But I am still a little unsure about
something. First, here is more background....

- MySQL has exclusive use of the server (ie the J2EE app is not on it)
- the server has 8GB of RAM
- my-large.cnf is in /etc

>>Rob:
>>Does this mean I have 549 live open connections via my connector/j
>>driver from my app?

>Gordon
>No, 549 connections since startup. This is not inconsistent with a
>maximum of 10 simultaneous connections.

That confuses me though. Because I have another MySQL install that I
look after, and it shows Opens:0, yet I know that machine is active.
May we compare my two machines?:
------------------------------
MACHINE 1
(involved in the problem I am asking about, MySQL has whole server,
8GB RAM)
Threads: 5 Questions: 12940 Slow queries: 0 Opens: 191 Flush
tables: 1 Open tables: 64 Queries per second avg: 0.246

MACHINE 2
(runs fine, MySQL has the whole server, 4GB RAM)
Threads: 2 Questions: 79842768 Slow queries: 0 Opens: 0 Flush
tables: 1 Open tables: 64 Queries per second avg: 1.558
-----------------------------

Two quick notes.....these 2 databases serve different data (they are
used by the same apps). And, MACHINE 1 was just restarted and has
only about an hour of very low use on it (but the office if filling in
now, it will rise). If Opens: 549 (from my previous posting) is the
connections since startup, why does MACHINE 2 have Opens: 0 ? I know
that database is consistently used, and runs nicely.

Also, I would estimate that I would reach 549 connections in....man,
about 5 minutes in the middle of the day. So that fact, plus MACHINE
2 having Opens: 0 is confusing me?

I see my server variable of table_cache is 64, so the fact both server
have reached Open Tables: 64 is not a surpirse. Is "Opens:" really
the number of database connections since uptime? If yes, what would
cause MACHINE 1 to be so low, and MACHINE 2 to be 0?

Rob

On Oct 4, 7:52 am, rob_mazur <maz...@gmail.com> wrote:
> Two quick notes.....these 2 databases serve different data (they are
> used by the same apps).

Whoops.....a very key typo above. I meant to say these two database
instances are *NOT* used by the same apps. Two different apps, two
different offices/networks, in fact.

Sorry for the confusion.

Rob

I am still comparing my two MySQL instances. Here is one key
difference that I saw out of MySQL Administrator:


MACHINE 1 (involved in the problem I am asking about, MySQL has whole
server, 8GB RAM)
Key buffer usage 4,455,424 s
maximal: 100
average: 11

MACHINE 2 (runs fine, MySQL has the whole server, 4GB RAM)
Key buffer usage 472,064 s
maximal: 0
average: 0

Out of the docs:
"The horizontal bar graph indicates how much of the allocated key
buffer is in use. If a fairly large percentage of the key buffer is
in use you may wish to increase the key_cache_size setting in the
Startup Variables section."

My horizontal bar on MACHINE 1 is 5/8 of the way across. I don't have
a key_cache_size in my un-modified /etc/my-large.cnf. Is that the
same as key_buffer (I have listed some of my config below). What is
the key_cache_size? Something related to cache availble to keep table
primary keys in memory?

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M