InnoDB Tables and Indexes Statistics

More often than not I've thought about the meaning of InnoDB statistics and how it works.

Every time I executed an 'ANALYZE TABLE' command the statistics related to the table were different and what's more nobody had modified the data of the table before.

It is odd, isn't it ?

So I decided to get down to the problem and following you'll find the first conclusion.


The innoDB statistics read only the first 8 pages of the subject. The other pages are only estimated. It looks like a bet, doesn't it ? 
You can change the amount of the initial pages to be loaded modifying the following parameter 'innodb_stats_sample_pages'. 
 The innoDB statistics are loaded into the memory every time:

1.  The table is read,
2. The command 'ANALYZE TABLE' is executed,
3. New records are loaded in the table,
4. The command 'SHOW TABLE/INDEX' is executed.

Frankly speaking I prefer the myISAM statistics. Those statistics are always complete. The table is completely read and what's more the related statistics are saved into the disk.

This is only a quick view of the statistics according to mysql. Lots of googling should be done to complete the task.

See you in my next blog.

Byeeeee

Oracle export and import of only DDL

Following you'll find the complete procedure that can be followed to restore an Oracle schema using a previous expdp dump and what's more without importing the data rows.


1) To complete the first step it is necessary to get the name of the tablespaces where I'm going to restore the schema using the backup done previously.     

   export ORACLE_SID=TEST
    export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
    impdp system DIRECTORY=DATA_PUMP JOB_NAME=IMP_SCOTT LOGFILE=imp_SCOTT.log SCHEMAS=SCOTT DUMPFILE=EXP_SCOTT.dmp SQLFILE=ddl_SCOTT.sql

Be careful:  With the option SQLFILE enabled only a DDL file is going to be produced.




2) Using the information obtained before now we are ready to create the new tablespaces:
      
        new object type path: SCHEMA_EXPORT/USER
    CREATE USER "SCOTT" IDENTIFIED BY VALUES 'xxxxxxxxx' DEFAULT TABLESPACE "SCOTTDATA"
        TEMPORARY TABLESPACE "TEMP";

    export ORACLE_SID=TEST
          sqlplus / as sysdba
          create tablespace SCOTTDATA datafile '+DG_DATI_DBF' size 256M autoextend on next 128M;
          create tablespace SCOTTINDX datafile '+DG_INDX_DBF' size 256M autoextend on next 128M;
       
3) Now we can import the schema without the data rows with the following command :

    impdp system DIRECTORY=DATA_PUMP JOB_NAME=IMP_SCOTT LOGFILE=imp_SCOTT.log SCHEMAS=SCOTT DUMPFILE=EXP_SCOTT.dmp CONTENT=METADATA_ONLY 
            
Please remember that who is writing doesn't know your environment so it's important to verify all the procedures in your test database before.

See you in my next Blog.

Byeee

Load Data Infile

Working with file.csv is always really boring. However, today I've put aside this opinion and I'm going to try it out.

Following you'll find an example :

CREATE TABLE `my_table` (
 `campo1` BIGINT(20) NOT NULL,
 `campo2` BIGINT(20) NULL DEFAULT NULL,
 `campo3` VARCHAR(255) NULL DEFAULT NULL,
 `campo4` VARCHAR(255) NULL DEFAULT NULL,
 `campo5` BIGINT(20) NULL DEFAULT NULL,
 PRIMARY KEY (`campo2`)
)COLLATE='latin1_swedish_ci' ENGINE=InnoDB;

MYSQL> load data infile 'file.csv' into table my_table fields terminated by ';' optionally enclosed by '"' lines terminated by '\n';

This is the row :

AAAAAA;2;2;"BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB";1

That's all.

See you in my next blog.

Byeeeee

MySQL vs Killed Session

Most of time, I'm used to installing and testing every new MySQL instance and every now and again I execute a Cartesian product between two tables to try out the performance of the instance.

Following you'll find what cropped up !

mysql> select * from payment a, payment b;

After a while the mysql client was closed by an unknown process and what's more I got only the message : "killed"

Immediately I looked at the log of the database but nothing was written.

At first glance it didn't seem to be a database problem and I've been thinking about it for a while.

Suddenly I had an Idea !

Would it help if I looked at the log of the linux server ? It may !

At the end of the /var/log/messages I found the following error :

