locked tables?

[acidglitter]

on one of my sites the tables keep getting locked. does anyone know why or how this happens??

[fenway]

Any number of reasons... innodb or myisam?

[mushroom]

If a table gets corrupted it may go into a locked state.

 

Some back up programs will lock tables while they do their work.

 

and ??

[acidglitter]

all of my tables are myisam

[fenway]

Are you the only one working with these tables? Do you ever issue explicit LOCK statements? Is maintenance happening concurrently on the production DB?

[acidglitter]

I'm the only one, no I've never even used a lock statement, and I'm pretty sure there isn't any maintenance.

[fenway]

MyISAM locks don't come from that many places...

[acidglitter]

I just asked my server, but I really don't know how they would be getting locked. The only thing I could think of that could be a problem is that SELECT queries are executed super often and I don't have anything like indexes.

[acidglitter]

i just closed down my site for a couple hours to remake one of the tables. i put the fields selected the most often in one table and more specific things in another table. i was hoping that might help if theres too much stress on the tables. then i put the site back online and after pretty much 3 people viewed the home page the whole site stopped loading all over again! i don't know why this is happening at all. :(

[fenway]

The only thing I could think of that could be a problem is that SELECT queries are executed super often and I don't have anything like indexes.

What do you mean you don't have indexes? WHY NOT?????

[acidglitter]

because i don't understand them. but i will try looking up tutorials and stuff for them today. my server is saying that the tables on my site are locked because of the way the queries are written. would anyone be able to help me rewrite my queries? or would it probably fix everything if my table had indexes?

[luca200]

would anyone be able to help me rewrite my queries?

It's hard to say  knowing anything about your tables and your queries...

[fenway]

because i don't understand them. but i will try looking up tutorials and stuff for them today. my server is saying that the tables on my site are locked because of the way the queries are written. would anyone be able to help me rewrite my queries? or would it probably fix everything if my table had indexes?

You're doing a full table scan for EVERY query, so of course your tables are locked all of the time.  There are many good sites linked in the stickes on thsi forum.  Nothing fixes everything.

[acidglitter]

well i went through my queries and realized that this was being done a lot..

DATEDIFF(DATE(vip), CURDATE())

on one of the pages it actually happened 3 times in one query. so i made a page that still uses that, but then gets the actual days left from that and updates a new field called daysleft (with an INT type). that page will only execute 4 times a day (by a cronjob) to keep it up to date. then i put an index on daysleft, and now the rest of my queries are using that field instead of that code above. so i'm HOPING that this will fix my problem..!! does anyone have any suggestions? the rest of my queries are pretty simple so thats the only thing that seems like it could be causing the tables to lock.

[acidglitter]

okay once my site got 7 visitors at once the whole thing just stopped loading again!!! UGH i don't know what else to do.

[luca200]

It's hard to say  knowing anything about your tables and your queries...

looks like it's very hard to understand..... 

[acidglitter]

i'm not really sure which pages are causing the problems, or if its all of them combined, so these are just queries from two pages i think are probably viewed the most..

 

SELECT friendid, picture, name, daysleft
FROM members WHERE daysleft > 0
ORDER BY daysleft DESC

 

SELECT friendid, name, picture, points,
daysleft as 'vip'
FROM members
WHERE friendid NOT IN
	(SELECT theirs FROM added WHERE yours='$friendid')
ORDER BY (daysleft > 0) DESC, points DESC
LIMIT $limit

 

SELECT points,addedtoday,
(SELECT COUNT('yours') FROM added WHERE theirs='$friendid') AS 'requests'
FROM members_detailed WHERE friendid='$friendid'

 

 

[fenway]

UGH i don't know what else to do.

We can help you deal with one query at a time... like for instance, why is VIP not a DATE column to begin with? That's the slow part... and having a cron job isn't the answer.

 

It's unlikely that's the only issue... turn on the slow-query-log and you'll see..

[acidglitter]

UGH i don't know what else to do.

We can help you deal with one query at a time... like for instance, why is VIP not a DATE column to begin with? That's the slow part... and having a cron job isn't the answer.

 

It's unlikely that's the only issue... turn on the slow-query-log and you'll see..

 

 

vip is a date column. what i did was try to get how many days from now until the saved date and turn that into just a number. i've talked to my server before and there's no way i could get a slow-query-log to work. i'm hoping there are other ways to find the problem.. though it does seem like my site is down less often since i added the indexes

[fenway]

Well, let's see the EXPLAIN output of some of your queries.

[acidglitter]

okay, for the same 3 queries i put above, in the same order, this is what explain says...

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE members range daysleft daysleft 4 NULL 142 Using where

 

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY members ALL NULL NULL NULL NULL 49561 Using where; Using filesort

2 DEPENDENT SUBQUERY added ALL NULL NULL NULL NULL 2612 Using where

 

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY members_detailed const PRIMARY PRIMARY 8 const 1

2 SUBQUERY added ALL NULL NULL NULL NULL 2616 Using where

[fenway]

The first query is good -- you see how there's no filesort, and the key isn't NULL?

 

The second query is bad for two reasons: first, there's no index on the members (table scan = ALL)... second, you're using a dependent subquery, which si executed once for EVERY matching row of members -- or ~2600 times!  Could you explain what you're trying to query?

 

For the 3rd one you still have no index usage -- add an index on friendID for the members_detailed table, and add a composite index for ( theirs, yours ) to added.

 

Also, your queries would really benefit from table aliases and column prefixes.

[acidglitter]

first thanks for your replies. your help has been very useful!

 

and then, i added an index to yours and theirs on added, so the 2nd and 3rd queries now look like..

 

SELECT friendid, name, picture, points,
daysleft as 'vip'
FROM members
WHERE friendid NOT IN
	(SELECT theirs FROM added WHERE yours='$friendid')
ORDER BY (daysleft > 0) DESC, points DESC
LIMIT $limit

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY members ALL NULL NULL NULL NULL 50800 Using where; Using filesort

2 DEPENDENT SUBQUERY added ref yours,theirs yours 4 const 3 Using where

 

SELECT points,addedtoday,
(SELECT COUNT('yours') FROM added WHERE theirs='$friendid') AS 'requests'
FROM members_detailed WHERE friendid='$friendid'

 

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY members_detailed const PRIMARY PRIMARY 8 const 1

2 SUBQUERY added ref theirs theirs 4 const 68 Using where; Using index

 

 

so is the 3rd query okay now? i'm not sure what to fix for the 2nd query because friendid is a primary key. the 2nd query is to select members you haven't added yet so you can add them.

[fenway]

Yes, the 3rd query is now fine.

 

The 2nd is a mess -- I asked for the explanation of the desired output of the query.  What are you trying to achieve? NOT IN is terrible for index usage.

[acidglitter]

the subquery is getting the friend ids of everyone you've added so far (from the added table), the main query is getting friend ids of people you haven't yet added so you can add them.