Tables getting locked even for 3-4 users

[Deepak Agarwal-2]

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz
avgqu-sz   await  svctm  %util
sda               0.00   142.00    0.00   57.00     0.00  1592.00    27.93
    1.31   22.93   3.51  20.00

It normally comes when I hit anything.

On Fri, Jun 22, 2012 at 2:42 AM, Deepak Agarwal <[hidden email]>wrote:

> Ok. Thanks. I will do that and paste the results here tomorrow.
>
>
> On Fri, Jun 22, 2012 at 2:31 AM, Mike <[hidden email]> wrote:
>
>> If you run "iostat -x 5", for about 1 minute, what is the "%util" value?
>>
>> On Thu, Jun 21, 2012 at 1:53 PM, Deepak Agarwal <[hidden email]
>> >wrote:
>>
>> > Will run it again. Had done it before getting load on mysql.
>> >
>> > On Fri, Jun 22, 2012 at 2:22 AM, Deepak Agarwal <[hidden email]
>> > >wrote:
>> >
>> > > Yes. All set.
>> > >
>> > >
>> > > On Fri, Jun 22, 2012 at 2:21 AM, Mike <[hidden email]> wrote:
>> > >
>> > >> Have you tried the mysql tuning primer script?
>> > >>
>> > >> https://launchpad.net/mysql-tuning-primer
>> > >>
>> > >> Download, execute, and double any recommendations.
>> > >>
>> > >>
>> > >> On Thu, Jun 21, 2012 at 1:06 PM, Deepak Agarwal <
>> [hidden email]
>> > >> >wrote:
>> > >>
>> > >> > Latest mysql with timestamp truncation problem.
>> > >> >
>> > >> > On Fri, Jun 22, 2012 at 1:31 AM, Deepak Agarwal <
>> [hidden email]
>> > >> > >wrote:
>> > >> >
>> > >> > > mysql latest version.
>> > >> > >
>> > >> > >
>> > >> > > On Fri, Jun 22, 2012 at 1:18 AM, Mike <[hidden email]>
>> wrote:
>> > >> > >
>> > >> > >> Postgresql/mysql?
>> > >> > >>
>> > >> > >> On Thu, Jun 21, 2012 at 12:04 PM, Deepak Agarwal <
>> > >> [hidden email]
>> > >> > >> >wrote:
>> > >> > >>
>> > >> > >> > I actually did that. But, now I am facing issue while quickly
>> > >> > >> > removing/adding items from/to shopping cart. May be I need to
>> > look
>> > >> > >> database
>> > >> > >> > closely.
>> > >> > >> >
>> > >> > >> > On Thu, Jun 21, 2012 at 11:32 PM, Mike <[hidden email]>
>> > >> wrote:
>> > >> > >> >
>> > >> > >> > > Here is what I would do:
>> > >> > >> > >
>> > >> > >> > > Update framework/webapp/config/serverstats.properties
>> > >> > >> > >
>> > >> > >> > > stats.persist.visit=false
>> > >> > >> > > stats.persist.visitor=false
>> > >> > >> > >
>> > >> > >> > > I have ALL of the 'stats' set to 'false'.
>> > >> > >> > >
>> > >> > >> > > Restart ofbiz, and blow away the contents of server_hit and
>> > >> > optionally
>> > >> > >> > > visit:
>> > >> > >> > >
>> > >> > >> > > delete from server_hit;
>> > >> > >> > > delete from visit;
>> > >> > >> > >
>> > >> > >> > > The 'server_hit' is probably your problem.  And, if needed,
>> > >> rebuild
>> > >> > >> the
>> > >> > >> > > indexes of the database.
>> > >> > >> > >
>> > >> > >> > > There is probably another way, or maybe even a service
>> (unknown
>> > >> to
>> > >> > me)
>> > >> > >> > that
>> > >> > >> > > may keep these tables tightly trimmed.
>> > >> > >> > >
>> > >> > >> > > On Thu, Jun 21, 2012 at 10:48 AM, Deepak Agarwal <
>> > >> > >> [hidden email]
>> > >> > >> > > >wrote:
>> > >> > >> > >
>> > >> > >> > > > visit: 7400
>> > >> > >> > > > server_hit: 94000
>> > >> > >> > > > communication_event: 51
>> > >> > >> > > >
>> > >> > >> > > > On Thu, Jun 21, 2012 at 10:44 PM, Mike <
>> [hidden email]
>> > >
>> > >> > >> wrote:
>> > >> > >> > > >
>> > >> > >> > > > > Take a look at visit, server_hit, and
>> communication_event
>> > >> > tables.
>> > >> > >> > > > >
>> > >> > >> > > > > select count(*) from visit;
>> > >> > >> > > > > select count(*) from server_hit;
>> > >> > >> > > > > select count(*) from communication_event;
>> > >> > >> > > > >
>> > >> > >> > > > > What do yo get?
>> > >> > >> > > > >
>> > >> > >> > > > > On Thu, Jun 21, 2012 at 9:02 AM, Deepak Agarwal <
>> > >> > >> > [hidden email]
>> > >> > >> > > > > >wrote:
>> > >> > >> > > > >
>> > >> > >> > > > > > Alright. I will try this. However, Basically database
>> > >> queries
>> > >> > >> are
>> > >> > >> > > > timing
>> > >> > >> > > > > > out. I checked from 'Show processlist' command and I
>> can
>> > >> see
>> > >> > the
>> > >> > >> > > > queries
>> > >> > >> > > > > > are stuck.
>> > >> > >> > > > > >
>> > >> > >> > > > > > On Thu, Jun 21, 2012 at 9:22 PM, Mike <
>> > >> [hidden email]>
>> > >> > >> > wrote:
>> > >> > >> > > > > >
>> > >> > >> > > > > > > Sounds like classic garbage collection.  Java
>> freezes
>> > >> while
>> > >> > it
>> > >> > >> > > sifts
>> > >> > >> > > > > > > through memory looking for stuff to delete.  The
>> larger
>> > >> the
>> > >> > >> > memory,
>> > >> > >> > > > the
>> > >> > >> > > > > > > longer it takes.
>> > >> > >> > > > > > >
>> > >> > >> > > > > > > -Xms2048M -Xmx2048M -server -Xincgc
>> > >> > >> > > > > > >
>> > >> > >> > > > > > > Try the above.  The -Xincgc will continuously GC on
>> a
>> > >> > >> separate,
>> > >> > >> > > slow
>> > >> > >> > > > > > > running thread.  Also, run a separate instance of
>> OFBiz
>> > >> for
>> > >> > >> > > > > > > frontend/back-end, connected to same DB.
>> > >> > >> > > > > > >
>> > >> > >> > > > > > > On Thu, Jun 21, 2012 at 8:47 AM, Deepak Agarwal <
>> > >> > >> > > > [hidden email]
>> > >> > >> > > > > > > >wrote:
>> > >> > >> > > > > > >
>> > >> > >> > > > > > > > Memory and CPU remain good. memory hardly reaches
>> 1
>> > GB
>> > >> and
>> > >> > >> cpu
>> > >> > >> > > few
>> > >> > >> > > > > > > > percentage.
>> > >> > >> > > > > > > >
>> > >> > >> > > > > > > > On Thu, Jun 21, 2012 at 9:14 PM, Mike <
>> > >> > [hidden email]
>> > >> > >> >
>> > >> > >> > > > wrote:
>> > >> > >> > > > > > > >
>> > >> > >> > > > > > > > > What does the "Memory Total" line say on the
>> > >> > >> "WebToosl/Cache
>> > >> > >> > > > > > > Maintenance"
>> > >> > >> > > > > > > > > menu?
>> > >> > >> > > > > > > > >
>> > >> > >> > > > > > > > > On Thu, Jun 21, 2012 at 8:38 AM, Deepak Agarwal
>> <
>> > >> > >> > > > > > [hidden email]
>> > >> > >> > > > > > > > > >wrote:
>> > >> > >> > > > > > > > >
>> > >> > >> > > > > > > > > > -Xms2048M -Xmx2048M -XX:MaxPermSize=1024m
>> -server
>> > >> > >> > > > > > > > > >
>> > >> > >> > > > > > > > > > On Thu, Jun 21, 2012 at 9:03 PM, Mike <
>> > >> > >> > [hidden email]>
>> > >> > >> > > > > > wrote:
>> > >> > >> > > > > > > > > >
>> > >> > >> > > > > > > > > > > What are the java memory settings for
>> starting
>> > >> > OFBiz?
>> > >> > >> > >  Mine:
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > > > JAVA_VMOPTIONS="-Xms1280M -Xmx1524M -server
>> > >> -Xincgc"
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > > > On Thu, Jun 21, 2012 at 8:14 AM, Deepak
>> > Agarwal <
>> > >> > >> > > > > > > > [hidden email]
>> > >> > >> > > > > > > > > > > >wrote:
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > > > > We are expereicing frequent table locks
>> for
>> > >> > Shipping
>> > >> > >> > > table
>> > >> > >> > > > ,
>> > >> > >> > > > > > > visit
>> > >> > >> > > > > > > > > and
>> > >> > >> > > > > > > > > > > > server_hit table. Even with 3-4 users
>> working
>> > >> we
>> > >> > are
>> > >> > >> > > > getting
>> > >> > >> > > > > > > > gateway
>> > >> > >> > > > > > > > > > > > timeout because queries doesn't return .
>> At
>> > db
>> > >> > >> level it
>> > >> > >> > > > shows
>> > >> > >> > > > > > > > locked
>> > >> > >> > > > > > > > > > the
>> > >> > >> > > > > > > > > > > > table (and takes 40-50 sec to execute the
>> > >> update
>> > >> > >> > query).
>> > >> > >> > > > > > > > > > > > Servers are multi core, 8GB RAM, 10k RPM
>> > >> > harddrive.
>> > >> > >> I
>> > >> > >> > > guess
>> > >> > >> > > > > it
>> > >> > >> > > > > > > is a
>> > >> > >> > > > > > > > > > good
>> > >> > >> > > > > > > > > > > > configuration. All the optimization has
>> been
>> > >> done
>> > >> > >> (like
>> > >> > >> > > > table
>> > >> > >> > > > > > > cache
>> > >> > >> > > > > > > > > > > > increase, cache disable for ofbiz...etc).
>> > >> > >> > > > > > > > > > > >
>> > >> > >> > > > > > > > > > > > Any suggestions? Any body having the same
>> > >> problem
>> > >> > ?
>> > >> > >> > > > > > > > > > > >
>> > >> > >> > > > > > > > > > > > --
>> > >> > >> > > > > > > > > > > > Thanks,
>> > >> > >> > > > > > > > > > > > Deepak Agarwal,
>> > >> > >> > > > > > > > > > > >
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > > > --
>> > >> > >> > > > > > > > > > > Thanks,
>> > >> > >> > > > > > > > > > > Deepak Agarwal,
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > > >
>> > >> > >> > > > > > > > > >
>> > >> > >> > > > > > > > >
>> > >> > >> > > > > > > > > --
>> > >> > >> > > > > > > > > Thanks,
>> > >> > >> > > > > > > > > Deepak Agarwal,
>> > >> > >> > > > > > > > >
>> > >> > >> > > > > > > > > Paxcel Technologies Pvt Ltd.
>> > >> > >> > > > > > > > > Hartron Complex, Sector 18, Gurgaon, India.
>> > >> > >> > > > > > > > > E-Mail: [hidden email]
>> > >> > >> > > > > > > > > Mobile: [phone]
>> > >> > >> > > > > > > > >
>> > >> > >> > > > > > > > >
>> > >> > >> > > > > > > >
>> > >> > >> > > > > > >
>> > >> > >> > > > > > > --
>> > >> > >> > > > > > > Thanks,
>> > >> > >> > > > > > > Deepak Agarwal,
>> > >> > >> > > > > > >
>> > >> > >> > > > > > > Paxcel Technologies Pvt Ltd.
>> > >> > >> > > > > > > Hartron Complex, Sector 18, Gurgaon, India.
>> > >> > >> > > > > > > E-Mail: [hidden email]
>> > >> > >> > > > > > > Mobile: [phone]
>> > >> > >> > > > > > >
>> > >> > >> > > > > > >
>> > >> > >> > > > > >
>> > >> > >> > > > >
>> > >> > >> > > > > --
>> > >> > >> > > > > Thanks,
>> > >> > >> > > > > Deepak Agarwal,
>> > >> > >> > > > >
>> > >> > >> > > > > Paxcel Technologies Pvt Ltd.
>> > >> > >> > > > > Hartron Complex, Sector 18, Gurgaon, India.
>> > >> > >> > > > > E-Mail: [hidden email]
>> > >> > >> > > > > Mobile: [phone]
>> > >> > >> > > > >
>> > >> > >> > > > >
>> > >> > >> > > >
>> > >> > >> > >
>> > >> > >> > > --
>> > >> > >> > > Thanks,
>> > >> > >> > > Deepak Agarwal,
>> > >> > >> > >
>> > >> > >> > > Paxcel Technologies Pvt Ltd.
>> > >> > >> > > Hartron Complex, Sector 18, Gurgaon, India.
>> > >> > >> > > E-Mail: [hidden email]
>> > >> > >> > > Mobile: [phone]
>> > >> > >> > >
>> > >> > >> > >
>> > >> > >> >
>> > >> > >>
>> > >> > >> --
>> > >> > >> Thanks,
>> > >> > >> Deepak Agarwal,
>> > >> > >>
>> > >> > >> Paxcel Technologies Pvt Ltd.
>> > >> > >> Hartron Complex, Sector 18, Gurgaon, India.
>> > >> > >> E-Mail: [hidden email]
>> > >> > >> Mobile: [phone]
>> > >> > >>
>> > >> > >>
>> > >> >
>> > >> >
>> > >> > --
>> > >> > Thanks,
>> > >> > Deepak Agarwal,
>> > >> >
>> > >> > Paxcel Technologies Pvt Ltd.
>> > >> > Hartron Complex, Sector 18, Gurgaon, India.
>> > >> > E-Mail: [hidden email]
>> > >> > Mobile: [phone]
>> > >> >
>> > >>
>> > >> --
>> > >> Thanks,
>> > >> Deepak Agarwal,
>> > >>
>> > >> Paxcel Technologies Pvt Ltd.
>> > >> Hartron Complex, Sector 18, Gurgaon, India.
>> > >> E-Mail: [hidden email]
>> > >> Mobile: [phone]
>> > >>
>> > >>
>> >
>> >
>> > --
>> > Thanks,
>> > Deepak Agarwal,
>> >
>> > Paxcel Technologies Pvt Ltd.
>> > Hartron Complex, Sector 18, Gurgaon, India.
>> > E-Mail: [hidden email]
>> > Mobile: [phone]
>> >
>>
>> --
>> Thanks,
>> Deepak Agarwal,
>>
>> Paxcel Technologies Pvt Ltd.
>> Hartron Complex, Sector 18, Gurgaon, India.
>> E-Mail: [hidden email]
>> Mobile: [phone]
>>
>>

--
Thanks,
Deepak Agarwal,

Paxcel Technologies Pvt Ltd.
Hartron Complex, Sector 18, Gurgaon, India.
E-Mail: [hidden email]
Mobile: [phone]