Jan 15 15:31:54 MysqlVm01 kernel: mysqld cpuset=/ mems_allowed=0
Jan 15 15:31:54 MysqlVm01 kernel: Pid: 2041, comm: mysqld Not tainted 2.6.39-400.17.1.el6uek.x86_64 Jan 15 15:31:55 MysqlVm01 kernel: Out of memory: Kill process 7236 (mysql) score 937 or sacrifice child
Jan 15 15:31:55 MysqlVm01 kernel: Killed process 7236 (mysql) total-vm:10779428kB,


The server ran out of memory and because of that the kernel agent process decided to sacrifice a single client process to shutdown the entire server machine.

That was the problem.

See you in my next blog.

Byeeee

TNS Listener Remote Poisoning PARTE 1

Oggi inizio a riportare i passi necessari per risolvere il problema del "TNS Listener Remote Poisoning Vulnerability " dividerò l'argomento in vari POST poichè abbastanza corposo.

Iniziamo dal principio, nell'aprile del 2008 il Sig Joxean Koret si accorse che era possibile mettersi in mezzo tra listener, client e database e riuscire ad ottenere importanti e riservate informazioni in modo fraudolento.

Da quanto sono riuscito a comprendere fino ad ora, non esiste una Patch per risolvere questo Bug e l'unico modo è quello di fare in modo che il Listener accetti solo connessioni sicure (TCPS).

Il tutto cmq è stato oggetto di un Alert su cui potrete trovare una descrizione + dettagliata del problema: Oracle Security Alert for CVE-2012-1675



Per risolvere occorre far uso delle "Advanced Security features" di Oracle che sono di solito a pagamento ma che per questo problema sia state rese free.


La procedura cambia a seconda che si tratti di una istanza singola o di un RAC come riportato :

• My Oracle Support Note 1340831.1 for Oracle Database deployments that use Oracle Real Application Clusters (RAC).
• My Oracle Support Note 1453883.1 for Oracle Database deployments that do not use RAC.

Al momento sto eseguendo delle prove sulla singola istanza, sulla quale è necessario anche applicare la seguente Patch : 12880299 poi andrà configurata la connessione TCPS.

L'installazione di questa Patch non è niente di che e leggendo il README della patch stessa si può fare rapidamente, riporto solo un BUG che ho incontrato durante gli ultimi passi e per il quale occorre modificare il file "crsconfig_lib.pm" nella home dell'utente grid gestore del servizio ASM e listenter nella mia istanza.

 
 Ecco l'errore :

[root@OracleVm05 install]# /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -patch

Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params

Undefined subroutine &main::read_file called at crspatch.pm line 86.

 

   Ecco la soluzione :  

This is because of Unpublished Bug:10011084 and Unpublished Bug:10323264. 'perl' executable not from Grid Home is called.


In sostanza si deve sostituire questa riga con l'altra:

From
     my @exp_func = qw(check_CRSConfig validate_olrconfig validateOCR
To
     my @exp_func = qw(check_CRSConfig validate_olrconfig validateOCR read_file

Fatto questo l'installazione della Patch si conclude correttamente :

[root@OracleVm05 install]# ./roothas.pl -patch

Using configuration parameter file: ./crsconfig_params

ACFS-9200: Supported

CRS-4123: Oracle High Availability Services has been started.

  

Alla prossima puntata....



Ciaoao

Percona Toolkit for MySQL

Inizio questo nuovo anno segnalando un interessante Tool per chi smanetta con Mysql che può essere senz'altro utile e vale la pena di approfondire :  Percona Toolkit for MySQL.

Si tratta di un rpm da installare su Linux:

Le dipendenze che ho trovato necessarie sono :


rpm -i perl-Net-SSLeay-1.35-9.el6.x86_64.rpm
rpm -i perl-Net-LibIDN-0.12-3.el6.x86_64.rpm
rpm -i perl-IO-Socket-SSL-1.31-2.el6.noarch.rpm
rpm -i percona-toolkit-2.2.6-1.noarch.rpm
 

Di seguito tutti gli eseguibili che vengono installati con l'rpm percona-toolkit:

pt-archiver
pt-collect
pt-config-diff
pt-deadlock-logger
pt-diskstats
pt-duplicate-key-checker
pt-fifo-split
pt-find
pt-fk-error-logger
pt-heartbeat
pt-index-usage
pt-kill
pt-log-player
pt-mext
pt-mysql-summary
pt-online-schema-change
pt-pmp
pt-query-advisor
pt-query-digest
pt-show-grants
pt-sift
pt-slave-delay
pt-slave-find
pt-slave-restart
pt-stalk
pt-summary
pt-table-checksum
pt-table-sync
pt-tcp-model
pt-trend
pt-upgrade
pt-variable-advisor
pt-visual-explain


Riporto come esempio l'output del comando "pt-mysql-summary" :

/usr/bin/pt-mysql-summary --user=root --password=xxxx 

# Percona Toolkit MySQL Summary Report #######################
              System time | 2014-01-09 15:10:53 UTC (local TZ: CET +0100)
# Instances ##################################################
  Port  Data Directory             Nice OOM Socket
  ===== ========================== ==== === ======
   3306 /mysql/mysql5.6/data/data  0    0   /tmp/mysql.sock
# MySQL Executable ###########################################
       Path to executable | /usr/local/mysql/bin/mysqld
              Has symbols | Yes
# Report On Port 3306 ########################################
                     User | em_agent@localhost
                     Time | 2014-01-09 16:10:53 (CET)
                 Hostname | MysqlVm01
                  Version | 5.6.14-log MySQL Community Server (GPL)
                 Built On | linux-glibc2.5 x86_64
                  Started | 2014-01-09 15:47 (up 0+00:23:40)
                Databases | 5
                  Datadir | /mysql/mysql5.6/data/data/
                Processes | 4 connected, 1 running
              Replication | Is not a slave, has 0 slaves connected
                  Pidfile | /mysql/mysql5.6/admin/MysqlVm01.pid (exists)
# Processlist ################################################

  Command                        COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  Query                                 1       1         0         0
  Sleep                                 3       0       600       350

  User                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  em_agent                              2       1         0         0
  root                                  2       0         0         0

  Host                           COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  10.5.10.155                           2       0         0         0
  localhost                             2       1         0         0

  db                             COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
  information_schema                    2       0         0         0
  NULL                                  2       1         0         0

  State                          COUNT(*) Working SUM(Time) MAX(Time)
  ------------------------------ -------- ------- --------- ---------
                                        3       0         0         0
  init                                  1       1         0         0

# Status Counters (Wait 10 Seconds) ##########################
Variable                                Per day  Per second     11 secs
Aborted_clients                              60
Aborted_connects                            900
Bytes_received                          6000000          80         700
Bytes_sent                          300000000000     3500000        6000
Com_admin_commands                         1500
Com_change_db                               250
Com_help                                   1250
Com_select                                17500                       2
Com_set_option                              900
Com_show_binlogs                            125
Com_show_databases                          250
Com_show_engine_status                     1500
Com_show_fields                            3500
Com_show_master_status                      125
Com_show_processlist                        125
Com_show_slave_status                      1500
Com_show_status                            3000
Com_show_table_status                       175
Com_show_tables                             175
Com_show_variables                         3000
Connections                                1500                       1
Created_tmp_disk_tables                    9000                       1
Created_tmp_files                           350
Created_tmp_tables                        70000                       6
Flush_commands                               60
Handler_commit                               60
Handler_external_lock                     40000                       1
Handler_read_first                          700
Handler_read_key                           4000
Handler_read_next                          1500
Handler_read_rnd_next                   4000000          45         150
Handler_rollback                             60
Handler_write                           1750000          20         100
Innodb_buffer_pool_bytes_data         500000000        6000
Innodb_buffer_pool_pages_flushed             60
Innodb_buffer_pool_read_requests         450000           4
Innodb_buffer_pool_reads                  30000
Innodb_buffer_pool_write_requests            60
Innodb_data_fsyncs                          300
Innodb_data_read                      600000000        7000
Innodb_data_reads                         30000
Innodb_data_writes                          300
Innodb_data_written                     2000000          25
Innodb_dblwr_pages_written                   60
Innodb_dblwr_writes                          60
Innodb_log_writes                            60
Innodb_os_log_fsyncs                        175
Innodb_os_log_written                     30000
Innodb_pages_read                         30000
Innodb_pages_written                         60
Innodb_rows_read                        2000000          20
Innodb_num_open_files                      1750
Innodb_available_undo_logs                 8000
Key_read_requests                          7000
Key_reads                                  1750
Open_table_definitions                     7000
Opened_files                              60000                       4
Opened_table_definitions                   7000
Opened_tables                            100000           1
Qcache_not_cached                         17500                       2
Queries                                   35000                       5
Questions                                 35000                       5
Select_full_join                            700
Select_range                                 60
Select_scan                               25000                       1
Slow_queries                                 60
Table_locks_immediate                     20000
Table_open_cache_hits                     40000
Table_open_cache_misses                   35000
Table_open_cache_overflows                25000
Threads_created                             300
Uptime                                    90000           1           1
# Table cache ################################################
                     Size | 431
                    Usage | 25%
# Key Percona Server features ################################
      Table & Index Stats | Not Supported
     Multiple I/O Threads | Enabled
     Corruption Resilient | Not Supported
      Durable Replication | Not Supported
     Import InnoDB Tables | Not Supported
     Fast Server Restarts | Not Supported
         Enhanced Logging | Not Supported
     Replica Perf Logging | Disabled
      Response Time Hist. | Not Supported
          Smooth Flushing | Not Supported
      HandlerSocket NoSQL | Not Supported
           Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
       InnoDB compression | ACTIVE
# Query cache ################################################
         query_cache_type | OFF
                     Size | 1.0M
                    Usage | 1%
         HitToInsertRatio | 0%
# Schema #####################################################
Specify --databases or --all-databases to dump and summarize schemas
# Noteworthy Technologies ####################################
                      SSL | No
     Explicit LOCK TABLES | No
           Delayed Insert | No
          XA Transactions | No
              NDB Cluster | No
      Prepared Statements | No
 Prepared statement count | 0
# InnoDB #####################################################
# MyISAM #####################################################
                Key Cache | 1.0G
                 Pct Used | 20%
                Unflushed | 0%
# Security ###################################################
                    Users | 6 users, 0 anon, 0 w/o pw, 0 old pw
            Old Passwords | 0
# Binary Logging #############################################
# Noteworthy Variables #######################################
     Auto-Inc Incr/Offset | 1/1
   default_storage_engine | InnoDB
               flush_time | 0
             init_connect |
                init_file |
                 sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
         join_buffer_size | 256k
         sort_buffer_size | 64k
         read_buffer_size | 256k
     read_rnd_buffer_size | 1M
       bulk_insert_buffer | 0.00
      max_heap_table_size | 16M
           tmp_table_size | 16M
       max_allowed_packet | 4M
             thread_stack | 256k
                      log |
                log_error | /mysql/mysql5.6/admin/MysqlVm01.err
             log_warnings | 1
         log_slow_queries |
log_queries_not_using_indexes | OFF
        log_slave_updates | OFF
# Configuration File #########################################
              Config File | /etc/my.cnf

[client]
port                                = 3306
socket                              = /tmp/mysql.sock
default-character-set               = utf8

[mysqld_safe]
socket                              = /tmp/mysql.sock
nice                                = 0
log-error                           = /mysql/mysql5.6/admin/MysqlVm01.err
pid-file                            = /mysql/mysql5.6/admin/MysqlVm01.pid

[mysqld]
user                                = mysql
pid-file                            = /mysql/mysql5.6/admin/MysqlVm01.pid
socket                              = /tmp/mysql.sock
port                                = 3306
basedir                             = /usr/local/mysql
datadir                             = /mysql/mysql5.6/data/data
log-error                           = /mysql/mysql5.6/admin/MysqlVm01.err
tmpdir                              = /tmp
character-set-server                = utf8
innodb_stats_persistent             = 1         # Also use ANALYZE TABLE for all tables periodically
innodb_read_io_threads              = 16
innodb_write_io_threads             = 4
table_open_cache_instances          = 16
metadata_locks_hash_instances       = 256
innodb_log_file_size                = 2048M
innodb_checksum_algorithm           = strict_crc32
innodb_buffer_pool_size             = 2048M
innodb_data_home_dir                = /mysql/mysql5.6/data/innodata
innodb_data_file_path               = ibdata1:2048M:autoextend:max:4096M
innodb_log_group_home_dir           = /mysql/mysql5.6/logs/innologs
key_buffer_size                     = 1024M
myisam_sort_buffer_size             = 32M
sort_buffer_size                    = 32k
long_query_time                     = 1
log_queries_not_using_indexes       = 0
slow-query-log
slow-query-log-file                 = /mysql/mysql5.6/admin/MysqlVm01-slow.log
read_buffer_size                    = 262144
read_rnd_buffer_size                = 1M
sort_buffer_size                    = 64K

Ci sono arrivato cercando qualcosa che mi permettesse di identificare gli "unused index" su mysql ma sono sicuro che mi potrà essere anche utile per verificare ad esempio la consistenza dei dati in configurazione master e slave, oppure analizzare le query leggendole dallo slow query log file ecc.


Alla Prossima.


Ciaoao