Dataprix

February 2008

Preguntas y respuestas acerca de SQL: Índices no clúster y retención de permisos

El SQL recursivo es algo que muy pocos utilizan -sobre todo por mero y simple desconocimiento- pero que en determinadas ocasiones resulta ser una herramienta muy útil. Ya hemos visto ejemplos de ello en Oracle: calcular un factorial, convertir cadenas a tablas y tablas a cadenas, pero hay muchas otras tareas para las que el SQL recursivo puede ser el camino a seguir.

Como vimos, Oracle implementa la recursividad mediante la cláusula ‘CONNECT BY’, pero ¿existe algo así en Teradata? La respuesta es sí.

En Teradata la recursividad se implementa con una sintaxis que tiene por estructura algo así:

WITH RECURSIVE nombre_tabla (col1, col2, col3...) AS ( SELECT "RAIZ" UNION ALL SELECT "RECURSIVO" (que hace referencia al "RAIZ" por medio de nombre_tabla) ) SELECT "TOTAL" (sobre nombre_tabla)

El truco del asunto está en comprender que nombre_tabla es una tabla temporal. Dicha tabla se va llenando con los resultados del SELECT “RAIZ” PRIMERO, y luego con los del SELECT “RECURSIVO” en cada ejecución. Así, cada ejecución del SELECT “RECURSIVO” se realiza contra contra esta tabla temporal que tiene los resultados de la ejecución anterior. Esto se repite hasta que el SELECT “RECURSIVO” deja de retornar filas. Entonces se ejecuta el SELECT “TOTAL” contra la tabla temporal nombre_tabla.

Visto así es mucho más sencillo de lo que parecía al principio (¿o no?).

Para ver un ejemplo de cómo funciona, vamos a hacer algo parecido a lo que hicimos en Oracle para convertir filas a cadenas de caracteres, pero lo vamos a utilizar aquí para mostrar los índices de las tablas de una base de datos con las columnas que los componen en su orden de definición:

Teradata BTEQ 08.02.03.03 for WIN32. Copyright 1984-2006, NCR Corporation. ALL RIGHTS RESERVED. Enter your logon or BTEQ command: .LOGON DBNAME/yyyyyy .LOGON DDBNAME/yyyyyy Password: *** Logon successfully completed. *** Teradata Database Release is V2R.06.02.01.18 *** Teradata Database Version is 06.02.01.17 *** Transaction Semantics are BTET. *** Character Set Name is 'ASCII'. *** Total elapsed time was 3 seconds. WITH RECURSIVE JERARQUICA (DATABASENAME, TABLENAME, INDEXNAME, COLUMNNAME, COLUMNPOSITION) AS ( SELECT a.DATABASENAME, a.TABLENAME, COALESCE(a.INDEXNAME,'PI') INDEXNAME, CAST(TRIM(a.COLUMNNAME) AS VARCHAR(128)), a.COLUMNPOSITION FROM DBC.INDICES a WHERE a.DATABASENAME='DBNAME' AND a.COLUMNPOSITION = 1 UNION ALL SELECT a.DATABASENAME, a.TABLENAME, COALESCE(a.INDEXNAME,'PI') INDEXNAME, b.COLUMNNAME || ',' || TRIM(a.COLUMNNAME) COLUMNNAME, a.COLUMNPOSITION FROM DBC.INDICES a, JERARQUICA b WHERE a.DATABASENAME = b.DATABASENAME AND a.TABLENAME = b.TABLENAME AND COALESCE(a.INDEXNAME,'PI') = b.INDEXNAME AND a.COLUMNPOSITION = b.COLUMNPOSITION + 1 ) SELECT DATABASENAME, TABLENAME, INDEXNAME, MAX(COLUMNNAME) COLUMNAS FROM JERARQUICA GROUP BY DATABASENAME, TABLENAME, INDEXNAME ; DATABASENAME TABLENAME INDEXNAME COLUMNAS ------------ --------- --------- ----------------- DBNAME TABLE01 PI COL01 DBNAME TABLE02 PI COL01 DBNAME TABLE03 PI COL01,COL02,COL03 DBNAME TABLE04 PI COL01 DBNAME TABLE05 PI COL01 DBNAME TABLE06 PI COL01,COL02 DBNAME TABLE07 PI COL01,COL02,COL03 DBNAME TABLE08 PI COL01,COL02 DBNAME TABLE09 PI COL01,COL02,COL03 ...

Evidentemente los nombres de la base de datos, las tablas y las columnas se han ocultado, pero probad en vuestro entorno y veréis lo bien que funciona…

Saludos.

Carlos.

      

Sun ha sacado su contraparte a la HP Oracle Database Machine: Sun Data Warehouse Appliance, se trata de un “join venture” con Greenplum, un (no demasiado conocido, al menos por mí) software de base de datos de “Data Warehousing” que bebe, como Teradata, en los principios del “share nothing”.

Saludos.

Carlos.

      

Las herramientas de carga de Teradata ponen en una tabla filas con información de los registros (líneas) que han fallado (lo mismo hace Oracle con su sql*loader utilizando ficheros y ‘logs’).

En cualquier caso, muchas veces hay que ver qué esta fallando en la línea y, eventualmente, editarla para corregir los errores.

Esto se puede hacer fácilmente con un mero editor de texto… si el fichero no es muy grande, claro. Pero ocurre que actualmente estoy tabajando con herramientas Teradata (fastload, multiload) para cargar en la base de datos ficheros de 15 o 20 Gb.

