mysql performance - from old forunms

[Michael]

Topic: AIX 4.3:3 mysql performance
Posted by: Lanny ? 12/04/2006, 17:55:14
Author Profile | Post Reply | Alert | Recommend | Current page | Edit | Main Forum
My mysql is extremely slow. It is not the CPU, but I am getting this from VMstats. Can anyone please suggest if the high number of faults so sy indicates a specific problem?
kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
1 2 72315 5804 0 56 0 0 0 0 189 16480 142 8 27 29 36


 

[Michael]

Well, if this is the statistics from boot - whci I cant see in a single line vmstat output, you are probably i/o bound.

Here is the tool you can use for gathering statistics, and a wiki for using it.

ftp://ftp.software.ibm.com/aix/tools/perftools/perfpmr/perf433/

http://www-941.ibm.com/collaboration/wiki/display/WikiPtype/Performance+Monitoring+Tips+and+Techniques

[Michael]

Thinking about this a bit longer I must recall a small, 'unsuited' program I wrote in mysql (an implementation of the Dijkstra, or star algorithm). I had a couple of JOIN statements that were taking 30 seconds - lots and lots of i/o.

Using the SQL statement DESCRIBE SELECT ....; I was able to find which indexes I needed for the queries, and finally, I learned how to apply the "LEFT JOIN" statement so that a SELECT statement that had been taking about 25 seconds dropped back to less than 0.2 seconds.

As is almost nearly the issue, performance problems are not really caused by OS settings, but by application code - misusing system resources.