Oct 05 2011

mysqldump: do not forget to routines!

Tag: MySQLJens @ 23:45

For creating MySQL backups you can easily use utility mysqldump. However, this utility don’t dump stored procedures and functions by default. With using parameter --routines, stored routines (procedures and functions created by CREATE PROCEDURE and CREATE FUNCTION) will be included into database dump. This behavior of mysqldump is quite confusing, because TRIGGERS are included in database dump by default.

So, if you are using stored procedures and functions, do not forget to update your commands like this:

mysqldump --routines [other parameters] > [database dump file]

For more information, see mysqldump manual pages :


Mar 02 2010

Compiling SysBench on Debian Lenny

Tag: Debian,English,HW Tunning,Linux,MySQLJens @ 23:00

SysBench is a system performance benchmark primarily written for MySQL server benchmarking. In actual version 0.4.12, SysBench supports different benchmark modes: file I/O performance, CPU and scheduler performance, memory allocation and transfer speed, threads implementation performance and database server performance. It’s widely used on Linux machines, especially for identifying bottlenecks on database servers (with using file I/O performance and MySQL database support).

There are no exists pre-compiled binary package on current stable Debian (Lenny). If you want to use SysBench on this version, there are at least two different options:

  1. use pre-compiled package from unstable (Squeeze) version,
  2. compile itself from source SysBench package.

With the first option, there are two problems, why it is not a good idea: 1) mixing stable and unstable packages and 2) unstable Debian package don’t contains last version of SysBench (currently contains version 0.4.10-1).

In compilation SysBench from source, they may occurs some problems:

  • Wrong machine/OS detection in compile configuration phase:
    # ./configure
    checking build system type... Invalid configuration `x86_64-unknown-linux-': machine `x86_64-unknown-linux' not recognized
    configure: error: /bin/sh config/config.sub x86_64-unknown-linux- failed
  • Unreachable binary mysql_configor undetectable location of MySQL libraries, which are required for SysBench MySQL support:
    ********************************************************************************
    ERROR: cannot find MySQL libraries. If you want to compile with MySQL support,
           you must either specify file locations explicitly using
           --with-mysql-includes and --with-mysql-libs options, or make sure path to
           mysql_config is listed in your PATH environment variable. If you want to
           disable MySQL support, use --without-mysql option.
    ********************************************************************************
    This could be easy solved with installing libmysqlclient15-dev debian package.
  • libtoolerror in compilation progress
    # make
    ... some lines cropped ...
    ../libtool: line 838: X--tag=CC: command not found
    ../libtool: line 871: libtool: ignoring unknown tag : command not found
    ../libtool: line 838: X--mode=link: command not found
    ../libtool: line 1004: *** Warning: inferring the mode of operation is deprecated.: command not found
    ../libtool: line 1005: *** Future versions of Libtool will require --mode=MODE be specified.: command not found
    ../libtool: line 2231: X-g: command not found
    ../libtool: line 2231: X-O2: command not found
    ../libtool: line 1951: X-L/usr/lib/mysql: No such file or directory
    ../libtool: line 2400: Xsysbench: command not found
    ../libtool: line 2405: X: command not found
    ../libtool: line 2412: Xsysbench: command not found
    ../libtool: line 2420: mkdir /.libs: No such file or directory
    ../libtool: line 2547: X-lmysqlclient_r: command not found
    ../libtool: line 2547: X-lrt: command not found
    ../libtool: line 2547: X-lm: command not found
    ../libtool: line 2629: X-L/root/sysbench-0.4.12/sysbench: No such file or directory
    ../libtool: line 2547: X-lmysqlclient_r: command not found
    ../libtool: line 2547: X-lrt: command not found
    ../libtool: line 2547: X-lm: command not found
    ../libtool: line 2629: X-L/root/sysbench-0.4.12/sysbench: No such file or directory
    ../libtool: line 2547: X-lmysqlclient_r: command not found
    ../libtool: line 2547: X-lrt: command not found
    ../libtool: line 2547: X-lm: command not found
    ../libtool: line 5162: Xgcc -pthread "" "" -o @OUTPUT@ sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o  tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/oltp/libsboltp.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -L/root/sysbench-0.4.12/sysbench -lmysqlclient_r -lrt -lm: No such file or directory
    ../libtool: line 5163: Xgcc -pthread "" "" -o @OUTPUT@ sysbench.o sb_timer.o sb_options.o sb_logger.o db_driver.o  tests/fileio/libsbfileio.a tests/threads/libsbthreads.a tests/memory/libsbmemory.a tests/cpu/libsbcpu.a tests/oltp/libsboltp.a tests/mutex/libsbmutex.a drivers/mysql/libsbmysql.a -L/root/sysbench-0.4.12/sysbench -lmysqlclient_r -lrt -lm: No such file or directory
    ../libtool: line 5168: X: command not found
    ../libtool: line 5172: : command not found
    ... some lines cropped ...

