Re: MySQL Closing/Opening tables

[Dan Nelson]

In the last episode (Feb 27), dbrb2002-...@yahoo.com said:
> Thanks Dan.. thats a valuable point.. and this actually happening with
> MyISAM tables only..
> 
> But the question is; when I set the table_cache to higher than total
> tables..  then it should stop closing the table in first place..so that
> only un-opened tables will be opened and kept in cache..  it will avoid
> closing and re-opening..  but looks like it is not the case..

Even though the State column says "closing tables", it's just flushing dirty
key blocks.  I bet if you ran "show status like 'Opened_tables'" you
wouldn't see that number increment at all.

-- 
Dan Nelson
dnel...@allantgroup.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: MySQL Closing/Opening tables

[Eric Bergen]

MySQL can open a single table multiple times depending on how many
clients need to use it. This means that having a table_cache the same
as the total_tables will only work if your mysql server only has one
client.

For more details read:
http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

On Fri, Feb 27, 2009 at 2:53 PM,   wrote:
> Thanks Dan.. thats a valuable point.. and this actually happening with MyISAM 
> tables only..
>
> But the question is; when I set the table_cache to higher than total tables.. 
> then it should stop closing the table in first place..so that only un-opened 
> tables will be opened and kept in cache.. it will avoid closing and 
> re-opening.. but looks like it is not the case..
>
> Unless the table_cache is also used(unlikely) for temporary tables which are 
> created by select queries..
>
>
>
>
> 
> From: Dan Nelson 
> To: dbrb2002-...@yahoo.com
> Cc: mysql@lists.mysql.com
> Sent: Friday, February 27, 2009 1:15:25 PM
> Subject: Re: MySQL Closing/Opening tables
>
> In the last episode (Feb 27), dbrb2002-...@yahoo.com said:
>> Recently I noticed the server takes lot of time on and off when opening
>> and closing tables.  And I tried to increase the table_cache more the the
>> total tables (file_limit is properly set); and the problem still continues
>> and lowering it also continues..  and tried to set in middle..  same
>
> MyISAM tables flush dirty index blocks at the end of every update; this can
> cause a long wait inside "closing tables".  If you have just deleted a lot
> of rows or did some other update touching many rows, you might have to flush
> a lot of dirty blocks.  Running "show status like 'Key_blocks_not_flushed'"
> during one of these periods should show the count starting out large,
> dropping rapidly, then leveling off when that table's blocks have been
> flushed.
>
> Fixes include:
>
> * Altering your troublesome tables and adding the DELAY_KEY_WRITE=1 option.
>  This will force you to repair those tables after a mysql or OS crash,
>  since the on-disk copies of the index will almost always be out of synch.
>
> * Switching to an engine with logging like InnoDB will allow mysql to write
>  the changes to a transaction log immediately, then trickle out the actual
>  key block updates over time.  If you want to try out mysql 6.0, the maria
>  engine is basically MyISAM with logging.
>
> --
>    Dan Nelson
>    dnel...@allantgroup.com
>



-- 
Eric Bergen
eric.ber...@provenscaling.com
http://www.provenscaling.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: MySQL Closing/Opening tables

[dbrb2002-sql]

Thanks Dan.. thats a valuable point.. and this actually happening with MyISAM 
tables only..

But the question is; when I set the table_cache to higher than total tables.. 
then it should stop closing the table in first place..so that only un-opened 
tables will be opened and kept in cache.. it will avoid closing and 
re-opening.. but looks like it is not the case..

Unless the table_cache is also used(unlikely) for temporary tables which are 
created by select queries..





From: Dan Nelson 
To: dbrb2002-...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Friday, February 27, 2009 1:15:25 PM
Subject: Re: MySQL Closing/Opening tables

In the last episode (Feb 27), dbrb2002-...@yahoo.com said:
> Recently I noticed the server takes lot of time on and off when opening
> and closing tables.  And I tried to increase the table_cache more the the
> total tables (file_limit is properly set); and the problem still continues
> and lowering it also continues..  and tried to set in middle..  same

MyISAM tables flush dirty index blocks at the end of every update; this can
cause a long wait inside "closing tables".  If you have just deleted a lot
of rows or did some other update touching many rows, you might have to flush
a lot of dirty blocks.  Running "show status like 'Key_blocks_not_flushed'"
during one of these periods should show the count starting out large,
dropping rapidly, then leveling off when that table's blocks have been
flushed.

