Home > mysql > Terrible mysqld performance after system upgrade

Terrible mysqld performance after system upgrade

October 20Hits:1
Advertisement

I recently replaced my old Core2duo Mac with 4GB and OS X 10.6.8 with a 4-core i5 with 16GB, an SSD and 10.7.2.

The new system is wonderfully fast for everything except mysqld. It's OK with light loads but when I give it some real work to do, it behaves in a very peculiar way.

I've tried mysql-5.1.59-osx10.6-x86_64 and mysql-5.5.16-osx10.6-x86_64 with similar results.

The workload is a batch file that takes a large database we get from a supplier and manipulates the data into a form we can use in our app on the production servers. On the old Mac it took 3 to 4 hours to run.

On the new one it runs fast for a while and then bogs down, after which each query runs amazingly slowly with trivial IO and the CPU pegged at 100%, on one core (the batch file runs only one query at a time through the regular mysql cli client). Other apps also slow down badly and use a lot of CPU even though, according to top, there's 10GB inactive memory.

For example, right now mysqld is running

select t.t_id, a.a_id from t inner join a on a.x=t.x into outfile; 

This query has been running for about an hour while it took only 4 minutes on the old system. It's using 100% cpu on one core and it writes a 1MB block to the outfile every minute or so. Using iosnoop I can't see any reads from either table a or t (both MyISAM) so I guess they are cached in VM. The relevant index was preloaded in the MyISAM key buffer. So there's definitely no IO bottleneck. And yet this new system is much slower (~20x) than the old one.

I've got the 5.5 performance schema but I don't understand it. And I have dtrace but I'm not really competent to drive it other than using the given utilities like iosnoop.

What could possibly be going on? What could I do to uncover relevant info?

EDIT: This is all my.cnf has for mysqld—defaults for everything else.

[mysqld] datadir = /Users/fsb/mysql port = 3306 socket = /tmp/mysql.sock key_buffer_size = 1536M performance_schema = ON 

Answers

Your observation is not at all surprising.

If there is one thing I have learned over the years as a MySQL DBA is this: MySQL is only as performance-enhanced as you configure it. I can say this with absolute certainty because I have installed PostgreSQL and MySQL and have seen PostgreSQL perform much better "out-of-the-box" than MySQL "out-of-the-box".

To prove this point outside of my own opinion,

Percona recently performed a "Battle Royale" among multiple releases of MySQL

  • MySQL 4.1
  • MySQL 5.0
  • MySQL 5.1 (with built-in InnoDB)
  • MySQL 5.1 with InnoDB-plugin
  • MySQL 5.5
  • MySQL 5.6

All tests were performed with MySQL unconfigured. The results?

  • MySQL 4.1 performs the best in a single-threaded environment
  • MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 InnoDB built, 5.5 and 5.6

What do I get out of this? Your must configure MySQL 5.5/5.6 to get multicore enhancements engaged.

Given the my.cnf you are using, MySQL is still running, for all intents and purpose, "out-of-the-box".

Please execute the necessary due diligence (query tuning, desired configuration, DB infrastructure, Proper Storage Engine Selection and Tuning) to get MySQL up-to-snuff to perform better for you.

Do you have a sufficient amount of swap space?

Do you have a sufficient amount of /tmp and /var/tmp space?

I am just throwing out a wild guess, but what about tmp space for the MySQL database.