Compilation step-by-step

Prerequisites: gcc, make and automake for compilation, mysql-server and libmysqlclient15-dev for MySQL support.

# apt-get install gcc make automake mysql-server libmysqlclient15-dev

Obtaining source code (actual version may differ) and extract archive:

# wget http://downloads.sourceforge.net/project/sysbench/sysbench/0.4.12/sysbench-0.4.12.tar.gz
# tar xfz sysbench-0.4.12.tar.gz
# cd sysbench-0.4.12

Fixing wrong machine/OS detection in configuration phase: edit file config/config.sub and before line line 312 (or line, where is comment: # Object if more than one company name word.) add following lines (with respect your machine/distro, in our case x86_64-unknown-linux; ensured from error message).

x86_64-unknown-linux)
    basic_machine=x86_64-pc
    os=-linux
    ;;

Fixing libtool error via replacing ranlib in file configure.ca, at line 75 (change AC_PROG_LIBTOOL to AC_PROG_RANLIB) and recreate all automake utilities with autogen:

# sed -i configure.ac -e 's/AC_PROG_LIBTOOL/AC_PROG_RANLIB/'
# ./autogen.sh

Now, you should run classical compilation progress:

./configure
make
make install

That’s all, sysbench binary is created in sysbench directory (sysbench/sysbench), you can copy it into some PATH folder and use it as you wish:

# cp sysbench/sysbench /usr/bin/sysbench

Some useful links


Nov 23 2009

MySQL: Hromadná kontrola tabulek

Tag: Debian,MySQLJens @ 10:00

V aktuální old-stable (Etch) a vyšších verzí debianu se při spouštění MySQL 5.x automaticky provádí kontrola tabulek (CHECK TABLE) a případně i oprava (REPAIR TABLE) poškozených tabulek. Toto má nastarosti skript /etc/mysql/debian-start a funkce check_for_crashed_tables (definovaná v /usr/share/mysql/debian-start.inc.sh). Občas je ale potřeba explicitně prověřit všechny databáze a tabulky, to lze nejjednodušeji udělat z příkazové řádku pomocí utility mysqlcheck:

/usr/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf --verbose --all-databases

Konfigurační soubor /etc/mysql/debian.cnf je přístupný pouze pro roota (tedy výše zmíněný příkaz lze spustit pouze pod rootem) a jsou v něm uloženy autentizační údaje pro systémový učet Debianu (debian-sys-maint) do MySQL.


Aug 06 2009

Upgrade databáze a použití LAST_INSERT_ID()

Tag: MySQL,PHP,Zend FrameworkJens @ 10:40

Při vytváření nových verzí/revizí nějaké aplikace (ať již pomocí subversion či jiného nástroje) je často vhodné a někdy i potřebné aktualizovat strukturu databáze (přidání nových tabulek, sloupců, atd.) případně aktualizovat data (číselníky, systémové hodnoty) v databázi tak, aby nová změna v kódu byla kompatibilní se změnou struktury či dat v databázi (pro úplnost pouze doplním, že článek je napsán pro databázi MySQL 5.x).

Otázka je, jak tuto změnu provést tak, aby jsme při aktualizaci projektu na příslušnou revizi měli k dispozici i aktuální verzi databáze. Jedním z jednodušších řešení je, že spolu s commitem upraveného kódu (například nějakého modelu) commitneme i skript, který po aktualizaci projektu na příslušnou revizi spustíme a tím zajistíme upgrade databáze.

Tento skript může mít různé podoby, buď ho můžeme napsat přímo v PHP a nebo v případě jednodušší aktualizace použijeme pouze několik SQL příkazů. Nedávno jsem zrovna řešil poměrně jednoduchý problém, bylo třeba přidat skript do databáze šablonu i s jednou jazykovou mutací — to znamená: přidat jeden řádek do tabulky sablona a jeden řádek do tabulky sablona_mutace. Jelikož je však při definici ID šablon i mutací použit extra typ AUTO_INCREMENT, tak vložení druhého řádku s mutací bude záviset na ID vloženého řádku šablony. V SQL skriptu nemůžu přímo ID definovat, protože v době kdy se bude skript (upgrade) provádět může mnou vybrané ID šablony už být použito, proto je potřeba nechat sloupec NULL a MySQL vloží vlastní ID dle hodnoty AUTO_INCREMENT tabulky.

Řešením tohoto problému je použití funkce LAST_INSERT_ID(), po vložení řádku šablony definujeme cizí klíč ID šablony v řádku mutace právě pomocí této funkce. Výsledek by pak mohl vypadat nějak takto:

INSERT INTO sablona (id, nazev, kategorie, datum)
    VALUES (NULL, 'Neuskutečněná schůzka', 'system', NOW());

