SEO

Hi,

Generally, in data modelling there are some independent table, user related
tables, master tables and one main table which is related with most of the
tables. Almost in all the queries main table is involved to fetch the `id`.
In this way main table is used maximum. some times the main table gets
locked due to the other query. When the hits on database server increases
the locking time will increase.

Is there any way to reduce the locking time of main table.
for eg main_dup can be created. To reflect all the changes from main table
to main_dup trigger can be used. So main_dup can be used to reduce the
locking time of table.

The above is my idea. Is there any other way to reduce the locking period.

--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapatikc@gmail.com

what is the reason for creating main_dup. If your thinking of taking a
backup of all the changes from main table, then the trigger also will have
to wait, till the locks on the main table are released.

This trigger would add another feather to the lock/load on the machine.

regards
anandkl


On 5/13/08, Krishna Chandra Prajapati <prajapatikc@gmail.com> wrote:
>
> Hi,
>
> Generally, in data modelling there are some independent table, user
> related
> tables, master tables and one main table which is related with most of the
> tables. Almost in all the queries main table is involved to fetch the
> `id`.
> In this way main table is used maximum. some times the main table gets
> locked due to the other query. When the hits on database server increases
> the locking time will increase.
>
> Is there any way to reduce the locking time of main table.
> for eg main_dup can be created. To reflect all the changes from main table
> to main_dup trigger can be used. So main_dup can be used to reduce the
> locking time of table.
>
> The above is my idea. Is there any other way to reduce the locking period.
>
> --
> Krishna Chandra Prajapati
> MySQL DBA,
> Ed Ventures e-Learning Pvt.Ltd.
> 1-8-303/48/15, Sindhi Colony
> P.G.Road, Secunderabad.
> Pin Code: 500003
> Office Number: 040-66489771
> Mob: 9912924044
> URL: ed-ventures-online.com
> Email-id: prajapatikc@gmail.com
>

If you use InnoDB you should not have a problem as it used row-level
locking and isolated transitions.

Other than that you can split your tables into smaller ones using either
partitioning or the federated engine...

Ben




Krishna Chandra Prajapati wrote:
> Hi,
>
> Generally, in data modelling there are some independent table, user related
> tables, master tables and one main table which is related with most of the
> tables. Almost in all the queries main table is involved to fetch the `id`.
> In this way main table is used maximum. some times the main table gets
> locked due to the other query. When the hits on database server increases
> the locking time will increase.
>
> Is there any way to reduce the locking time of main table.
> for eg main_dup can be created. To reflect all the changes from main table
> to main_dup trigger can be used. So main_dup can be used to reduce the
> locking time of table.
>
> The above is my idea. Is there any other way to reduce the locking period.
>

Hi,

MyISAM is being used on production server. It applies table level locking.
From mytop view, I see that the table gets locked very frequently for 5 to
10 seconds. Reports are generated everyday. so it scans billions of data (
1years data). Changing to innodb will be doing soon and optimising queries
also. What else can be the solution for time being.

Thanks,
Krishna Chandra Prajapati

On Tue, May 13, 2008 at 1:14 PM, Ben Clewett <ben@clewett.org.uk> wrote:

> If you use InnoDB you should not have a problem as it used row-level
> locking and isolated transitions.
>
> Other than that you can split your tables into smaller ones using either
> partitioning or the federated engine...
>
> Ben
>
>
>
>
>
> Krishna Chandra Prajapati wrote:
>
> > Hi,
> >
> > Generally, in data modelling there are some independent table, user
> > related
> > tables, master tables and one main table which is related with most of
> > the
> > tables. Almost in all the queries main table is involved to fetch the
> > `id`.
> > In this way main table is used maximum. some times the main table gets
> > locked due to the other query. When the hits on database server
> > increases
> > the locking time will increase.
> >
> > Is there any way to reduce the locking time of main table.
> > for eg main_dup can be created. To reflect all the changes from main
> > table
> > to main_dup trigger can be used. So main_dup can be used to reduce the
> > locking time of table.
> >
> > The above is my idea. Is there any other way to reduce the locking
> > period.
> >
> >


--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapatikc@gmail.com

Hi,

I am looking for a solution to reduce the table locks as mytop shows that
table gets locked very frequently. During report generation.

Thanks,
Prajapati

On Tue, May 13, 2008 at 1:10 PM, Ananda Kumar <anandkl@gmail.com> wrote:

> what is the reason for creating main_dup. If your thinking of taking a
> backup of all the changes from main table, then the trigger also will have
> to wait, till the locks on the main table are released.
>
> This trigger would add another feather to the lock/load on the machine.
>
> regards
> anandkl
>
>
> On 5/13/08, Krishna Chandra Prajapati <prajapatikc@gmail.com> wrote:
> >
> > Hi,
> >
> > Generally, in data modelling there are some independent table, user
> > related
> > tables, master tables and one main table which is related with most of
> > the
> > tables. Almost in all the queries main table is involved to fetch the
> > `id`.
> > In this way main table is used maximum. some times the main table gets
> > locked due to the other query. When the hits on database server
> > increases
> > the locking time will increase.
> >
> > Is there any way to reduce the locking time of main table.
> > for eg main_dup can be created. To reflect all the changes from main
> > table
> > to main_dup trigger can be used. So main_dup can be used to reduce the
> > locking time of table.
> >
> > The above is my idea. Is there any other way to reduce the locking
> > period.
> >
> > --
> > Krishna Chandra Prajapati
> > MySQL DBA,
> > Ed Ventures e-Learning Pvt.Ltd.
> > 1-8-303/48/15, Sindhi Colony
> > P.G.Road, Secunderabad.
> > Pin Code: 500003
> > Office Number: 040-66489771
> > Mob: 9912924044
> > URL: ed-ventures-online.com
> > Email-id: prajapatikc@gmail.com
> >
>
>


