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 中文
Previous 30 Newer Entries Showing entries 91 to 120 of 131 Next 11 Older Entries

Displaying posts with tag: tips (reset)

How to pretty-print my.cnf with a one-liner
+2 Vote Up -0Vote Down

When I'm looking at a server, I often want to see the /etc/my.cnf file nicely formatted, and with comments stripped. This Perl one-liner will pretty-print the file:

PLAIN TEXT CODE:
  • perl -ne 'm/^([^#][^\s=]+)\s*(=.*|)/ && printf("%-35s%s\n", $1, $2)' /etc/my.cnf
  • [client]                           
  • port                               = 3306
  • socket                             = /var/run/mysqld/mysqld.sock
  • [mysqld_safe]       
  •   [Read more...]
    The feature I love in TokuDB
    +3 Vote Up -0Vote Down

    Playing with TokuDB updates I noticed in SHOW PROCESSLIST unsual for MySQL State.

    PLAIN TEXT CODE:
  • mysql> show processlist;
  • +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+
  • | Id | User | Host      | db     | Command | Time | State                     | Info                        |
  • +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+
  • |  3 | root | localhost | sbtest | Query   |   30 | Updated about 764000 rows | update sbtest set email=zip |
  •   [Read more...]
    webinar on Data Reduction and Smoothing in MySQL
    Employee +0 Vote Up -0Vote Down

    If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

    On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL (http://www.mysql.com/news-and-events/web-seminars/display-361.html).

    Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

    In addition to being practical, Michael's advice is justified by rigorous statistical analysis, and the tips he provides

      [Read more...]
    webinar on Data Reduction and Smoothing in MySQL
    Employee +0 Vote Up -0Vote Down

    If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

    On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL (http://www.mysql.com/news-and-events/web-seminars/display-361.html).

    Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

    In addition to being practical, Michael's advice is justified by rigorous statistical analysis, and the tips he

      [Read more...]
    webinar on Data Reduction and Smoothing in MySQL
    Employee +0 Vote Up -0Vote Down

    If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

    On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL (http://www.mysql.com/news-and-events/web-seminars/display-361.html).

    Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

    In addition to being practical, Michael's advice is justified by rigorous statistical analysis, and the tips he

      [Read more...]
    A rule of thumb for choosing column order in indexes
    +1 Vote Up -0Vote Down

    I wanted to share a little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it's generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake of simplicity.

    Let's start with this query, which returns zero rows but does a full table scan. EXPLAIN says there are no possible_keys.

    PLAIN TEXT SQL:
  • SELECT * FROM tbl WHERE STATUS='waiting' AND source='twitter'
  •  AND no_send_before <= '2009-05-28 03:17:50' AND tries <= 20
  •  ORDER BY date ASC LIMIT 1;
  • Don't try to figure out the meaning of the query, because that'll

      [Read more...]
    Using netcat to copy MySQL Database
    +2 Vote Up -0Vote Down

    This is mainly a cheat sheet for me to remember. Nothing rocket science.

    It often makes sense to use netcat/nc to copy MySQL database between hosts in trusted networks. It bypasses encryption overhead of SSH and depending on configuration can be significantly faster.

    Also note MySQL should be down when you copy data unless you're copying from snapshot etc.

    So to copy go to the mysql data directory on both boxes; such as cd /var/lib/mysql . Make sure target directory is empty. Now on the TARGET server do nc -l 4000 | tar xvf - and on the SOURCE server do tar -cf - . | nc target_ip 4000

    Also note - the port you're using should be open in the firewall.


    Entry posted by peter |

      [Read more...]
    Designing Views With Query Builder
    +0 Vote Up -0Vote Down

    If you often need to create and modify views in your MySQL development you will like dbForge Studio for MySQL in-place query editing feature. This feature allows integration of powerful Query Builder tool with view editor without annoying copy/paste.

    Suppose you have previously created view with some SELECT statement. To view and design this statement with Query Builder you need to perform these steps:

  • Open view editor.
  • Right-click on SELECT statement.
  • In pop-up menu select ‘Design SQL…’ command.
  • In opened Query Builder re-design you SELECT statement (See picture below).
  • After you click OK new statement automatically gets pasted into the view  editor.
  • If you need to design new SELECT statement when you are creating view use

      [Read more...]
    Mass killing of MySQL Connections
    +0 Vote Up -1Vote Down

    Every so often I run into situation when I need to kill a lot of connections on MySQL server - for example hundreds of instances of some bad query is running making server unusable. Many people have special scripts which can take the user, source host or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:

    PLAIN TEXT SQL:
  • mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root';
  • +------------------------+
  • | concat('KILL ',id,';') |
  • +------------------------+
  • | KILL 3101;             |
  • | KILL 2946;             |
  •   [Read more...]
    Hint: throttling xtrabackup
    +0 Vote Up -0Vote Down

    Using xtrabackup for copying files can really saturate your disks, and that why we made special option --throttle=rate to limit rate of IO per second. But it really works when you do local copy.
    What about stream backup ? Even you copy just to remote box with
    innobackupex --stream=tar | ssh remotebox "tar xfi -"
    , read may be so intensive so your mysqld feels impact, slave getting behind, etc...

    For this there is a nice small utility - pv .

    With pv you run:
    innobackupex --stream=tar | pv -q -L10m | ssh remotebox "tar xfi -"

    and it will limit channel rate to 10 M per second.


    Entry posted by Vadim | No





      [Read more...]
    What time 18446744073709550.000 means
    +1 Vote Up -0Vote Down

    Sometimes when you do profiling you can see number like this in timestamps. Periodically in our patches there was Query_time: 18446744073709550.000 in slow.log file (well, it was fixed recently, but still appears in other places).

    I faced this problem several years ago when only 2-core AMD Opteron systems appeared and I noticed sysbench getting crazy showing query execution time 18446744073709550.000 or like this.

    Obviously this is unsigned integer which was received by subtraction bigger number from smaller.
    But how it can be if we use:

    start_time = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp);
    ...
    query_execution
    ...
    end_time = clock_gettime(CLOCK_THREAD_CPUTIME_ID, &tp);

    total_time = end_time - start_time;

    How we can get query executed in negative time ? It would be too good to be real






      [Read more...]
    Why MySQL’s binlog-do-db option is dangerous
    +2 Vote Up -0Vote Down

    I see a lot of people filtering replication with binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db. Although there are uses for these, they are dangerous and in my opinion, they are overused. For many cases, there's a safer alternative.

    The danger is simple: they don't work the way you think they do. Consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn't exist on the slave) isn't replicated. (I'll come back to this in a second, so if you already see the problem, don't rush to the comment form.)

    Now you do the following:

    PLAIN TEXT CODE:
  • $ mysql
  • mysql> delete from garbage.junk;
  • mysql> use garbage;
  •   [Read more...]
    How to reduce Internet traffic when connecting to remote host?
    +0 Vote Up -0Vote Down

    Recently we received a letter from our customer who was unsatisfied with the amount of Internet traffic that dbForge Studio for MySQL produced when he was working with the remote MySQL server via VPN connection.

    Here are two tips on how you can reduce your traffic in version 3.50.

    First tip: Turn off code completion

    SQL editor code completion feature requires all object names from all databases to be queried from server. This is no problem when working with MySQL server on local network but it takes time on remote connection. To turn off loading completions from database go to Tools->Options->Text Editor->Code Completion option tab and turn off option ‘Load completions from database’.

    Second tip: Use compression

    Version 3.50 of dbForge Studio for MySQL contains database connection

      [Read more...]
    Btw xtrabackup is not only backup..
    +3 Vote Up -0Vote Down

    It is obvious thing for me, but I just figured out it may be not common understanding. Xtrabackup is also can be used (not only can, but we actually use it this way) to clone one slave to another, or just setup new slave from the master. And it is done in almost non-blocking way ( true for InnoDB setups) for cloned server. Here is command

    PLAIN TEXT CODE:
  • innobackupex-1.5.1 --stream=tar /tmp/--slave-info | ssh user@DESTSERVER "tar xfzi - -C /DESTDIR"
  • When it finished on destination server you run

    PLAIN TEXT CODE:
  • innobackupex-1.5.1 --apply-log --use-memory=2G  /DESTDIR
  • And you have ready database directory,

      [Read more...]
    Interesting Programmer Links
    +0 Vote Up -0Vote Down
    • Peeping into memcached.
    • Really interesting read about how to examine what’s stored in memcached.

      Peep uses ptrace to freeze a running memcached server, dump the internal key metadata, and return the server to a running state. If you have a good host ejection mechanism in your client, such as in the Twitter libmemcached builds, you won’t even have to change the production server pool. The instance is not restarted, and no data is lost.

    • Quick look at MySQL 5.4
    • Highlights include scalability improvements, subquery optimizations and join improvements, improved stored procedure management, out parameters in prepared statements, and new information schema additions.

      [Read more...]
    How to decrease InnoDB shutdown times
    +0 Vote Up -0Vote Down

    Sometimes a MySQL server running InnoDB takes a long time to shut down. The usual culprit is flushing dirty pages from the buffer pool. These are pages that have been modified in memory, but not on disk.

    If you kill the server before it finishes this process, it will just go through the recovery phase on startup, which can be even slower in stock InnoDB than the shutdown process, for a variety of reasons.

    One way to decrease the shutdown time is to pre-flush the dirty pages, like this:

    PLAIN TEXT CODE:
  • mysql> set global innodb_max_dirty_pages_pct = 0;
  • Now run the following command:

      [Read more...]
    PROCEDURE ANALYSE
    +1 Vote Up -0Vote Down

    Quite common task during schema review is to find the optimal data type for the column value - for example column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Does it contain any NULLs or it can be defined NOT NULL which reduces space needed and speeds up processing in most cases ?

    These and similar tasks are often done by bunch of SQL queries while really MySQL has a native feature to perform this task - PROCEDURE ANALYSE

    Here is sample run of PROCEDURE ANALYSE on drupal schema of Percona web site. The output contains row for each column in the table but I've omitted everything by few rows.

    PLAIN TEXT SQL:
  • mysql>
  •   [Read more...]
    MySQL random freezes could be the query cache
    +0 Vote Up -0Vote Down

    I feel like I've been seeing this a lot lately.

    occasionally, seemingly innocuous selects take unacceptably long.

    Or

    Over the past few weeks, we've been having bizarre outages during which everything seems to grind to a halt... and then fixes itself within 5 minutes. We've got plenty of memory, we're not running into swap, and we can't find any queries that would seem to trigger outages: just tons of simple session requests all hung up for no obvious reason.

    Problems like this are always hard to debug. If it happens twice a week for 5 minutes at a time, your chance of getting someone logged onto the machine to watch it in action are pretty slim. And of course, when they do look at it, they see nothing wrong on the surface; it takes some very clever, very fast work with OS-level

      [Read more...]
    Converting Character Sets
    +0 Vote Up -0Vote Down

    The web is going the way of utf8.  Drizzle has chosen it as the default character set, most back-ends to websites use it to store text data, and those who are still using latin1 have begun to migrate their databases to utf8.  Googling for "mysql convert charset to utf8" results in a plethora of sites, each with a slightly different approach, and each broken in some respect.  I'll outline those approaches here and show why they don't work, and then present a script that can generically be used to convert a database (or set of tables) to a target character set and collation.

    Approach #1:

    PLAIN TEXT SQL:
  • ALTER TABLE `t1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
  • Take the following table as an example why this approach will not

      [Read more...]
    The perils of InnoDB with Debian and startup scripts
    +0 Vote Up -0Vote Down

    Are you running MySQL on Debian or Ubuntu with InnoDB? You might want to disable /etc/mysql/debian-start. When you run /etc/init.d/mysql start it runs this script, which runs mysqlcheck, which can destroy performance.

    It can happen on a server with MyISAM tables, if there are enough tables, but it is far worse on InnoDB. There are a few reasons why this happens -- access to open an InnoDB table is serialized by a mutex, for one thing, and the mysqlcheck script opens all tables. One at a time.

    It's pretty easy to get into a "perfect storm" scenario. For example, I'm working with one client right now who has a hosted multi-tenanting application that keeps each customer in its own database. So they have a lot of databases and a lot of tables. And they're running on Amazon EC2 with 8G of RAM and EBS storage, which is slower

      [Read more...]
    Optimizing repeated subexpressions in MySQL
    +0 Vote Up -0Vote Down

    How smart is the MySQL optimizer? If it sees an expression repeated many times, does it realize they're all the same and not calculate the result for each of them?

    I had a specific case where I needed to find out for sure, so I made a little benchmark. The query looks something like this:

    PLAIN TEXT SQL:
  • SELECT sql_no_cache
  •    pow(sum(rental_id), 1),
  •    pow(sum(rental_id), 2),
  •    pow(sum(rental_id), 3),
  •    pow(sum(rental_id), 4),
  •    pow(sum(rental_id), 5),
  •    pow(sum(rental_id), 6),
  •  
  •   [Read more...]
    ActiveMQ Tips: Flow Control and Stalled Producers Problem
    +0 Vote Up -0Vote Down

    It’s been a few months since we‘ve started actively using ActiveMQ queue server in our project. For some time we had pretty weird problems with it and even started thinking about switching to something else or even writing our own queue server which would comply with our requirements. The most annoying problem was the following: some time after activemq restart everything worked really well and then activemq started lagging, queue started growing and all producer processes were stalling on push() operations. We rewrote our producers from Ruby to JRuby, then to Java and still – after some time everything was in a bad shape until we restarted the queue server.

    So, long story short, after a lots of docs and source code reading we’ve found really interesting thing. There is a

      [Read more...]
    Profiling MySQL stored routines
    +0 Vote Up -0Vote Down

    These days I'm working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven't worked much with stored procedures, I though it's going to be a piece of cake. In the end - it was, but there's a catch.

    My initial idea was - I'll just analyze queries in the slow query log generated by our mysql build running with long_query_time=0, get the slowest ones and work on them. It wasn't really all the way I expected..

    For a showcase I have created a function "whatstheweatherlike". Let's call it and see what shows up in the slow query log:

    PLAIN TEXT SQL:
  • mysql> SELECT whatstheweatherlike(5);
  •   [Read more...]
    Dropping unused indexes
    +0 Vote Up -0Vote Down

    Vadim wrote some time ago about how to find unused indexes with single query.

    I was working on the system today and found hundreds of unused indexes on dozens of tables so just dropping indexes manually did not look fun. So I extended Vadim's query to generate ALTER TABLE statements automatically. I also made it to look only at tables which were accessed:

    PLAIN TEXT SQL:
  • mysql> SELECT concat('alter table ',d.table_schema,'.',d.table_name,' drop index ',group_concat(index_name separator ',drop index '),';') stmt FROM (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics
  •   [Read more...]
    High-Performance Click Analysis with MySQL
    +0 Vote Up -0Vote Down

    We have a lot of customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work.  The first thing these have in common is that they're generally some kind of loggable event.

    The next characteristic of a lot of these systems (real or planned) is the desire for "real-time" analysis.  Our customers often want their systems to provide the freshest data to their own clients, with no delays.

    Finally, the analysis is usually multi-dimensional.  The typical user wants to be able to generate summaries and reports in many different ways on demand, often to support the functionality of the application as well as to provide reports to their clients.  Clicks by day, by customer, top ads by clicks, top ads by click-through ratio, and so on for dozens of different types

      [Read more...]
    A quick way to get memcached status
    +0 Vote Up -0Vote Down

    There are all sorts of different interfaces to memcached, but you don't need any of them to make requests from the command line, because its protocol is so simple. Try this, assuming it's running on the usual port on the local machine:

    PLAIN TEXT CODE:
  • echo stats | nc 127.0.0.1 11211
  • STAT pid 22020
  • STAT uptime 3689364
  • STAT time 1227753109
  • STAT version 1.2.5
  • STAT pointer_size 64
  • STAT rusage_user 4543.071348
  • STAT rusage_system 8568.293421
  • STAT curr_items 139897
  • STAT total_items 51710845
  • STAT bytes 360147055
  • STAT curr_connections 40
  • STAT total_connections 66762
  • STAT connection_structures 327
  • STAT cmd_get
  •   [Read more...]
    Poor man’s query logging
    +0 Vote Up -0Vote Down

    Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with --log (or --general-log in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by --log-slow-queries parameter (MySQL 5.1 requires also --slow-query-log), which was designed to store poorly performing queries that run at least 2 seconds. Percona actually extended the slow log to, among others, include any query regardless of the execution time.

    The problem is that for both you need to prepare earlier either by enabling the logging before starting the database instance or, even more work, by applying the patch and

      [Read more...]
    Living with backups
    +0 Vote Up -0Vote Down

    Everyone does backups. Usually it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files from the data directory to a safe location. Obviously both ways involve much more complex operations than it would seem by my last sentence, but it is not important right now. Either way the data is out and ready to save someone’s life (or job at least). Unfortunately taking backup does not come free of any cost. On the contrary, it’s more like doing very heavy queries against each table in the database when mysqldump is used or reading a lot of data when copying physical files, so the price may actually be rather high. And the more effectively the server resources are utilized, the more that becomes a problem.

    What happens when you try to get all the data?

    The most obvious answer is that it needs to be read,

      [Read more...]
    MySQL, AIX5L and malloc()
    +2 Vote Up -0Vote Down

    Some time ago I get brand new IBM POWER6 server as the replacement for "old" P5 used to host Oracle database. Because we planed to use advanced virtualization with VIOS + LPAR/DLPAR I conceived the idea to use one spare partition for MySQL tests. Because I had no past experience with it and there is not much documentation all around the web, I tried to set-up system and database traditional way. The first problem I hit was memory allocation and I think it is the best place share my remarks. Let's start from the beginning..

    For any reason, you decided to run MySQL database on AIX 5L operating system. You compiled it successfully, configured and.. unluckily database didn't start due to memory allocation problem?

    Basically, for some historical reasons AIX OS will allow your application to allocate maximum 256MB of memory per process by default. To use more, you have

      [Read more...]
    A quest for the full InnoDB status
    +0 Vote Up -0Vote Down

    When running InnoDB you are able to dig into the engine internals, look at various gauges and counters, see past deadlocks and the list of all open transactions. This is in your reach with one simple command -- SHOW ENGINE InnoDB STATUS. On most occasions it works beautifully. The problems appear when you have a large spike in number of connections to MySQL, which often happens when several transactions kill the database performance resulting in very long execution times for even simplest queries, or a huge deadlock.

    In such rare cases SHOW ENGINE InnoDB STATUS often fails to provide the necessary information. The reason is that its output is limited to 64000 bytes, so a long list of transactions or a large deadlock dump may easily exhaust the limit. MySQL in such situation truncates the output so it fits the required size and obviously this is not

      [Read more...]
    Previous 30 Newer Entries Showing entries 91 to 120 of 131 Next 11 Older Entries

    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.