MySql - Occupazione spazio nelle tabelle

SELECT
   table_schema,table_name, count(*) TABLES,
   round(sum(table_rows)/1000000,2) rows,
   round(sum(data_length)/(1024*1024*1024),2) DATA,
   round(sum(index_length)/(1024*1024*1024),2) idx, 
   round(sum(data_length+index_length)/(1024*1024*1024),2) total_size,
   round(sum(index_length)/sum(data_length),2) idxfrac
FROM
   information_schema.TABLES
group by table_schema,table_name
order by 7 desc

MySql - Salvataggio di tabella su file

Salvataggio di tabella su file:

SELECT * FROM prova
INTO OUTFILE '/mnt/prova.csv'
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';

Se Infobright potrebbe dare il seguente errore:
The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance. 

Per risolverlo eseguire prima lo statement:

set @bh_dataformat = 'txt_variable';

Caricamento da file:

LOAD DATA INFILE '/mnt/prova.csv' INTO TABLE prova1

Vertica - Query di monitoraggio

Verificare lo spazio occupato dalla tabelle sul disco

SELECT t.table_schema,t.table_name AS table_name,
SUM(ps.wos_row_count + ps.ros_row_count) AS row_count,
SUM(ps.wos_used_bytes + ps.ros_used_bytes)/(1024*1024) AS MB_count
FROM tables t
JOIN projections p ON t.table_id = p.anchor_table_id
JOIN projection_storage ps on p.projection_name = ps.projection_name
WHERE (ps.wos_used_bytes + ps.ros_used_bytes) > 500000
GROUP BY t.table_schema,t.table_name
ORDER BY MB_count DESC;

Vedere l'elenco della tabelle

SELECT * FROM tables;

Verificare le query attive

select node_name, user_name, client_hostname, session_id, transaction_start, statement_start, current_statement
from sessions

Killare una sessione
Inserire come parametro il campo session_id

SELECT CLOSE_SESSION('vertica01.pgh.wpahs774:0x1db5bb');

PostgreSql - Impostazioni server per DataWarehouse

Significato dei principali paramentri di configurazione del server PostgreSql presenti nel file postgresql.conf e valorizzazione consigliata per applicazioni di tipo Data Warehouse.

fsync

Se questo parametro è attivo, il server PostgreSQL proverà ad assicurarsi che gli aggiornamenti siano scritti fisicamente su disco, eseguendo chiamate di sistema fsync() o vari metodi equivalenti (si veda wal_sync_method). Questo assicura che il cluster database possa recuperare a uno stato consistente dopo un blocco del sistema operativo o dell'hardware.
Mentre disabilitare fsync spesso è un beneficio in termini di prestazioni, questo può risultare in corruzione irrecuperabile dei dati in caso di blocco o arresto inaspettato del sistema. Così è consigliabile disabilitare fsync se è possibile ricreare facilmente l'intero database a partire da dati esterni.
Esempi di circostanze sicure per disabilitare fsync includono il caricamento iniziale di un nuovo cluster di database a partire da un file di backup, l'uso di un cluster per l'elaborazione di statistiche all'ora che vengono quindi ricreate, o per un clone in sola lettura del database che viene ricreato frequentemente e non viene usato per il failover. Hardware di alta qualità da solo non è sufficiente a giustificare la disabilitazione di fsync.
In molte situazioni, disabilitare synchronous_commit per le transazioni non critiche può fornire molti dei potenziali benefici equivalenti a disattivare fsync, senza i rischi collegati di corruzione di dati.
fsync può essere impostato solo nel file postgresql.conf o dalla linea di comando del server. Se di disabilita questo parametro, considerare anche la disabilitazione di full_page_writes.

Per datawarehouse valorizzato a off

synchronous_commit

Specifica se il commit della transazione aspetterà che i record WAL siano scritti su disco prima che il comando restituisca un indicazione di «successo» al client. L'impostazione predefinita, e sicura, è on. Quando impostato a off, ci può essere un ritardo tra quando viene riportato il successo al client e quando la transazione è veramente garantita essere sicura rispetto a un blocco del server. (Il massimo ritardo è tre volte wal_writer_delay). Diversamente da fsync, impostare questo parametro a off non crea nessun rischio di inconsistenza del database: un blocco del sistema operativo o del database potrebbe risultare nella perdita di alcune transazioni presumibilmente sottoposte a commit, ma lo stato del database sarà comunque lo stesso come se quelle transazioni fossero state annullate di recente. Quindi, disabilitare synchronous_commit può essere un'alternativa utile quando le prestazioni sono più importanti rispetto alla certezza assoluta sulla durabilità di una transazione.
Questo parametro può essere cambiato in qualsiasi momento; il comportamento per qualsiasi transazione è determinato dall'impostazione effettiva quando viene effettuato il commit. È inoltre possibile, e utile, avere alcune transazione che fanno il commit in modo sincrono e altre che lo fanno in modo asincrono. For example, to make a single multistatement transaction commit asynchronously when the default is the opposite, issue SET LOCAL synchronous_commit TO OFF within the transaction.