Fixes include:

* Altering your troublesome tables and adding the DELAY_KEY_WRITE=1 option. 
  This will force you to repair those tables after a mysql or OS crash,
  since the on-disk copies of the index will almost always be out of synch.

* Switching to an engine with logging like InnoDB will allow mysql to write
  the changes to a transaction log immediately, then trickle out the actual
  key block updates over time.  If you want to try out mysql 6.0, the maria
  engine is basically MyISAM with logging.

-- 
Dan Nelson
dnel...@allantgroup.com

RE: MyISAM large tables and indexes managing problems

[Rolando Edwards]

Have you tried disabling indexes while loading?
Here is what I mean...

CREATE TABLE tb1 (A INT NOT NULL AUTO INCREMENT PRIMARY KEY,B VARCHAR(20),C 
VARCHAR(10));

Load tb1 with data

Create a new table, tb2, with new structure (indexing B and C columns)
CREATE TABLE tb2 LIKE tb1;
ALTER TABLE tb2 ADD INDEX NDX1 (B);
ALTER TABLE tb2 ADD INDEX NDX2 (C);

Load tb2 with non-unique indexes turned off

ALTER TABLE tb2 DISABLE KEYS;
INSERT INTO tb2 SELECT * FROM tb1;

Only the Primary Key got loaded in tb2
Now build the other two indexes

ALTER TABLE tb2 ENABLE KEYS;

This should build the indexes linearly, loading key entries into the .MYI file 
of the MyISAM table.

Give it a try !!!


Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM : RolandoLogicWorx
Skype : RolandoLogicWorx
redwa...@logicworks.net


-Original Message-
From: Claudio Nanni [mailto:claudio.na...@gmail.com] 
Sent: Friday, February 27, 2009 4:43 PM
To: mysql@lists.mysql.com
Subject: MyISAM large tables and indexes managing problems

Hi,
I have one 15GB table with 250 million records and just the primary key,
it is a very simple table but when a report is run (query) it just takes
hours,
and sometimes the application hangs.
I was trying to play a little with indexes and tuning (there is not great
indexes to be done though)
but eveytime I try to alter table for indexes it just hogs the disk space
and takes hours
to try to build indexes in various passages(.TMD) but it is a real pain
since I cannot even kill the mysql process,
and I had to kill the server with table corruption and had to stop/start and
repair table.
Does anybody experience problems in managing a simple MyISAM table with  250
million records and a primary key?
I tried also to duplicate the table, add indexes and insert into it (also
using INNODB for the new table) but it is really
taking ages everytime. And I had to move the 'tmpdir' to the data partition
because it was filling the / 100%.

MySQL is 5.0.x on 64bit RHEL 5 with 16GB RAM and NAS storage.

Any hint on how to manage big tables?

Thanks

Claudio Nanni

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: MyISAM large tables and indexes managing problems

[Claudio Nanni]

Great Brent, helps a lot!
it is very good to know your experience.
I will speak to developers and try to see if there is the opportunity to 
apply the 'Divide et Impera' principle!
I am sorry to say MySQL it is a little out of control when dealing with 
huge tables, it is the first time I had to kill MySQL deamon a couple of 
times.


Thanks again Brent

Claudio



Brent Baisley wrote:

I've used a similar setup and hit up to 400 million with 5-7 million
records being added and deleted per day. Processing the table like you
mention gave me the exact same result. The query was just too big for
MySQL to handle. If you can break down your query into multiple
smaller queries, it will run much quicker. I went from 7-8 hours down
to 10 minutes. I broke my query into increments based on date/time and
merged the results.
I also switched to using MERGE tables so I could create a much narrow
set of tables to query on (i.e. current_month). Of course, this means
splitting your table into separate tables based on a certain criteria.
Basically, divide and conquer.

Hope that helps.

Brent Baisley

On Fri, Feb 27, 2009 at 4:42 PM, Claudio Nanni  wrote:
  

