Slow edit on big MyISAM table

[ASTRAPI]

Hello

On my forum i have 1.400.000 posts and increase daily by 5.000 new posts and i want a solution as when i edit any of these is very slow as the table is big.

I was thinking to convert the table to Innodb but i am not sure if this is the best solution for the future also....

The read of the table is very fast and the problem is only the edit.

The server is powerfull with 16gb ram and 2x quad xeon.

Thank you

[mat206]

Hello

On my forum i have 1.400.000 posts and increase daily by 5.000 new posts and i want a solution as when i edit any of these is very slow as the table is big.

I was thinking to convert the table to Innodb but i am not sure if this is the best solution for the future also....

The read of the table is very fast and the problem is only the edit.

The server is powerfull with 16gb ram and 2x quad xeon.

Thank you

Convert to InnoDB, use Sphinx, and you won't regret it.. seriously. I'm getting 1300-1500 users in any 15 minute span and my server would probably choke and die if it weren't for InnoDB.. oh wait, it did.. when I was still using MyISAM.

MyISAM does full table locks on updates.. most of the problems come from the session table when you start scaling up. But posts is pretty big as well..

[ASTRAPI]

Thanks for your help :)

Do you recommend me to convert only this big table to Innodb or i must convert also any other big table?

Also i am using sphinx already and is cool.

And for the sessions table i use Heap (memory).

Also a friend recommend me to use partions for this sql table but i don't know if anyone test this and have realy good performance ....

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

This will work only if this table doesn't have FULLTEXT index in it but i am not sure if it has or not...

Thank you

[Mat B]

If you switch to InnoDB, you won't have any fulltext indexes - it doesn't support them. That's why Sphinx was also mentioned.

[Axel Wers]

Maybe solution would be when posts were split to more tables. I have little issue with this too. When I optimize tables, all tables are optimized almost immediately, but posts table I am optimizing almost 3-4 minutes.

[Gary.]

ASTRAPI - Wow, Honestly you do some playing with your server.

Mine been running over 300 days with no setting change, reboot... Ohh.. wait.. Just 4 that was when the kernal exploit was out so I had to re-build a custom kernal again. simplez

You know the saying dont you, If it aint broken do not fix it ! In your case your on a constant fix.

Now the questions you ask I do not get in and single way and I been doing server management for many years for myself and clients.. The questions and errors do not tie up with what your trying to figure out.. I only assume fiddling is in progress.

First thing you need to ask yourelf is, What are you trying to achive..

Security, Stability, Performance.

Only two out of the three will evey work togather and I would also like to quote on what you say.

The server is powerfull with 16gb ram and 2x quad xeon.

The server is only good on the way it's setup, Obviously if it's a bad setup you will see issues yes, You may have a good setup but a bad kernal... Ghost CPU load... You have a load generated somewhere you cannot find.... People assume it's a setting and they assume a kernal is just a peice of software what will have not affects on stability and performance.. Wrong.. The kernal itself will have a whopping impact on CPU loads / Memory and such, Even with 0 connections. Thats why you always see when kernals I mention I also say "I use a custom one" , Why.. Well I only use what I need, If your'e blasing out all modules on the default RHEL then fool the user for just Upgrading rather than thinking the outcome.

[ASTRAPI]

My server is working perfect Gary :)

I have only one problem about editing on the huge posts table and i ask about it.

I don't play with my server daily :)

This problem is normal and all have it with Myisam huge tables.

Some solve it with converting to Innodb and some with mysql partitioning and in general i ask for solutions....

[Gary.]

mysql partitioning is a MUST, Never use /tmp

Have you optimized the tables ? Whats the mysql load, Click load averages link top right of WHM.

Also what the average mysql laod via TOP in SSH

[AlexJ]

mysql partitioning is a MUST, Never use /tmp

Please elaborate for me. I am not sure what you mean by never use /tmp because I think I am using /tmp or so.

[Gary.]

Its default ( datadir=/var/lib/mysql )

See if you get a dedicated server, You will have a small partition then the disc.

Now the small partition is where the mysql and other system files / things will be located so if the server becomes busy you will see alot of reads / writes on that drive which can cause high I/O times and will slow things down.

What I do, Most other people and webhosts will do is use 1 powerfull drive just for the mysql server and databases so things will be speedy.

I worked on a server recently and I moved there mysql and data to a non used 200GB drive, This was reporting around 13K Rpm, So lets say the max was 15,000 RPM.

Once moved the server loads dropped by around 70%, Now I'm not saying this may be the case for all, But for big and busy databases, Then the above will be a step forward.

This can easily be done within 10 mins, Depending on the database sizes, And the configuration of the my.cnf and paths.

[ASTRAPI]

Maybe i am confused but partitioning on my link is not related to hard disk partitioning that it will not help in my opinion a lot if it is using the same disk:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

I think is not the same partitioning a disk and partitioning the mysql tables or not?

Thank you

[Gary.]

No its different as partitioning is the same disc,

I'm talking about using mysql to run on it's own drive to give max performance, The faster the drive the better.

All you do then is synlink to point to the new drive :)