Related Articles

  • Terrible mysqld performance after system upgradeOctober 20

    I recently replaced my old Core2duo Mac with 4GB and OS X 10.6.8 with a 4-core i5 with 16GB, an SSD and 10.7.2. The new system is wonderfully fast for everything except mysqld. It's OK with light loads but when I give it some real work to do, it beha

  • How can I perform an offline upgrade using Ubiquity on the installation disc?April 19

    I'm trying to upgrade an Ubuntu 12.10 installation to Ubuntu 13.04 without an Internet connection. Now that the alternate and desktop CDs have been merged, I'm trying to use the 13.04 Desktop CD in order to upgrade my 12.10 system. In Ubiquity, there

  • What can I expect regarding performance if I upgrade from Windows 7 to 8? October 7

    What can I expect regarding performance if I upgrade from Windows 7 to 8? Especially, are there any risks of a poorer performance? (With little RAM for example) This is for a Netbook with 1G RAM. What about Windows 8.1? --------------Solutions-------

  • Can apt-get upgrade perform a dist-upgrade from one version of debian stable to the next?April 29

    I just wanted to do a regular upgrade on my Debian Wheezy system. I did the usual $ sudo apt-get update $ sudo apt-get upgrade I noticed that the number of packages to be upgraded was pretty large but I just hit Y and proceeded with the upgrade. To m

  • Are there any performance issues after upgrade from Android 2.1 to 2.2?January 31

    I have Samsung Galaxy S GT-i9000, now I want to upgrade its OS from 2.1 to 2.2. However, through Google i have found that many people are facing performance issues after the upgrade. Does anyone have experience with this, and can you tell me how to g

  • Is it safe to perform a firmware upgrade on a Canon 60D camera?June 1

    How safe is it to perform a firmware upgrade on a camera? I know what firmware upgrade is and what advantages/possible disadvantages it brings, I'm just specifically asking how safe or unsafe it is to perform one. Can something go wrong? What should

  • How to perform a distribution upgrade using apt-fast?July 25

    My distribution upgrade from natty to ocelot has been running on and off for quite some time. The speeds at which it downloads is really slow, and I was wondering if there was a way to use the apt-fast command to perform a distribution upgrade to oce

  • Pacman Full system upgrade ask for replacing packagesMarch 15

    Every time I do a full system upgrade pacman ask me the same question : tarting full system upgrade... :: Replace mga-dri with extra/libgl? [Y/n] n :: Replace r128-dri with extra/libgl? [Y/n] n :: Replace sis-dri with extra/libgl? [Y/n] n :: Replace

  • Pacman Full system upgrade failureApril 11

    I'm trying to do a full system upgrade using pacman on ArchLinux. I have kernel 3.2. When typing sudo pacman -Syu I got the following errors :: Synchronizing package databases... core is up to date extra is up to date community is up to date :: Start

  • Arch Linux and xfce4 working strange after system upgrade

    Arch Linux and xfce4 working strange after system upgradeOctober 28

    I'm having a hard time trying to figure this out. I've been running Arch Linux and Xfce4 smoothly for several months but suddenly after a full system upgrade in which the system asked me to replace some Python packages, things got weird. For instance

  • What exactly happens when I use Kies to perform a "Firmware upgrade and initialisation"?November 6

    I bought an unlocked Telstra Samsung Galaxy S3 4G, and it's loaded with some Telstra software. I wanted to get rid of those, so I loaded up Kies and it gives me the option to perform a "Firmware upgrade and initialisation". I don't want it delet

  • POP3 no longer works after system upgradeApril 12

    After upgrading server from Fedora Core 13 to 14 I can no longer log in to the mail server using webmail (imap) or Thunderbird (POP3). When I try with Thunderbird/POP3 is receive the error message: Sending of username did not succeed. Mail server 192

  • System upgrade 13.04 to 13.10 Filesystem check or mount failedOctober 28

    I got started the system upgrade. Unfortunately, my upgrade just froze in the middle of the installation and I, was prompted to force my PC down by pressing the reset button. Now my Ubuntu won't to start. I found this: Run from maintenance shell one

  • Application on server crashes when accessed via browser - after full system upgradeDecember 28

    After a recent full-system upgrade one of my applications stopped working. It's Fatrat 1.2.0_beta2-11 on Archlinux (installed from community repository). Without enabling web access the application works just fine, but when web access is enabled and

  • HR performance management systemFebruary 19

    I am looking for software similar to workday or successfactors for HR performance management system. But the examples I mentioned are a bit expensive. The main purpose of the software is to monitor employee performance and compensation. For example l

  • Does performing a System Refresh keep these folders?August 19

    Alright, let's make this simple. I'm really contemplating performing a system refresh. What I've been looking for all over is if the process keeps some certain folders. Namely my Documents, Pictures, Videos, Downloads, and Desktop folders. There's so

  • Filesytem check or mount failed on System UpgradeSeptember 7

    I tried to do a system upgrade, and everything seemed correctly, but after restart the system didn't boot and receive this error Filesystem check or mount failed There's several answers here and here where recommend to do: mount -o remount,rw / dpkg

  • System upgrade kills TexSeptember 27

    After a system upgrade, when I attempt to use LaTeX, I get the following error from within my compiler (TexMaker). I have no idea what it means. What should I do? (I did not set up TeX on the computer in the first place and don't know how to do that;

  • Getting error virtualbox-guest-modules: requires linux 4.0 on antergos while doing system upgradeApril 18

    I am running Antergos Linux 64 bit on virtual box. when I try to do full system upgrade I get a error: resolving dependencies... looking for conflicting packages... error: failed to prepare transaction (could not satisfy dependencies) :: virtualbox-g

  • performance development system for HR April 21

    I need to develop a Performance Development System for HR Department. We have a Sharepoint O365 subscription. Is it possible to achieve this system completely by just by configuring out of the box features of sharepoint? Are there any 3rd party tool/

Copyright (C) 2017 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 1.062 s.