Hi,
I have one 15GB table with 250 million records and just the primary key,
it is a very simple table but when a report is run (query) it just takes
hours,
and sometimes the application hangs.
I was trying to play a little with indexes and tuning (there is not great
indexes to be done though)
but eveytime I try to alter table for indexes it just hogs the disk space
and takes hours
to try to build indexes in various passages(.TMD) but it is a real pain
since I cannot even kill the mysql process,
and I had to kill the server with table corruption and had to stop/start and
repair table.
Does anybody experience problems in managing a simple MyISAM table with  250
million records and a primary key?
I tried also to duplicate the table, add indexes and insert into it (also
using INNODB for the new table) but it is really
taking ages everytime. And I had to move the 'tmpdir' to the data partition
because it was filling the / 100%.

MySQL is 5.0.x on 64bit RHEL 5 with 16GB RAM and NAS storage.

Any hint on how to manage big tables?

Thanks

Claudio Nanni




  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

MyISAM large tables and indexes managing problems

[Claudio Nanni]

Hi,
I have one 15GB table with 250 million records and just the primary key,
it is a very simple table but when a report is run (query) it just takes
hours,
and sometimes the application hangs.
I was trying to play a little with indexes and tuning (there is not great
indexes to be done though)
but eveytime I try to alter table for indexes it just hogs the disk space
and takes hours
to try to build indexes in various passages(.TMD) but it is a real pain
since I cannot even kill the mysql process,
and I had to kill the server with table corruption and had to stop/start and
repair table.
Does anybody experience problems in managing a simple MyISAM table with  250
million records and a primary key?
I tried also to duplicate the table, add indexes and insert into it (also
using INNODB for the new table) but it is really
taking ages everytime. And I had to move the 'tmpdir' to the data partition
because it was filling the / 100%.

MySQL is 5.0.x on 64bit RHEL 5 with 16GB RAM and NAS storage.

Any hint on how to manage big tables?

Thanks

Claudio Nanni

is there another way

[PJ]

Is there another way to determine what the query found? The input is a
string "whatever".
I'm looking for a return of a string or null or nuber of rows or
something that will permit to to channel the execution of the file.
The snippet below is good for (there is a match) and (there is no match)
but I get the feeling that there must be a quicker way to determine if
the $result is positive or negative. ??? :-\

  while ( $row = mysql_fetch_array($result) ) {
echo("" . $row["title"] . "");
  }
  if ($row["title"] == "")
  echo ("Empty!")

-- 

Phil Jourdan --- p...@ptahhotep.com
   http://www.ptahhotep.com
   http://www.chiccantine.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

whoa!

[PJ]

What is this supposed to mean from the manual:
The use of |mysql_num_rows()|
 depends on
whether you use |mysql_store_result()|
 or
|mysql_use_result()|
 to return
the result set
|mysql_num_rows()|
 is intended
for use with statements that return a result set, such as |SELECT|
.
Does this mean you have to use
mysql_store_result() before using mysql_num_rows() ? kind of doesn't
make sense to have to do that. And there are no clear cut examples or
explanations...
I do not wish to piss-and-moan but I do find that there is a lot to be
desired in the manual; things that are very ;contradictory and/ or
unclear and certainly lacking in examples.

And please, somebody guide me to some tutorial or something where I can
learn to set up proper error checking so you guys don't have to listen
to my problems. :'(
-- 

Phil Jourdan --- p...@ptahhotep.com
http://www.ptahhotep.com
http://www.chiccantine.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: MySQL Closing/Opening tables

[Wm Mussatto]

Looks like the system is doing a lot of disk WRITES.  Your writes/sec are
much higher than your reads/sec. But the time stuff waits in the queue is
low. Did you try top -i with the x option entered?  That will produce a
colored line if a dask is I/O bound.