INSERT INTO sablona_mutace (id, sablona_id, jazyk_id, telo)
   VALUES (NULL, LAST_INSERT_ID(), 1, 'Schůzka ne neuskutečnila ...');

Takto jednouše by mohl vypada SQL skript, PHP skript kterým by jste udělali to samé například v Zend_Frameworku by mohl vypadat přibližně takto:

...
$sablona = array(
  'nazev' => 'Neuskutečněná schůzka',
  'kategorie' => 'system',
  'datum' => new Zend_Db_Expr('NOW()'),
);
$sablona_id = Sablona::getInstance()->insert($sablona);

$mutace = array(
  'sablona_id' => $sablona_id,
  'jazyk_id' => 1,
  'telo' => 'Schůzka ne neuskutečnila ...',
);
$mutace_id = SablonaMutace::getInstance()->insert($mutace);
...

Poznámka k PHP příkladu: modely jsou potomky třídy Zend_Db_Table a jsou vytvořeny jako singleton; hodnoty sloupce kategorie a jazyk_id v příkladu jsou magic konstanty což není úplně programátorsky čisté — je to pouze pro jednoduchost příkladu.


Apr 09 2009

Seřazení tabulky pomocí ALTER TABLE

Tag: MySQLJens @ 22:00

Kolega mě nedávno upozornil na zajímavé použít příkazu ALTER TABLE v MySQL. Jedná o přeřazení tabulky pomocí zvoleného sloupce (nebo sloupců). Nejdříve jsem si říkal, na co to asi může být užitečné, ale když už jsem to několikrát použil, tak jsem zjistil že takovou funkci může být velmi užitečná.

ALTER TABLE table_name ORDER BY col_name [, col_name] ...

V MySQL je řazení jeden ze zásadních bottlenecků. Často je samotný dotaz velmi rychlý, přidáte-li řazení, rychlost jde exponenciálně dolů. Částečně tomu lze pomoci vytvoření správného indexu. Ale ani to nemusí být vždy ideální řešení – indexy vám nepomůžou například v případě řazení podle více sloupců a kombinaci ASC a DESC (více viz nepoužití indexů při řazení). V některých případech pak lze s úspěchem použít ALTERT TABLE … ORDER BY ….

Konkrétní příklad použití

Mějme tabulku články, články se většinou na stránku vypisují podle datumu (dne uveřejnění) sestupně, pokud je datum shodný, tak články seřadíme podle primárního klíče (id) vzestupně. V tabulce články máme cca 118 tisíc řádků.

0) Dotaz na články bez řazení:

mysql> SELECT SQL_NO_CACHE id, datum FROM clanky LIMIT 10;
+------+---------------------+
| id   | datum               |
+------+---------------------+
| 8211 | 2007-10-14 15:00:00 |
| 8213 | 2007-10-14 15:00:00 |
| 8218 | 2007-10-14 15:00:00 |
| 8220 | 2007-10-14 15:00:00 |
| 8231 | 2007-10-14 15:00:00 |
| 8237 | 2007-10-14 19:16:22 |
| 8240 | 2007-10-14 19:40:28 |
| 8241 | 2007-10-14 19:45:01 |
| 8242 | 2007-10-14 19:48:34 |
| 8245 | 2007-10-14 18:57:17 |
+------+---------------------+
10 rows in set (0.09 sec)

Výchozí řazení tabulky je podle toho, jak řádky vkládáme, tedy vetšinou podle primárního klíče.

1) Řazení bez použít klíčů:

mysql> SELECT SQL_NO_CACHE id, datum FROM clanky ORDER BY datum DESC, id LIMIT 10;
+--------+---------------------+
| id     | datum               |
+--------+---------------------+
| 175364 | 2038-01-19 03:14:08 |
| 176782 | 2038-01-19 03:14:08 |
| 178837 | 2038-01-19 03:14:08 |
| 178838 | 2038-01-19 03:14:08 |
| 181779 | 2038-01-19 03:14:08 |
| 191940 | 2038-01-19 03:14:08 |
| 215340 | 2009-04-09 19:02:39 |
| 215339 | 2009-04-09 19:02:33 |
| 215338 | 2009-04-09 19:02:31 |
| 215337 | 2009-04-09 19:02:29 |
+--------+---------------------+
10 rows in set (0.21 sec)

2) Řazení s použitím klíče:

mysql> CREATE INDEX razeni ON clanky (datum DESC, id);
Query OK, 118970 rows affected (1.24 sec)
Records: 118970  Duplicates: 0  Warnings: 0
mysql> OPTIMIZE TABLE clanky;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| bench.clanky | optimize | status   | OK       |
+--------------+----------+----------+----------+
1 row in set (0.05 sec)
mysql> SELECT SQL_NO_CACHE id, datum FROM clanky ORDER BY datum DESC, id LIMIT 10;
+--------+---------------------+
| id     | datum               |
+--------+---------------------+
| 175364 | 2038-01-19 03:14:08 |
| 176782 | 2038-01-19 03:14:08 |
| 178837 | 2038-01-19 03:14:08 |
| 178838 | 2038-01-19 03:14:08 |
| 181779 | 2038-01-19 03:14:08 |
| 191940 | 2038-01-19 03:14:08 |
| 215340 | 2009-04-09 19:02:39 |
| 215339 | 2009-04-09 19:02:33 |
| 215338 | 2009-04-09 19:02:31 |
| 215337 | 2009-04-09 19:02:29 |
+--------+---------------------+
10 rows in set (0.20 sec)

Výsledek při přidání klíče v tomto případě nemá téměř žádný vliv, při kombinace ASC a DESC řazení MySQL klíče nezohledňuje.

3) Použít řazení pomocí seřazené tabulky:

mysql> DROP INDEX razeni ON clanky;
Query OK, 118970 rows affected (1.03 sec)
Records: 118970  Duplicates: 0  Warnings: 0

mysql> OPTIMIZE TABLE clanky;
+--------------+----------+----------+----------+
| Table        | Op       | Msg_type | Msg_text |
+--------------+----------+----------+----------+
| bench.clanky | optimize | status   | OK       |
+--------------+----------+----------+----------+
1 row in set (0.02 sec)

mysql> ALTER TABLE clanky ORDER BY datum DESC, id;
Query OK, 118970 rows affected (1.45 sec)
Records: 118970  Duplicates: 0  Warnings: 0

mysql> SELECT SQL_NO_CACHE id, datum FROM clanky LIMIT 10;
+--------+---------------------+
| id     | datum               |
+--------+---------------------+
| 175364 | 2038-01-19 03:14:08 |
| 176782 | 2038-01-19 03:14:08 |
| 178837 | 2038-01-19 03:14:08 |
| 178838 | 2038-01-19 03:14:08 |
| 181779 | 2038-01-19 03:14:08 |
| 191940 | 2038-01-19 03:14:08 |
| 215340 | 2009-04-09 19:02:39 |
| 215339 | 2009-04-09 19:02:33 |
| 215338 | 2009-04-09 19:02:31 |
| 215337 | 2009-04-09 19:02:29 |
+--------+---------------------+
10 rows in set (0.10 sec)

Zde je vidět dvojnásobný nárůst rychlosti. Navíc, mám experimentálně ověřeno, že pokud vybíráte více sloupců (né tedy jen id a datum), tak se rychlost řazeného výsledku dramaticky propadá. To proto, že řazení probíhá v sort bufferu v paměti, velikost tohoto bufferu definuje proměnná sort_buffer_size v nastavení MySQL. V momentě kdy řadíte velké výsledky, buffer “přeteče” a řazení se začne odehrávat na disku — viz algoritmus filesort v MySQL — což celý proces dále zpomaluje.

Jediný vážný problém tohoto způsobu využití seřazení tabulky je ten, že v případě vložení nebo aktualizace řádků se “seřazenost” tabulky nezachová. Dále pak tento princip nefunguje na InnoDB typy tabulek. I přes tyto nevýhody lze tuto vlastnost MySQL často s úspěchem využít a zefektivnit tak některé dotazy.

Zajímavé odkazy k tématu:


Jun 02 2008

Nulový počet řádků v MySQL

Tag: MySQLJens @ 19:01

Jak udělat co nejjednodušší dotaz v MySQL tak, aby vrátil vždy nulový počet řádků? Je to blbost, ale někdy se to může hodit — spoléháte-li např. v PHP na výsledek dotazu: if ( $res->fetchRow() ) nebo počet řádků výsledků: $res->rowCount()

Můžete použít něco takového:

mysql> SELECT 0 LIMIT 0;
Empty set (0.00 sec)

jednoduché (nepotřebujete žádnou tabulku ani funkci), rychlé** a v určitých případech užitečné :) Možná to jde i jednodušeji, pokud ano, pište jak na to do komentářů, rád se přiučím.

**Benchmark (IBM ThinkPad T41 – Intel(R) Pentium(R) M processor 1400MHz)

mysql> SELECT BENCHMARK(1000000,(SELECT 0 LIMIT 0));
+---------------------------------------+
| BENCHMARK(1000000,(SELECT 0 LIMIT 0)) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.04 sec)

Feb 06 2008

Změna pořadí sloupců v MySQL

Tag: MySQLJens @ 11:46

Při upravě tabulek pomocí jinak výborného nástroje phpMyAdmin jsem zjistil, že nemá funkci pro změnu pořadí sloupců tabulky. Proto jsem musel hledat syntaxi ALTER TABLE v manuálu MySQL.

Pokračování článku…