• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

Bad performance of MySQL on Sun Solaris machine


I've got a problem with my new X4150 Server. This Server I use to run a MySQL Database, but the server has a poor performance, if I compare it to other systems I own.

The X4150 Server is a 2x X5460 (8x 3.16 GHz) System with 8GB RAM, the MySQL Data is on a 3 Drives RaidZ Pool. I use Solaris 10 and mysql 5.1.33.

My reference System is a noname Intel Dual Core @ 2.40GHz with 4GB RAM running gentoo linux. On that system I use mysql 5.0.60.

For my test I have two tables, one has 2k entries and the other 23k. I try to connect the two tables (no indexes for extra load), with a left join (SELECT * FROM `Artikelbeschreibungen 0_6004` besch left join unimet uni on besch.ArtNr = uni.ArtNr order by besch.ArtNr;).

On the X4150 the Query takes about 8min, on the other sys about 4min. So how can I improve the performance of the Solaris system?
2 Solutions
If you are testing, you should use similar versions of the application.
check iostat, etc. to see where the bottleneck is.
I.e. is the issue with the storage?  What else is running on the server?
When the query completes what is being reported for the amount of time to perform the query?
Does the reported mysql time to perform the query matchs between the two systems?
LL0rdAuthor Commented:

I just installed the same version of mysql on my vista desktop computer. Well, the results:

X4150: 1 row in set (7 min 37.28 sec)
Desktop: 1 row in set (11 min 12.65 sec)

Could it be, that the mysql engine gets itself slower from version to version?
Is there a way, how I can optimize the performance of mysql? In the machines I have 8 CPU Cores, but only one works on a query.

Thats my iostat during the query:

                 extended device statistics                    tty         cpu
device    r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b  tin tout  us sy wt id
sd0       0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0    0    5   2  8  0 90
sd1       3.0    2.1  207.3   22.2  0.0  0.1   22.6   0   2
sd2       0.1    0.1    1.8    0.5  0.0  0.0   16.7   0   0
sd3       0.1    0.1    1.8    0.5  0.0  0.0   16.6   0   0
sd4       0.1    0.1    6.5    0.5  0.0  0.0   14.3   0   0
nfs1      0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0

Open in new window

Check out the optimzation guide for mysql on x64.
Optimzing with dtrace:

Also, have a look at the Sun Web Stack versions with optimizations for various apps including mysql (formerly known as coolstack):
It is not that the engine is getting slower, but the engine is getting more complex to provide quicker responses when indexed, and optimized databases/tables.
In your example you are trying to test a raw query. The engine on initial query tries to perform other tasks to improve the performance of similar queries.

If you have not rebooted either system and if you run the same or slightly modified query, how long does the subsequent query take?
In a way it is somewhat similar to a person asked to look for answers in a set of books on the desk.  The first run will take the longest.  The subsequent ones will be faster because the person would normally remember what books are part of the set and approximately which book one should consult.

Optimizations Rowley suggested should be done in addition to the Database optimization, adding indexes.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I try to connect the two tables (no indexes for extra load), with a left join (SELECT * FROM `Artikelbeschreibungen 0_6004` besch left join unimet uni on besch.ArtNr = uni.ArtNr order by besch.ArtNr;).

you HAVE to put a index on besch.ArtNr to give this query a chance to run smoothly.
please create the index, and repeat the tests.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now