On Fri, February 27, 2009 11:51, dbrb2002-...@yahoo.com wrote:
> Thanks for the quick followup Baron..
>
>  vmstat
> procs ---memory-- ---swap-- -io --system--
> -cpu--
>  r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id
> wa st
>  3  0100 499380 139256 560400000   190   693   11   11 20  2
> 70  8  0
>
> iostat -dx 5 5
>
> Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
> avgqu-sz   await  svctm  %util
> sda   0.02   187.72  0.28 10.36 4.66  1584.73   149.44
> 2.03  191.16   5.23   5.56
> sdb   1.85   371.84 21.72 56.86  1514.44  3956.6769.63
> 2.63   33.43   3.44  27.03
>
> Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
> avgqu-sz   await  svctm  %util
> sda   0.00  1527.80  0.40 73.80 3.20 12812.80   172.72
> 1.59   21.46   0.24   1.76
> sdb   0.0011.60  2.40 10.8065.60   950.4076.97
> 0.085.82   3.67   4.84
>
> Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
> avgqu-sz   await  svctm  %util
> sda   0.00   173.00  0.00 11.60 0.00  1476.80   127.31
> 0.58   50.24   8.22   9.54
> sdb   0.00 5.80  0.00 13.40 0.00   153.6011.46
> 2.61  195.13   5.63   7.54
>
> Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
> avgqu-sz   await  svctm  %util
> sda   0.00  1624.40  0.00 126.00 0.00 14003.20   111.14
>  5.31   42.14   0.38   4.84
> sdb   0.00   599.60  0.00 82.20 0.00 15697.60   190.97
> 1.79   21.75   2.32  19.08
>
> Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz
> avgqu-sz   await  svctm  %util
> sda   0.00  1538.80  0.20 61.60 1.60 12803.20   207.20
> 5.10   82.48   2.37  14.62
> sdb   0.20 1.00  7.40  2.40   200.0027.2023.18
> 0.22   22.73  10.73  10.52
>
>
> The above is when low to medium load..
>
>
>
> 
> From: Baron Schwartz 
> To: dbrb2002-...@yahoo.com
> Cc: mysql@lists.mysql.com
> Sent: Friday, February 27, 2009 5:50:11 AM
> Subject: Re: MySQL Closing/Opening tables
>
> On Fri, Feb 27, 2009 at 4:19 AM,   wrote:
>> Hi
>>
>> Recently I noticed the server takes lot of time on and off when opening
>> and closing tables. And I tried to increase the table_cache more the the
>> total tables (file_limit is properly set); and the problem still
>> continues and lowering it also continues.. and tried to set in middle..
>> same
>>
>> Any thoughts on fixing this ? I am going crazy..
>>
>> Sometimes the threads spin 10-60secs in just opening and closing tables
>> state..
>
> Have you checked to see if your disk is saturated with requests?  Try
> this:
>
> vmstat 5 5
> iostat -dx 5 5
>
> Assuming you're on a Unix-like OS.
>
> --
> Baron Schwartz, Director of Consulting, Percona Inc.
> Our Blog: http://www.mysqlperformanceblog.com/
> Our Services: http://www.percona.com/services.html
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: MySQL Closing/Opening tables

[Dan Nelson]

In the last episode (Feb 27), dbrb2002-...@yahoo.com said:
> Recently I noticed the server takes lot of time on and off when opening
> and closing tables.  And I tried to increase the table_cache more the the
> total tables (file_limit is properly set); and the problem still continues
> and lowering it also continues..  and tried to set in middle..  same

MyISAM tables flush dirty index blocks at the end of every update; this can
cause a long wait inside "closing tables".  If you have just deleted a lot
of rows or did some other update touching many rows, you might have to flush
a lot of dirty blocks.  Running "show status like 'Key_blocks_not_flushed'"
during one of these periods should show the count starting out large,
dropping rapidly, then leveling off when that table's blocks have been
flushed.

Fixes include:

* Altering your troublesome tables and adding the DELAY_KEY_WRITE=1 option. 
  This will force you to repair those tables after a mysql or OS crash,
  since the on-disk copies of the index will almost always be out of synch.

* Switching to an engine with logging like InnoDB will allow mysql to write
  the changes to a transaction log immediately, then trickle out the actual
  key block updates over time.  If you want to try out mysql 6.0, the maria
  engine is basically MyISAM with logging.

-- 
Dan Nelson
dnel...@allantgroup.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: MySQL Closing/Opening tables

[dbrb2002-sql]

Thanks for the quick followup Baron..

 vmstat
procs ---memory-- ---swap-- -io --system-- 
-cpu--
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id wa st
 3  0100 499380 139256 560400000   190   693   11   11 20  2 70  8  0

iostat -dx 5 5

Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz 
  await  svctm  %util
sda   0.02   187.72  0.28 10.36 4.66  1584.73   149.44 2.03 
 191.16   5.23   5.56
