Re: Monitoring and analysis tool
Daniel Caune wrote: Hi, Our MySQL server used for our development environment is slowing down, certainly because of a massive query execution by different processes. We are trying to determine which kind of query is the most executed and on which database instance(s). We are quite novice in administrating MySQL. We imagine that we can configure MySQL so that it logs every queries executed in its queries.log file. However is there any analysis tool that would import this log file and that would generate a complete report providing the number of similar queries (same DML order on the same table) per databases per minute? P.S.: we are using mytop and innotop, but it seems that they don't support such a feature. We see a lot of different queries executed against MySQL but we can't figure out, which kind of queries is the most executed, on which database. Turn on the slow query log (log_slow_queries), set long_query_time to 1 (second), and then use the mysqldumpslow tool to aggregate all of the slow queries: [EMAIL PROTECTED]:~/mysql/mysql-5.0-bk] $ mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verboseverbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html Regards Mark -- Mark Leith, Manager of Support, Americas MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to configure libmysql to use a particular source IP?
Hello We have the following setup: a host A is running several web applications from different customers, each application/customer (virtual host) having it's own different incoming IP. A host B is running a MySQL instance, which is being used by the applications on A. Note that every customer has a different incoming IP, but for outgoing connections, every application is using the same source IP, the first one of A. Now one of the applications on A is randomly wreaking havoc, leading the MySQL server to block (Host 'A' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'). Since all customers' applications are using the same source IP, all of them are blocked at once. So my question is: how can we tell libmysql to use a particular source IP, so that we can configure each customer to use a different one (e.g. his incoming IP)? There are both PHP and Perl users, so it should preferably be a solution that works for both. Christian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update but insert if not exist
I am trying to think of a trick way to handle something. I have been successful in using the multiple inserts in one row by using the ,(xx,xx,xx),(xx,xx,xx),(xx,xx,xx) in a values block. BUT Is it possible to insert if not there for example Update if anyone not found then insert new with same criteria as update Where region_id in (2,3,4,5,6) Thanks Steffan --- T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 YAHOO : Steffan_Cline MSN : [EMAIL PROTECTED] GOOGLE: Steffan.Cline Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update but insert if not exist
try REPLACE http://dev.mysql.com/doc/refman/5.1/en/replace.html -afan Steffan A. Cline wrote: I am trying to think of a trick way to handle something. I have been successful in using the multiple inserts in one row by using the ,(xx,xx,xx),(xx,xx,xx),(xx,xx,xx) in a values block. BUT Is it possible to insert if not there for example Update if anyone not found then insert new with same criteria as update Where region_id in (2,3,4,5,6) Thanks Steffan --- T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 YAHOO : Steffan_Cline MSN : [EMAIL PROTECTED] GOOGLE: Steffan.Cline Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Monitoring and analysis tool
-Message d'origine- De : Mark Leith [mailto:[EMAIL PROTECTED] Envoyé : dimanche, décembre 16, 2007 04:53 À : Daniel Caune Cc : mysql@lists.mysql.com Objet : Re: Monitoring and analysis tool Daniel Caune wrote: Hi, Our MySQL server used for our development environment is slowing down, certainly because of a massive query execution by different processes. We are trying to determine which kind of query is the most executed and on which database instance(s). We are quite novice in administrating MySQL. We imagine that we can configure MySQL so that it logs every queries executed in its queries.log file. However is there any analysis tool that would import this log file and that would generate a complete report providing the number of similar queries (same DML order on the same table) per databases per minute? P.S.: we are using mytop and innotop, but it seems that they don't support such a feature. We see a lot of different queries executed against MySQL but we can't figure out, which kind of queries is the most executed, on which database. Turn on the slow query log (log_slow_queries), set long_query_time to 1 (second), and then use the mysqldumpslow tool to aggregate all of the slow queries: Thanks. However how can I trace every queries run against MySQL traced into slow-queries.log in order to use the mysqldumpslow tool? The minimum and default values of long_query_time are 1 and 10, respectively. It seems that I can't set 0 for long_query_time. -- Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring and analysis tool
Daniel, On Dec 16, 2007 12:37 PM, Daniel Caune [EMAIL PROTECTED] wrote: -Message d'origine- De: Mark Leith [mailto:[EMAIL PROTECTED] Envoyé: dimanche, décembre 16, 2007 04:53 À: Daniel Caune Cc: mysql@lists.mysql.com Objet: Re: Monitoring and analysis tool Daniel Caune wrote: Hi, Our MySQL server used for our development environment is slowing down, certainly because of a massive query execution by different processes. We are trying to determine which kind of query is the most executed and on which database instance(s). We are quite novice in administrating MySQL. We imagine that we can configure MySQL so that it logs every queries executed in its queries.log file. However is there any analysis tool that would import this log file and that would generate a complete report providing the number of similar queries (same DML order on the same table) per databases per minute? P.S.: we are using mytop and innotop, but it seems that they don't support such a feature. We see a lot of different queries executed against MySQL but we can't figure out, which kind of queries is the most executed, on which database. Turn on the slow query log (log_slow_queries), set long_query_time to 1 (second), and then use the mysqldumpslow tool to aggregate all of the slow queries: Thanks. However how can I trace every queries run against MySQL traced into slow-queries.log in order to use the mysqldumpslow tool? The minimum and default values of long_query_time are 1 and 10, respectively. It seems that I can't set 0 for long_query_time. Correct. But if you are willing to patch your server, you can: http://www.mysqlperformanceblog.com/2007/10/31/new-patch-for-mysql-performance/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring and analysis tool
Baron Schwartz wrote: Correct. But if you are willing to patch your server, you can: http://www.mysqlperformanceblog.com/2007/10/31/new-patch-for-mysql-performance/ This is in 5.1 as well now :) Regards Mark -- Mark Leith, Manager of Support, Americas MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
XQuare and MySQL 4.1.x
I recently ran across http://www.mysql.com/news-and-events/newsletter/2004-01/a000303.html and thus http://xquark.objectweb.org/. It appears that is will only work with 4.0 and I'm using 4.1.x. I've never installed MySQL, but I could attempt to install a newer version is something works with 5.x. I'd prefer to get the XQuery functionality with the 4.1 is at all possible. Any resources anyone can point me to? Thank you! -- Douglas A. Whitfield Co-Founder Carolina Open Source Initiative http://www.ibiblio.org/cosi http://www.last.fm/group/Carolina+Open+Source+Initiative cell: 919-360-0306
Re: best practice MySQl backup onto tape
Jenny Chen wrote:
Hi, Keith,
In my case, it is just single MySQL server(no replication).
Thanks for your info, I'll check with mk-parallel-dump tools, since
the speed of backup is important considering the database is locked for the
duration of the backup.
Well, if you're saying that you're only using innodb then you don't need
to lock the server. I'm using the attached script for backups. We
configure our slaves to use the given $slave_socket (to prevent normal
programs from writing to the db by accident), that's the reason for the
optional --access-slave flag.
Christian.
#!/usr/bin/perl -w
# Fre Sep 30 12:46:38 MEST 2005
(my $email='christian%jaeger,mine,nu')=~ tr/%,/@./;
use strict;
my $slave_socket= --socket=/var/run/mysqld/mysqld_safe.sock;
$0=~ /(.*?)([^\/]+)\z/s or die ?;
my ($mydir, $myname)=($1,$2);
sub usage {
print STDERR map{$_\n} @_ if @_;
print $myname [${myname}_options] -- additional_mysqldump_options
call mysqldump with options to safely backup innodb databases.
warning: does not handle myisam tables safely!
options: only one:
--access-slave access socket '$slave_socket'
(Christian T+J $email)
;
exit @_ ? 1 : 0;
}
my @args;
my $DEBUG=0;
my ($opt_access_slave);
for (my $i=0; $i=$#ARGV; $i++) {
local $_=$ARGV[$i];
if (/^--?h(elp)?$/) {
usage
} elsif ($_ eq '--') {
push @args, @ARGV[$i+1..$#ARGV];
last;
} elsif (/^--?d(ebug)?$/) {
$DEBUG=1;
} elsif (/^--access-slave$/) {
$opt_access_slave=1;
# } elsif (/^--?X(?:XXX(?:=(.*))?)?$/) {
# if (defined $1) {
# $XXX=$1
# } else {
# $XXX=$ARGV[++$i] or usage missing argument for '$_' option;
# }
} elsif (/^-./) {
usage(Unknown option '$_'\n);
} else {
push @args, $_
}
}
usage unless @args;
if ($opt_access_slave) {
unshift @args, $slave_socket;
}
sub xexec {
if ($DEBUG) {
print join ( ¦ ,@_),\n
} else {
exec @_ or exit 127;
}
}
xexec
qw(mysqldump -O single-transaction=TRUE --skip-lock-tables --add-drop-table
--all --extended-insert --quick --skip-add-locks ),@args;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error restart mysqld after network broken
Here is my errors in my locahost.locaodomain.err: 071217 14:02:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/ 2 071217 14:02:12 InnoDB: Started; log sequence number 0 46429 3 071217 14:02:13 [Warning] NDB: server id set to zero will cause any other mysqld with bin log to log with wrong server id 4 071217 14:02:13 [Note] Starting MySQL Cluster Binlog Thread 5 071217 14:02:13 [Note] Event Scheduler: Loaded 0 events 6 071217 14:02:13 [Note] /usr/local/mysql/bin/mysqld: ready for connections. 7 Version: '5.1.21-beta' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL) 8 071217 14:02:14 - mysqld got signal 6; 9 This could be because you hit a bug. It is also possible that this binary 10 or one of the libraries it was linked against is corrupt, improperly built, 11 or misconfigured. This error can also be caused by malfunctioning hardware. 12 We will try our best to scrape up some info that will hopefully help diagnose 13 the problem, but since we have already crashed, something is definitely wrong 14 and this may fail. 15 16 key_buffer_size=8388600 17 read_buffer_size=131072 18 max_used_connections=2 19 max_threads=151 20 threads_connected=0 21 It is possible that mysqld could use up to 22 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337618 K 23 bytes of memory 24 Hope that's ok; if not, decrease some variables in the equation. 25 26 thd: 0x0 27 Attempting backtrace. You can use the following information to find out 28 where mysqld died. If you see no messages after this, something went 29 terribly wrong... 30 Cannot determine thread, fp=0xb7ff3b28, backtrace may not be correct. 31 Stack range sanity check OK, backtrace follows: 32 0x81fe0c9 33 0xad9402 34 0x4bf07451 35 0x8465f7f 36 0x8452aa4 37 0x8451929 38 0x84adb92 39 0x84b6d58 40 0x84b549d 41 0x84ae0cd 42 0x84adfb7 43 0x849d6b8 44 0x4c0502db 45 0x4bfaa12e 46 New value of fp=(nil) failed sanity check, terminating stack trace! 47 Please read http://dev.mysql.com/doc/refman/5.1/en/resolve-stack-dump.html 48 and follow instructions on how to resolve the stack trace. 49 Resolved stack trace is much more helpful in diagnosing the 50 problem, so please do resolve it 51 The manual page at http://www.mysql.com/doc/en/Crashing.htmlcontains 52 information that should help you find out what is causing the crash. 53 071217 14:02:14 mysqld_safe Number of processes running now: 0 [EMAIL PROTECTED] ~]# service mysqld status ERROR! MySQL is not running, but lock exists [EMAIL PROTECTED] ~]# ps aux | grep mysql root 2232 0.1 0.1 4596 1320 ?S13:52 0:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/usr/local/mysql/data/ --pid-file=/usr/local/mysql/data//localhost.localdomain.pid nobody4873 1.3 1.4 114512 14452 ?Sl 13:58 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ --user=nobody --log-error=/usr/local/mysql/data//localhost.localdomain.err --pid-file=/usr/local/mysql/data//localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306 root 4889 0.0 0.0 4124 660 pts/0D+ 13:58 0:00 grep mysql I don't know how to solve this,anybody can help me,thanks.