Por supuesto, ni intentar abrir un monstruo de estos con un editor.

Otra posibilidad es hacer algo así:

$ head nombre_fichero -n numero_línea | tail -n 1

Pero intentad hacer:

$ head nombre_fichero -n 182358216 | tail -n 1

y veréis lo que pasa.

Así que pensando, pensando, se me ocurrió recurrir al temido awk (¡awk!¡Qué miedo!). Pero no es para tanto.
Un poco de lectura del ‘man’, documentación, etc, etc y terminé discurriendo esto:

$ awk 'NR == numero_línea {print}' nombre_fichero

Los resultados son mucho más rápidos que con head/tail. Así, para un fichero con 188660096 líneas que tenía cuatro ‘pochas’ hice :

$ awk 'NR == 182358216 {print}' fichero_grande.txt > fichero_grande_bad.txt $ awk 'NR == 103504305 {print}' fichero_grande.txt >> fichero_grande_bad.txt $ awk 'NR == 165070071 {print}' fichero_grande.txt >> fichero_grande_bad.txt $ awk 'NR == 182358218 {print}' fichero_grande.txt >> fichero_grande_bad.txt

y en pocos minutos tenía las cuatro líneas en un fichero a mi disposición.

Y lo mejor: ¡¡ejecutado con (el para mí imprescindible) cygwin en un entorno Windows!!

Saludos.

Carlos.

      

Esta es vieja: se trata de calcular la edad basándose en dos fechas dadas (p.ej.: entre “hoy” y la fecha de nacimiento).

En principio la cosa parece fácil: hacer substracciones de las fechas basadas en los días o, mejor, en los meses. Pero la cosa se vuelve un poco más oscura cuando vemos que no todos los meses tienen el mismo número de días (28, 30, 31 incluso 29 en años bisiestos)

Oracle elimina estos engorros mediante una muy útil función: “MONTHS_BETWEEN”, que acepta dos fechas, y devuelve el número de meses entre ambas. La lógica de la función y la aritmética de fechas de Oracle se enargan de todos los cálculos necesarios para devolver la solución deseada (aunque hay que haber actualizado al menos a 9.2.0.6 para evitar molestos ‘bugs’):

SQL*Plus: Release 9.2.0.7.0 - Production on Mar Oct 7 17:49:42 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Release 9.2.0.7.0 - 64bit Production JServer Release 9.2.0.7.0 - Production SQL> SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('19720401','YYYYMMDD')) 2 > / 12 FROM DUAL; MONTHS_BETWEEN(SYSDATE,TO_DATE('19720401','YYYYMMDD'))/12 --------------------------------------------------------- 36,5181297

Con una división por 12 y algún ‘TRUNC’, pan comido.

Es capaz incluso de calcular sin problemas cuando la “fecha de nacimiento” está muy lejos en el pasado:

SQL> SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('00010101','YYYYMMDD')) 2 > / 12) FROM DUAL; TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE('00010101','YYYYMMDD'))/12) ---------------------------------------------------------------- 2007 SQL>

En Teradata las cosas no son tan fáciles, ya que no existe función “MONTHS_BETWEEN” ni ninguna otra análoga, por lo que hay que ‘buscarse la vida’, como dicen los modernos.

Hay soluciones indicadas que se basan en hacer un ‘CAST’ a MONTH(4) (que en realidad son INTERVAL MONTH()) y convertirlo a INTEGER antes de dividirlo por 12 (años):

SELECT CURRENT_DATE, CAST((CURRENT_DATE - CAST('1972-04-01' AS DATE) MONTH(4)) AS INTEGER) / 12; *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. Date ((Date - '1972-04-01') MONTH/12) -------- -------------------------------- 08/10/07 36 BTEQ -- Enter your DBC/SQL request or BTEQ command:

La cosa en principio funciona bien, pero se estropea si intentamos calcular la edad de Cristo (más o menos):

SELECT CURRENT_DATE, CAST((CURRENT_DATE - CAST('0001-01-01' AS DATE) MONTH(4)) AS INTEGER) / 12; *** Failure 7453 Interval field overflow. Statement# 1, Info =0 *** Total elapsed time was 1 second. BTEQ -- Enter your DBC/SQL request or BTEQ command:

Así que el hábil ‘CAST’ nos deja con el culo al aire para edades ‘provectas’, por lo que hay que probar otros caminos.

Hay quien prefiere enredarse con complejas llamadas a ‘EXTRACT’ sobre las fechas e ir extrayendo YEAR, MONTH, DAY e ir construyendo una aritmética con los trozos (CASE incluidas)…

Hay también quien prefiere recurrir a ‘JOINs’ con SYS_CALENDAR.CALENDAR y construir de nuevo la aritmética…

Por mi parte, prefiero volver la vista a la forma en que Teradata almacena fechas. En efecto, Teradata almacena las fechas de una forma particular: mediante enteros (’INTEGERs’) siguiendo la fórmula:

(YEAR - 1900) * 10000 + MONTH * 100 + DAY

Así:

SELECT CURRENT_DATE, CAST(CURRENT_DATE AS INTEGER); *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. Current Date Current Date ------------ ------------ 2008-10-07 1081007 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Entonces, si hacemos algo así como:

SELECT CURRENT_DATE, ( CAST(CURRENT_DATE AS INTEGER) - CAST(CAST('1972-04-01' AS DATE) AS INTEGER) ) / 10000 EDAD; *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. Current Date EDAD ------------ ----------- 2008-10-07 36 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Y también funciona para calcular la edad que tendría hoy Nerón (por poner un ejemplo):

