Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 15

Displaying posts with tag: my.cnf (reset)

Some new (and useful!!) MySQL 5.6 variables
+1 Vote Up -0Vote Down

Upgrading from MySQL 5.5 to MySQL 5.6 is a fairly straightforward process. However, the move to version 5.6 does bring with it some new varaibles with which to work.

One of the recent innovations (pioneered by Percona in Percona Server 5.5 if I recall correctly) is the ability to "dump" the innodb buffer pool***. This can be done any time MySQL is running and can be configured to be executed during MySQL shutdown. One reason to do this is to have a server "pre-warmed" when you perform a server restart. Why this is important is that during server operation the innodb buffer pool will fill up. Typically the server will operate much better when this buffer pool is full instead of being empty or paritally full because the return of data from the buffer pool is much faster than returning data from disk. 

There are four important variables with each

  [Read more...]
Thoughts on Upcoming MySQL 5.6 Defaults
+0 Vote Up -0Vote Down

Read the original article at Thoughts on Upcoming MySQL 5.6 Defaults

During Oracle Open World 2012 and the parallel MySQL Connect conference, the new 5.6 version was announced. It’s only release candidate right now, but that means the GA release is just around the corner. With that James Day has posted changes to various of the new parameter defaults. Many of them you may not run [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:
  • 5 Ways to Boost MySQL Scalability
  • 5 Ways to fortify MySQL replication
  •   [Read more...]
    Lessons Learned
    +1 Vote Up -1Vote Down

    Ah, the sometimes bitter pill of experience.

    Recently I got "the call" about a server that was having problems. I began standard troubleshooting procedures but didn't see anything abnormal. After about 15 minutes of getting irritated I opened up the my.cnf and looked through it. Imagine my suprise when I saw the innodb_log_file_size parameter was set to 5M (five megabytes). Even though it was hard-coded in the my.cnf, this is actually the default size (at least through MySQL 5.1). A log file size of five megabytes is pitifully small and was proving to be a severe bottleneck for the system. The two log files were being , filled up and flushed more frequently than once a second.

    Once the source of the trouble was discovered it was a simple matter of filing a RFC and resolving the issue. We increased the log file size to 2000 megabytes so flushing of the

      [Read more...]
    How to log all MySQL queries in Drupal
    +3 Vote Up -0Vote Down
    In order to benchmark a Drupal site performance you need to see all the database queries related to your Drupal site. In case you don’t have access to the my.cnf file If you don’t have access to the my.cnf file, you can log the queries from the Drupal code itself: cp includes/database.mysql-common.inc includes/database.mysql-common.inc.backup.orig vim includes/database.mysql-common.inc [...]
    How to start mysqld using numactl
    +1 Vote Up -0Vote Down

    Various people have complained about Linux swapping unexpectedly on boxes running mysqld, when apparently mysqld was not using all the memory and there was quite a bit of free memory available.

    There’s also an article by Jeremy Cole. However, his solution requires a one-line change to mysqld_safe which while it’s small does not work very well if you have to maintain a large number of servers and are using packages asa package upgrade will overwrite the modified file mysqld_safe and then restart mysqld with the unmodified script. This leads to the need to repatch the script and then restart mysqld. Not very helpful.

    So I was looking for another solution and came up with this option which basically requires a minor change to /etc/my.cnf and the

      [Read more...]
    Fixing MySQL with a comment in the config file
    +3 Vote Up -0Vote Down

    A customer called with an emergency issue: A server that normally runs many MySQL instances wouldn’t start them up. Not only would it not start all of them, it wouldn’t even start the first one. The multiple instances were started through the mysql_multi init script. Perhaps you already know what was wrong!

    It turns out that this server’s /etc/init.d/mysql_multi wouldn’t start unless it found the text “mysqld_multi” in the /etc/my.cnf file. Not a [mysqld_multi] config file section, but the text string “mysqld_multi”. It was using this text as a proxy for “I found a [mysqld_multi] configuration section.” This was a rather brittle test, as you can imagine.

    After reading the source, I determined that the my.cnf file was fine and the configuration should not be changed,

      [Read more...]
    How do I identify the MySQL my.cnf file?
    +2 Vote Up -0Vote Down

    As part of my upcoming FREE my.cnf check advice I first need to ask people to provide the current MySQL configuration file commonly found as a file named my.cnf

    If only that question was easy to answer!

    Use of configuration files

    MySQL will by default use at least one configuration file from the following defaults. MySQL also uses a cascade approach for configuration files. When you have multiple files in the appropriate paths you can see unexpected behavior when you override certain values in different files.

    You can however for example specify –no-defaults to use no configuration file, or add options to your command line execution, so even looking at all configuration files is no guarantee of your operating configuration.

    However

      [Read more...]
    Free advice on your my.cnf
    +3 Vote Up -0Vote Down

    Today, while on IRC in #pentaho I came across a discussion and a published my.cnf. In this configuration I found some grossly incorrect values for per session buffers (see below).

    It doesn’t take a MySQL expert to spot the issues, however there is plenty of bad information available on the Internet and developers not knowing MySQL well can easily be mislead. This has spurred me to create a program to rid the world of bad MySQL configuration. While my task is potential infinite, it will enable me to give back and hopefully do a small amount of good. You never know, saving those CPU cycles may save energy and help the planet.

    Stay tuned for more details of my program.

    [mysqld]
    ...
    sort_buffer_size = 6144K
    myisam_sort_buffer_size = 1G
    join_buffer_size = 1G
    bulk_insert_buffer_size = 1G
    read_buffer_size     = 6144K
    read_rnd_buffer_size = 6144K
      [Read more...]
    Be sure to know your my.cnf [sections]
    +3 Vote Up -0Vote Down

    The MySQL configuration file, e.g. /etc/my.cnf has a number of different section headings including [mysql], [mysqld], [mysqld_safe]. It is important that you ensure you put the right variables into the right section. For example, the following my.cnf configuration file will not operate as the user probably expects.

    [mysqld]
    ...
    log-bin=mysql-bin
    server-id=1
    query_cache_size = 100M
    query_cache_type = 1
    
    ...
    
    [mysqld_safe]
    ...
    key_buffer_size=600M
    skip-innodb
    ...
    

    In this example, this configuration does not give you a MyISAM key buffer of 600M, it’s actually the default of 8M.

    mysql> show global variables like 'key_buffer_size';
    +-----------------+---------+
    | Variable_name   | Value   |
    +-----------------+---------+
    | key_buffer_size | 8388600 |
    +-----------------+---------+
    

    Be sure to add the right options to the

      [Read more...]
    thread_stack_size in my.cnf
    +2 Vote Up -0Vote Down

    Many configs have thread_stack_size configured explicitly, but that can cause rather bad trouble:

    • if the stack inside a thread it’s too small, you can get segfault crashes (stack overflow, essentially). Particularly on 64-bit.
    • if the stack is too large, your system cannot handle as many connections since it all eats RAM.

    Let mysqld sort it out, on startup it does a calculation based on the CPU architecture, and that’s actually the most sensible. So for almost all setups, remove any thread_stack_size=… line you might have in my.cnf.

    Have you checked your MySQL error log today?
    +2 Vote Up -0Vote Down

    As a consultant I would be rich if I made money every time when asking “Have you checked the MySQL error log?”

    Today’s special found in a 13GB MySQL server error log.

    090819 22:49:37InnoDB: Warning: difficult to find free blocks from
    InnoDB: the buffer pool (1101071 search iterations)! Consider
    InnoDB: increasing the buffer pool size.
    InnoDB: It is also possible that in your Unix version
    InnoDB: fsync is very slow, or completely frozen inside
    InnoDB: the OS kernel. Then upgrading to a newer version
    InnoDB: of your operating system may help. Look at the
    InnoDB: number of fsyncs in diagnostic info below.
    InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
    InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs
    InnoDB: Starting InnoDB Monitor to print further
    InnoDB: diagnostics to the standard output.
    090819 22:49:37InnoDB:
      [Read more...]
    Why isn't MySQL using the my.cnf settings I've specified?
    Employee +0 Vote Up -0Vote Down

    You are just getting started with MySQL on OpenSolaris. You've installed the OpenSolaris Community Edition and CoolStack MySQL. To explore this new environment you decide to run some tests using the sysbench benchmark. After running a number of tests you realize that for some reason the options you are setting in /etc/my.cnf are not getting used.

    What is going on? Not to worry, you've just hit a problem common to new users of MySQL on OpenSolaris.

    Run this command:

     # /opt/coolstack/mysql/bin/mysqladmin | more

    page down until you see:

     Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /opt/coolstack/mysql/etc/my.cnf

      [Read more...]
    Why isn't MySQL using the my.cnf settings I've specified?
    Employee +0 Vote Up -0Vote Down

    You are just getting started with MySQL on OpenSolaris. You've installed the OpenSolaris Community Edition and CoolStack MySQL. To explore this new environment you decide to run some tests using the sysbench benchmark. After running a number of tests you realize that for some reason the options you are setting in /etc/my.cnf are not getting used.

    What is going on? Not to worry, you've just hit a problem common to new users of MySQL on OpenSolaris.

    Run this command:

     # /opt/coolstack/mysql/bin/mysqladmin | more

    page down until you see:

     Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /opt/coolstack/mysql/etc/my.cnf

      [Read more...]
    Why isn't MySQL using the my.cnf settings I've specified?
    Employee +0 Vote Up -0Vote Down

    You are just getting started with MySQL on OpenSolaris. You've installed the OpenSolaris Community Edition and CoolStack MySQL. To explore this new environment you decide to run some tests using the sysbench benchmark. After running a number of tests you realize that for some reason the options you are setting in /etc/my.cnf are not getting used.

    What is going on? Not to worry, you've just hit a problem common to new users of MySQL on OpenSolaris.

    Run this command:

     # /opt/coolstack/mysql/bin/mysqladmin | more

    page down until you see:

     Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /opt/coolstack/mysql/etc/my.cnf

      [Read more...]
    An Interesting Replication Tip
    +0 Vote Up -0Vote Down
    We recently moved some databases to different servers, and I altered our mySQL slave configuration files to take into account some of the databases we wanted to replicate (Replicate-do-db) and others that we wanted to ignore (Replicate-ignore-db) -- each of these server cases were mutually exclusive.All went well, until I found a peculiar error:Error 'Table 'db101.table101' doesn't exist' on
    Showing entries 1 to 15

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.