--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapatikc@gmail.com

Table level locking is inherent to MyIsam.

Look into partitioning, as this breaks table into two or more other
tables which will lock separately.

Or use InnoDB:

ALTER TABLE ... SET ENGINE=InnoDB; (I think)

Ben


Krishna Chandra Prajapati wrote:
> Hi,
>
> I am looking for a solution to reduce the table locks as mytop shows that
> table gets locked very frequently. During report generation.
>
> Thanks,
> Prajapati
>
> On Tue, May 13, 2008 at 1:10 PM, Ananda Kumar <anandkl@gmail.com> wrote:
>
>> what is the reason for creating main_dup. If your thinking of taking a
>> backup of all the changes from main table, then the trigger also will have
>> to wait, till the locks on the main table are released.
>>
>> This trigger would add another feather to the lock/load on the machine.
>>
>> regards
>> anandkl
>>
>>
>> On 5/13/08, Krishna Chandra Prajapati <prajapatikc@gmail.com> wrote:
>>> Hi,
>>>
>>> Generally, in data modelling there are some independent table, user
>>> related
>>> tables, master tables and one main table which is related with most of
>>> the
>>> tables. Almost in all the queries main table is involved to fetch the
>>> `id`.
>>> In this way main table is used maximum. some times the main table gets
>>> locked due to the other query. When the hits on database server
>>> increases
>>> the locking time will increase.
>>>
>>> Is there any way to reduce the locking time of main table.
>>> for eg main_dup can be created. To reflect all the changes from main
>>> table
>>> to main_dup trigger can be used. So main_dup can be used to reduce the
>>> locking time of table.
>>>
>>> The above is my idea. Is there any other way to reduce the locking
>>> period.
>>>
>>> --
>>> Krishna Chandra Prajapati
>>> MySQL DBA,
>>> Ed Ventures e-Learning Pvt.Ltd.
>>> 1-8-303/48/15, Sindhi Colony
>>> P.G.Road, Secunderabad.
>>> Pin Code: 500003
>>> Office Number: 040-66489771
>>> Mob: 9912924044
>>> URL: ed-ventures-online.com
>>> Email-id: prajapatikc@gmail.com
>>>
>>
>
>

Thanks a lot

On Tue, May 13, 2008 at 4:30 PM, Ben Clewett <ben@clewett.org.uk> wrote:

> Table level locking is inherent to MyIsam.
>
> Look into partitioning, as this breaks table into two or more other tables
> which will lock separately.
>
> Or use InnoDB:
>
> ALTER TABLE ... SET ENGINE=InnoDB; (I think)
>
> Ben
>
>
> Krishna Chandra Prajapati wrote:
>
> > Hi,
> >
> > I am looking for a solution to reduce the table locks as mytop shows
> > that
> > table gets locked very frequently. During report generation.
> >
> > Thanks,
> > Prajapati
> >
> > On Tue, May 13, 2008 at 1:10 PM, Ananda Kumar <anandkl@gmail.com> wrote:
> >
> > what is the reason for creating main_dup. If your thinking of taking a
> > > backup of all the changes from main table, then the trigger also will
> > > have
> > > to wait, till the locks on the main table are released.
> > >
> > > This trigger would add another feather to the lock/load on the
> > > machine.
> > >
> > > regards
> > > anandkl
> > >
> > >
> > > On 5/13/08, Krishna Chandra Prajapati <prajapatikc@gmail.com> wrote:
> > >
> > > > Hi,
> > > >
> > > > Generally, in data modelling there are some independent table, user
> > > > related
> > > > tables, master tables and one main table which is related with most
> > > > of
> > > > the
> > > > tables. Almost in all the queries main table is involved to fetch
> > > > the
> > > > `id`.
> > > > In this way main table is used maximum. some times the main table
> > > > gets
> > > > locked due to the other query. When the hits on database server
> > > > increases
> > > > the locking time will increase.
> > > >
> > > > Is there any way to reduce the locking time of main table.
> > > > for eg main_dup can be created. To reflect all the changes from main
> > > > table
> > > > to main_dup trigger can be used. So main_dup can be used to reduce
> > > > the
> > > > locking time of table.
> > > >
> > > > The above is my idea. Is there any other way to reduce the locking
> > > > period.
> > > >
> > > > --
> > > > Krishna Chandra Prajapati
> > > > MySQL DBA,
> > > > Ed Ventures e-Learning Pvt.Ltd.
> > > > 1-8-303/48/15, Sindhi Colony
> > > > P.G.Road, Secunderabad.
> > > > Pin Code: 500003
> > > > Office Number: 040-66489771
> > > > Mob: 9912924044
> > > > URL: ed-ventures-online.com
> > > > Email-id: prajapatikc@gmail.com
> > > >
> > > >
> > >
> >
> >


--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: prajapatikc@gmail.com