[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Bad performance of MySQL on Sun Solaris machine

Posted on 2009-04-18
Medium Priority
Last Modified: 2013-12-27

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?
Question by:LL0rd
LVL 81

Expert Comment

ID: 24177110
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?

Author Comment

ID: 24178287

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

LVL 13

Accepted Solution

Rowley earned 750 total points
ID: 24178373
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):
LVL 81

Assisted Solution

arnold earned 750 total points
ID: 24179785
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.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24376520
>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.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month19 days, 6 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question