SELECT CURRENT_DATE, ( CAST(CURRENT_DATE AS INTEGER) - CAST(CAST('0037-12-15' AS DATE) AS INTEGER) ) / 10000 EDAD; *** Query completed. One row found. 2 columns returned. *** Total elapsed time was 1 second. Current Date EDAD ------------ ----------- 2008-10-07 1970 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Saludos.

Carlos.

      

Recently I was tasked with investigating slippage between master and slave in a standard replication setup.

The client was using Maatkit's mk-table-checksum to check his slave data was indeed a fair copy of that of the master.

mk-table-checksum --algorithm=BIT_XOR h=hostname.local,u=root,p=xxx --replicate=checksum.checksum --emptyrepltbl --chunksize=500000 --databases mydb --sleep 1

He could then examine the checksum.checksum table and see all was well, however there were various tables with different crc values.

PLAIN TEXT SQL:

1. db: mydb
2. tbl: Foo_History
3. chunk: 0
4. boundaries: 1=1
5. this_crc: 30627c76fe658fd9b77eaddf1ea8c03a
6. this_cnt: 2593
7. master_crc: bdbadd7dae2636a8cf515bb886fb1295
8. master_cnt: 2593
9. ts: 2008-09-24 04:50:05

So, now I needed to find out what was updating the table. Here is where tools like mysqlsla and Maatkit's mk-log-parser come into their own as they both allow you to quickly parse the binary log files, extracting the relevant statements.

mysqlbinlog bin_log.000001 | mysqlsla -lt binary -

Check out http://hackmysql.com/mysqlsla_filters for how to filter by statement.

Looking through the binary logs I could see this table is actually an audit table for changes to the Foo table. The trail is kept using two triggers on that table.

PLAIN TEXT SQL:

1. SELECT trigger_name,
2. event_object_table,
3. Event_Manipulation
4. FROM information_schema.triggers WHERE trigger_schema = 'mydb'
5. AND  action_statement LIKE '%Foo_History%'\G
6. *************************** 1. row ***************************
7.       trigger_name: Foo_Update
8. event_object_table: Foo
9. Event_Manipulation: UPDATE
10. *************************** 2. row ***************************
11.       trigger_name: Foo_Delete
12. event_object_table: Foo
13. Event_Manipulation: DELETE

So whats the problem with that?, well there is a situation where two overlapping transactions updating the Foo table can be reordered once serialized on the slave.

Here is an example:

I recreated the tables and triggers, populating the Foo table with a handful of rows and then ran the following.

Here is the update trigger:

PLAIN TEXT SQL:

1. CREATE TRIGGER Foo_Update After UPDATE ON Foo
2. FOR Each Row INSERT INTO Foo_History (Foo_History_ID, Name, Value, Field_Id)
3. VALUES (Old.Foo_History_ID, Old.Name, Old.Value, Old.Field_Id);

Transaction 1 starts and updates.

PLAIN TEXT SQL:

1. Start Transaction;
2. UPDATE Foo SET Value=6 WHERE Field_ID = 3;

Transaction 2 starts, updates and commits.

PLAIN TEXT SQL:

1. Start Transaction;
2. UPDATE Foo SET Value=6 WHERE Field_Id = 51;
3. Commit;

Transaction 1 commits last.

PLAIN TEXT SQL:

1. Commit;

Now when these statements get run on the slave they will be serialized, thus changing the order of the inserts made by the trigger. The Foo_History table is now out of sync.

Master:

PLAIN TEXT SQL:

1. *************************** 1. row ***************************
2.     Foo_History_Id: 1
3.           Name: maxlength
4.          Value: 7
5.       Field_Id: 3
6. *************************** 2. row ***************************
7.     Foo_History_Id: 2
8.           Name: maxlength
9.          Value: 7
10.       Field_Id: 51

Slave:

PLAIN TEXT SQL:

1. *************************** 1. row ***************************
2.     Foo_History_Id: 1
3. Name: maxlength
4.          Value: 7
5.   Field_Id: 51
6. *************************** 2. row ***************************
7.     Foo_History_Id: 2
8. Name: maxlength
9.          Value: 7
10.       Field_Id: 3

As you can see from the above, the updates were performed in a different order, with the inserts being assigned a different Foo_History_Id. This is because the statements are written to the binary log in commit order.

Entry posted by Ewen | 10 comments

Add to: | | | |

Oracle ha anunciado su “HP Oracle Database Machine“. Se trata de una solución “hardware/software” para ofrecer un alto rendimiento en sistemas de “Data Warehousing” (”I/O-intensive“) con grandes volúmenes de datos.

Oracle afirma que el sistema “is a complete, optimized and preconfigured package of software, servers, and storage” (completo, optimizado y preconfigurado paquete de software, servidores y almacenamiento). Está basado en lo que llaman “Exadata Storage Servers” que son sistemas de almacenamiento ‘inteligentes’ que efectúan algunas labores de preproceso de las búsquedas (SELECTs…) antes de enviárselos al motor de Base de Datos en sí (Oracle RAC). Con esto se consigue mejorar el rendimiento y ,sobre todo, los I/O bottlenecks.

La solución auna hardware de HP y Software Oracle.

Pues bien, este sistema es básicamente igual en arquitectura a los servidores Teradata: Teradata, aunque es conocido como un sistema de bases de datos para “Data Warehousing”, es mucho más: la solución Teradata engloba software y hardware. Teradata corre básicamente en servidores Teradata específicamente diseñados para ejecutar el software Teradata de la manera más eficiente y con máximo rendimiento. El diseño “hardware” del almacenamiento, comunicaciones, memoria, procesadores… está pensado por y para conseguir máximo rendimiento con este software específico.

