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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
