Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Mysql Load Problem

[SHAN9211]

Hello Sirs!

I am having problem in my mysql, last year i shifted my server and by mistake i dumped database 2 times, that time i didn't care much but now when i am having more then 70000 members , database crashes very often, and every time i have to restart server then repair database.... site works fine for 3 to 4 hours then suddenly mysql load goes to 20 , 40 and then site stops responding ,i did a top and it showed me that its that is causing the load but as being newbie i don't know how to check which table is causing and how to resolve, please help me,and i have vbulletin 3.8.5 installed with xbt tracker....

sorry for my bad English.....

[helpyoulinux]

In the top, you will get the process id of the the mysql. From process id you can see the details of the db.

[cedced]

Try use APC cache or memcache, it will increase server performance.

[SHAN9211]

sir i am completely newbie for this can u please pass me any guide to install or to setup

APC cache or memcache

[SHAN9211]

Originally Posted by helpyoulinux

In the top, you will get the process id of the the mysql. From process id you can see the details of the db.

i do get the id of mysql but i dont know how to get to that table or coloumn with that id in order to delete that table or column can you please guide me?

and pass me ur email id....please

[LnxtecH]

Originally Posted by SHAN9211

sir i am completely newbie for this can u please pass me any guide to install or to setup

APC cache or memcache

Try google search.
http://www.google.co.in/#hl=en&sourc...5c7405d4a7e2c8

[c10k_Consulting]

Originally Posted by SHAN9211

i do get the id of mysql but i dont know how to get to that table or coloumn with that id in order to delete that table or column can you please guide me?

and pass me ur email id....please

Type

SHOW FULL PROCESSLIST

When your having isues, it will give you a starting point.

[pubcrawler]

You need to turn on SLOW QUERY Logging and logging of queries not using indexes in my.cnf config file.

You likely need to optimize your queries and use query cache in MySQL. Proper indexes need put in place.

Test slow queries by prepending:
EXPLAIN (the query)

That will show you useful information on the query - which indexes (if any) are being used and help you to isolate some problems.

As for the restarts and corruption, I believe you are using MyISAM. Convert your tables to InnoDB and the corruption *SHOULD* go away.

When doing the repairs to tables you need to take down all front ends so you app isn't connecting to the database, otherwise this will create a big dogpile and cause more issues.

[SHAN9211]

THANKS Guyz, i am very great full , i will update you on results

[voidSecurity]

also for search you could use : sphinix ( going to improve IO/disk )

[Gary4gar]

If you are total new to Server World, then I suggest to get some help. Either ask some friend to do it or hire somebody to do it for you.

[SHAN9211]

Originally Posted by Gary4gar

If you are total new to Server World, then I suggest to get some help. Either ask some friend to do it or hire somebody to do it for you.

can anyone please pass me his online contact for help, i think i am unable to solve the problem, i would be very great full