Re: Perl DBI -- How to select all returned values into an array
On Sat, Aug 25, 2001 at 09:24:25PM -0500, Paul DuBois wrote:
At 10:07 AM -0700 8/24/01, Katherine Porter wrote:
For single values I usually use this DBI function and query:
my $val =3D $dbh-selectrow_array(SELECT value FROM tab1 WHERE test=3D=
2);
However, what if I want to store a bunch of values into an array?
my @vals =3D $dbh-?(SELECT value FROM tab1 WHERE test 10);
What's the syntax I'm missing above? Any help appreciated!
my $ref = $dbh-selectcol_arrayref (single-column query);
my @val = (defined ($ref) ? @{$ref} : () );
Or just:
my @val = @{ $dbh-selectcol_arrayref(single-column query) || []};
And if you have RaiseError set then you don't need the '|| []' part.
Also, from DBI 1.20 onwards you can now say
$dbh-selectcol_arrayref(select f1, f2 from ..., { Columns=[1,2] });
and have all the fields flattened into a list. Very handy when building a hash.
But then for building a hash DBI 1.20 also has selectall_hashref() :-)
The Perl DBI - One call does it all - almost :-)
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Case-preserving is not consistent
On Sun, Aug 19, 2001 at 10:21:46AM -0400, Philip Mak wrote:
On Sun, 19 Aug 2001, Sinisa Milivojevic wrote:
If this is happening on Windows, we truly can not do anything about
it.
You could help there by forcing all table names to be lower-case by
starting mysql service with a corresponding option.
No, it's happening on Linux. Here's a transcript of what happens. I create
a table called test with a column called HELLO. Then when I execute
modify column hello, the case of the column becomes hello. This will
cause perl scripts that use $sth-fetchrow_hashref to fail.
You could, of course, use $dbh-{FetchHashKeyName} = 'NAME_lc';
these days.
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL to Oracle schema conversion utility
Here's a MySQL to Oracle schema conversion utility that I've written.
It's rather more functional than the m2o one posted recently.
Tim.
p.s. I could have implemented the SQL 'create table' parser using
Parse::YAPP and the yacc syntax in the mysql source code but I wanted
to use this as an excuse to play with Parse::RecDescent.
#!/home/value/apps/perl -w
=head1 NAME
mysql2ora - Convert MySQL schema into Oracle schema (including triggers etc)
=head1 SYNOPSIS
mysql2ora [-options] mysql_schema.sql oracle_schema.sql
=head1 DESCRIPTION
Converts MySQL schema into Oracle schema, including emulation of some
MySQL features...
Handles the emulation of an auto_increment field by defining a
sequence and a trigger that uses the sequence to automatically set the
auto_increment field. (Has an option to specify the initial value of
the sequence.)
Handles the emulation of a timestamp field by defining a
and a trigger that automatically sets the timestamp field.
Automatically detects all Oracle reserved words for both table and
field names, and supports multiple methods to deal with them.
Automatically detects long names (30) characters and truncates them
intelligently to ensure that they remain unique. (It applies the same
techniques to the names of automatically generated sequences and triggers.)
Automatically generates CHECK clauses for enumerated types (and sets
the length of the field to the length of the longest valid value).
For SET types it automatically sets the length of the field to the sum
of the length of the valid values allowing for a comma delimiter
between each. (It doesn't generate a CHECK clause for SET types. That'll
be a little task for someone.)
For partial-field indices it has some huristics to do reasonable things
in some cases. (But typically you'll need to reconsider those for Oracle anyway.)
=head2 OPTIONS
=over 4
=item B--only
Ignore any tables with names that don't match the specified regular expression.
=item B--drop
Add DROP statements into the generated SQL before each new schema
object is created.
=item B--autoinc
Specify the start value of all generated auto_increment sequences. Defaults to 1.
If you are migrating existing data then this should be greater than the
highest value in any auto_increment field in your database.
=item B--unreserve
Specify an sprintf() format string to use to Iunreserve a reserved word.
The default is 'C%s', in other words, don't change the name just
put double quotes around it to make it acceptable to Oracle. If you
use this approach then you'll need to quote all references to the field
in your SQL statements. If you run your mysqld in ANSI mode then you
can use double quotes for both MySQL and Oracle SQL and thus use the
same SQL statements for both.
Another common alternative is 'C%s_', which just appends an
underscore character to the name.
=back
=head2 LIMITATIONS
The parser has been tested on the output of Cmysqldump -d and not on
hand-written SQL. So it's possible that some legal SQL will not parse
correctly.
Also, it has only been tested on the schemas that I have available to
me. It's quite probable that for some less common types it doesn't do
the right thing.
The parser error handling isn't good. I know that Parse::RecDescent can
generate good errors but I don't seem to be using it the right way to
get them.
=head2 TO DO (by someone)
Add optional support for the auto_increment trigger storing the last value
into a session global to simplify emulation of last_insert_id.
CHECK clause for SET types.
Handle embedded quotes etc in default values etc.
Query mysql db to set initial start value of auto_increment sequence
=head2 AUTHOR
Copyright Tim Bunce, 2001. Released under the same terms as Perl.
=cut
use strict;
use Carp;
use Parse::RecDescent;
use Data::Dumper;
use Getopt::Long;
use vars qw(%VARIABLE %opt);
%opt = (
maxname = 30, # oracle names must be =30 chars
d = 0,
quiet = 0,
autoinc = 1,
unreserve = '%s',
);
GetOptions(\%opt,
'd!',
'quiet!',
'drop!',
'prefix!',
'only=s',
'unreserve=s',
'autoinc=s',
'maxname=i',
) or die;
# Enable warnings within the Parse::RecDescent module.
$::RD_ERRORS = 1; # Make sure the parser dies when it encounters an error
$::RD_WARN = 1; # Enable warnings. This will warn on unused rules c.
$::RD_HINT = 1; # Give out hints to help fix problems.
$::RD_TRACE = $opt{d}?90:undef; # Trace behaviour to help fix problems.
my $grammar = '_EOGRAMMAR_';
{ my @col_defn;
my @key_defn;
my $schema_obj_name = '(initial)';
}
VALUE : /[-+]?\.?\d+(?:[eE]\d+)?/
{ $item[1] }
| /'.*?'/ # XXX doesn't handle embedded quotes
{ $item[1] }
| /NULL/
{ 'NULL' }
NAME: ` /\w+/ `
{ $item[2] }
| /\w+/
{ $item[1] }
parens_value_list : '(' VALUE(s /,/) ')'
{ $item[2] }
parens_name_list
Re: LOAD DATA INFILE performance testing
DELAY_KEY_WRITE=1 Tim. On Mon, Aug 06, 2001 at 07:36:17AM -0700, Jeff Tanner wrote: I running a test on mysql to test its performance of doing a bulk insert into a table using LOAD DATA INFILE. The table is simple: CREAT TABLE test ( valueCHAR(32) NOT NULL PRIMARY KEY ) The test is simple: a) clear table b) time performance of inserting X unique values into table from a single file (each value on a new line) # of unique valuestime in seconds 10 0.05 1000.05 1K 0.10 10K0.75 100K 10 500K 125 1M 500 5M 55000 My concern is the last entry of 5 million entries. Is there a mysql system variable I should reset to improve performance? Thanks Jeff Tanner Viathan Seattle, WA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl Script: MySQL Slow Query Log Parser
Great. Thanks. Tim. - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Tim Bunce [EMAIL PROTECTED] Cc: Nathanial Hendler [EMAIL PROTECTED]; Mysql [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, July 20, 2001 12:05 AM Subject: Re: Perl Script: MySQL Slow Query Log Parser On Wed, Jun 27, 2001 at 01:45:27PM +0100, Tim Bunce wrote: You'll find mysqldumpslow (in the mysql distribution) does all that and much much more It's a pity that it's not mentioned in the online documentation since it's a _very_ useful tool. I've submitted a patch against the manual to correct that oversight. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 33 days, processed 263,361,083 queries (89/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Does anyone remember a MySQL to Oracle schema migration tool?
I recall that maybe a month or four ago someone posted a message (on the [EMAIL PROTECTED] list I think) saying that they'd written a utility to parse MySQL create table statements and write out equivalent Oracle create table statements. I remember replying to the message (possibly privately) and I'd like to take a look at the utility now, but I can't find the message now (either in my email or online archives). Can anyone help out? Thanks. Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: direct ISAM access vs. SQL parser
On Fri, Jul 06, 2001 at 02:51:34PM +0300, Sinisa Milivojevic wrote: Volker Paepcke writes: Hi! One year ago Michael Widenius posted this: We have on the other hand played with the idea to at some point implement SQL commands of type: ISAM INSERT table_name (...) ISAM UPDATE table_name SET ... WHERE key_name=(key_part1,key_part2,...); ISAM READ table_name FIRST ISAM READ table_name NEXT ISAM READ table_name NEXT LIMIT 10 #This would read the next 10 rows ISAM READ table_name PREV ISAM READ table_name LAST ISAM READ table_name WHERE key_name=(key_part1,key_part2,...); Most of the above would be trivial to parse but could give you direct access to the lover ISAM/MyISAM levels with fill cacheing. This has been implemented 2 months ago, only it is not called ISAM, but HANDLER. It may have been implemented 2 months ago (for v4.0), but it doesn't seem to be documented. Will is be documented soon? Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: direct ISAM access vs. SQL parser
On Fri, Jul 06, 2001 at 04:36:08PM +0300, Sinisa Milivojevic wrote: Tim Bunce writes: On Fri, Jul 06, 2001 at 02:51:34PM +0300, Sinisa Milivojevic wrote: It may have been implemented 2 months ago (for v4.0), but it doesn't seem to be documented. Will is be documented soon? Tim. It is in the manual under this heading : `HANDLER' Syntax Perhaps, but it's not listed here: http://www.mysql.com/doc/manual.php?search_query=handlerdepth=0 Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Perl Script: MySQL Slow Query Log Parser
You'll find mysqldumpslow (in the mysql distribution) does all that and much much more It's a pity that it's not mentioned in the online documentation since it's a _very_ useful tool. Tim. On Mon, Jun 25, 2001 at 11:59:59PM -0700, Nathanial Hendler wrote: I wrote a perl script that will parse slow_queries logs, and output some useful information. It's kind of hard to explain, but I'll try. I wanted to see what queries were taking a lot of time, and how often they were happening. I wrote a perl script that parses the log files, ignores queries that take less than n seconds, and normalizes the queries and reports the info for each queries sorted by query occurance. 'normalize' meaning... this... SELECT * FROM ween WHERE pandy_fackler = 1; SELECT * FROM ween WHERE pandy_fackler = 15; becomes... SELECT * FROM ween WHERE pandy_fackler = XXX; this... SELECT names FROM things WHERE name LIKE '%wazoo%'; SELECT names FROM things WHERE name LIKE '%tada%'; becomes... SELECT names FROM things WHERE name LIKE 'XXX'; This has proven to be a very interesting and useful tool. You should DL it and try it on your long_queries log file. Whoever runs mysql.com should put a copy on the website. It might just be the greatest thing ever (my program, not the website). For more info, and to get a copy, you can get it at: http://www.retards.org/mysql/index.php I'd like to hear people's thought on it. I'm the only person to test it so far, so it'd be nice to know that it works for other people. Thanks, Nathan Hendler Tucson, AZ USA http://retards.org/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql to Oracle migration
On Tue, Jun 12, 2001 at 04:42:42PM -0700, Jeremy Zawodny wrote: On Tue, Jun 12, 2001 at 05:06:59PM -0500, Zhu George-CZZ010 wrote: Is there a way/tool to migrate the Application from MySql to Oracle 8i? Oracle has one available on their web site... Mostly a waste of space. Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql to Oracle migration
On Tue, Jun 26, 2001 at 05:20:25PM -0400, Sherzod Ruzmetov wrote: Can someone explain me why is it waste of space??? No support for emulating autoincrement fields. No support for mysql specific types. No support for mysql specific functions. No support for ... In fact no support for almost anything useful that mysql does. The tragedy (or perhaps advantage, for this audience) is that Oracle is capable of doing a good migration through triggers and PL/SQL etc etc. Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: New user InnoDB comments
On Wed, May 16, 2001 at 03:01:26PM +0300, Heikki Tuuri wrote: Andreas, sorry, it is the default MySQL behavior to create a MyISAM table if another handler is not present. There was a long thread of emails between MySQL developers if MySQL should give an error message if a table cannot be created to be of type BDB or InnoDB because the handler is not present, but I think there was no decision to change the behavior. I think it would be better if the database would give an error. Transactional applications cannot be used with MyISAM tables. Don't try to choose (you'll never make everyone happy), let the user decide. Perhaps by allowing the TYPE= clause to take a list of fallback types and add a session option to say if an additional fallback to MYISAM should be assumed. Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem getting INNOBASE (3.23.37) working on FreeBSD (4.2)
On Sun, May 13, 2001 at 06:09:12PM +0300, Heikki Tuuri wrote: Suggested improvements would be the addition of COALESCE TABLESPACE Do you mean reorganization and compaction of a tablespace? The way to do it is to dump and reload all tables in the tablespace. During my tests I've found that a 100MB tablespace file gets filled up even if I'm doing work on a 30MB table, and I can't add another table without dropping and reloading the first. It'd be nice if there was a way to do this wihtout taking tables offline. The current workaround is just to allocate much more tablespace than you really need. I see. The 30 MB table can grow to take up to 120 MB if the fillfactor of index pages (also data pages are clustered index pages) drops to 25 %. Also, if InnoDB has allocated a 64-page chunk to a table the chunk will be freed to other tables' use only when it is completely empty. Possible improvements: - We could change the page merge threshold from 25 % to 40 %. Then the fillfactor of the tree would stay higher. A drawback is increased CPU usage by merges in some circumstances. - We could change file space allocation for secondary indexes so that they would always allocate only individual pages, not 64-page chunks. The drawback is that then inter-table fragmentation can take place: dropping a table will not free complete 64-page chunks to other tables. - We could write a background process to reorganize and compact tables. A weakness is that if the database load takes 100 % of disk bandwidth, the background process will not run, and the user may be surprised by the size increase of tables. I think putting the threshold to 40 % is the easiest improvement here. Please make such thresholds configurable. Different tables have different usage patterns and there'll never be a value optimal for all uses. Tim. It also occurred to me that ALTER TABLE can be used to reorg a table: ALTER it to MyISAM and then back to InnoDB. Regards, Heikki http://www.innodb.fi -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql uses 99% cpu under freebsd 4.3
FYI, people experiencing this problem should try to make the mysqld as idle as possible (ie disconnect or idle the connections) and then run truss -p NNN (where NNN is the pid of the mysqld process) and post a typical chunk of the output. That would give valuable info to the mysql developers. Tim. On Tue, Apr 10, 2001 at 03:48:26PM +0300, Heikki Tuuri wrote: Hi! People are reporting performance problems also from the FreeBSD port of Innobase. There seems to be a runaway thread which gets free sometimes and eats up 100 % of the CPU. It was not fixed with the latest patches to Innobase. I will try setting thread priorities in 3.23.37. That might help, but I guess the real reason is different. I will probably finish my work on 37 today and tomorrow I will try to repeat the problems on our FreeBSD computer. Regards, Heikki Johan Andersson writes: I've had the same problem on an Intel P-III 800 w 256M RAM running FreeBSD 4.1-RELEASE with both the distribution from the ports tree and the source dist. from mysql.com. I tried to solve the problem with analysing all the database traffic, but mysql were taking all free CPU that were availble on the same database traffic (same database) that were running fine on a debian linux machine (Pentium 166, 192M RAM) ... So something weird is happening on some FreeBSD machines. But I also have two machines that are _dedicated_ mysql servers of the same configuration, but other hardware brand (motherboard, scsi controllers, disks) that runs perfect on the same FreeBSD release! The machine with the problem were also running Apache/PHP .. (How )is MySQL using sharedmemory segments ? I know that Oracle takes up loads of them and may bring problem for apache that also needs some seg's.. ? Regards, Johan Andersson Consultant QbranchHi! Can anyone of you guys come with a repeatable test case that will always lead to the situation as described. Then we could try to fixit.Regards, Sinisa - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql uses 99% cpu under freebsd 4.3
I suspect the point is that many more threads simply makes the problem appear more often and thus be reproducible and thus more easily fixed. Tim. On Wed, Apr 11, 2001 at 04:49:19PM +0200, Lars Andersson wrote: My 4.2 STABLE dosent get so many queries, according to mysqladmin (Queries per second avg: 0.700) the last time it acted up. By the way, it is a dual CPU machine. On another machine with only one CPU running exactly the same code and relese of FreeBSD I havent seen this problem. /Lars Andersson _ Lars Andersson, Tekniker Cable Wireless - pi.se http://www.pi.se On Wed, 11 Apr 2001, Andrey Kotrekhov wrote: On Wed, 11 Apr 2001, Sinisa Milivojevic wrote: Hi! Hi Ken, This seems to be a problem with threads on FreeBSD, although on which side, yet remains to be seen. It seems to appear that it is surfacing not just under heavy load, but when there are many threads running. Many means more then 300 or 400. My mysql server is not under heavy load. We have less then 20 connection at ones. But we have the same problem under FreeBSD-4.2 STABLE The mysqld can catch all CPU ones a day, sometimes ones a week. IMHO it is not depend from number of threads or number of queries at ones. ___ Andrey Kotrekhov [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql uses 99% cpu under freebsd 4.3
On Wed, Apr 11, 2001 at 12:33:44PM -0400, Ken Menzel wrote: The list reject my trace (it was too large), so here is a smaller one! Stops after info from first top starts showing CPU usage of 99% Ken Hi Tim, I just had it happen. Overall average 14 queries per second on this machine. Single CPU Kernel Dell 2400 600MHZ FreeBSD-stable from January. Almost generic kernel. I have attahced some output from truss, followed by 'top' followed by the stop and start of mysql, followed by the output from 'top' again. Hope this helps someone! Ken - Ken Menzel ICQ# 9325188 www.icarz.com [EMAIL PROTECTED] pread(0x186,0x18563010,0x2a9,0x0,0x4bb094,0x0) = 681 (0x2a9) lseek(173,0x1b20,0) = 6944 (0x1b20) read(0xad,0xbf515028,0x14) = 20 (0x14) pread(0xad,0x187e5018,0x33,0x0,0x1b2d,0x0) = 51 (0x33) lseek(173,0x1b74,0) = 7028 (0x1b74) read(0xad,0xbf515028,0x14) = 20 (0x14) pread(0xad,0x187e504b,0x17,0x0,0x1b78,0x0) = 23 (0x17) SIGNAL 27 SIGNAL 27 pread(0x186,0x18563010,0x2a9,0x0,0x2cac03,0x0) = 681 (0x2a9) gettimeofday(0x2828a568,0x0) = 0 (0x0) sigprocmask(0x3,0x2828a5d8,0x0) = 0 (0x0) sigaltstack(0x282a5c20,0x0) = 0 (0x0) poll(0x8212000,0x54,0x0) = 0 (0x0) sigreturn(0x182a1864)= 20 (0x14) pread(0xe5,0x1843a09f,0x9,0x0,0x4f777,0x0) = 9 (0x9) lseek(229,0x4f758,0) = 325464 (0x4f758) read(0xe5,0xbf39ef98,0x14) = 20 (0x14) pread(0xe5,0x1843a0a8,0x9,0x0,0x4f763,0x0) = 9 (0x9) lseek(229,0x5ae64,0) = 372324 (0x5ae64) read(0xe5,0xbf39ef98,0x14) = 20 (0x14) pread(0xe5,0x1843a0b1,0xd,0x0,0x5ae68,0x0) = 13 (0xd) lseek(229,0x4963c,0) = 300604 (0x4963c) That's different to what I was seeing. I saw a tight loop of poll(...) = 1 gettimeofday(...) = ... poll(...) = 1 gettimeofday(...) = ... poll(...) = 1 gettimeofday(...) = ... poll(...) = 1 gettimeofday(...) = ... Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need: DB Link from Oracle to MySQL
On Wed, Apr 11, 2001 at 06:14:51PM -0500, pmetha wrote: Hello all: I need to create a db link type structure within Oracle 817 EE to connect to MySQL so that I can read mySQL tables. Can any kind person point me in the direction of how to do this if it is possible at all. We're experimenting (slowly as a background task) with using the Oracle Transparent Gateway (or whatever they're calling it this week) to talk via MyODBC to MySQL, all hosted on the same Solaris box. Fetching works fine (once you get round uppercase/lowercase issues). That's about as far as we've got. Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql uses 99% cpu under freebsd 4.3
I've seen what's probably the same thing under 4.2-STABLE. The poll() system call returns 1 but there's no corresponding file handle marked in the data structures that were passed to poll(). (It could be a mysqld bug if they're asking to poll() for certain kinds of events but then not checking for those events, but that seems unlikely. I've no time to check the code.) Tim. On Mon, Apr 09, 2001 at 04:03:05PM -0400, Andrew Schmidt wrote: Have you tried this under a 'stable' version of freebsd? In FreeBSD 4.2 beta, mysql would crash with user locks. Not mysql's fault. now, I understand RC's are generally stable; but I would still make sure that this bug doesn't show up in a stable os. regards, -- Andrew - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 09, 2001 3:34 PM Subject: mysql uses 99% cpu under freebsd 4.3 Description: mysql uses 99% cpu and becomes extremely unresponsive under high load How-To-Repeat: send about 300 simultaneous visitors to www.chicagobusiness.com and tell them to click around. Fix: restart mysql. this usally helps, at least for a few minutes. sometimes it climbs back to 99% though. Submitter-Id: submitter ID Originator: Jon Nathan Organization: Chaffee Interactive MySQL support: extended email support Synopsis: mysql uses 99% cpu and becomes unresponsive Severity: serious Priority: high Category: mysql Class: support Release: mysql-3.23.36 (Source distribution) from freebsd ports Environment: System: FreeBSD d1.crain.com 4.3-RC FreeBSD 4.3-RC #1: Tue Apr 3 16:17:52 GMT 2001 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/CHAFFEE i386 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.2 19991024 (release) Compilation info: CC='cc' CFLAGS='-O2 -pipe -mpentiumpro ' CXX='c++' CXXFLAGS='-O2 -pipe -mpentiumpro -felide-constructors -fno-rtti -fno-except ions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1170734 Apr 3 12:24 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Apr 3 12:24 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 559764 Apr 3 12:24 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/data/db --without-perl --without-debug --witho ut-readline --without-bench --with-mit-threads=no --with-libwrap --with-low- memory --enable-assembler --with-berkeley-db --with-charset=latin1 --prefix= /usr/local i386--freebsd4.3 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Signal to Noise Ratio...
On Thu, Apr 05, 2001 at 09:12:08PM -0400, Steve Werby wrote: "Michael Widenius" [EMAIL PROTECTED] wrote: This thing has been up a couple of times over the years. The problem is that requiring people to be subscribers to the mailing list before posting will only confuse the poor people that don't expect to get 100 emails per day after subscribing. In that case, you may want to consider implementing a MLM (mailing list manager) that allows subscribers to disable mail delivery. I know Mailman and some others have that feature. What MLM is MySQL using? In conjunction with that feature you could have an autoreply that gives non-subscribers who attempt to post a canned message with info. on the lists and instructions on subscribing. I can highly recommend ezlm, as used by all the perl.org mailing lists (including the 5000 subscribers on dbi-announce). It's very powerful and simple. Supports digests and post-only subscriptions. Easy moderation for moderated lists etc etc. Canned message with info for non-subscribers. Excellent bounce management etc etc. (Sasha, would it be easy to fix the last thing). - Add better spam filters; Matt, do you have any ideas for this? Yesterday in this thread I mentioned Spam Bouncer http://www.spambouncer.org/. It's a set of procmail recipes that are pretty powerful, easy to modify and are pretty effective. I especially like the ability to automatically notify senders of false positives and provide them a password to resend the message and bypass the filtering system (which conceptually a spammer wouldn't do). That would be handy, given that mysql lists current spam filter just bounced a message from me due to the "just dollar symbol" in "just dollar symbolsth-execute"! Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: turning logging on/off while server is running
On Fri, Apr 06, 2001 at 12:13:22AM -0400, Thalis A. Kalfigopoulos wrote: On Thu, 5 Apr 2001, Roel Vanhout wrote: Hi all, Is there a way to turn the query logging on and off while the database server is running? Right now I have a script that stops and restarts the database with loggin on or off depending on the parameters, but this is not so great; I was wondering if there is another way. Also, is there a way to query the server if logging is on or off? I'd like to write a nice gnome applet to start/stop logging but I'm not sure how to get this value. You can get whether the server is loging or not through $ mysqladmin -p variables and check the value of, you guessed it, the entry 'log' I don't know if you can make the server turn logging on/off while it is running. But you can swithc logging on/off for a particular session giving: SET SQL_LOG_OFF=1 (the client must have the Process privilege) I think run-time changing of some config settings, like logging, should be on the to-do for v4, if it isn't already. Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql speed :)
On Fri, Apr 06, 2001 at 06:51:39AM -0700, Martin Hubert wrote:
But does that really change anything on the database server side ?
In other words is MySQL doing anything with respect to using prepared
statements ?
No. (Sadly MySQL doesn't support prepared statements. Pity.)
But if you're measuring the elapsed time of the perl script to do
benchmarks, as here, then it'll give a more accurate picture by
removing irrelevant overheads.
Tim.
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 3:01 AM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql speed :)
On Fri, Apr 06, 2001 at 08:41:32AM -0700, William R. Mussatto wrote:
How do you get it not to try an quote numbers? I realize this should be
obvious, but I can't find an exampl in the msql/mysql book.
It's magic. Or specifically it relies on Perl's internal magic.
Plus, of course, it wouldn't actually matter if it did in this case
since mysqld will happily convert the strings to numbers anyway.
Tim.
On Fri, 6 Apr 2001, Tim Bunce wrote:
Date: Fri, 6 Apr 2001 11:00:57 +0100
From: Tim Bunce [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sincerely,
William Mussatto, Senior Systems Engineer
CyberStrategies, Inc
ph. 909-920-9154 ext. 27
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql speed :)
On Fri, Apr 06, 2001 at 10:16:46AM -0400, Johnson, Gregert wrote:
What would really help would be to use multiple row inserts, i.e.
INSERT INTO speed1 VALUES (a,b,c),(d,e,f),(g,h,I),...
So, prepare a series of inserts, each with a few hundred (or even thousand) row
value sets.
In that case the benefit probably swings the other way and I wouldn't
bother with placeholders when generating insert statements for very
large numbers of rows. But it's obviously not portable and it's hard to
know which rows have failed if any do.
Tim.
--Greg Johnson
-Original Message-
From: Martin Hubert [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 9:52 AM
To: Tim Bunce; Heikki Tuuri
Cc: [EMAIL PROTECTED]; FileCopyMartin
Subject:RE: Mysql speed :)
But does that really change anything on the database server side ?
In other words is MySQL doing anything with respect to using prepared
statements ?
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 3:01 AM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Warning: do not use INSERT DELAYED on Innobase tables
On Fri, Apr 06, 2001 at 07:26:14PM +0300, Heikki Tuuri wrote: Hi! The MySQL manual says that INSERT DELAYED only works for MyISAM and ISAM tables, but the parser does not check that the table type is correct. We have to block this in the parser. If you use it on Innobase tables, that can cause several database corruption! Rather than block it, why not just ignore the 'delayed' so existing code won't break if the table type is changed, just slow down. Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Some more about mysql perfomance.
On Sat, Apr 07, 2001 at 02:14:42AM +0300, Michael Widenius wrote: The reason singly queries are slower are of course that the initialization phase (reading the data from a socket, parsing, locking, checking which keys to use) is about half of the query time. Peter Heikki made tests which also shows some strange things - for Peter example why INSERT is FASTER then SELECT. I haven't seen the test but I can imagine this is true in some context. The reason for this is that a SELECT has to go through many optimization stages to find out what indexes to use and what queries to do. This is one basic fault with SQL; The optimizer has to do a lot of work... Most high-end relational databases address this by... a) storing the query execution plan etc in a cache keyed by the sql statement text. That way, if another statement with the same text is executed a ready-made execution plan is available. b) to make that effective they support placeholders that abstract out literal values from the statement text, so the cached plan can be reused regardless of the literal values boind to the placeholders for a particular execution. I appreciate that doing (b) would require major changes to the protocol etc, but it's just occured to me that there's a very simple way to avoid that but still get the benefits of (a)... Imagine if, when a statement arrived, mysqld made a char-by-char copy, but in that copy skipped out the literal values and kept a seperate list of those. That would be a very fast and simple piece of code. That 'abstracted' statement could then be used as the key to the statement cache. If it matched an entry in the cache then mysql could skip the generation of the query execution plan! (To simplify access rights issues you could also add the username to the abstracted statement.) What do you think Monty? Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Locked tables while Sending data !?
Hi! I just come across this old email: On Thu, Mar 09, 2000 at 03:14:28PM +0200, sinisa wrote: This happens in the case when mysql does not have to create temporrary tables in order to obtain result set, but is reading from the live table itself. Changing a table during such reading operation would break things. It would be helpful if there was a simple efficient way to force the use of a temporary table for these situations. Using an ORDER BY would be inefficient. Would adding "HAVING 1=1" (without a group by) work? Tim. The SELECT option SQL_BUFFER_RESULT will force MySQL to create a temporary table for the results and unlock the other tables involved in the query as soon as all rows have been fetched. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql 3.23.36 problem
On Thu, Mar 29, 2001 at 07:57:41PM +0300, Michael Widenius wrote: mysql rename tables test1 to test3, test2 to test1, test3 to test2; Peter ERROR 1192: Can't execute the given command because you have active locked Peter tables or an active transaction I bet this happens when you have a server that is compiled w transaction support but you are using --skip-bdb and --skip-innobase ? Here is a fix for this: What's the scope of this bug? Does it only affect the use of 'circular' rename, or all renames, or table locking in general, or tables in general? Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: very strange but reproducable error in 3.23.35
On Mon, Mar 19, 2001 at 02:24:52PM +0200, Sinisa Milivojevic wrote: Hi! I tested your test case and you are right ! We've just discovered a very similar sounding problem immediately after upgrading to 3.23.35. In our case an update is reporting 0 zero rows updated which then triggers an insert which fails with a duplicate entry error. When the code retries the same sequence a little later the update works. Thank you for a reproducible test case. Yes, many thanks. Saved me the work. We will fix it shortly. Please do, along with your test suite that should have caught such a basic error. Meanwhile I'm surprised and disappointed to have to say that 3.23.35, like .34, is NOT suitable for production use :( Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Updated mysqldumpslow command
Attached is an updated version of the mysqldumpslow command that's
supplied in the mysql distribution.
The mysqldumpslow, for those who've not tried it, makes it very
easy to summarize and analyze the contents of the 'slow query log'.
This update was prompted by the need to support the newer format
of the slow query log, which now includes user and time information.
I took the opportunity to add assorted extra useful bells and whistles.
It's well worth experimenting with this handy utility.
Enjoy.
Tim.
#!/my/gnu/bin/perl
# mysqldumpslow - parse and summarize the MySQL slow query log
# Original version by Tim Bunce, sometime in 2000.
# Further changes by Tim Bunce, 8th March 2001.
use strict;
use Getopt::Long;
# t=time, l=lock time, r=rows
# at, al, and ar are the corresponding averages
my %opt = (
s = 'at',
h = '*',
);
GetOptions(\%opt,
'v+', # verbose
'd+', # debug
's=s', # what to sort by (t, at, l, al, r, ar etc)
'r!', # reverse the sort order (largest last instead of first)
't=i', # just show the top n queries
'a!', # don't abstract all numbers to N and strings to 'S'
'n=i', # abstract numbers with at least n digits within names
'g=s', # grep: only consider stmts that include this string
'h=s', # hostname of db server for *-slow.log filename (can be wildcard)
'i=s', # name of server instance (if using mysql.server startup script)
'l!', # don't subtract lock time from total time
) or die "Bad option";
unless (@ARGV) {
my $defaults = `my_print_defaults mysqld`;
my $basedir = ($defaults =~ m/--basedir=(.*)/)[0]
or die "Can't determine basedir from 'my_print_defaults mysqld' output:
$defaults";
warn "basedir=$basedir\n" if $opt{v};
my $datadir = ($defaults =~ m/--datadir=(.*)/)[0];
if (!$datadir or $opt{i}) {
# determine the datadir from the instances section of /etc/my.cnf, if any
my $instances = `my_print_defaults instances`;
die "Can't determine datadir from 'my_print_defaults mysqld' output: $defaults"
unless $instances;
my @instances = ($instances =~ m/^--(\w+)-/mg);
die "No -i 'instance_name' specified to select among known instances:
@instances.\n"
unless $opt{i};
die "Instance '$opt{i}' is unknown (known instances: @instances)\n"
unless grep { $_ eq $opt{i} } @instances;
$datadir = ($instances =~ m/--$opt{i}-datadir=(.*)/)[0]
or die "Can't determine --$opt{i}-datadir from 'my_print_defaults
instances' output: $instances";
warn "datadir=$datadir\n" if $opt{v};
}
@ARGV = $datadir/$opt{h}-slow.log;
die "Can't find '$datadir/$opt{h}-slow.log'\n" unless @ARGV;
}
warn "\nReading mysql slow query log from @ARGV\n";
my @pending;
my %stmt;
$/ = ";\n#";# read entire statements using paragraph mode
while ( defined($_ = shift @pending) or defined($_ = ) ) {
warn "[[$_]]\n" if $opt{d}; # show raw paragraph being read
my @chunks = split /^\/.*Version.*started
with[\000-\377]*?Time.*Id.*Command.*Argument.*\n/m;
if (@chunks 1) {
unshift @pending, map { length($_) ? $_ : () } @chunks;
warn "".join("\n",@chunks)."" if $opt{d};
next;
}
s/^#? Time: \d{6}\s+\d+:\d+:\d+.*\n//;
my ($user,$host) = s/^#? User\@Host:\s+(\S+)\s+\@\s+(\S+).*\n// ? ($1,$2) :
('','');
s/^# Time: (\d+) Lock_time: (\d+) Rows_sent: (\d+).*\n//;
my ($t, $l, $r) = ($1, $2, $3);
$t -= $l unless $opt{l};
# remove fluff that mysqld writes to log when it (re)starts:
s!^/.*Version.*started with:.*\n!!mg;
s!^Tcp port: \d+ Unix socket: \S+\n!!mg;
s!^Time.*Id.*Command.*Argument.*\n!!mg;
s/^use \w+;\n//;# not consistently added
s/^SET timestamp=\d+;\n//;
s/^[]*\n//mg; # delete blank lines
s/^[]*/ /mg; # normalize leading whitespace
s/\s*;\s*(#\s*)?$//;# remove trailing semicolon(+newline-hash)
next if $opt{g} and !m/$opt{g}/io;
unless ($opt{a}) {
s/\b\d+\b/N/g;
s/\b0x[0-9A-Fa-f]+\b/N/g;
s/'.*?'/'S'/g;
s/".*?"/"S"/g;
# -n=8: turn log_20001231 into log_
s/([a-z_]+)(\d{$opt{n},})/$1.('N' x length($2))/ieg if $opt{n};
# abbreviate massive "in (...)" statements and similar
s!(([NS],){100,})!sprintf("$2,{repeated %d times}",length($1)/2)!eg;
}
my $s = $stmt{$_} ||= { users={}, hosts={} };
$s-{c} += 1;
$s-{t} += $t;
$s-{l} += $l;
$s-{r} += $r;
$s-{users}-{$user}++ if $user;
$s-{hosts}-{$host}++ if $host;
warn "{{$_}}\n\n" if $opt{d}; # show processed statement string
}
foreach (keys %stmt) {
my $v
Re: Antwort: ReisserFS
On Fri, Feb 23, 2001 at 11:29:21AM +0200, Tnu Samuel wrote: [EMAIL PROTECTED] wrote: On 22.02.2001 16:22:13 Simon Windsor wrote: Has anyone user MySql on a ReisserFS file system ? HERE! No problems whatsoever - why should there be problems anyway? Only problem we know is that in our tests on ReiserFS is MySQL was 30% faster on writes :/ How does it compare to FreeBSD with "soft updates" enabled? Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbit: ADO and MySQL/MyODBC
On Mon, Feb 05, 2001 at 01:36:50PM -0700, Steve Ruby wrote:
Henrik Lebtien Mohr wrote:
Hi there
I use ASP and ADO 2.5 to access the data in my MySQL RDBMS.
I get an error "Multiple-step error" when I try to update a recordset like
the following:
with rs
.fields("date") = null 'timestamp datatype
.fields("nOnline") = .fields("nOnline") + 1 'int datatype
.update
end with
There is no server-side cursor handling on mysql so unless you
are using something other than MyODBC to emulate cursors you must
treat all recordsets as static.
It _ought_ to be possible to persuade ADO to persaude the ODBC driver
manager to enable the use of the standard ODBC cursor library for that
connection.
No idea how though.
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables over Multiple Drives
On Tue, Jan 30, 2001 at 12:44:43PM +0100, Tonu Samuel wrote: On Tue, 30 Jan 2001, Jason Frisvold wrote: Thanks for the reply.. Is that a stable way of doing things? Is there a performance hit when using the --with-raid option? Very small if at all. RAID does some additional syscalls sometime but they should be enough rare to not sense this. So why isn't the --with-raid option set in the binary download version? Tim [who would like it to be]. I assume I will need to move the files manually and link them manually as well? Yes and no. When you create table using CREATE TABLE RAID_TYPE=RAID0 then MySQL creates directories 00/, 01/ and so on into data directory and creates tables into them. You can create symlinks to toher disks named 00, 01, 02 and MySQL does not rewrite them. Tonu - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Tables over Multiple Drives
On Tue, Jan 30, 2001 at 01:13:43PM -0800, Jeremy D. Zawodny wrote: On Tue, Jan 30, 2001 at 05:08:46PM +, Tim Bunce wrote: On Tue, Jan 30, 2001 at 10:57:16PM +0800, Sam Wong wrote: So why isn't the --with-raid option set in the binary download version? The file will be bigger and slower in result I doubt it would be significantly bigger. I believe --with-raid support is a very small and simple layer between mysqld and the file i/o system calls. At the open source database summit, I asked Monty why it wasn't enabled in the binaries they provide, and he said that it was for performance reasons. On each table open, MySQL has to see if it is a RAID table and do a bit of extra work. Why not determine it by checking only if the open of the non-raid fail failed? That way there'd be no penalty for those not using the raid feature. I'm not sure what you mean by "slower in result". He has estimated it as a performance hit of a "few percent", but if your tables stay open (because you don't have many, or you have a good-sized table cache) it really shouldn't be an issue. I can't see where a "few percent" hit would come from unless tables were being opened at the rate of many per second. Anyone smart enough to be using the raid feature would also know how to tune mysql to keep the files open. Those not using the feature should see no hit. Anyway, even if there is a hit, why not allow a runtime config option to disable the raid feature? Checking a global in C is basically free. He said that MaxSQL would have many of the compile-time options enabled for folks who still wanted to use a binary release. But it doesn't appear to have materialized... I wish it would. Monty, can you give us an update? Tim. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.22 database on 3.23 with field names 'when' will causes errors
On Tue, Jan 23, 2001 at 01:26:59PM +0200, Tomi Junnila wrote:
* Santeri Paavolainen [EMAIL PROTECTED] wrote on 23.01.01 13:05:
Fix:
SELECTs can be worked around with table aliases, others not.
Oops, I forgot to include the cure. The first way to do this I encountered
is to simply mysqldump the whole table into a text file, then search and
replace "when" with something else, and finally drop the table and run the
text file into mysql.
Another way to do this might be with "create table temporary_table ({fields
valid in 3.23}) select {fields from old table with aliases} from old_table"
but I haven't tried this myself.
And another way would be to put the offending in quotes.
From memory you can use the non-portable form:
`when`
or, if running with --ansi, use the standard
"when"
Tim.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
