Bad performance of MySQL on Sun Solaris machine

Posted on 2009-04-18
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 76

    Expert Comment

    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?
    LVL 1

    Author Comment


    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

    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 76

    Assisted Solution

    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 142

    Expert Comment

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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now