Per datawarehouse valorizzato a off

full_page_writes 

Quando questo parametro è attivo, il server PostgreSQL™ scrive l'intero contenuto di ogni pagina disco nel WAL durante la prima modifica di quella pagina dopo un checkpoint. Questo è necessario perchè la scrittura di una pagina che è in elaborazione durante un blocco del sistema operativo potrebbe essere completata solo parzialmente, portando a una pagina su disco che contiene un insieme di dati vecchi e nuovi. The row-level change data normally stored in WAL will not be enough to completely restore such a page during post-crash recovery. Storing the full page image guarantees that the page can be correctly restored, but at the price of increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)
Disabilitare questo parametro velocizza le operazioni normali, ma potrebbe portare o a una corruzione non recuperabile dei dati, o a una corruzione dei dati silenziosa, dopo un fallimento del sistema. I rischi solo simili a disabilitare fsync, sebbene minori, e dovrebbe essere disabilitato solo nelle stesse circostanze raccomandate per quel parametro.

Per datawarehouse valorizzato a off


max_connections 
Per datawarehouse scegliere un valore tra 10 e 40
 
shared_buffers (integer)

Imposta l'ammontare di memoria che il server database usa per i buffer di memoria condivisa. Il valore predefinito è tipicamente 32 megabyte (32MB), ma potrebbe essere meno se le impostazioni del kernel non lo supportano (come determinato durante l'initdb). Questa impostazione deve essere almeno 128 kilobytes. (Valori non predefiniti di BLCKSZ cambiano il minimo). Comunque, impostazioni significativamente maggiori rispetto al minimo sono di solito necessarie per buone prestazioni. Questo parametro può essere impostato solo all'avvio del server.
Se si ha un server database dedicato con 1GB o più di RAM, un valore di partenza ragionevole per shared_buffers è il 25% della memoria del sistema. Ci sono molti carichi di lavoro anche dove sono in vigore grandi valori per shared_buffers, ma dato chePostgreSQL fa affidamento anche sulla cache del sistema operativo, è improbabile che un'allocazione di più del 40% della RAM per shared_buffers funzionerà meglio rispetto a un quantitativo minore. Impostazioni più grandi per shared_buffers di solito richiedono un incremento corrispondente in checkpoint_segments, per diffondere il processo di scrittura di grandi quantità di dati nuovi o cambiati in un periodo di tempo più lungo.
Su sistemi con meno di 1GB di RAM, una percentuale di RAM più piccola è appropriata, quindi da lasciare spazio adeguato per il sistema operativo. Inoltre, su Windows, valori grandi per shared_buffers non sono così efficaci. Si potrebbero avere risultati migliori mantenendo il valore relativamente basso e usando maggiormente la cache del sistema operativo. L'intervallo utile pershared_buffers su sistemi Windows è generalmente da 64MB a 512MB.
Incrementare questo parametro potrebbe causare che PostgreSQL richieda più memoria System V condivisa rispetto a quello che permette il valore predefinito del sistema operativo. Si veda la sezione «Memoria condivisa e semafori» della guida per informazioni su come aggiustare questi parametri, se necessario. 
¼ of RAM

work_mem
Specifica l'ammontare di memoria che deve essere usata da operazioni di ordinamento interne e dalle tabelle hash prima di scrivere in file disco temporanei. Il valore predefinito è un megabyte (1MB). Si noti che per una query complessa, diverse operazioni di ordinamento o hash potrebbero essere eseguite in parallelo; ogni operazione potrà usare tanta memoria quanto specificato da questo valore prima che cominci a scrivere dati in file temporanei. Inoltre, diverse sessioni in esecuzione potrebbero fare tali operazioni concorrentemente. Perciò, la memoria totale usata potrebbe essere molte volte più grande di work_mem; è necessario tenerlo a mente quando si sceglie il valore. Operazioni di ordinamento sono usate per ORDER BY, DISTINCT e join merge. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
128MB to 1GB
 