Así pues: Oracle está siguiendo un camino que Teradata empezó hace muchos años…

Saludos.

Carlos.

      

Ever wonder what is stored in InnoDB buffer pool at the moment ?

It is not so hard actually - we made a short patch for MySQL 5.0 which show innodb buffer pool content

PLAIN TEXT SQL:

1. mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_CONTENT; 
2. +-----------+-------+--------+---------+----------+------------+-----------+--------------+--------------+----------------+-----------------+--------------+------------------+
3. | BLOCK_NUM | SPACE | OFFSET | RECORDS | DATASIZE | FLUSH_TYPE | FIX_COUNT | LRU_POSITION | PAGE_TYPE_ID | PAGE_TYPE      | INDEX_NAME      | TABLE_SCHEMA | TABLE_NAME       |
4. +-----------+-------+--------+---------+----------+------------+-----------+--------------+--------------+----------------+-----------------+--------------+------------------+
5. |         0 |     0 |      7 |       0 |        0 |          0 |         0 |            2 |            6 | unknown        |                 |              |                  |
6. |         1 |     0 |      1 |       0 |        0 |          0 |         0 |            3 |            5 | unknown        |                 |              |                  |
7. |         2 |     0 |      3 |       0 |        0 |          0 |         0 |            4 |            6 | unknown        |                 |              |                  |
8. |         3 |     0 |      2 |       0 |        0 |          0 |         0 |            5 |            3 | inode          |                 |              |                  |
9. |         4 |     0 |      4 |       0 |        0 |          0 |         0 |            6 |        17855 | INDEX          | CLUST_IND       |              | SYS_IBUF_TABLE_0 |
10. |         5 |     0 |     11 |      23 |     1560 |          0 |         0 |            7 |        17855 | INDEX          | CLUST_IND       |              | SYS_INDEXES      |
11. |         6 |     0 |      5 |     242 |        0 |          3 |         0 |            8 |            7 | unknown        |                 |              |                  |
12. |         7 |     0 |      6 |    3006 |        0 |          0 |         0 |            9 |            6 | unknown        |                 |              |                  |
13. |         8 |     0 |     45 |       0 |        0 |          0 |         0 |           10 |            2 | undo_log       |                 |              |                  |
14. |         9 |     0 |     50 |       0 |        0 |          0 |         0 |           11 |            2 | undo_log       |                 |              |                  |
15. |        10 |     0 |      8 |      12 |      894 |          0 |         0 |           12 |        17855 | INDEX          | CLUST_IND       |              | SYS_TABLES       |
16. |        11 |     0 |      0 |       0 |        0 |          0 |         0 |           13 |            8 | unknown        |                 |              |                  |
17. |        12 |     0 |     10 |      93 |     5864 |          0 |         0 |           14 |        17855 | INDEX          | CLUST_IND       |              | SYS_COLUMNS      |
18. |        13 |     0 |      9 |      12 |      354 |          0 |         0 |           15 |        17855 | INDEX          | ID_IND          |              | SYS_TABLES       |
19. |        14 |     0 |     12 |      32 |     1313 |          0 |         0 |           16 |        17855 | INDEX          | CLUST_IND       |              | SYS_FIELDS       |
20. |        47 |     0 |     46 |       0 |        0 |          0 |         0 |           49 |        17855 | INDEX          | ID_IND          |              | SYS_FOREIGN      |
21. |        48 |     0 |     47 |       0 |        0 |          0 |         0 |           50 |        17855 | INDEX          | FOR_IND         |              | SYS_FOREIGN      |
22. |        49 |     0 |     48 |       0 |        0 |          0 |         0 |           51 |        17855 | INDEX          | REF_IND         |              | SYS_FOREIGN      |
23. |        50 |     0 |     49 |       0 |        0 |          0 |         0 |           52 |        17855 | INDEX          | ID_IND          |              | SYS_FOREIGN_COLS |
24. |        51 |     0 |     51 |      14 |     1589 |          0 |         0 |           53 |        17855 | INDEX          | CLUST_IND       |              | SYS_IBUF_TABLE_0 |
25. |        52 |     0 |     52 |     138 |    16037 |          0 |         0 |           54 |        17855 | INDEX          | CLUST_IND       |              | SYS_IBUF_TABLE_0 |
26. |        53 |     0 |     53 |      72 |     8148 |          0 |         0 |           55 |        17855 | INDEX          | CLUST_IND       |              | SYS_IBUF_TABLE_0 |
27. |        54 |     0 |     54 |      55 |     6313 |          0 |         0 |           56 |        17855 | INDEX          | CLUST_IND       |              | SYS_IBUF_TABLE_0 |
28. |        55 |     0 |     55 |      39 |     3959 |          0 |         0 |           57 |        17855 | INDEX          | CLUST_IND       |              | SYS_IBUF_TABLE_0 |
29. |        56 |     0 |     56 |      24 |     2816 |          0 |         0 |           58 |        17855 | INDEX          | CLUST_IND       |              | SYS_IBUF_TABLE_0 |
30. |        57 |     0 |     57 |     286 |        0 |          0 |         0 |           59 |            4 | ibuf_free_list |                 |              |                  |
31. |        58 |     0 |     58 |     928 |        0 |          0 |         0 |           60 |            4 | ibuf_free_list |                 |              |                  |
32. |        59 |     0 |     59 |      64 |        0 |          0 |         0 |           61 |            4 | ibuf_free_list |                 |              |                  |
33. |        60 |     0 |     60 |     132 |        0 |          0 |         0 |           62 |            4 | ibuf_free_list |                 |              |                  |
34. |        61 |     0 |     61 |      69 |        0 |          0 |         0 |           63 |            4 | ibuf_free_list |                 |              |                  |
35. |        62 |     0 |     62 |      44 |        0 |          0 |         0 |           64 |            4 | ibuf_free_list |                 |              |                  |
36. |        63 |     0 |     63 |      44 |        0 |          0 |         0 |           65 |            2 | undo_log       |                 |              |                  |
37. |        64 |    17 |      3 |       4 |       60 |          0 |         0 |           66 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
38. |        66 |    17 |      2 |       0 |        0 |          0 |         0 |           68 |            3 | inode          |                 |              |                  |
39. |        67 |    17 |     38 |    1048 |    15720 |          0 |         0 |           69 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
40. |        68 |    17 |   1715 |     128 |    15077 |          0 |         0 |           70 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
41. |        69 |    17 |     37 |    1048 |    15720 |          0 |         0 |           71 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
42. |        70 |    17 |    886 |     134 |    15147 |          0 |         0 |           72 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
43. |        71 |    17 |     36 |     524 |     7860 |          0 |         0 |           73 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
44. |        72 |    17 |     29 |     137 |    15085 |          0 |         0 |           74 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
45. |        73 |    17 |     39 |     155 |     2325 |          0 |         0 |           75 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
46. |        74 |    17 |   2670 |     130 |    15114 |          0 |         0 |           76 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
47. |        75 |    17 |   2591 |     117 |    15112 |          0 |         0 |           77 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
48. |        76 |    17 |    714 |     146 |    15067 |          0 |         0 |           78 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
49. |        77 |    17 |    409 |     142 |    15110 |          0 |         0 |           79 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
50. |        78 |    17 |   2739 |     133 |    15110 |          0 |         0 |           80 |        17855 | INDEX          | GEN_CLUST_INDEX | art          | a87              |
51. |        79 |     1 |      3 |       3 |       39 |          0 |         0 |           81 |        17855 | INDEX          | PRIMARY         | art          | author87         |
52. |        80 |     1 |      1 |       0 |        0 |          0 |         0 |           51 |            5 | unknown        |                 |              |                  |
53. |        81 |     1 |      2 |       0 |        0 |          0 |         0 |           51 |            3 | inode          |                 |              |                  |
54. |        82 |     1 |    137 |     807 |    10491 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
55. |        83 |     1 |   3491 |     140 |    15095 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
56. |        84 |     1 |   3799 |     139 |    15144 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
57. |        85 |     1 |   2852 |     173 |    15070 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
58. |        86 |     1 |   3096 |     167 |    15054 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
59. |        87 |     1 |   3340 |     286 |    15082 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
60. |        88 |     1 |   3648 |     138 |    15127 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
61. |        89 |     1 |   3892 |     151 |    15088 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
62. |        90 |     1 |   3009 |     187 |    15119 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
63. |        91 |     1 |      4 |       3 |      102 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
64. |        92 |     1 |    138 |     360 |    11649 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
65. |        93 |     1 |   3255 |     354 |     9201 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
66. |        94 |     1 |   1534 |     309 |    10506 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
67. |        95 |     1 |   3440 |     328 |     8198 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
68. |        96 |     1 |    479 |     498 |    11689 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
69. |        97 |     1 |   3246 |     346 |     8981 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
70. |        98 |     1 |   2518 |     427 |    11450 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
71. |        99 |     1 |   2070 |     316 |     8273 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
72. |       100 |     1 |   3259 |     324 |     8988 |          0 |         0 |           51 |        17855 | INDEX          | site_id         | art          | author87         |
73. |       101 |     1 |    135 |     601 |     7813 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
74. |       102 |     1 |      5 |     140 |     7812 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
75. |       103 |     1 |      6 |     286 |    15067 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
76. |       122 |     1 |     32 |      94 |    15024 |          0 |         0 |           51 |        17855 | INDEX          | PRIMARY         | art          | author87         |
77. +-----------+-------+--------+---------+----------+------------+-----------+--------------+--------------+----------------+-----------------+--------------+------------------+
78. 90 rows IN SET (0.01 sec

The binaries, RPMS (RedHat/Centos) and full source code also available to download

P.S. There is Jeremy Cole's patch for InnoDB plugin MySQL 5.1, and main idea was taken from there.

Entry posted by Vadim | 4 comments

Add to: | | | |

A paginated display is one of the top optimization scenarios we see in the real world. Search results pages, leaderboards, and most-popular lists are good examples. You know the design pattern: display 20 results in some most-relevant order. Show a "next" and "previous" link. And usually, show how many items are in the whole list and how many pages of results there are.

Rendering such a display can consume more resources than the entire rest of the site!

As an example, I'm looking at slow log analysis results (with our microslow patches, set to log all queries) for one client; the slow log contains 6300 seconds' worth of queries, and the two main queries for the paginated display consumed 2850 and 380 seconds, respectively.

Why is it so expensive? I typically see queries like this:

PLAIN TEXT SQL:

1. SELECT .... FROM ... ORDER BY .... LIMIT X, 20

If the ORDER BY can't use an index (commonly the case), it uses a filesort. Suppose there are a million rows that meet any WHERE conditions. That means a million rows are retrieved, stored, filesorted, then most of them are discarded and only 20 retrieved. If the user clicks the "next" button the same process happens again, only a different 20 are retrieved. And to show the list of pages and the total count, you either a) use SQL_CALC_FOUND_ROWS (see our post on this) or b) execute a separate SELECT to count the rows.

There are ways to optimize so you don't have to do quite so much offsetting and limiting. I wrote about this on O'Reilly's website in an article on optimizing ranked data. But frankly it's not that easy to do in the real world; you can usually optimize for one access method to the data at some significant cost in complexity and maintenance (which might be worth it) but not for many different ways of accessing the same data, which is more typical in websites we work on.

Beyond indexing, re-organizing data, or query optimizations, there are two big things you can do. One is caching aggressively to prevent these queries from running. The other is to rethink the paradigm. Just because everyone lays out such pages in the same way doesn't mean you need to. Think about how you use such pages. Do you really go clicking directly to the Nth page of results, or the last page? "Hmm, it found 13928 results. Let me look at the least relevant search results for my query." Generally not -- you usually look at the most helpful stuff, which is supposed to be first in the list.

With that in mind, here are four suggestions for optimizing paginated displays that can give significantly better performance.

1. On the first query, fetch and cache all the results. Now it's easy to know how many results there are, and fetching subsequent pages is no extra work for the database. In this model, you get to keep your "found X rows, showing page N of M" display that many people cherish.
2. Don't show all results. Not even Google lets you see the millionth result. You get to see N results and after that, you're done. Limit the results to 100 or 500 or something. Remember, the further you go into the list, the more rows you are scanning and discarding with that LIMIT. This technique works great in conjunction with the first one. If you want to show 500 results, maybe you can fetch 501 and if the 501st row exists, display "more than 500 results found."
3. Don't show the total count or the intermediate links to other pages. Show only the "next" link. (If people want to see the "previous" results, they can use their browser's back button.) You can do this by fetching one more result than you want to display -- for example, fetch 21 rows and display only 20. If there's a 21st row, render the "next" link; if not, you're at the end of the list. This way you don't have to calculate how many results there are, and if caching is difficult then this is a simple way to avoid some of the costs.
4. Estimate how many results there are. Again, Google does this and nobody complains. Use EXPLAIN and look at the "rows" column -- that's a fine estimate for some scenarios. (This tip doesn't work in as many scenarios as others, but it's still acceptable in many.)

