Vlad Fedorkov

Performance consulting for MySQL and Sphinx

Why is stock MySQL slow?

“I’ve installed MySQL and it doesn’t work fast enough for me”. MySQL server is heart of database driven application (if it uses MySQL as database of course!) and any slowness related to running queries is affecting all application layers.

MySQL server tuning and query slowness hunting are always step by step process and without knowing all the data (SHOW GLOBAL VARIABLES, SHOW GLOBAL STATUS, SHOW TABLE STATUS LIKE ‘tablename’, EXPLAIN details for slow query is just some of the required information) it would be generally a blind guess. But there are still few things which is related to newly installed MySQL server.

If you are using stock MySQL you might need to check memory pool size which MySQL used to load index data to avoid slow IO requests and increase queries speed. Connect to MySQL and fire two queries:

SHOW VARIABLES LIKE 'key_buffer_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Default values are very small. If you see something less than 128MB (8Mb for older versions) that might be an issue. key_buffer_size used by MyISAM tables while innodb_buffer_pool_size required for InnoDB tables. I would recommend to set one of them to some relatively big value (generally from 25% to 80% of total memory on the box depends on the case).

How do you know which table type you are using? While connected to mysql please type:

SHOW CREATE TABLE 'table_name';

and find ENGINE=MyISAM or ENGINE=InnoDB on the last line. Increase key_buffer_size in my.cnf (/etc/my.cnf or /etc/mysql/my.cnf) if you see MyISAM or innodb_buffer_pool_size for InnoDB and restart MySQL.

Have fun!

Category: Performance
  • internet advertising says:

    Banner ads typically include graphics and text which entice Internet users to click on the advertisement.
    As with everything else, it is only the mindset of the entrepreneur
    that dictates the success and failure of a company.

    What you give away doesn’t have to be costly, but it must be valuable.

    January 7, 2016 at 6:21 pm
  • คลิปโป้ says:

    Pretty element of content. I just stumbled upon your site and
    in accession capital to assert that I get in fact loved account your blog posts.
    Anyway I’ll be subscribing on your feeds or even I achievement
    you get right of entry to persistently quickly.

    January 9, 2016 at 3:04 am
  • คลิปโป้ says:

    I’ve read a few just right stuff here. Certainly value
    bookmarking for revisiting. I wonder how a lot attempt
    you set to make any such wonderful informative web site.

    January 10, 2016 at 3:13 am
  • หนังx says:

    Excellent, what a blog it is! This web site gives useful information to us, keep it up.

    January 11, 2016 at 5:09 am
  • Angeles says:

    If you’re educated (or seeking an education) you will probably find a ton of opportunity in a small
    town. Make the process pain-free with debt collection software for a
    small business that automates this tedious
    process and adds to your company’s bottom line. Two:
    Increase in income – When you are able to increase
    your traffic to the business, you will easily be able to also increase your income.

    January 19, 2016 at 1:44 am
  • Greta says:

    Then send the postcard to all of the addresses in your area, and try to draw customers in. If,
    on the other hand, you are not successful in paying your loans back, you are going to develop “bad credit. Always choose a new account, and look for a specialized bank whenever possible.

    January 19, 2016 at 2:59 pm
  • Sharyn says:

    Then send the postcard to all of the addresses in your area, and try to draw customers in. Make the process pain-free with debt collection software for a small business
    that automates this tedious process and adds to your company’s
    bottom line. Always choose a new account, and look for a specialized bank whenever possible.

    January 27, 2016 at 2:44 pm
  • carolpacevedo.cabanova.com says:

    Tax law about businesses in the USA is ever changing.
    When job offers do come and I’ll admit, some companies may fear hiring you because they fear
    a former owner being an employee, negotiate the best salary you can along with
    the best benefits you can for you and your family.
    Clients do not look no matter whether you’ve a small or big budgeted communication service.

    January 28, 2016 at 2:17 pm
  • http://lewispwalden.mywapblog.com says:

    The following are the benefits that you have to be aware of the most.
    Make the process pain-free with debt collection software
    for a small business that automates this tedious process and adds to your
    company’s bottom line. What you give away doesn’t have to be costly, but it
    must be valuable.

    January 29, 2016 at 7:21 pm
  • Nick says:

    Look into their years of experience in managing
    small business accounts so that you can get professional advice
    on various financial matters. Make the process pain-free with debt collection software
    for a small business that automates this tedious process and
    adds to your company’s bottom line. Clients do not look no matter whether you’ve a small or
    big budgeted communication service.

    January 31, 2016 at 2:46 am
  • Braces Treatment says:

    Starting a small business with no money may sound impossible but there are ways to kick off a very profitable business without ever
    spending a single dime of your own money. Platforms: Android,
    Blackberry, i – Phone, i – Pad, Windows Phone. Selling the same
    things, to the same market, in the same way.

    April 25, 2016 at 8:51 pm

Your email address will not be published. Required fields are marked *

*