maintenance_work_mem (integer)

Specifica l'ammontare di mamoria massimo da usare per operazioni di manutenzione, tipo VACUUM, CREATE INDEX e ALTER TABLE ADD FOREIGN KEY. Il valore predefinito è 16 megabyte (16MB). Dato che solo una di queste operazioni può essere eseguita alla volta da una sessione di database, e normalmente un'installazione non ne ha molte in esecuzione consorrentememte, è sicuro impostare questo valore significativamente maggiore rispetto a work_mem. Valori maggiori potrebbero aumentare le prestazioni del vacuum e del ripristino dei dump del database.
Si noti che quando autovacuum è in esecuzione, questa memoria potrebbe essere allocata autovacuum_max_workers volte, quindi fare attenzione a non impostare un valore predefinito troppo alto.
512MB to 1GB
 
temp_buffers 
Imposta il massimo numero di butter temporanei usati da ogni sessione di database. Questi sono buffer locali alla sessione usati solo per accedere a tabelle temporanee. Il valore predefinito è otto megabyte (8MB). L'impostazione può essere cambiata all'interno di sessioni individuali, ma solo prima del primo utilizzo di tabelle temporanee all'interno della sessione; tentativi successivi di cambiare il valore non avranno effetto su quella sessione.
Una sessione allocherà buffer temporanei come richiesto dal limite fornito da temp_buffers. Il costo di impostare un valore grande in sessioni che effettivamente non necessitano di molti buffer temporanei è solo quello di un destrittore di buffer, o circa 64 byte per incremento in temp_buffers. Comunque se un buffer è effettivamente usato, 8192 byte aggiuntivi saranno consumati (o in generale,BLCKSZ byte). 
128MB to 1GB 

effective_cache_size 
¾ of RAM
 
wal_buffers
L'ammontare di memoria usata nella memoria condivisa per dati WAL. Il valore predefinito è 64 kilobyte (64kB). L'impostazione necessita solo di essere larga abbastanza per contenere l'ammontare di dati WAL generati da una transazione tipica, dato che i dati sono scritto fuori dal disco ad ogni commit di transazione. Questo parametro può essere impostato solo all'avvio del server.
Incrementare questo parametro potrebbe causare che PostgreSQL richieda più memoria condivisa System V rispetto a quello che permette la configurazione predefinita del sistema operativo.
16MB 

PostgreSql - Gestione utenti, esempi di grant

Creazione utente: 

create user pippo with password 'pippo'

Gestione grant:

• per poter usare lo schema:

GRANT USAGE ON SCHEMA dwh to sce

• per poter creare tabelle nello schema

GRANT CREATE ON SCHEMA dwh to sce

• possibilità di leggere tutte le tabelle dello schema

GRANT SELECT ON ALL TABLES IN SCHEMA dwh to sce

• possibilità di leggere la tabella specifica

GRANT SELECT ON dwh.fct_booking to sce

PostreSQL - Enabling Implicit Cast From Integer To Boolean

I’ve been using MySQL for a while with my pet projects, but recently I’ve been writing some more complicated queries, and I’m running into a few obnoxious limitations, mostly involving known view performance problems. I’ve decided to start testing the waters in PostgreSQL, so I tried importing my data into PG by running a MySQL-generated insert statement, only to run into a PostgreSQL newbie gotcha; “Column is of type boolean but expression is of type integer”.

By default, PG does not automatically interpret “1″ or “0″ as a boolean. The arguments in favor of this limitation are that it prevents “unintended” conversions. However, I’ve been developing in Python for years, which does do this automatic conversion, and its never bitten me.

The code to disable this feature is relatively simple, although I can’t find anyone who’s mentioned it, so I post it here:

UPDATE pg_cast SET castcontext = 'i' WHERE oid IN (
 SELECT c.oid
 FROM pg_cast c
 inner join pg_type src ON src.oid = c.castsource
 inner join pg_type tgt ON tgt.oid = c.casttarget
 WHERE src.typname LIKE 'int%' AND tgt.typname LIKE 'bool%'
)

This SQL updates PG’s int-to-bool cast path to “implicit” mode, meaning PG will now automatically cast an integer to boolean if the target type is a boolean. Otherwise, in the default “emplicit” mode, you’d have to use the CAST() syntax.

After the update, the insert statement runs perfectly.