These suggestions can take a lot of work off the database server without impacting the user's experience at all.

Entry posted by Baron Schwartz | 21 comments

Add to: | | | |

With database volumes growing exponentially (see this previous post) and CPUs far out performing disks (see this previous post), compression has become a hot topic among database management solutions. Just don't believe everything you hear about compression. Product marketing is ripe with claims of anywhere from 2:1 to 30:1 compression (Note: A ratio of 2:1 is equivalent to 50% compression while 30:1 is equivalent to 96.6% compression). While these ratios may be true for data cooked up in the lab, real world compression rates will vary dramatically depending on the data in your warehouse and how you load and query it.

Compression is very data dependent. It is expected that "your mileage may vary," but with compression the differences may vary by orders of magnitude. Analyzing applications across industries we have found databases are compressible to different extents.

Compression in the Real World

In financial services, stock market trade data for all US exchanges includes 250 days of data and 10,000 instruments each year. At 100 million trades per day (a conservative number), a dataset that records the date and time, instrument, price, and volume will use between 3GB and 10GB (uncompressed) per day depending on the representation (e.g., binary vs. ASCII). By one year, this raw data will be anywhere from 1-3TB. Using an off the shelf Lempel-Ziv (LZ) algorithm may compress it by 2:1.

In the telecommunications space, call detail records include information describing the call path, times, features, and switches. An average record may be 550 bytes long, and a regional telco may record 500 million per day resulting in uncompressed source data of 275GB per day (or 100TB a year). This data, also using off the shelf LZ, compresses by 5:1. These compression factors relative to raw data can vary dramatically depending on whether the data is preprocessed (e.g., encoding long names into ids, changing timestamps with time-zones into GMT, or replacing empty strings with nulls).  

