[nycphp-talk] innodb settings
Max Gribov
max at neuropunks.org
Mon Apr 26 15:30:41 EDT 2010
On 04/26/2010 12:53 PM, Nicholas Hart wrote:
> Hi,
>
> I am troubleshooting problems with slow inserts with innodb. Anyone
> have list of recommended config settings to reduce or eliminate these
> delays?
When you insert in innodb, it will check foreign keys, and like any
insert it will update indexes, so those are probably THE bottlenecks..
What kind of inserts are they? Are you doing INSERT ... SELECT type
stuff? If so, you can try this in my.cnf:
(from:
http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html)
innodb_autoinc_lock_mode=2
binlog_format=row
If you have inserts with LOCK TABLES you can use this to disable that,
which can be dangerous under high load:
innodb_table_locks = false
You can also try this (also from
http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html)
transaction-isolation=READ-COMMITTED
You can also package your inserts into the same statement like:
insert into tablename (field1, field2) values(1,2), (2,3), (4,5);
this makes inserts faster, and you can make that command as long as
max_allowed_packet setting.
You can also use this:
innodb_flush_log_at_trx_commit = 2
(from:
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/)
Also innodb_thread_concurrency should be set to 2 * (num_cpu + num_dsk)
When all else fails, see if you can change the tables in question to
myisam or even move them into something like mongo, which does way more
inserts per second than mysql.
>
> Thanks,
> Nick
>
> On Mon, Apr 26, 2010 at 12:00 PM, <talk-request at lists.nyphp.org
> <mailto:talk-request at lists.nyphp.org>> wrote:
>
> Send talk mailing list submissions to
> talk at lists.nyphp.org <mailto:talk at lists.nyphp.org>
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://lists.nyphp.org/mailman/listinfo/talk
> or, via email, send a message with subject or body 'help' to
> talk-request at lists.nyphp.org <mailto:talk-request at lists.nyphp.org>
>
> You can reach the person managing the list at
> talk-owner at lists.nyphp.org <mailto:talk-owner at lists.nyphp.org>
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of talk digest..."
>
>
> Today's Topics:
>
> 1. Re: MAMP or XAMPP ? (John Campbell)
> 2. Re: MAMP or XAMPP ? (mmw)
> 3. Re: MAMP or XAMPP ? (Mitch Pirtle)
> 4. Re: MAMP or XAMPP ? (Ajai Khattri)
> 5. Re: MAMP or XAMPP ? (mmw)
> 6. Re: MAMP or XAMPP ? (Tim Lieberman)
> 7. SimpleSAMLphp (Rich Bark)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Sun, 25 Apr 2010 13:09:10 -0400
> From: John Campbell <jcampbell1 at gmail.com
> <mailto:jcampbell1 at gmail.com>>
> To: NYPHP Talk <talk at lists.nyphp.org <mailto:talk at lists.nyphp.org>>
> Subject: Re: [nycphp-talk] MAMP or XAMPP ?
> Message-ID:
> <h2l8f0676b41004251009x6af9ba23ofc406840f88523f2 at mail.gmail.com
> <mailto:h2l8f0676b41004251009x6af9ba23ofc406840f88523f2 at mail.gmail.com>>
> Content-Type: text/plain; charset=ISO-8859-1
>
> I think MAMP is a pain in the ass, and prefer to get the stack from
> MacPorts. The default MAMP install leaves you with stuff like a
> different version of PHP for cli vs Apache, and it is a pain to
> install additional modules.
>
>
>
> On Sat, Apr 24, 2010 at 11:58 PM, Ajai Khattri <ajai at bitblit.net
> <mailto:ajai at bitblit.net>> wrote:
> >
> > Which is better ?
> >
> > --
> > Aj.
> >
> > _______________________________________________
> > New York PHP Users Group Community Talk Mailing List
> > http://lists.nyphp.org/mailman/listinfo/talk
> >
> > http://www.nyphp.org/Show-Participation
> >
>
>
> ------------------------------
>
> Message: 2
> Date: Sun, 25 Apr 2010 13:20:14 -0400
> From: "mmw" <mmwaldman at nyc.rr.com <mailto:mmwaldman at nyc.rr.com>>
> To: "'NYPHP Talk'" <talk at lists.nyphp.org
> <mailto:talk at lists.nyphp.org>>
> Subject: Re: [nycphp-talk] MAMP or XAMPP ?
> Message-ID: <2A.13.29484.EC974DB4 at hrndva-omtalb.mail.rr.com
> <mailto:2A.13.29484.EC974DB4 at hrndva-omtalb.mail.rr.com>>
> Content-Type: text/plain; charset="US-ASCII"
>
> I did have trouble installing xampp the first time around. I had
> to modify
> some file. Don't ask me which one.
>
> Last time I installed, it went well.
>
> I use WEBuilder, instead of DreamWeaver or whatever, so with a
> little help
> from the tech folks, I got it all setup to preview php files in my
> editor.
>
> WEBuilder was like $60 or something. I've encountered one minor
> problem
> which I have not reported.
>
> Xampp with WEBuilder cuts down on development time quite a bit.
>
> Michele
>
> > -----Original Message-----
> > From: talk-bounces at lists.nyphp.org
> <mailto:talk-bounces at lists.nyphp.org>
> > [mailto:talk-bounces at lists.nyphp.org
> <mailto:talk-bounces at lists.nyphp.org>] On Behalf Of John Campbell
> > Sent: Sunday, April 25, 2010 1:09 PM
> > To: NYPHP Talk
> > Subject: Re: [nycphp-talk] MAMP or XAMPP ?
> >
> > I think MAMP is a pain in the ass, and prefer to get the stack from
> > MacPorts. The default MAMP install leaves you with stuff like a
> > different version of PHP for cli vs Apache, and it is a pain to
> > install additional modules.
> >
> >
> >
> > On Sat, Apr 24, 2010 at 11:58 PM, Ajai Khattri
> > <ajai at bitblit.net <mailto:ajai at bitblit.net>> wrote:
> > >
> > > Which is better ?
> > >
> > > --
> > > Aj.
> > >
> > > _______________________________________________
> > > New York PHP Users Group Community Talk Mailing List
> > > http://lists.nyphp.org/mailman/listinfo/talk
> > >
> > > http://www.nyphp.org/Show-Participation
> > >
> > _______________________________________________
> > New York PHP Users Group Community Talk Mailing List
> > http://lists.nyphp.org/mailman/listinfo/talk
> >
> > http://www.nyphp.org/Show-Participation
>
>
>
> ------------------------------
>
> Message: 3
> Date: Sun, 25 Apr 2010 13:25:44 -0400
> From: Mitch Pirtle <mitch.pirtle at gmail.com
> <mailto:mitch.pirtle at gmail.com>>
> To: NYPHP Talk <talk at lists.nyphp.org <mailto:talk at lists.nyphp.org>>
> Subject: Re: [nycphp-talk] MAMP or XAMPP ?
> Message-ID:
> <s2h330532b61004251025vf6c12869p221f7cbb2b9a9d8a at mail.gmail.com
> <mailto:s2h330532b61004251025vf6c12869p221f7cbb2b9a9d8a at mail.gmail.com>>
> Content-Type: text/plain; charset=UTF-8
>
> On Sun, Apr 25, 2010 at 1:09 PM, John Campbell
> <jcampbell1 at gmail.com <mailto:jcampbell1 at gmail.com>> wrote:
> > I think MAMP is a pain in the ass, and prefer to get the stack from
> > MacPorts. ? The default MAMP install leaves you with stuff like a
> > different version of PHP for cli vs Apache, and it is a pain to
> > install additional modules.
>
> ++ on this one. I also use rails apps, memcache, and a bunch of other
> non-standard stuff like LightTPD and macports makes use of all these
> things a snap.
>
> -- Mitch
>
>
> ------------------------------
>
> Message: 4
> Date: Sun, 25 Apr 2010 13:46:50 -0400 (EDT)
> From: Ajai Khattri <ajai at bitblit.net <mailto:ajai at bitblit.net>>
> To: NYPHP Talk <talk at lists.nyphp.org <mailto:talk at lists.nyphp.org>>
> Subject: Re: [nycphp-talk] MAMP or XAMPP ?
> Message-ID: <Pine.LNX.4.44.1004251346240.13111-100000 at bitblit.net
> <mailto:Pine.LNX.4.44.1004251346240.13111-100000 at bitblit.net>>
> Content-Type: TEXT/PLAIN; charset=US-ASCII
>
> On Sun, 25 Apr 2010, mmw wrote:
>
> > Huh? One's for mac one's for pcs.
>
> Err, nope:
> http://www.apachefriends.org/en/xampp-macosx.html
>
>
> --
> Aj.
>
>
>
> ------------------------------
>
> Message: 5
> Date: Sun, 25 Apr 2010 13:52:12 -0400
> From: "mmw" <mmwaldman at nyc.rr.com <mailto:mmwaldman at nyc.rr.com>>
> To: "'NYPHP Talk'" <talk at lists.nyphp.org
> <mailto:talk at lists.nyphp.org>>
> Subject: Re: [nycphp-talk] MAMP or XAMPP ?
> Message-ID: <E0.01.29484.B4184DB4 at hrndva-omtalb.mail.rr.com
> <mailto:E0.01.29484.B4184DB4 at hrndva-omtalb.mail.rr.com>>
> Content-Type: text/plain; charset="US-ASCII"
>
> Didn't know xampp made a mac version.
>
> But when I looked up mamp or whatever, it was specifically for mac.
>
> Michele
>
> > -----Original Message-----
> > From: talk-bounces at lists.nyphp.org
> <mailto:talk-bounces at lists.nyphp.org>
> > [mailto:talk-bounces at lists.nyphp.org
> <mailto:talk-bounces at lists.nyphp.org>] On Behalf Of Ajai Khattri
> > Sent: Sunday, April 25, 2010 1:47 PM
> > To: NYPHP Talk
> > Subject: Re: [nycphp-talk] MAMP or XAMPP ?
> >
> > On Sun, 25 Apr 2010, mmw wrote:
> >
> > > Huh? One's for mac one's for pcs.
> >
> > Err, nope:
> > http://www.apachefriends.org/en/xampp-macosx.html
> >
> >
> > --
> > Aj.
> >
> > _______________________________________________
> > New York PHP Users Group Community Talk Mailing List
> > http://lists.nyphp.org/mailman/listinfo/talk
> >
> > http://www.nyphp.org/Show-Participation
>
>
>
> ------------------------------
>
> Message: 6
> Date: Sun, 25 Apr 2010 15:03:42 -0400
> From: Tim Lieberman <tim_lists at o2group.com
> <mailto:tim_lists at o2group.com>>
> To: NYPHP Talk <talk at lists.nyphp.org <mailto:talk at lists.nyphp.org>>
> Subject: Re: [nycphp-talk] MAMP or XAMPP ?
> Message-ID: <E95E4BEF-8E1E-4A14-92F0-2FDE9DC25BF5 at o2group.com
> <mailto:E95E4BEF-8E1E-4A14-92F0-2FDE9DC25BF5 at o2group.com>>
> Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
>
> On Apr 25, 2010, at 1:25 PM, Mitch Pirtle wrote:
>
> > On Sun, Apr 25, 2010 at 1:09 PM, John Campbell
> > <jcampbell1 at gmail.com <mailto:jcampbell1 at gmail.com>> wrote:
> >> I think MAMP is a pain in the ass, and prefer to get the stack from
> >> MacPorts. The default MAMP install leaves you with stuff like a
> >> different version of PHP for cli vs Apache, and it is a pain to
> >> install additional modules.
> >
> > ++ on this one. I also use rails apps, memcache, and a bunch of
> other
> > non-standard stuff like LightTPD and macports makes use of all these
> > things a snap.
>
> If you've got some time to experiment, it's not all that hard to build
> an AMP stack from source on MacOS.
>
> It is more work than using MAMP/XAMMP or MacPorts/Fink, but IMO, the
> flexibility is worth it in the long run.
>
> I try to maintain a set of shell scripts so I can tweak/recompile as
> needed.
>
> I tend to stick everything under some non-standard directory, so I can
> have multiple stacks for testing. For example, if I needed it, I'd
> have no problems setting up 4 stacks for 5.2/5.3 under mod_php/
> fastcgi. This way, I can keep my local environment very close to
> whatever the production environment is for any particular project.
>
> -Tim
>
>
>
> ------------------------------
>
> Message: 7
> Date: Mon, 26 Apr 2010 10:08:31 -0400
> From: "Rich Bark" <rich at 1webguru.com <mailto:rich at 1webguru.com>>
> To: <talk at lists.nyphp.org <mailto:talk at lists.nyphp.org>>
> Subject: [nycphp-talk] SimpleSAMLphp
> Message-ID: <003401cae549$f7b2e470$e718ad50$@com>
> Content-Type: text/plain; charset="us-ascii"
>
> Anyone here have experience with simpleSAMLphp? I cannot get it
> to work.
>
>
>
> Thanks,
>
> Rich
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> <http://lists.nyphp.org/pipermail/talk/attachments/20100426/6cbb0ae8/attachment-0001.html>
>
> ------------------------------
>
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org <mailto:talk at lists.nyphp.org>
> http://lists.nyphp.org/mailman/listinfo/talk
>
> End of talk Digest, Vol 42, Issue 17
> ************************************
>
>
>
>
> --
>
> -Nicholas Hart
> Parts Authority IT
> 516-678-3900 X3242
>
>
> _______________________________________________
> New York PHP Users Group Community Talk Mailing List
> http://lists.nyphp.org/mailman/listinfo/talk
>
> http://www.nyphp.org/Show-Participation
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20100426/c23de234/attachment.html>
More information about the talk
mailing list