PostgreSQL - Comandi utili per amministrare il db

Verificare l'utilizzo degli indici

select * from pg_stat_user_indexes

Verificare lo stato delle tabelle

select * from pg_stat_user_tables

Verificare le query in esecuzione

select * from pg_stat_activity

Verificare l'utilizzo dei checkpoint

select * from pg_stat_bgwriter

mysqldump

The most simple way is to issue this command:

mysqldump -u [user] -p [database_name] > [backupfile].dump

This command is going to ask you for the [user] password and then will create a script which later can be used to retore the data.

Another way is to use the optimized way.

mysqldump --opt -u [user_name] -p [database_name] > [backup_file].dump

This command will use an optimized method, and will include in the script MySQL commands that will erase (drop) tables that already exists and create them again before populate the data inside.

Maybe the best way to run this command is to use the option of gzip the output file. (for obvious reasons)

Once you have your backup file, you may want to restore it someday, this is the way to do it. (remember tu unzip your file, if zipped, before)

mysql [database_name] < [backup_file].dump

Remeber that you can run

man mysqldump

for more help.

Backuping a single table from a database
mysqldump -u user_name -p database_name table_name > /var/www/backups/table_name.sql

Restoring the table into another database
mysql -u -p database_name < /var/www/backups/table_name.sql

PostgreSQL - Funzione aggregazione concatenazione sulle stringhe

Tabella PROVA

A   B
------
1   a
1   b
2   c
2   d


SELECT A,array_to_string( array_agg( B order by B ), ' - ' )  as CONCATENAZIONE
FROM PROVA
group by A

oppure

SELECT A, string_agg(distinct B,', ' order by B)
FROM PROVA
GROUP BY A


RISULTATO:



A  CONCATENAZIONE
---------------------------
1              a - b

2              c - d

PostgreSQL - Lavorare con le date

 Esempi di query con date

WHERE "BOOKING_DATE" >= '20040101'::date;
WHERE "BOOKING_DATE" >= '2004-01-01'::date;
WHERE "BOOKING_DATE" >= DATE '20040101';
WHERE "BOOKING_DATE" >= DATE '2004-01-01';

SqlServer - Lavorare con le date

Convert (datatype, datetime string, date style)

• mon dd yyyy hh:miAM (or PM) 

  Select convert(varchar, getdate(), 100)

• mon dd yyyy hh:mi:ss:mmmAM (or PM)

  Select convert(varchar, getdate(), 109) 

• yyyymmdd

  Select convert(varchar, getdate(), 112)

• dd mon yyyy hh:mm:ss:mmm(24h)

  Select convert(varchar, getdate(), 113)

• hh:mm:ss:mmm(24h) 

  Select convert(varchar, getdate(), 114)

• yyyy-mm-dd hh:mi:ss(24h)

  Select convert(varchar, getdate(), 120)

• yyyy-mm-dd hh:mi:ss.mmm(24h)

  Select convert(varchar, getdate(), 121)

• yyyy-mm-dd Thh:mm:ss:mmm(no spaces)

  Select convert(varchar, getdate(), 126)

  
  
  

  No century (yy)	Con century (yyyy)	Formato
  -	0 or 100	mon dd yyyy hh:miAM (or PM)
  1	101	mm/dd/yyyy
  2	102	yy.mm.dd
  3	103	dd/mm/yyyy
  4	104	dd.mm.yy
  5	105	dd-mm-yy
  6	106	dd mon yy
  7	107	Mon dd, yy
  8	108	hh:mi:ss
  -	9 or 109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
  10	110	mm-dd-yy
  11	111	yy/mm/dd
  12	112	yymmdd yyyymmdd
  -	13 or 113	dd mon yyyy hh:mi:ss:mmm(24h)
  14	114	hh:mi:ss:mmm(24h)
  -	20 or 120	yyyy-mm-dd hh:mi:ss(24h)
  -	21 or 121	yyyy-mm-dd hh:mi:ss.mmm(24h)
  -	126	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
  -	127	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
  -	130	dd mon yyyy hh:mi:ss:mmmAM
  -	131	dd/mm/yy hh:mi:ss:mmmAM

  
  

Elenco delle query in esecuzione (PostgreSQL e MySql)

Ecco come vedere l'elenco delle query in esecuzione:

PostgreSQL

select * from pg_stat_activity

MySql

show full processlist

Cancellare una query in esecuzione:

PostgreSQL

select pg_cancel_backend(8560)