sdb   1.85   371.84 21.72 56.86  1514.44  3956.6769.63 2.63 
  33.43   3.44  27.03

Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz 
  await  svctm  %util
sda   0.00  1527.80  0.40 73.80 3.20 12812.80   172.72 1.59 
  21.46   0.24   1.76
sdb   0.0011.60  2.40 10.8065.60   950.4076.97 0.08 
   5.82   3.67   4.84

Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz 
  await  svctm  %util
sda   0.00   173.00  0.00 11.60 0.00  1476.80   127.31 0.58 
  50.24   8.22   9.54
sdb   0.00 5.80  0.00 13.40 0.00   153.6011.46 2.61 
 195.13   5.63   7.54

Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz 
  await  svctm  %util
sda   0.00  1624.40  0.00 126.00 0.00 14003.20   111.14 
5.31   42.14   0.38   4.84
sdb   0.00   599.60  0.00 82.20 0.00 15697.60   190.97 1.79 
  21.75   2.32  19.08

Device: rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz 
  await  svctm  %util
sda   0.00  1538.80  0.20 61.60 1.60 12803.20   207.20 5.10 
  82.48   2.37  14.62
sdb   0.20 1.00  7.40  2.40   200.0027.2023.18 0.22 
  22.73  10.73  10.52


The above is when low to medium load.. 




From: Baron Schwartz 
To: dbrb2002-...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Friday, February 27, 2009 5:50:11 AM
Subject: Re: MySQL Closing/Opening tables

On Fri, Feb 27, 2009 at 4:19 AM,   wrote:
> Hi
>
> Recently I noticed the server takes lot of time on and off when opening and 
> closing tables. And I tried to increase the table_cache more the the total 
> tables (file_limit is properly set); and the problem still continues and 
> lowering it also continues.. and tried to set in middle.. same
>
> Any thoughts on fixing this ? I am going crazy..
>
> Sometimes the threads spin 10-60secs in just opening and closing tables 
> state..

Have you checked to see if your disk is saturated with requests?  Try this:

vmstat 5 5
iostat -dx 5 5

Assuming you're on a Unix-like OS.

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

Re: MySQL Closing/Opening tables

[Wm Mussatto]

On Fri, February 27, 2009 05:50, Baron Schwartz wrote:
> On Fri, Feb 27, 2009 at 4:19 AM,   wrote:
>> Hi
>>
>> Recently I noticed the server takes lot of time on and off when opening
>> and closing tables. And I tried to increase the table_cache more the the
>> total tables (file_limit is properly set); and the problem still
>> continues and lowering it also continues.. and tried to set in middle..
>> same
>>
>> Any thoughts on fixing this ? I am going crazy..
>>
>> Sometimes the threads spin 10-60secs in just opening and closing tables
>> state..
>
> Have you checked to see if your disk is saturated with requests?  Try
> this:
>
> vmstat 5 5
> iostat -dx 5 5
Slight variant if you use  logical volumes.
iostat -x 10 /dev/sda /dev/sdb /dev/sdc /dev/sdd /dev/sde
Where the /dev/...'s are the actual base disks.  W/O the -d you get cpu
loads as well.   I use top -i (then z for color) if I need to know what
processes are running.  The is on Debian GNU Linux.
Look at the await column:
"The average time (in milliseconds) for I/O requests issued to the  device
 to  be served.  This includes the time spent by the requests in queue and
the time spent servicing them. "

> Assuming you're on a Unix-like OS.
>
> --
> Baron Schwartz, Director of Consulting, Percona Inc.
> Our Blog: http://www.mysqlperformanceblog.com/
> Our Services: http://www.percona.com/services.html
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=mussa...@csz.com
>
>
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Monitoring MySQL availability

[Pascal Charest]

Hi,

I'm using Nagios or Zabbix as a monitoring system for my/my clients
infrastructures.



When the client doesn't want an access to his monitoring system, I install
Nagios with a couples of fine-tuned configuration/plugins. This is mainly a
legacy position since I've been using that for years.

pro: easy to get help, lots and lots of sysadmin know nagios / community
very active
pro: very modular
pro: its nagios!

con: file based, need to be reloaded to activate new config
con: very bad error reporting when syntax is off.
con: ugly ;-)



