[28440] in Perl-Users-Digest
Perl-Users Digest, Issue: 9804 Volume: 10
daemon@ATHENA.MIT.EDU (Perl-Users Digest)
Wed Oct 4 18:06:07 2006
Date: Wed, 4 Oct 2006 15:05:09 -0700 (PDT)
From: Perl-Users Digest <Perl-Users-Request@ruby.OCE.ORST.EDU>
To: Perl-Users@ruby.OCE.ORST.EDU (Perl-Users Digest)
Perl-Users Digest Wed, 4 Oct 2006 Volume: 10 Number: 9804
Today's topics:
Re: A Camel in My Mind's Eye <tadmc@augustmail.com>
Re: A Camel in My Mind's Eye <thyorison@yahoo.com>
Re: A Camel in My Mind's Eye <tadmc@augustmail.com>
Re: Archive::Zip - how to create huge zip files ? <nospam-abuse@ilyaz.org>
Re: FAQ 5.2 (Was: inserting lines) <brian.d.foy@gmail.com>
Re: FAQ 5.2 (Was: inserting lines) <brian.d.foy@gmail.com>
Re: FAQ 5.2 <nospam-abuse@ilyaz.org>
One-Liner Help with -e + ksh heredoc <smalder73@gmail.com>
Re: One-Liner Help with -e + ksh heredoc <this.address@is.invalid>
Re: One-Liner Help with -e + ksh heredoc anno4000@radom.zrz.tu-berlin.de
Re: One-Liner Help with -e + ksh heredoc <smalder73@gmail.com>
Re: One-Liner Help with -e + ksh heredoc <tadmc@augustmail.com>
Re: One-Liner Help with -e + ksh heredoc <this.address@is.invalid>
Re: perl and mysql: slow inserts with innodb <tadmc@augustmail.com>
Re: perl and mysql: slow inserts with innodb rickyars@gmail.com
Re: perl and mysql: slow inserts with innodb rickyars@gmail.com
Re: perl and mysql: slow inserts with innodb xhoster@gmail.com
Re: perl and mysql: slow inserts with innodb rickyars@gmail.com
Re: perl and mysql: slow inserts with innodb <no@email.com>
Re: perl and mysql: slow inserts with innodb xhoster@gmail.com
Re: perl and mysql: slow inserts with innodb rickyars@gmail.com
Re: Perl Async .10 anno4000@radom.zrz.tu-berlin.de
Re: Reducing dependencies when initializing Log4perl? <robb@acm.org>
Re: sorting array reference of hashes <uri@stemsystems.com>
Re: sorting array reference of hashes anno4000@radom.zrz.tu-berlin.de
Re: sorting array reference of hashes <tadmc@augustmail.com>
Re: sorting array reference of hashes <user@example.net>
Re: Spliting values and reversing a hash anno4000@radom.zrz.tu-berlin.de
Re: Spliting values and reversing a hash <rvtol+news@isolution.nl>
Validating Date-Time Ranges in a Schedule Database <ClipperMiami@gmail.com>
Digest Administrivia (Last modified: 6 Apr 01) (Perl-Users-Digest Admin)
----------------------------------------------------------------------
Date: Wed, 4 Oct 2006 13:08:14 -0500
From: Tad McClellan <tadmc@augustmail.com>
Subject: Re: A Camel in My Mind's Eye
Message-Id: <slrnei7u4e.3cs.tadmc@magna.augustmail.com>
Ray Eston Smith Jr <thyorison@yahoo.com> wrote:
> I'm just starting to learn Perl (I'm on page 65 of the camel book).
> As yet I have nothing to contribute to Perl discussions, not even
> intelligent questions. However, having obsessively studied Hamlet for
> the past 13 years,
I once posted a "quote" from Hamlet written in Perl code:
http://groups.google.com/group/comp.lang.perl.misc/msg/82d203670d040a18
HTH!
--
Tad McClellan SGML consulting
tadmc@augustmail.com Perl programming
Fort Worth, Texas
------------------------------
Date: 4 Oct 2006 11:58:23 -0700
From: "Ray Eston Smith Jr" <thyorison@yahoo.com>
Subject: Re: A Camel in My Mind's Eye
Message-Id: <1159988303.394222.267690@i3g2000cwc.googlegroups.com>
Tad McClellan wrote:
>
> I once posted a "quote" from Hamlet written in Perl code:
>
> http://groups.google.com/group/comp.lang.perl.misc/msg/82d203670d040a18
>
>
> HTH!
>
>
> --
> Tad McClellan SGML consulting
> tadmc@augustmail.com Perl programming
> Fort Worth, Texas
Here's the quote you wrote:
#!/usr/bin/perl -w
use strict;
my $b2;
do {
print '2B or !2B: ';
chomp($b2 = <STDIN>);
} until $b2 =~ /^!?2B$/;
if ( $b2 =~ /^!/ )
{ kill TERM => $$ }
else
{ print `/usr/games/fortune` }
Here's my attempt to interpret it:
The program will print '2B or !2B' (pronounced "To be or not to be")
If the user responds with anything other than '2B' or '!2B',
then the program will repeat the '2B or !2B' prompt.
When the user responds with '!2B',
the program ends (kills itself) and returns control to the command
line.
When the user responds with '2B',
the program prints '/usr/games/fortune',
where I presume he would find slings and arrows.
------------------------------
Date: Wed, 4 Oct 2006 14:41:41 -0500
From: Tad McClellan <tadmc@augustmail.com>
Subject: Re: A Camel in My Mind's Eye
Message-Id: <slrnei83jl.4cr.tadmc@magna.augustmail.com>
Ray Eston Smith Jr <thyorison@yahoo.com> wrote:
>
> Tad McClellan wrote:
>>
>> I once posted a "quote" from Hamlet written in Perl code:
>>
>> http://groups.google.com/group/comp.lang.perl.misc/msg/82d203670d040a18
>>
>>
>> HTH!
>>
>>
>> --
>> Tad McClellan SGML consulting
>> tadmc@augustmail.com Perl programming
>> Fort Worth, Texas
>
> Here's the quote you wrote:
> #!/usr/bin/perl -w
> use strict;
>
>
> my $b2;
> do {
> print '2B or !2B: ';
> chomp($b2 = <STDIN>);
>
>
>
> } until $b2 =~ /^!?2B$/;
>
>
> if ( $b2 =~ /^!/ )
> { kill TERM => $$ }
> else
> { print `/usr/games/fortune` }
>
> Here's my attempt to interpret it:
> The program will print '2B or !2B' (pronounced "To be or not to be")
> If the user responds with anything other than '2B' or '!2B',
> then the program will repeat the '2B or !2B' prompt.
> When the user responds with '!2B',
> the program ends (kills itself) and returns control to the command
> line.
> When the user responds with '2B',
> the program prints '/usr/games/fortune',
Notice which way the originale single quotes are slanted.
When the user responds with '2B',
the program _executes_ the program named "fortune".
(which outputs a random quote, like a fortune cookie.)
> where I presume he would find slings and arrows.
Where he would suffer outrageous fortune.
--
Tad McClellan SGML consulting
tadmc@augustmail.com Perl programming
Fort Worth, Texas
------------------------------
Date: Wed, 4 Oct 2006 21:09:06 +0000 (UTC)
From: Ilya Zakharevich <nospam-abuse@ilyaz.org>
Subject: Re: Archive::Zip - how to create huge zip files ?
Message-Id: <eg17ti$1vt9$1@agate.berkeley.edu>
[A complimentary Cc of this posting was sent to
MoshiachNow
<lev.weissman@creo.com>], who wrote in article <1159964275.746749.87220@k70g2000cwa.googlegroups.com>:
> However - If I'm zipping huge (20GB) database files into the zip
Zip directory info has only 4byte-long integers. No go.
Hope this helps,
Ilya
------------------------------
Date: Wed, 04 Oct 2006 13:51:21 -0500
From: brian d foy <brian.d.foy@gmail.com>
Subject: Re: FAQ 5.2 (Was: inserting lines)
Message-Id: <041020061351210646%brian.d.foy@gmail.com>
In article <slrnei5ta3.uhj.tadmc@magna.augustmail.com>, Tad McClellan
<tadmc@augustmail.com> wrote:
> You should probably review the answer that was there before 5.8.0
> (which I liked better than what we have now).
> Here is the POD from 5.6.1 for easy reference (=head2 line wrapped by me):
>
> ------------------------------------------------------
> =head2 How do I change one line in a file/delete a line in a file/
> insert a line in the middle of a file/append to the beginning of a
> file?
The old answer doesn't answer any of the questions. It might be a nice
answer for some other question, but it doesn't address any one of those
operations. Hence, it was removed.
Many of the old perlfaq had the problem of answering something close to
the question because it was too hard or too involved to get into a real
answer.
--
Posted via a free Usenet account from http://www.teranews.com
------------------------------
Date: Wed, 04 Oct 2006 15:10:02 -0500
From: brian d foy <brian.d.foy@gmail.com>
Subject: Re: FAQ 5.2 (Was: inserting lines)
Message-Id: <041020061510023903%brian.d.foy@gmail.com>
In article <031020061558043174%jgibson@mail.arc.nasa.gov>, Jim Gibson
<jgibson@mail.arc.nasa.gov> wrote:
> I recently complained about the lack of alternate approaches to the FAQ
> "How do I change one line in a file/delete a line in a file/insert a
> line in the middle of a file/append to the beginning of a file?"
Hmmm...., I must have missed that. I didn't see anything in Google
Groups from you in response to previous postings to this answer, and it
looks like you've never posted to perlfaq-workers. I won't see
complaints unless they show up as responses to the autoposter or in
perlfaq-workers, so I won't be able to do anything about them otherwise.
Anyway, here's the new answer. I moved the one liner stuff to the end
so I could explain the basic process before I got to the special
features (because hey, if they are asking the question, they aren't
just missing the knowledge about -p :)
And, as always, code does not explain anything to newbies, so every
code example gets a prose sentence that says the same thing. :)
Thanks,
----
The basic idea of inserting, changing, or deleting a line from a text
file involves reading and printing the file to the point you want to
make the change, making the change, then reading and printing the rest
of the file. Perl doesn't provide random access to lines (especially
since the record input separator, C<$/>, is mutable), although modules
such as C<Tie::File> can fake it.
A Perl program to do these tasks takes the basic form of opening a
file, printing its lines, then closing the file:
open my $in, '<', $file or die "Can't read old file: $!";
open my $out, '>', "$file.new" or die "Can't write new file: $!";
while( <$in> )
{
print $out $_;
}
close $out;
Within that basic form, add the parts that you need to insert, change,
or delete lines.
To prepend lines to the beginning, print those lines before you enter
the loop that prints the existing lines.
open my $in, '<', $file or die "Can't read old file: $!";
open my $out, '>', "$file.new" or die "Can't write new file: $!";
print "# Add this line to the top\n"; # <--- HERE'S THE MAGIC
while( <$in> )
{
print $out $_;
}
close $out;
To change existing lines, insert the code to modify the lines inside
the C<while> loop. In this case, the code finds all lowercased
versions of "perl" and uppercases them. The happens for every line, so
be sure that you're supposed to do that on every line!
open my $in, '<', $file or die "Can't read old file: $!";
open my $out, '>', "$file.new" or die "Can't write new file: $!";
print "# Add this line to the top\n";
while( <$in> )
{
s/\b(perl)\b/Perl/g;
print $out $_;
}
close $out;
To change only a particular line, the input line number, C<$.>, is
useful. Use C<next> to skip all lines up to line 5, make a change and
print the result, then stop further processing with C<last>.
while( <$in> )
{
next unless $. == 5;
s/\b(perl)\b/Perl/g;
print $out $_;
last;
}
To skip lines, use the looping controls. The C<next> in this example
skips comment lines, and the C<last> stops all processing once it
encounters either C<__END__> or C<__DATA__>.
while( <$in> )
{
next if /^\s+#/; # skip comment lines
last if /^__(END|DATA)__$/; # stop at end of code marker
print $out $_;
}
Do the same sort of thing to delete a particular line by using C<next>
to skip the lines you don't want to show up in the output. This
example skips every fifth line:
while( <$in> )
{
next unless $. % 5;
print $out $_;
}
If, for some odd reason, you really want to see the whole file at once
rather than processing line by line, you can slurp it in (as long as
you can fit the whole thing in memory!):
open my $in, '<', $file or die "Can't read old file: $!"
open my $out, '>', "$file.new" or die "Can't write new file: $!";
my @lines = do { local $/; <$in> }; # slurp!
# do your magic here
print $out @lines;
Modules such as C<File::Slurp> and C<Tie::File> can help with that
too. If you can, however, avoid reading the entire file at once. Perl
won't give that memory back to the operating system until the process
finishes.
You can also use Perl one-liners to modify a file in-place. The
following changes all 'Fred' to 'Barney' in F<inFile.txt>, overwriting
the file with the new contents. With the C<-p> switch, Perl wraps a
C<while> loop around the code you specify with C<-e>, and C<-i> turns
on in-place editing. The current line is in C<$_>. With C<-p>, Perl
automatically prints the value of C<$_> at the end of the loop. See
L<perlrun> for more details.
perl -pi -e 's/Fred/Barney/' inFile.txt
To make a backup of C<inFile.txt>, give C<-i> a file extension to add:
perl -pi.bak -e 's/Fred/Barney/' inFile.txt
To change only the fifth line, you can add a test checking C<$.>, the
input line number, then only perform the operation when the test
passes:
perl -pi -e 's/Fred/Barney/ if $. == 5' inFile.txt
To add lines before a certain line, you can add a line (or lines!)
before Perl prints C<$_>:
perl -pi -e 'print "Put before third line\n" if $. == 3' inFile.txt
You can even add a line to the beginning of a file, since the current
line prints at the end of the loop:
perl -pi -e 'print "Put before first line\n" if $. == 1' inFile.txt
To insert a line after one already in the file, use the C<-n> switch.
It's just like C<-p> except that it doesn't print C<$_> at the end of
the loop, so you have to do that yourself. In this case, print C<$_>
first, then print the line that you want to add.
perl -ni -e 'print; print "Put after fifth line\n" if $. == 5'
inFile.txt
To delete lines, only print the ones that you want.
perl -ni -e 'print unless /d/' inFile.txt
... or ...
perl -pi -e 'next unless /d/' inFile.txt
To delete lines, only print the ones that you want.
perl -ni -e 'print unless /d/' inFile.txt
... or ...
perl -pi -e 'next unless /d/' inFile.txt
--
Posted via a free Usenet account from http://www.teranews.com
------------------------------
Date: Wed, 4 Oct 2006 21:20:33 +0000 (UTC)
From: Ilya Zakharevich <nospam-abuse@ilyaz.org>
Subject: Re: FAQ 5.2
Message-Id: <eg18j1$20b6$1@agate.berkeley.edu>
[A complimentary Cc of this posting was NOT [per weedlist] sent to
Dr.Ruud
<rvtol+news@isolution.nl>], who wrote in article <eg0n77.1a4.1@news.isolution.nl>:
> Uri Guttman schreef:
> >> [atribution repaired] Ruud:
>
> >>> my $text ; { local $/ ; $text = <> }
> >
> > anyhow that would be simpler as:
> >
> > my $text = do { local $/ ; <> }
>
> AFAIK that would be using more memory.
env PERL_DEBUG_MSTATS=2 perl -wle "my $t = do {local $/; <>}; undef" a.zip
env PERL_DEBUG_MSTATS=2 perl -wle "my $t; do {local $/; $t = <>; undef}; undef" a.zip
agrees with you; moreover, the `undef' at the end of do-block is not
relevant. The first one uses about 2x of the size of ZIP (due to
rounding-to-power-of-2). the second one twice more than this.
[I do not remember whether readline() was included when I optimized
the case "the result of operator is assigned to a lexical"; maybe it
was somebody else?
Thanks,
Ilya
------------------------------
Date: 4 Oct 2006 11:50:59 -0700
From: "s/war/peace/g;" <smalder73@gmail.com>
Subject: One-Liner Help with -e + ksh heredoc
Message-Id: <1159987852.371790.152050@b28g2000cwb.googlegroups.com>
I'm in the midst of writing a ksh script. I've got a file of junk I
need to manipulate with some hash magic in perl, but I'm trying to keep
the perl code encapsulated in my ksh script. I'm trying to do
something like this...
perl -e 'magic' -i file
But I want the magic to look more like...
perl -e <<EOF
magic
EOF
-i file
This way the code is not cryptic. So far I can not seem to make this
work... am I on crack for thinking I should be able to do something
like this? Any suggestions?
------------------------------
Date: Wed, 4 Oct 2006 19:59:22 +0100
From: Stephane CHAZELAS <this.address@is.invalid>
Subject: Re: One-Liner Help with -e + ksh heredoc
Message-Id: <slrnei814a.aro.stephane.chazelas@spam.is.invalid>
2006-10-4, 11:50(-07), s/war/peace/g;:
> I'm in the midst of writing a ksh script. I've got a file of junk I
> need to manipulate with some hash magic in perl, but I'm trying to keep
> the perl code encapsulated in my ksh script. I'm trying to do
> something like this...
>
> perl -e 'magic' -i file
>
> But I want the magic to look more like...
>
> perl -e <<EOF
> magic
> EOF
> -i file
[...]
What about
perl -e '
magic
' -i file
Or
perl -e "$(
cat << \EOF
magic
EOF
)" -i file
Or
magic=$(
cat << \EOF
magic
EOF
)
perl -e "$magic" -i file
If your system supports the /dev/fd/<n> thing:
perl /dev/fd/3 -i file 3<< \EOF
magic
EOF
--
Stéphane
------------------------------
Date: 4 Oct 2006 19:18:46 GMT
From: anno4000@radom.zrz.tu-berlin.de
Subject: Re: One-Liner Help with -e + ksh heredoc
Message-Id: <4oifomFeopg4U1@news.dfncis.de>
s/war/peace/g; <smalder73@gmail.com> wrote in comp.lang.perl.misc:
> I'm in the midst of writing a ksh script. I've got a file of junk I
> need to manipulate with some hash magic in perl, but I'm trying to keep
> the perl code encapsulated in my ksh script. I'm trying to do
> something like this...
>
> perl -e 'magic' -i file
>
> But I want the magic to look more like...
>
> perl -e <<EOF
> magic
> EOF
> -i file
>
> This way the code is not cryptic. So far I can not seem to make this
> work... am I on crack for thinking I should be able to do something
> like this? Any suggestions?
You are misunderstanding ksh here documents. They don't return
a string, they supply the given text via standard input. Thus in a
ksh script
perl <<EOF
print "hihi\n";
print "haha\n";
EOF
would print the expected two lines. How well that mixes with -i
I don't know.
Anno
------------------------------
Date: 4 Oct 2006 12:40:03 -0700
From: "s/war/peace/g;" <smalder73@gmail.com>
Subject: Re: One-Liner Help with -e + ksh heredoc
Message-Id: <1159990803.920478.250280@m7g2000cwm.googlegroups.com>
TYVM!
I had avoided using the multi line -e ' syntax because I have some
pipes and quotes in my code and other characters the shell seems to
like to interpret... hadn't thought about the cat <<EOF though that's a
neato trick. Many Thanks!
Stephane CHAZELAS wrote:
> 2006-10-4, 11:50(-07), s/war/peace/g;:
> > I'm in the midst of writing a ksh script. I've got a file of junk I
> > need to manipulate with some hash magic in perl, but I'm trying to keep
> > the perl code encapsulated in my ksh script. I'm trying to do
> > something like this...
> >
> > perl -e 'magic' -i file
> >
> > But I want the magic to look more like...
> >
> > perl -e <<EOF
> > magic
> > EOF
> > -i file
> [...]
>
> What about
>
> perl -e '
> magic
> ' -i file
>
> Or
>
> perl -e "$(
> cat << \EOF
> magic
> EOF
> )" -i file
>
> Or
>
> magic=3D$(
> cat << \EOF
> magic
> EOF
> )
>
> perl -e "$magic" -i file
>
> If your system supports the /dev/fd/<n> thing:
>=20
> perl /dev/fd/3 -i file 3<< \EOF
> magic
> EOF
>=20
> --=20
> St=E9phane
------------------------------
Date: Wed, 4 Oct 2006 14:43:57 -0500
From: Tad McClellan <tadmc@augustmail.com>
Subject: Re: One-Liner Help with -e + ksh heredoc
Message-Id: <slrnei83nt.4cr.tadmc@magna.augustmail.com>
anno4000@radom.zrz.tu-berlin.de <anno4000@radom.zrz.tu-berlin.de> wrote:
> s/war/peace/g; <smalder73@gmail.com> wrote in comp.lang.perl.misc:
>> I'm in the midst of writing a ksh script. I've got a file of junk I
>> need to manipulate with some hash magic in perl, but I'm trying to keep
>> the perl code encapsulated in my ksh script. I'm trying to do
>> something like this...
>>
>> perl -e 'magic' -i file
>>
>> But I want the magic to look more like...
>>
>> perl -e <<EOF
>> magic
>> EOF
>> -i file
>>
>> This way the code is not cryptic. So far I can not seem to make this
>> work... am I on crack for thinking I should be able to do something
>> like this? Any suggestions?
>
> You are misunderstanding ksh here documents. They don't return
> a string, they supply the given text via standard input.
they supply the given text right "here" (where the here-doc appears),
hence the name "here document".
--
Tad McClellan SGML consulting
tadmc@augustmail.com Perl programming
Fort Worth, Texas
------------------------------
Date: Wed, 4 Oct 2006 21:01:18 +0100
From: Stephane CHAZELAS <this.address@is.invalid>
Subject: Re: One-Liner Help with -e + ksh heredoc
Message-Id: <slrnei84oe.ce4.stephane.chazelas@spam.is.invalid>
2006-10-4, 12:40(-07), s/war/peace/g;:
> TYVM!
>
> I had avoided using the multi line -e ' syntax because I have some
> pipes and quotes in my code and other characters the shell seems to
> like to interpret... hadn't thought about the cat <<EOF though that's a
> neato trick. Many Thanks!
[...]
The shell doesn't interpret anything inside '...'. So, as long
as you don't have single quotes in your perl code, it should be
OK.
--
Stéphane
------------------------------
Date: Wed, 4 Oct 2006 13:04:32 -0500
From: Tad McClellan <tadmc@augustmail.com>
Subject: Re: perl and mysql: slow inserts with innodb
Message-Id: <slrnei7ttg.3cs.tadmc@magna.augustmail.com>
rickyars@gmail.com <rickyars@gmail.com> wrote:
> i'm trying to insert 500k rows from a text file into a innodb table and
> it's taking a little over 4 hours.
> my $select_handle =
> $dbh->prepare('SELECT agent_id, agent_fullname FROM agents WHERE
> agent_shortname = ? AND RDEOUTPUT_rde_id = ?');
Does your DB have an index on the agent_shortname and RDEOUTPUT_rde_id columns?
--
Tad McClellan SGML consulting
tadmc@augustmail.com Perl programming
Fort Worth, Texas
------------------------------
Date: 4 Oct 2006 12:28:10 -0700
From: rickyars@gmail.com
Subject: Re: perl and mysql: slow inserts with innodb
Message-Id: <1159990090.833246.130570@i42g2000cwa.googlegroups.com>
hi tad,
yes, the table has an index for both columns.
Tad McClellan wrote:
> rickyars@gmail.com <rickyars@gmail.com> wrote:
> > i'm trying to insert 500k rows from a text file into a innodb table and
> > it's taking a little over 4 hours.
>
> > my $select_handle =
> > $dbh->prepare('SELECT agent_id, agent_fullname FROM agents WHERE
> > agent_shortname = ? AND RDEOUTPUT_rde_id = ?');
>
>
> Does your DB have an index on the agent_shortname and RDEOUTPUT_rde_id columns?
>
>
> --
> Tad McClellan SGML consulting
> tadmc@augustmail.com Perl programming
> Fort Worth, Texas
------------------------------
Date: 4 Oct 2006 12:33:39 -0700
From: rickyars@gmail.com
Subject: Re: perl and mysql: slow inserts with innodb
Message-Id: <1159990419.090249.158200@h48g2000cwc.googlegroups.com>
hi tad,
yes, the table has an index for both columns.
Tad McClellan wrote:
> rickyars@gmail.com <rickyars@gmail.com> wrote:
> > i'm trying to insert 500k rows from a text file into a innodb table and
> > it's taking a little over 4 hours.
>
> > my $select_handle =
> > $dbh->prepare('SELECT agent_id, agent_fullname FROM agents WHERE
> > agent_shortname = ? AND RDEOUTPUT_rde_id = ?');
>
>
> Does your DB have an index on the agent_shortname and RDEOUTPUT_rde_id columns?
>
>
> --
> Tad McClellan SGML consulting
> tadmc@augustmail.com Perl programming
> Fort Worth, Texas
------------------------------
Date: 04 Oct 2006 19:53:27 GMT
From: xhoster@gmail.com
Subject: Re: perl and mysql: slow inserts with innodb
Message-Id: <20061004155511.076$cz@newsreader.com>
rickyars@gmail.com wrote:
> i'm trying to insert 500k rows from a text file into a innodb table and
> it's taking a little over 4 hours.
How is the CPU usage divided between mysql and perl during that time?
Where/how are you committing?
>
> would someone tell me what is wrong with this code?
There isn't necessarily anything wrong with your code. Some things
are just slow.
> would someone show
> me how to do a multi-value input in perl (e.g. 10k rows with single
> insert statment)?
Do you know that mysql syntax for doing that? What part of that are you
having trouble with? This is probably one place where I would not use
placeholders, but rather use $dbh->quote($data). If you want to do it in
one command, you could use (from right to left) a map to escape and quote
the data, a join to put commas in, a map to wrap those in paranthesis, and
a join to put commas between them. And with a
while (my @chunk = splice @data,0,10_000) {
around all of it to break it into chunks of 10k.
> my $select_handle =
> $dbh->prepare('SELECT agent_id, agent_fullname FROM agents WHERE
> agent_shortname = ? AND RDEOUTPUT_rde_id = ?');
> my $insert_handle =
> $dbh->prepare('INSERT INTO actual VALUES
> (DEFAULT,?,?,?,?,?,?,?,?,?)');
>
> while (<$GTRUTH>) {
> chomp;
> my @gtruth = split;
>
> # get FK: AGENTS_agent_id
> $select_handle->execute($gtruth[3],$rde_id);
> my ($AGENTS_agent_id, $agent_fullname) =
> $select_handle->fetchrow;
>
> $insert_handle->execute(
> $AGENTS_agent_id, # FK from agents
> $rde_id, # FK from
> rdeoutput $gtruth_line[0], #
> ground truth time 0,
> # target is DEAD $gtruth_line[7],
> # move status $gtruth_line[8] #
> action );
How long does it take if you comment out the $insert_handle->execute and
only do the $select_handle part? How about just printing the formatted
data into a text file (rather than inserting it) then using mysql LOAD DATA
command?
> }
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
------------------------------
Date: 4 Oct 2006 13:04:36 -0700
From: rickyars@gmail.com
Subject: Re: perl and mysql: slow inserts with innodb
Message-Id: <1159992276.228028.171500@m73g2000cwd.googlegroups.com>
> How is the CPU usage divided between mysql and perl during that time?
> Where/how are you committing?
almost no CPU usage and small memory footprint (6MB). the database is
on a computer in another city, but we have T1 lines.
> There isn't necessarily anything wrong with your code. Some things
> are just slow.
i'm starting to figure this out.
> How long does it take if you comment out the $insert_handle->execute and
> only do the $select_handle part? How about just printing the formatted
the select handle roughly halves the run time.
> data into a text file (rather than inserting it) then using mysql LOAD DATA
> command?
this is finally what i resorted to. i can parse the files to CSV text
documents in < 1 min. using LOAD DATA i can fill the table in 40
seconds. i would really like to know why my code is ~400 time slower
than LOAD DATA.
------------------------------
Date: Wed, 04 Oct 2006 21:20:03 +0100
From: Brian Wakem <no@email.com>
Subject: Re: perl and mysql: slow inserts with innodb
Message-Id: <4oijbaFept6fU1@individual.net>
rickyars@gmail.com wrote:
>> How is the CPU usage divided between mysql and perl during that time?
>> Where/how are you committing?
>
> almost no CPU usage and small memory footprint (6MB). the database is
> on a computer in another city, but we have T1 lines.
There's your bottleneck. I'd say 500k rows in 4 hours (34.7/secs) is
acceptable under the circumstances. Even on a T1 it will be vastly slower
than if running on a local network or the same machine.
If you can run the script on the same machine as the db I expect you will
cut the 4hrs to nearer 4 minutes.
--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png
------------------------------
Date: 04 Oct 2006 20:46:11 GMT
From: xhoster@gmail.com
Subject: Re: perl and mysql: slow inserts with innodb
Message-Id: <20061004164755.720$0T@newsreader.com>
rickyars@gmail.com wrote:
> > How is the CPU usage divided between mysql and perl during that time?
> > Where/how are you committing?
>
> almost no CPU usage and small memory footprint (6MB). the database is
> on a computer in another city, but we have T1 lines.
You are probably limited by latency, not by throughput. A wide area T1
line may have high throughput, but I would guess the latency is still quite
poor.
Can you just ship the file over to the remote computer and run your
script over there?
> > There isn't necessarily anything wrong with your code. Some things
> > are just slow.
>
> i'm starting to figure this out.
>
> > How long does it take if you comment out the $insert_handle->execute
> > and only do the $select_handle part? How about just printing the
> > formatted
>
> the select handle roughly halves the run time.
So even if you used multi-valued insert statements, it could only halve
the time. Can you just pull the entire selected table into a look-up
hash rather than making a round-trip each time?
> > data into a text file (rather than inserting it) then using mysql LOAD
> > DATA command?
>
> this is finally what i resorted to. i can parse the files to CSV text
> documents in < 1 min. using LOAD DATA i can fill the table in 40
> seconds. i would really like to know why my code is ~400 time slower
> than LOAD DATA.
It's just the way things are. When you do it row by row, you send a small
message to the server and can't move on to the next one until you receive
the mysql-level acknowledgement/results for the last one. With LOAD DATA
you can send big messages and send many at once without waiting for
acknowledgements.
Xho
--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB
------------------------------
Date: 4 Oct 2006 14:38:33 -0700
From: rickyars@gmail.com
Subject: Re: perl and mysql: slow inserts with innodb
Message-Id: <1159997913.694429.325430@m7g2000cwm.googlegroups.com>
Everyone, thank you for your comments and suggestions. I ended up
storing foreign keys into hashes and then writing out my data to a CSV
file. Afterward LOAD DATA filled my tables in < 1 minute. Needless to
say we're happy (although I'm not to sure about the aesthetics of the
solution).
-ricky
------------------------------
Date: 4 Oct 2006 18:55:58 GMT
From: anno4000@radom.zrz.tu-berlin.de
Subject: Re: Perl Async .10
Message-Id: <4oieduFed69qU1@news.dfncis.de>
<xhoster@gmail.com> wrote in comp.lang.perl.misc:
> "Todd English" <toddenglish@gmail.com> wrote:
> > Thank you for the reply.
> >
> > > my hunch was that your problem occurred because you're deleting items
> > > from the hash while iterating over it, but on second thought I'm not so
> > > sure.
> >
> > I originally thought this as well, and had tried a couple of variants
> > to the code I posted where I didn't mess with the proc hash until all
> > procs had reported their return. But this didn't make any difference
> > and upon further reflection I didn't think this was the case.
>
> My original post on this topic apparently never showed up. The problem
> seems to come from the "kill 9,..." in Async's sub DESTROY. An Async child
> will inherit a copy of the objects to all of its older siblings from the
> parent. When the child exits, it will kill all of it's older siblings.
> Thus the program only works correctly if the children terminate in the same
> order that they began, so that a younger child never has an extent older
> sibling to kill out of order.
Oh, of course! Thats explains the erratic behavior.
> I have no idea why the kill is there, and getting rid of it makes the
> problem go away.
If at all, only the main process should do it.
Anno
------------------------------
Date: 4 Oct 2006 11:32:07 -0700
From: "robb@acm.org" <robb@acm.org>
Subject: Re: Reducing dependencies when initializing Log4perl?
Message-Id: <1159986727.055195.282760@m7g2000cwm.googlegroups.com>
Randal L. Schwartz wrote:
> See "perldoc -f caller".
Thanks! Now I have this, which is working great:
# 1. Figure out the name of the package or script.
my ($package, $filename, $line) = caller;
if ($package eq "main") {
($package) = $filename =~ /([^\/]+)$/;
}
# 2. Configure and retrieve a logger.
return Log::Log4perl->get_logger($package);
------------------------------
Date: Wed, 04 Oct 2006 14:05:18 -0400
From: Uri Guttman <uri@stemsystems.com>
Subject: Re: sorting array reference of hashes
Message-Id: <x764f0gdnl.fsf@mail.sysarch.com>
>>>>> "mp" == monkeys paw <user@example.net> writes:
mp> I have an array of hashes like so:
mp> $f = [ {
mp> 'file_name' => 'TakeTwo',
mp> 'id_type' => 'bill',
mp> 'id' => 'CA2005010A1',
mp> },
mp> {
mp> 'file_name' => 'steve',
mp> 'id_type' => 'bill',
mp> 'id' => 'CA2005000A3',
mp> },
mp> ];
mp> I want to sort by file_name.
mp> I am trying this:
mp> sort sort_by_filename @{$f};
mp> sub sort_by_filename {
mp> return $a->{file_name} cmp $b->{file_name}
mp> }
mp> This does not work. How would one do this??
never use the term "doesn't work" without explaining what you
expected. what kind of results did you get that you didn't like? were
those the only 2 records to be sorted?
now, i feel i know the answer to your problem as i bet 'TakeTwo' sorted
before steve. have you ever done ascii order sorts? ever heard of case
issues? you need to make the sort sub ignore case and i leave that as an
exercise to the OP.
uri
--
Uri Guttman ------ uri@stemsystems.com -------- http://www.stemsystems.com
--Perl Consulting, Stem Development, Systems Architecture, Design and Coding-
Search or Offer Perl Jobs ---------------------------- http://jobs.perl.org
------------------------------
Date: 4 Oct 2006 18:08:16 GMT
From: anno4000@radom.zrz.tu-berlin.de
Subject: Re: sorting array reference of hashes
Message-Id: <4oibkgFe5fk1U1@news.dfncis.de>
monkeys paw <user@example.net> wrote in comp.lang.perl.misc:
> I have an array of hashes like so:
>
> $f = [ {
> 'file_name' => 'TakeTwo',
> 'id_type' => 'bill',
> 'id' => 'CA2005010A1',
> },
> {
> 'file_name' => 'steve',
> 'id_type' => 'bill',
> 'id' => 'CA2005000A3',
> },
> ];
>
> I want to sort by file_name.
>
> I am trying this:
>
> sort sort_by_filename @{$f};
>
> sub sort_by_filename {
>
> return $a->{file_name} cmp $b->{file_name}
>
> }
>
> This does not work. How would one do this??
Your example is unfortunate because it already is in sorted order.
That raises the question how you know it doesn't work.
More generally, what did you expect it to do? Sort the array in place?
That isn't what sort() does, it returns the sorted list. To emulate
in-place sorting, you can do
@$f = sort sort_by_filename @$f;
Anno
------------------------------
Date: Wed, 4 Oct 2006 13:13:14 -0500
From: Tad McClellan <tadmc@augustmail.com>
Subject: Re: sorting array reference of hashes
Message-Id: <slrnei7udq.3cs.tadmc@magna.augustmail.com>
monkeys paw <user@example.net> wrote:
> sort sort_by_filename @{$f};
You should always enable warnings when developing Perl code!
> This does not work.
What did you observe that led you to conclude that it is not working?
Your program has no output statements in it, so the program is
_supposed_ to make no output.
> How would one do this??
my @sorted = sort sort_by_filename @{$f};
print "$_->{file_name}\n" for @sorted;
--
Tad McClellan SGML consulting
tadmc@augustmail.com Perl programming
Fort Worth, Texas
------------------------------
Date: Wed, 04 Oct 2006 14:20:09 -0400
From: monkeys paw <user@example.net>
Subject: Re: sorting array reference of hashes
Message-Id: <fq6dndLes6L-Zr7YnZ2dnUVZ_rudnZ2d@insightbb.com>
Sorry everyone, i did some more on this problem and it is working.
I simplified my example too much for proper explanation.
My answer was:
> $f = [ {
> 'file_name' => 'TakeTwo',
> 'id_type' => 'bill',
> 'id' => 'CA2005010A1',
> },
> {
> 'file_name' => 'steve',
> 'id_type' => 'bill',
> 'id' => 'CA2005000A3',
> },
> ];
@ordered = sort {lc($a->{file_name}) cmp lc($b->{file_name})} @{$f};
monkeys paw wrote:
> I have an array of hashes like so:
>
> $f = [ {
> 'file_name' => 'TakeTwo',
> 'id_type' => 'bill',
> 'id' => 'CA2005010A1',
> },
> {
> 'file_name' => 'steve',
> 'id_type' => 'bill',
> 'id' => 'CA2005000A3',
> },
> ];
>
> I want to sort by file_name.
>
> I am trying this:
>
> sort sort_by_filename @{$f};
>
> sub sort_by_filename {
>
> return $a->{file_name} cmp $b->{file_name}
>
> }
>
> This does not work. How would one do this??
------------------------------
Date: 4 Oct 2006 18:23:42 GMT
From: anno4000@radom.zrz.tu-berlin.de
Subject: Re: Spliting values and reversing a hash
Message-Id: <4oicheFecv3bU1@news.dfncis.de>
Charles DeRykus <ced@blv-sam-01.ca.boeing.com> wrote in comp.lang.perl.misc:
> John W. Krahn wrote:
> > attn.steven.kuo@gmail.com wrote:
> >> John W. Krahn wrote:
> >>> for my $key ( keys %hash ) {
> >>> for my $flavour ( @{ delete $hash{ $key } } ) {
> >>> push @{ $hash{ $flavour } }, $key;
> >>> }
> >>> }
> >> If I recall correctly, it isn't a good idea to delete
> >> and add keys to a hash while iterating over it.
> >
> > "for my $key ( keys %hash )" creates a list of the hash keys in memory and any
> > modifications to %hash inside the loop won't affect the contents of that list.
> >
> > perldoc -f delete
> ^^^^^^
> ITYM perldoc -f keys:
>
> The returned values are copies of the original keys
> in the hash, so modifying them will not affect the
> original hash. Compare "values".
If I read that right, it is about the keys not being lvalues the
way values are. That is one thing, changing the hash while looping
over its keys is another.
I believe it is safe to change the hash in
for my $key ( keys %hash ) {
# changes to %hash here
}
but the behavior of
for my $i ( 1 .. 100_000_000 ) {
can make a programmer wonder. If the loop over keys %hash were
transformed to use an implicit iterator like the second one
changing the hash might be unsafe.
Anno
------------------------------
Date: Wed, 4 Oct 2006 20:40:53 +0200
From: "Dr.Ruud" <rvtol+news@isolution.nl>
Subject: Re: Spliting values and reversing a hash
Message-Id: <eg16p1.1d0.1@news.isolution.nl>
Michele Dondi schreef:
> Dr.Ruud:
>> perl -MData::Dumper -wle '
>> my %in = (
>> 1 => ["Chocolate", "Vanilla", "Rocky Road"],
>> 2 => ["Strawberry", "Vanilla", "Pistachio"],
>> 3 => ["Cookie Dough", "Chocolate"],
>> 4 => ["Strawberry", "Pistachio"],
>> );
>> my %out;
>> for my $k (keys %in) {
>> for (@{$in{$k}}) {
>> $out{$_} .= $out{$_} ? ",$k" : $k;
>> }
>> }
>> print Dumper \%in, \%out;
>> '
>
> Funnily enough you chose exactly the other way around what I did, i.e.
> arrayrefs for the input and strings for the output.
I created the code last night, but hesitated to post it because I just
didn't like it. When I saw the other reactions, I felt that it was
different enough, so I posted it after all.
--
Affijn, Ruud
"Gewoon is een tijger."
------------------------------
Date: 4 Oct 2006 14:39:38 -0700
From: "John" <ClipperMiami@gmail.com>
Subject: Validating Date-Time Ranges in a Schedule Database
Message-Id: <1159997978.837729.104600@e3g2000cwe.googlegroups.com>
We have developed a manpower scheduling application for one of our
clients. The application allows the client to maintain independent
weekly work schedules for a number of customers and locations. Now the
client wants us to do conflict recognition to ensure that the same
employee(s) are not assigned to multiple customer/locations for the
same date and time period.
The application is in PERLscript under IIS and uses the JET engine for
the database and OLE/ADODB for accessing the database.
We have implemented this and it works in most cases. We do an SQL that
tests for the start date/time and end date/time within the time ranges
for all the other items in the database for the same date/time range at
all other locations i.e.
"SELECT sm_location_id, sm_customer_id, sd_id, sd_date, sd_time_in,
sd_time_out FROM Schedule_Master, Schedule_Detail WHERE sm_location_id
<> '$locationID' AND (sd_master = sm_id AND sd_employeeid =
'$sd_employeeid' AND (($sd_datetime_in BETWEEN sd_date & sd_time_in AND
sd_date & sd_time_out) OR ($sd_datetime_out BETWEEN sd_date &
sd_time_in AND sd_date & sd_time_out)))"
This works fine and catches situations where an employee appears in
more than one customer/location with a start and/or end time that
overlaps with another schedule item.
However, we've found a number of cases where an employee start/end time
is entirely within the start/end time range of another schedule item
e.g.:
Sked Empl Locn Start End
A 1234 5678 09:00 15:00
B 1234 3456 08:00 17:00
In this case the time range for Sked A is entirely within the range for
Sked B. Sked A flags the conflict with Sked B because the start and end
times are within the range for Sked B. However, Sked B does not flag a
conflict with sked A because Sked A begins and ends entirely within
Sked B.
Can anyone propose a change to the SQL that will detect the conflict in
both cases without tripping false conflicts?
------------------------------
Date: 6 Apr 2001 21:33:47 GMT (Last modified)
From: Perl-Users-Request@ruby.oce.orst.edu (Perl-Users-Digest Admin)
Subject: Digest Administrivia (Last modified: 6 Apr 01)
Message-Id: <null>
Administrivia:
#The Perl-Users Digest is a retransmission of the USENET newsgroup
#comp.lang.perl.misc. For subscription or unsubscription requests, send
#the single line:
#
# subscribe perl-users
#or:
# unsubscribe perl-users
#
#to almanac@ruby.oce.orst.edu.
NOTE: due to the current flood of worm email banging on ruby, the smtp
server on ruby has been shut off until further notice.
To submit articles to comp.lang.perl.announce, send your article to
clpa@perl.com.
#To request back copies (available for a week or so), send your request
#to almanac@ruby.oce.orst.edu with the command "send perl-users x.y",
#where x is the volume number and y is the issue number.
#For other requests pertaining to the digest, send mail to
#perl-users-request@ruby.oce.orst.edu. Do not waste your time or mine
#sending perl questions to the -request address, I don't have time to
#answer them even if I did know the answer.
------------------------------
End of Perl-Users Digest V10 Issue 9804
***************************************
