next »

[nutballs]

nope.

i never understood why the hell a read on myisam locks tables. thats retarded.

Frankly there should a switch for "i dont give a crap about state or concurrency".

errr except for on an Auto Increment. Forgot about that. (but that might be out of date)

[perkiset]

Because with fopen kind of file access (OS level), you can lock and unlock an entire file and that's it. It's the fastest there is and it's system aware. At the row level lock it's logically based and there's never a file system lock - it's intra-executable cooperative locking. So it takes much longer (because the lock is registered, managed and freed by innoDB code rather than the OS), but allows for locking a an abstract, logical portion of a file.

[Phaëton]

Longish term is to move to mongoDB but I need the issues i'm having fixed like NOW and don't have the time at the moment to dedicate to doing the conversion to MongoDB.

Speaking of that, you know of any good tools that will do all the work for me as far as data migration goes?  The DB has millions of records so part of my hesitation to migrate is figuring out how to move all of that data.

i would do an sql dump in phpmyadmin or write some export to sql  code with fetch_assoc
and then modify:

http://www.ozerov.de/bigdump.php

to stagger the import of the sql into the new db...

i used it to move a 1.45 gb sql file dump and it went off without a hitch.

[KaptainKrayola]

Very cool - thanks Phaëton, I'll give it a look.

[Phaëton]

Ping me if you need some help... im super bored lately and ive got some spare time...

[KaptainKrayola]

Ok so over the weekend I did some code changes and changed the main problem table over to InnoDB.  I moved the text that needed to be indexed to a related table that stored some other information and left it MyISAM.

I'm happy to say that the locking problems are all gone BUT this appears to have spawned a new issue.  Now it seems like the queries are a lot slower.  I have queries stuck on "copying to temp table" for like 500 seconds and such. 

I was reading some optimization articles this morning and put some InnoDB tweaks in place but that just broke the server and the InnoDB engine wouldn't start.

Some info about the DB - it's got around 3 million rows and the total size is about 16GB.  Most of that is due to the fact that I haven't removed the text column from the table that's now InnoDB so you can cut that size about in half. 

The server is a quad core dedicated machine with 8 gb ram.  CentOS (i believe)

So anyone have any suggestions on server settings I can use to optimize this?  I have been doing some EXPLAIN on the queries in question and they use the indexes and keys like they should it just seems like copying to temp table is retarded slow for some reason.

[nutballs]

hmmm. So how are you doing your writes? does that text field get written to alot, or actually i should say, updated alot?

I have a table with about 33,165,686 rows as of right now. Its total size is 2.3gb.

are you sure your database is finished converting? Also, is that the real size of that table. with innodb, you cant go by the file on disk.
use this if your curious:
SELECT table_schema,
table_name,
ROUND(data_length/1024/1024,2) total_size_mb
FROM information_schema.tables
WHERE table_name = 'YourTableNameHere' AND table_schema = 'YourDatabaseNameHere';


but the specific problem of copying to temp table. Why? Why are are you copying to temps? And obviously your copying a large amount for it to take that long...

[KaptainKrayola]

I'm not actively telling it to copy to a temp table it just does it on it's own for the particular query. 

What's crazy about the 'copying to temp table' and being a huge amount of data is that it really shouldn't be.  In some cases it's just the PK (auto increment id) of the table with a maximum of 1000 rows.  Something else must have been at play.

The text field is never updated.  The record is written once and then never touched again (other than setting a status on it once if needed)


So far today it seems those issues have corrected themselves.  It did seem like last night before I had dropped the 4GB worth of data from the original table.  I dropped the column and the table is like 300MB now instead of 4.3GB like it was before.  That seems to have fixed queries to that table EVEN THOUGH i stopped referencing the column yesterday before I dropped it.

I guess the fact that the table was so huge was causing queries referencing anything in that table to be slow.  Perhaps the datafile was all fragmented or something.  I really don't know that much about how MySQL accesses data in the files so it's hard to say.

[perkiset]

I'm a bit ... Erm, relaxed ATM and suffering at the hands of the MOST CLASSLESS CRUISELINE I HAVE EVER EXPIERIENCED, so I can't be trusted. But can you simply to a "explain plan" of your SQL statement and post it? It sounds as though something is dreadfully wrong.

[KaptainKrayola]

you know what, I think that by looking for that query to do the explain i stumbled upon the one last offending query and why.  Before, it was a big problem with a similar query and that one works like a charm now. 

However, I found another query totally not using the fulltext index and for SOME REASON it's running like shit.

Let me get back to you guys on this.

[perkiset]

GICH.

[KaptainKrayola]

I do find it funny that MySQL will let you do a fulltext operation against a table and not use the fulltext index.  Not quite sure how that works but whatever.

[nutballs]

it does a crawl... the index is not required, technically. Of course for it to work even remotely acceptable, it needs it.

[KaptainKrayola]

Looks like a bad join was the issue.  I was doing a left outer join in the query before because it didn't matter if the secondary table had a row or not.  Now, since the fulltext is there it totally matters so i changed it to an inner join and it worked great

the query takes no time at all to execute. 

the only potential issue i see now is the explain says in the extras col:

"Using where; Using temporary; Using filesort "

again, the query is smoking fast now so i don't see it as an issue but if i can avoid it id like to.

[nutballs]

I think with full text, that normal behavior is to have those extras. Temp table is for sorting the results based on your factor number or whatever the fuck it's called. The file sort is also related to that temp table to select the results from that

Are you doing a limit? Like select 10 of the possible matches? If so, that's why I think.