In contrast, column stores often achieve up to three times more compression, reaching factors of 20:1 versus raw data by isolating variability in the data. In a column store, each block contains data of the same attribute and type, and sorted columns guarantee homogeneity even for trickle loads and high cardinality data. This also allows column stores to use more effective algorithms than vanilla LZ. 

Vertica's customers span many industries and applications, so we have been able to assess compression with a large variety of real world datasets. For example, looking at the compressibility of different datasets that customers load into Vertica, we see the following as typical compression ratios, relative to raw ASCII delimited data (e.g., comma separated values):

• CDR - 8:1 (87%)
• Consumer Data - 30:1 (96%)
• Marketing Analytics - 20:1 (95%)
• Network logging - 60:1 (98%)
• Switch Level SNMP - 20:1 (95%)
• Trade and Quote Exchange - 5:1 (80%)
• Trade Execution Auditing Trails - 10:1 (90%)
• Weblog and Click-stream - 10:1 (90%)

Achieving Better Compression

In order to achieve compression rates higher than LZ, Vertica implements a variety of homegrown encoding and compression algorithms specifically designed for a column store database. In addition to optimized block layouts and well-known algorithms, such as run length encoding and delta value encoding, Vertica uses optimized integer and floating point compression algorithms and compound encoding that combines algorithms to increase compression and overall system performance.