Zabbix is the one I use when the client will request reports and access. It
is "cute" and can be configured easily through a web-gui and got an
agent/proxy process that can be used in active mode (the monitored-server
send data back to master). Very easy to craft sql statement into monitored
item (such as "time behind master")

Graphic, screen and inventory management can be done through zabbix.

The problem with Zabbix is that there isn't as many sysadmin that are
competent with it. Certifications are available though and I know a couple
sysadmin in Ottawa/Montreal/Quebec with those skills.

I've seen/deployed Zabbix quite a few time with over 500 hosts each with
about 30 different checks (items) and 45 triggers (limit on items).

pro: cute
pro: database based, new config always live.
pro: easy to define "user/group" access to "servers/ressources".
pro: can scale with proxy (relay system) or with a cluster of server (tree
system)

con: original template are not that good
con: default: lots of data to your database.
con: supported by a small cie (community not as active as Nagios)
con: zabbix is really not that great with snmp



I've also tried Zenoss and various commercial offering, but they were all
"sub-quality"/"way costlier" than those 2 solutions.

Pascal Charest

--
Pascal Charest, skype: pascal.charest
Free software consultant @ Les Laboratoires Phoenix
http://www.labsphoenix.com


2009/2/27 Claudio Nanni 

> We monitor hundreds of production systems with Nagios, of any kind.
> I dont have time to search for better ones, but this is doing its job.
>
> Cheers
>
> Claudio
>
>
> 2009/2/27 Éric Fournier 
>
> >
> >
> >
> > Hi everybody ,
> >
> >  I'm searching for a good way to monitor
> MySQL
> > availability (to be able to be alerted when it goes down unplanned) and i
> > just wanted to poke around to know which ways you people find the most
> > efficient. Are you using third party software , scripts , snmp solution
> and
> > so ? I'm doing my own research for this but thought if some of you had
> pros
> > or cons about some methods i'd be glad to hear about it.
> >
> > Thanks and good day
> >
> > *Éric Fournier*
> > *
> > Devez-vous vraiment imprimer ce courriel ? Pensons à l'environnement.*
> > *
> > Ce message est confidentiel et est à l'usage exclusif du destinataire
> > identifié ci-dessus. Toute autre personne est, par les présentes, avisée
> > qu'il lui est strictement interdit de le diffuser, de le distribuer, d'en
> > dévoiler le contenu ou de le reproduire. Si vous avez reçu cette
> > communication par erreur et que le destinataire ne peut être joint ou
> vous
> > est inconnu, veuillez en informer l'expéditeur par courrier électronique
> > immédiatement et détruire l'original de ce message ainsi que toute
> copie.*
> >
> >
>

Re: Monitoring MySQL availability

[Claudio Nanni]

We monitor hundreds of production systems with Nagios, of any kind.
I dont have time to search for better ones, but this is doing its job.

Cheers

Claudio


2009/2/27 Éric Fournier 

>
>
>
> Hi everybody ,
>
>  I'm searching for a good way to monitor MySQL
> availability (to be able to be alerted when it goes down unplanned) and i
> just wanted to poke around to know which ways you people find the most
> efficient. Are you using third party software , scripts , snmp solution and
> so ? I'm doing my own research for this but thought if some of you had pros
> or cons about some methods i'd be glad to hear about it.
>
> Thanks and good day
>
> *Éric Fournier*
> *
> Devez-vous vraiment imprimer ce courriel ? Pensons à l'environnement.*
> *
> Ce message est confidentiel et est à l'usage exclusif du destinataire
> identifié ci-dessus. Toute autre personne est, par les présentes, avisée
> qu'il lui est strictement interdit de le diffuser, de le distribuer, d'en
> dévoiler le contenu ou de le reproduire. Si vous avez reçu cette
> communication par erreur et que le destinataire ne peut être joint ou vous
> est inconnu, veuillez en informer l'expéditeur par courrier électronique
> immédiatement et détruire l'original de ce message ainsi que toute copie.*
>
>

Re: MySQL Closing/Opening tables

[Baron Schwartz]

On Fri, Feb 27, 2009 at 4:19 AM,   wrote:
> Hi
>
> Recently I noticed the server takes lot of time on and off when opening and 
> closing tables. And I tried to increase the table_cache more the the total 
> tables (file_limit is properly set); and the problem still continues and 
> lowering it also continues.. and tried to set in middle.. same
>
> Any thoughts on fixing this ? I am going crazy..
>
> Sometimes the threads spin 10-60secs in just opening and closing tables 
> state..

