Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 10 Newer Entries Showing entries 91 to 100 of 133 Next 10 Older Entries

Displaying posts with tag: tips (reset)

Just do the math!
+2 Vote Up -0Vote Down

One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve.

Let me start with example to make it clear. Lets say you're doing some reports from your apache log files - how many distinct visitors hit the page and stuff like that. You picked full logs because they are great in flexibility - you can run any adhoc queries and drill down as much as you like. Initially traffic was small and young and with 10000 page views a day you few days of history the queries there instant which gave you a confidence this approach will work.

As the time passes and you get 1.000.000 events per day and looking to do reporting for up to the whole year worth of data you find things not

  [Read more...]
Replace or Rename: that is the question.
+0 Vote Up -1Vote Down

Forget tedious hours of searching links to the renamed object and replacing them.

To save your efforts, dbForge Studio for MySQL automates renaming of tables, columns of tables, views, aliases, stored routines, local variables, triggers, events, UDFs, and users and even offers a convenient preview of the changes before applying them.

To rename an object in the script, right-click it and select the Rename option from the menu. The Rename dialog box opens where you can enter a new name and view all references to the renamed object in the expressions, strings, and even comments.

All you have to do is to select required references and press

  [Read more...]
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...]
    Previous 10 Newer Entries Showing entries 91 to 100 of 133 Next 10 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.