Slow Database Server

[nellysoft]

Hi everyone,
Im having trouble with my database server run with PHP & MYSQL; i just installed this system on a SUSE Linux Enterprise Sever; the Server its self is an HP Proliant ML370G with 4GB RAM
The system was fine for like 3 weeks until recently when it started slowing down. Apparently, eveything is normal when you are accessing the server, for example when you are browsing on the database web interface; but problem is when you start working in the database, when you try submitting into the database, its really slow on processing, i have to first restart the mysql;
Could it be RAM? or the SQL itself?

Could anyone help on the situation.

[scathefire]

as far as mysql, here are some tips

apart from that, you'd need to monitor your memory and cpu when these slow periods are occuring.

[hemanshurpatel]

HI,
well i think it may be a either bad scripting issue (SQL query is not optimized), or memory leak sory of issue.
Can you check the "top" and "free", command status and let us know when your system starts working very slow.

it may be because of some php script problem, top will let us know about it.

Regards,

Hemanshu Patel

[jippie]

Although you mention slowing down over time, it is worth to check if MySQL has it's caching turned on. Check my wiki page on it:

wirespeed.xs4all.nl/mediawiki/index.php/Speeding_up_MySQL_by_enabling_the_cache

[nellysoft]

HI,
well i think it may be a either bad scripting issue (SQL query is not optimized), or memory leak sory of issue.
Can you check the "top" and "free", command status and let us know when your system starts working very slow.

it may be because of some php script problem, top will let us know about it.

Regards,

Hemanshu Patel

HI, Thx for your response; below are the respective outputs for the Top and Free commands.

adwatch:~ # top 

top - 12:30:40 up 9 days,  1:36,  2 users,  load average: 1.48, 1.65, 1.44
Tasks: 143 total,   1 running, 142 sleeping,   0 stopped,   0 zombie
Cpu(s): 29.2%us,  7.1%sy,  0.0%ni, 63.5%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   3919432k total,  3423260k used,   496172k free,   627948k buffers
Swap: 16771852k total,      140k used, 16771712k free,  2454944k cached

adwatch:~ # free
             total       used       free     shared    buffers     cached
Mem:       3919432    3424124     495308          0     628036    2454976
-/+ buffers/cache:     341112    3578320
Swap:     16771852        140   16771712

Thx Alot

[nellysoft]

Although you mention slowing down over time, it is worth to check if MySQL has it's caching turned on. Check my wiki page on it:

wirespeed.xs4all.nl/mediawiki/index.php/Speeding_up_MySQL_by_enabling_the_cache

Hi
Thx for your reply; i've just modified my mysql conf. im going to monitor the progress and i'll let you know. Thank you for the link too.

[linuxaomi]

Hi there,
mysql slow-query-function and mysql profiling should provide a more detailed overview of your slow-down period.
linuxaomi

[nmset]

How many rows in your tables ?

MySQL does not perform well when there are many concurrent clients querying tables with millions of rows.

EDIT : I'm talking about InnoDB, the most used engine with MySQL.

[linuxaomi]

InnoDB ? Has to be partitioned. Choose month or day of week as criteria.

[nmset]

Partitioning tables is an artificial technique, fleeing away from a real problem. Maintainance will increase many times while managing constantly the partitioned tables and the partitioning rules. In France we call this 'The ostrich hiding its head in the ground'. You'd better choose a DBMS that can handle easily huge amount of data on properly selected hardware.