|Database queries keep on locking|
Need Help! Succeeding queries will lock.
| 6:21 am on Nov 13, 2008 (gmt 0)|
Nice day to all,
I have database a tables tbmsg and tb_profileview which now contains 15 million rows and growing - I need to write and retrieve data in this table simultaneously. The problem is that upon execution of the query[read/write] the succeeding other queries will locked which causes mysite.com to load very slow. I'm using MySQL database with MyISAM as an storage engine used.
What is the best way to get rid of it?
| 4:30 pm on Nov 13, 2008 (gmt 0)|
Is your table properly indexed? Are your queries optimized? Does your DB server have sufficient CPU/Memory resources? Are you on a shared or dedicated server?
| 1:48 am on Nov 15, 2008 (gmt 0)|
This is the table structure for table `tb_profileview`
CREATE TABLE `tb_profileview` (
`userID` varchar(32) NOT NULL,
`viewer` varchar(32) NOT NULL,
`date` varchar(10) NOT NULL,
`impression` varchar(32) NOT NULL,
PRIMARY KEY (`userID`,`viewer`),
KEY `impression` (`impression`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The query we use is optimize, this is the CPU/Memory resource: Dual quad core (8 CPU's)Intel(R) Xeon(R)CPU E5310 @ 1.60GHz 8G ram and then we're on a dedicated server
| 9:26 pm on Nov 18, 2008 (gmt 0)|
Trying to read and write at the same time is tough.
Have a look at delayed inserts:
The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete. This is a common situation when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete.
Caching, either to files or to another DB can help a lot in situations like this (they MySQL query cache probably won't help much though as it's flushed whenever you modify an entry).
What data is being written each time?
What data is being read each time?
If you can isolate the changing data into it's own table or db that might help.
Also, your userID should probably be an int(10), not a varchar(32).
Probably the same thing for viewer, which I assume is really a foreign key.
What is impression? Does it need to be a KEY?
Do you really need a primary key that spans two fields? Perhaps it would be better to have one unique id be the primary key (this is what I usually do for every table).
| 9:54 pm on Nov 18, 2008 (gmt 0)|
In addition to physics' suggestions about the UserID & viewer fields, if you're searching by dates, the "date" field would probably be more efficient as DATETIME instead of VARCHAR. And I'll add my usual rant about not naming fields with reserved words like "date."
And, as physics mentioned, knowing what data is being written/read will further help us understand the specifics of the problem.
| 4:43 am on Dec 5, 2008 (gmt 0)|
Data that is being written each time...in-order is the `userID` where it is the ID of the profile being viewed, next is `viewer` where it is the userID of the one who viewed the profile, then `impression` is an integer value where it is incremented each time a `viewer` views a particular profile, and lastly `date` where it is the date the profile is being viewed.
Data that is being read each time...`userID` is used for query condition purposes, `viewer` and `date` are selected fields to be displayed.
You're right `viewer` is a foreign key
`userID` is of VARCHAR type 'cause it will store alphabet and number values.