Note that Vertica not only reads compressed data off of disk but also processes queries on the compressed data, saving memory and CPU bandwidth (see this previous post). This cannot be done when compressing with LZ. 

Applying LZ to any database, a server with enough CPU and a threaded storage subsystem will reduce I/O at the expense of CPU. The result is performance improvements as high as 30% and space savings up to 7:1 compared to raw data. These numbers approximate the best you can get out of a traditional database, storing records as variable sized rows with headers and applying per-block compression. These numbers also assume that data in any given block is homogenous (e.g., same date, instrument, and customer), which may be the case for bulk loaded data but is not necessarily the case when trickle loading. In a trickle load stream that adds only thousands of records per second, most databases add records into any block with free space, resulting in mixed data values and reducing overall compression. In addition, auxiliary structures such as indexes and materialized views add bloat that is more difficult to compress when these structures need to be updated as records are added.

In a recent head-to-head comparison with a competitor, a prospective customer was able to only test 300GB of raw data on a popular row store because its server was limited to 1TB of disk and the additional structures required to achieve adequate performance consumed three times the raw data size. Loading into Vertica the calculated capacity was close to 4TB of raw data on the same 1TB of disk.

You can use the compression ratio in the table to determine what your storage requirements would be using Vertica. With other vendors, you mileage will most certainly vary, but don't forget to factor in indexes, materialized views, and other auxiliary structures for row stores (such as per tuple headers), which are likely to require 2-5x these sizes.

The ability to choose grouping criteria at run time can mean the selection and delivery of enterprise data in a more focused and consumer-friendly manner. It also means doing more with fewer reports. BI Architect Bill Pearson leads a hands-on demonstration of a way to parameterize grouping with runtime sorting options.

The problem of MySQL Replication unable to catch up is quite common in MySQL world and in fact I already wrote about it. There are many aspects of managing mysql replication lag such as using proper hardware and configuring it properly. In this post I will just look at couple of query design mistakes which result in low hanging fruit troubleshooting MySQL Replication Lag

First fact you absolutely need to remember is MySQL Replication is single threaded, which means if you have any long running write query it clogs replication stream and small and fast updates which go after it in MySQL binary log can't proceed. It is either more than than just about queries - if you're using explicit transactions all updates from the transactions are buffered together and when dumped to binary log as one big chunk which can't be interleaved by any other query execution. So if you have transaction containing millions of simple updates instead of one large update to help MySQL replication lag it is not going to work.

This brings us to rule number one - if you care about replication latency you must not have any long running updates. Queries or transactions containing multiple update queries which add up to long time. I would keep the maximum query length at about 1/5th of the maximum replication lag you're ready to tolerate. So if you want your replica to be no more than 1 minute behind keep the longest update query to 10 sec or so. This is of course rule of thumb depending on differences in master/slave configuration, their load and concurrency you may need to keep the ratio higher or allow a bit longer queries.

What should you do if you need to update a lot of rows ? Use Query Chopping - this can be running update/delete with LIMIT in the loop, controlling maximum amount of values per batch in multiple row insert statement or Fetching data you're planning to update/delete and having multiple queries to delete it (see example below)

This brings us to yet another rule for smart replication - do not make Slave to do more work than it needs to do. It is crippled by having to do all of this in single thread already - do not make it even harder. If there is considerable effort needed to select rows for modification - spread it out and have separate select and update queries. In such case slave will only need to run UPDATE
Example:

PLAIN TEXT SQL:

1. UPDATE posts SET  spam=1 WHERE body LIKE "%cheap rolex%";

This query will perform full table scan in MySQL 5.0 (even if there are no spam posts) which will load slave significantly. You can replace it with:

PLAIN TEXT SQL:

1. SELECT id FROM posts WHERE  body LIKE "%cheap rolex%";
2.  
3. UPDATE posts SET spam=1 WHERE id IN (list of ids)

If there could be many ids matched on the first place you should also use query chopping and run update in chunks if application allows it.

In MySQL 5.1 with row level replication you will not have selection process running on SLAVE but it will not do the chopping for you.

In general this trick does not only work well for full table scan updates but in general for cases when there are much more rows examined than modified.

The next common mistake is using INSERT ... SELECT - which is in similar to what I just described but can be much worse as SELECT may end up being extremely complicated query. It is best to avoid INSERT ... SELECT going through replication in 5.0 for many reasons (locking, long query time, waste of execution on slave). Piping data through application is the best solution in many cases and is quite easy - it is trivial to write the function which will take SELECT query and the table to which store its result set and use in your application in all cases when you need this functionality.

Finally you should not overload your replication - Quite typically I see replication lagging when batch jobs are running. These can load master significantly during their run time and make it impossible for slave to run the same load through single thread. The solution in many cases is to simply space it out and slow down your batch job (such as adding sleep calls) to ensure there is enough breathing room for replication thread.

You can also have controlled execution of batch job - this is when they will check slave lag every so often and pause if it becomes too large. This is a bit more complicated approach but it saves you from running around and adjusting your sleep behavior to keep the progress fast enough and at the same time keep replication from lagging.

In many bad replication lags I've seen simply following these simple rules would avoid a lot of problems and often save massive hardware purchases or development efforts based on assumption MySQL replication can't possibly keep up any more.

Entry posted by peter | 4 comments

Add to: | | | |

Whilst working with a client recently I noticed a large number of temporary tables being created on disk.

show global status like 'Created_tmp%'