Have you checked to see if your disk is saturated with requests?  Try this:

vmstat 5 5
iostat -dx 5 5

Assuming you're on a Unix-like OS.

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Monitoring MySQL availability

[Éric Fournier]

Hi everybody , 

 I'm searching for a good way to monitor MySQL 
availability (to be able to be alerted when it goes down unplanned) and i 
just wanted to poke around to know which ways you people find the most 
efficient. Are you using third party software , scripts , snmp solution 
and so ? I'm doing my own research for this but thought if some of you had 
pros or cons about some methods i'd be glad to hear about it. 

Thanks and good day

Éric Fournier

Devez-vous vraiment imprimer ce courriel ? Pensons à l'environnement.

Ce message est confidentiel et est à l'usage exclusif du destinataire 
identifié ci-dessus. Toute autre personne est, par les présentes, avisée 
qu'il lui est strictement interdit de le diffuser, de le distribuer, d'en 
dévoiler le contenu ou de le reproduire. Si vous avez reçu cette 
communication par erreur et que le destinataire ne peut être joint ou vous 
est inconnu, veuillez en informer l'expéditeur par courrier électronique 
immédiatement et détruire l'original de ce message ainsi que toute copie.

Re: Does binlog stay in memory when replication happens?

[Baron Schwartz]

On Fri, Feb 27, 2009 at 7:04 AM, Cui Shijun  wrote:
> hi,
>  I wonder if it is safe to assume that binlog can stay in master's
> memory when replication happens

It's not safe to assume.  It varies from system to system depending on
operating system, filesystem, scheduler algorithm, amount of memory on
the machine, and probably a bunch of other variables.  The binlog is
first and foremost a file, and any portion of it that happens to be
cached in memory is an operating-system optimization that cannot be
counted on.

What does replication have to do with it?

>. If not, when the binlog getts corruptted, will the slave's binlog also get 
>corrupted?

Slave's binlog?  The slave's binlog is not tied to the master's
binlog.  I am not sure you understand clearly how replication works.

If the slave reads a corrupt binlog from the master's disk, then the
slave's RELAY log will be corrupt too.

>  Is there way to make the slave's binlog survive even in master's disk 
> failure?

Again I'm not sure what you are really asking.  You might be looking
for the Google synchronous replication patches.  Do a web search for
those and read up on what they offer.

-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: auto insert to another table

[Baron Schwartz]

You can probably use a trigger.  Check the section of the manual that
explains triggers.

Baron

On Fri, Feb 27, 2009 at 8:04 AM, Ron  wrote:
> Hi All,
>
> is it possible to auto insert to another table once a new data is inserted
> on a table? i'm using asterisk mysql cdr, what i'd like to do is once
> asterisk insert  new data on the cdr table, i will insert to another table
> which includes already how much the call was coz i dont want to queyr the
> cdr table all the time and compute how much.
>
> thank you
>
> regards,
> ron
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=ba...@xaprb.com
>
>



-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

auto insert to another table

[Ron]

Hi All,

is it possible to auto insert to another table once a new data is 
inserted on a table? i'm using asterisk mysql cdr, what i'd like to do 
is once asterisk insert  new data on the cdr table, i will insert to 
another table which includes already how much the call was coz i dont 
want to queyr the cdr table all the time and compute how much.


thank you

regards,
ron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Does binlog stay in memory when replication happens?

[Cui Shijun]

hi,
  I wonder if it is safe to assume that binlog can stay in master's
memory when replication happens. If not, when the binlog getts
corruptted, will the slave's binlog also get corrupted?
  Is there way to make the slave's binlog survive even in master's disk failure?

  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

MySQL Closing/Opening tables

[dbrb2002-sql]

Hi

Recently I noticed the server takes lot of time on and off when opening and 
closing tables. And I tried to increase the table_cache more the the total 
tables (file_limit is properly set); and the problem still continues and 
lowering it also continues.. and tried to set in middle.. same

Any thoughts on fixing this ? I am going crazy..

Sometimes the threads spin 10-60secs in just opening and closing tables state..

Thanks for reply
Rick