| Created_tmp_disk_tables | 91970 |
| Created_tmp_files | 19624 |
| Created_tmp_tables | 1617031 |

Looking at a 60 second sample showed there was a steady flow of queries creating disk based tables.

mysqladmin ext -ri60

| Created_tmp_disk_tables | 74 |
| Created_tmp_files | 3 |
| Created_tmp_tables | 357 |

Luckily this client was running the Percona patched version of MySQL.

The microslow patch adds a very useful feature, the ability to log queries by execution plan.

log_slow_filter=name

Log only the queries that followed certain execution plan. Multiple flags allowed in a comma-separated string. [qc_miss, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk]

So now all I had to do was set the filter to “tmp_table_on_disk,filesort_on_disk” and I would get only those which use on-disk temporary storage for intermediate results.

SET GLOBAL log_slow_filter:= “tmp_table_on_disk,filesort_on_disk”;

Wait a couple of minutes and then set the filter to an empty string to stop collection.

SET GLOBAL log_slow_filter:="";

Then it was just a question of examining the slow query log and fixing the queries.
More information about the patches is available here http://www.percona.com/percona-lab.html

Entry posted by Ewen | 3 comments

Add to: | | | |

The previous installment of our series began an overview of a distributed Service Broker environment. This article describes the remaining steps necessary to facilitate communication between services residing on separate computers in the same Windows domain, including assigning permissions to execute Service Broker-specific tasks and defining Adjacent Broker Protocol routes.

I'll be speaking at HighLoad++ conference in Moscow,Russia taking place 6,7 Oct 2008. This conference was quite success with very interesting presentation last year and I'm hoping it would be even better this year.

I also will have a full dayMaster Class focused on Scaling MySQL w Sharding and Replication based on material in our book

Interesting enough this year the conference became two another one being about same topic and very similar name taking place 22-23 September. It also sounds great and it would be hard for me to pick one or another one if not my 10 year wedding anniversary this week which made it impossible for travel.

Entry posted by peter | 3 comments

Add to: | | | |

When optimizing queries for customers, the first thing I do with a slow query is figure out what it's trying to do. You can't fully optimize a query unless you know how to consider alternative ways to write it, and you can't do that unless you know what the query "means." I frequently run into a situation where I'm forced to stop and ask the developers what they were trying to do with COUNT(). This is database-agnostic, not related to MySQL.

The problem is when the COUNT() contains a column name, like this:

PLAIN TEXT SQL:

1. SELECT count(col1) FROM TABLE;

If you know your SQL well, you know COUNT() has two meanings. 1) count the number of rows 2) count the number of values. Sometimes, but not always, these are the same thing. COUNT(*) always counts the number of rows in the result. If you write COUNT(col1) it counts the number of times col1 is not null. If it's never null, the result is the same as the number of rows.

The problem with that is that you don't know by looking at the query whether the developer wanted to count rows or values -- or, quite possibly, the number of distinct values in that column. You see, there's another form for COUNT():

PLAIN TEXT SQL:

1. SELECT count(DISTINCT col1) FROM TABLE;

So when I see a query that just does COUNT(col1) I am left with these guesses:

1. You meant to count the number of rows. You should have written COUNT(*) to convey that meaning accurately. If the column is nullable, there's a bug in your query -- it won't do what you think it does.
2. You meant to count the number of distinct values, but you left out the DISTINCT keyword. That's also a bug in your query.
3. You meant to count the number of times the column's value is not null. This in my experience is pretty unlikely -- people rarely do this.

It is impossible to read the developer's mind in these cases, so I always end up getting stalled waiting for them to reply and tell me what the query means so I can optimize it.

A good coding standard can help here -- you can quickly write a tool to grep your source code and search for instances of such constructs, and flag them as errors unless some magic comment is embedded in the code next to them:

PLAIN TEXT SQL:

1. SELECT count(col1) FROM TABLE; -- COUNT_COL_OK

Alas, there is a lot of misinformation on the web about COUNT(), so it is not a good place to learn about what it does. 90% of what you can find online is just wrong. So I would advise a good book on the topic, except I can't think of one -- maybe Joe Celko's books address this topic clearly? What's your recommendation?

Entry posted by Baron Schwartz | 17 comments

Add to: | | | |

Business Intelligence Architect Bill Pearson continues his exploration of Attribute Member Keys in another member of a group of articles surrounding significant components of the Analysis Services dimensional model. In this article we resume our examination of Attribute Member Keys, focusing upon composite keys and their properties.

Microsoft finalizó la compra del DataAllegro una empresa que se dedica a proveer productos de Appliances para Datawarehouse que pueden administrar hasta PetaBytes de información, más información en http://www.datallegro.com/pr/9_16_08_microsoft_closes.asp

 

Slds

Eduardo Castro - http://comunidadwindows.org

Costa Rica

      

Si habitualmente trabajas con Excel y eres un usuario medio o avanzado, no dejes de visitar periódicamente este blog del equipo de desarrollo del producto.

Allí encontrarás una gran cantidad de información sobre el producto, sobre cualquiera de sus áreas, desde artículos sobre las PivotTables y OLAP PivotTables, pasando por temas de formato de datos, programación, fórmulas, funciones, white papers, etc... e incluyendo temas de servidor como los Excel Services.

También tienes las últimas novedades, como el artículo publicado en agosto Using Excel & Excel Services with SQL Server Analysis Services 2008

This fifth installment of the “Check your SQL Server using Windows PowerShell” series illustrates how to access SQL Server instance properties and SQL Server configuration details using Windows PowerShell.