Estou despejando meu banco de dados Postgres usando pg_dump (em formato de texto simples) e, em seguida, restaurando-o simplesmente usando psql (com a -fopção).

O que levanta a questão: estou perdendo alguma coisa por não usar o pg_restore , que parece uma ferramenta de restauração especializada (em comparação com o genérico psql)?

Posso controlar opções como desabilitar gatilhos e outros usando pg_dumpparâmetros. Para que serve, então pg_restore? formatos de despejo de texto não simples ?

Estou com um problema bem chato. Eu quero usar o INNODB como meu principal mecanismo de banco de dados e desistir do MyISAM, pois preciso do primeiro para usar o galera-cluster para redundância.

Copiei (a descrição segue) a newbb_posttabela para uma nova tabela chamada newbb_innoposte alterei para InnoDB. As tabelas atualmente contêm 5,390,146entradas cada.

Executando essas seleções em um banco de dados recém-iniciado (portanto, nenhum armazenamento em cache está envolvido neste momento!) o banco de dados produz os seguintes resultados (omitindo a saída completa, observe que nem peço ao banco de dados para classificar os resultados):

SELECT post.postid, post.attach FROM newbb_post AS post WHERE post.threadid = 51506;

.
.
| 5401593 | 0 |
| 5401634 | 0 |
+---------+--------+
62510 linhas no conjunto (0,13 seg)

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
.
.
| 5397410 | 0 |
| 5397883 | 0 |
+---------+--------+
62510 linhas em conjunto (1 min 22,19 seg)

0,13 segundos a 86,19 segundos (!)

Estou me perguntando por que isso está acontecendo. Eu li algumas respostas aqui no Stackexchange envolvendo o InnoDB e algumas estão sugerindo aumentar o innodb_buffer_pooltamanho para 80% da RAM instalada. Isso não resolverá o problema, que a consulta inicial a um ID específico levará pelo menos 50 vezes mais e paralisará todo o servidor da Web, enfileirando conexões e consultas para o banco de dados. Depois disso, o cache/buffer pode ser ativado, mas existem mais de 100.000 threads nesse banco de dados, portanto, é muito provável que o cache nunca mantenha todas as consultas relevantes a serem atendidas.

As consultas acima são simples (sem junções) e todas as chaves são usadas:

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+------------- -------------------+----------+---- -----+-------+--------+-------+
| identificação | select_type | mesa | tipo | chaves_possíveis | chave | key_len | ref | linhas | Extra |
+------+-------------+-------+------+------------- -------------------+----------+---- -----+-------+--------+-------+
| 1 | SIMPLES | postar | ref | threadid,threadid_2,threadid_visible_dateline | threadid | 4 | const | 120144 | |
+------+-------------+-------+------+------------- -------------------+----------+---- -----+-------+--------+-------+

Esta é a tabela MyISAM:

CREATE TABLE `newbb_post` (
  `postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `threadid` int(10) unsigned NOT NULL DEFAULT '0',
  `parentid` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(100) NOT NULL DEFAULT '',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(250) NOT NULL DEFAULT '',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  texto médio `pagetext`,
  `allowsmilie` smallint(6) NOT NULL DEFAULT '0',
  `showsignature` smallint(6) NOT NULL DEFAULT '0',
  `ipaddress` varchar(15) NOT NULL DEFAULT '',
  `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `visible` smallint(6) NOT NULL DEFAULT '0',
  `attach` smallint(5) unsigned NOT NULL DEFAULT '0',
  `infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
  `reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
  `importthreadid` bigint(20) NOT NULL DEFAULT '0',
  `importpostid` bigint(20) NOT NULL DEFAULT '0',
  `converted_2_utf8` int(11) NOT NULL,
  `htmlstate` enum('off','on','on_nl2br') NOT NULL DEFAULT 'on_nl2br',
  CHAVE PRIMÁRIA (`postid`),
  KEY `threadid` (`threadid`,`userid`),
  KEY `importpost_index` (`importpostid`),
  KEY `dateline` (`dateline`),
  KEY `threadid_2` (`threadid`,`visible`,`dateline`),
  KEY `converted_2_utf8` (`converted_2_utf8`),
  KEY `threadid_visible_dateline` (`threadid`,`visible`,`dateline`,`userid`,`postid`),
  KEY `ipaddress` (`ipaddress`),
  KEY `userid` (`userid`,`parentid`),
  KEY `user_date` (`userid`,`dateline`)
) ENGINE=MyISAM AUTO_INCREMENT=5402802 DEFAULT CHARSET=latin1

e esta é a Tabela InnoDB (é exatamente a mesma):

CREATE TABLE `newbb_innopost` (
  `postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `threadid` int(10) unsigned NOT NULL DEFAULT '0',
  `parentid` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(100) NOT NULL DEFAULT '',
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(250) NOT NULL DEFAULT '',
  `dateline` int(10) unsigned NOT NULL DEFAULT '0',
  texto médio `pagetext`,
  `allowsmilie` smallint(6) NOT NULL DEFAULT '0',
  `showsignature` smallint(6) NOT NULL DEFAULT '0',
  `ipaddress` varchar(15) NOT NULL DEFAULT '',
  `iconid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `visible` smallint(6) NOT NULL DEFAULT '0',
  `attach` smallint(5) unsigned NOT NULL DEFAULT '0',
  `infraction` smallint(5) unsigned NOT NULL DEFAULT '0',
  `reportthreadid` int(10) unsigned NOT NULL DEFAULT '0',
  `importthreadid` bigint(20) NOT NULL DEFAULT '0',
  `importpostid` bigint(20) NOT NULL DEFAULT '0',
  `converted_2_utf8` int(11) NOT NULL,
  `htmlstate` enum('off','on','on_nl2br') NOT NULL DEFAULT 'on_nl2br',
  CHAVE PRIMÁRIA (`postid`),
  KEY `threadid` (`threadid`,`userid`),
  KEY `importpost_index` (`importpostid`),
  KEY `dateline` (`dateline`),
  KEY `threadid_2` (`threadid`,`visible`,`dateline`),
  KEY `converted_2_utf8` (`converted_2_utf8`),
  KEY `threadid_visible_dateline` (`threadid`,`visible`,`dateline`,`userid`,`postid`),
  KEY `ipaddress` (`ipaddress`),
  KEY `userid` (`userid`,`parentid`),
  KEY `user_date` (`userid`,`dateline`)
) ENGINE=InnoDB AUTO_INCREMENT=5402802 DEFAULT CHARSET=latin1

Servidor, com 32 GB de RAM:

Versão do servidor: 10.0.12-MariaDB-1~trusty-wsrep-log distribuição binária mariadb.org, wsrep_25.10.r4002

Se você precisar de todas as configurações de variáveis ​​innodb_, posso anexar isso a este post.

Atualizar:

Eu soltei TODOS os índices além do índice primário, depois o resultado ficou assim:

.
.
| 5402697 | 0 |
| 5402759 | 0 |
+---------+--------+
62510 linhas em conjunto (29,74 seg)

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+------------- --+------+---------+------+---------+------------- +
| identificação | select_type | mesa | tipo | chaves_possíveis | chave | key_len | ref | linhas | Extra |
+------+-------------+-------+------+------------- --+------+---------+------+---------+------------- +
| 1 | SIMPLES | postar | TODOS | NULO | NULO | NULO | NULO | 5909836 | Usando onde |
+------+-------------+-------+------+------------- --+------+---------+------+---------+------------- +
1 linha em conjunto (0,00 seg)

Depois disso, apenas adicionei um índice de volta ao mix, threadid, os resultados foram os seguintes:

.
.
| 5402697 | 0 |
| 5402759 | 0 |
+---------+--------+
62510 linhas no conjunto (11,58 seg)

EXPLAIN SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;
+------+-------------+-------+------+------------- --+----------+---------+-------+--------+-------+
| identificação | select_type | mesa | tipo | chaves_possíveis | chave | key_len | ref | linhas | Extra |
+------+-------------+-------+------+------------- --+----------+---------+-------+--------+-------+
| 1 | SIMPLES | postar | ref | threadid | threadid | 4 | const | 124622 | |
+------+-------------+-------+------+------------- --+----------+---------+-------+--------+-------+
1 linha em conjunto (0,00 seg)

Estranho é que, sem nenhum índice relevante, a varredura completa levou apenas 29 segundos em comparação com os 88 segundos usando índices (!).

Com apenas um índice perfeitamente adaptado, ele ainda leva 11 segundos para ser concluído - ainda muito lento para qualquer uso no mundo real.

Atualização 2:

Eu configurei o MySQL (5.5.38-0ubuntu0.14.04.1 (Ubuntu)) em outro servidor com exatamente a mesma configuração de hardware e exatamente o mesmo banco de dados/tabelas.

Os resultados são quase os mesmos, primeiro a tabela MyISAM:

.
.
| 5401593 | 0 |
| 5401634 | 0 |
+---------+--------+
62510 linhas em conjunto (0,14 seg)

E este é o resultado da tabela InnoDB

.
.
| 5397410 | 0 |
| 5397883 | 0 |
+---------+--------+
62510 linhas em conjunto (1 min 17,63 seg)

ATUALIZAÇÃO 3: o conteúdo de my.cnf

# Arquivo de configuração do servidor de banco de dados MariaDB.
#
# Você pode copiar este arquivo para um dos seguintes:
# - "/etc/mysql/my.cnf" para definir opções globais,
# - "~/.my.cnf" para definir opções específicas do usuário.
#
# Pode-se usar todas as opções longas que o programa suporta.
# Execute o programa com --help para obter uma lista de opções disponíveis e com
# --print-defaults para ver qual ele realmente entenderia e usaria.
#
# Para explicações veja
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Isso será passado para todos os clientes mysql
# Foi relatado que as senhas devem ser colocadas entre tiques/aspas
# especialmente se eles contiverem "#" caracteres...
# Lembre-se de editar /etc/mysql/debian.cnf quando mudar a localização do socket.
[cliente]
porta = 3306
socket = /var/run/mysqld/mysqld.sock

# Aqui estão as entradas para alguns programas específicos
# Os valores a seguir pressupõem que você tenha pelo menos 32M de ram

# Isso era formalmente conhecido como [safe_mysqld]. Ambas as versões estão atualmente analisadas.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
legal = 0

[mysqld]
#
# * Configurações básicas
#
usuário = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
porta = 3306
baseadoir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
salto-bloqueio externo
#
# Em vez de pular a rede, o padrão agora é ouvir apenas em
# localhost que é mais compatível e não menos seguro.
endereço de ligação = 127.0.0.1
#
# * Afinação
#
max_connections = 100
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16 milhões
tmp_table_size = 32M
max_heap_table_size = 32 milhões
#
# * MeuISAM
#
# Isso substitui o script de inicialização e verifica as tabelas MyISAM se necessário
# na primeira vez em que são tocados. Em caso de erro, faça uma cópia e tente um reparo.
myisam_recover = BACKUP
key_buffer_size = 128 milhões
#open-files-limit = 2000
table_open_cache = 400
myisam_sort_buffer_size = 512M
inserção_concorrente = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
#
# * Configuração do Cache de Consulta
#
# Cache apenas pequenos conjuntos de resultados, para que possamos encaixar mais no cache de consulta.
query_cache_limit = 128K
query_cache_size = 64M
# para configurações mais intensivas de gravação, defina como DEMAND ou OFF
#query_cache_type = DEMANDA
#
# * Log e Replicação
#
# Ambos os locais são girados pelo cronjob.
# Esteja ciente de que esse tipo de log é um assassino de desempenho.
# A partir do 5.1 você pode habilitar o log em tempo de execução!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# O log de erros vai para o syslog devido a /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# queremos saber sobre erros de rede e tal
log_warnings = 2
#
# Habilite o log de consultas lentas para ver consultas com duração especialmente longa
#slow_query_log[={0|1}]
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit = 1000
log_slow_verbosity = query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# O seguinte pode ser usado como logs de backup fáceis de reproduzir ou para replicação.
# nota: se você estiver configurando um slave de replicação, veja README.Debian sobre
# outras configurações que você pode precisar alterar.
#server-id = 1
#report_host = master1
#auto_increment_increment = 2
#auto_increment_offset = 1
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
# não é fabuloso para o desempenho, mas é mais seguro
#sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100M
# escravos
#relay_log = /var/log/mysql/relay-bin
#relay_log_index = /var/log/mysql/relay-bin.index
#relay_log_info_file = /var/log/mysql/relay-bin.info
#log_slave_updates
#somente leitura
#
# Se os aplicativos o suportarem, esse sql_mode mais estrito impede alguns
# erros como inserir datas inválidas etc.
#sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# O InnoDB é habilitado por padrão com um arquivo de dados de 10 MB em /var/lib/mysql/.
# Leia o manual para mais opções relacionadas ao InnoDB. Existem muitos!
default_storage_engine = InnoDB
# você não pode simplesmente alterar o tamanho do arquivo de log, requer um procedimento especial
#innodb_log_file_size = 50 milhões
innodb_buffer_pool_size = 20G
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
#
# * Recursos de segurança
#
# Leia o manual também, se você quiser chroot!
#chroot = /var/lib/mysql/
#
# Para gerar certificados SSL eu recomendo o OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
rápido
nomes de citações
max_allowed_packet = 16M

[mysql]
#no-auto-rehash # início mais rápido do mysql, mas sem conclusão da guia

[isamchk]
key_buffer = 16M

#
# * IMPORTANTE: Configurações adicionais que podem substituir as deste arquivo!
# Os arquivos devem terminar com '.cnf', caso contrário serão ignorados.
#
!incluído em /etc/mysql/conf.d/

E o conteúdo das variáveis ​​inno:

MariaDB [(nenhuma)]> MOSTRAR VARIÁVEIS COMO 'inno%';
+-------------------------------------------+----- -------------------+
| Nome_variável | Valor |
+-------------------------------------------+----- -------------------+
| innodb_adaptive_flushing | LIGADO |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | LIGADO |
| innodb_adaptive_hash_index_partitions | 1 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | DESLIGADO |
| innodb_api_enable_binlog | DESLIGADO |
| innodb_api_enable_mdl | DESLIGADO |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | DESLIGADO |
| innodb_buffer_pool_dump_now | DESLIGADO |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | DESLIGADO |
| innodb_buffer_pool_load_at_startup | DESLIGADO |
| innodb_buffer_pool_load_now | DESLIGADO |
| innodb_buffer_pool_populate | DESLIGADO |
| innodb_buffer_pool_size | 21474836480 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | tudo |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | LIGADO |
| innodb_cleaner_lsn_age_factor | high_checkpoint |
| innodb_cmp_per_index_enabled | DESLIGADO |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_corrupt_table_action | afirmar |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_disable_sort_file_cache | DESLIGADO |
| innodb_doublewrite | LIGADO |
| innodb_empty_free_list_algorithm | recuar |
| innodb_fake_changes | DESLIGADO |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antílope |
| innodb_file_format_check | LIGADO |
| innodb_file_format_max | Antílope |
| innodb_file_per_table | LIGADO |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 1 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | DESLIGADO |
| innodb_force_recovery | 0 |
| innodb_foreground_preflush | exponencial_backoff |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | DESLIGADO |
| innodb_ft_enable_stopword | LIGADO |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 400 |
| innodb_io_capacity_max | 2000 |
| innodb_kill_idle_transaction | 0 |
| innodb_large_prefix | DESLIGADO |
| innodb_lock_wait_timeout | 50 |
| innodb_locking_fake_changes | LIGADO |
| innodb_locks_unsafe_for_binlog | DESLIGADO |
| innodb_log_arch_dir | ./ |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | DESLIGADO |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | LIGADO |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_lru_scan_depth | 1024 |
| innodb_max_bitmap_file_size | 104857600 |
| innodb_max_changed_pages | 1000000 |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_dirty_pages_pct_lwm | 0 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 400 |
| innodb_optimize_fulltext_only | DESLIGADO |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | DESLIGADO |
| innodb_purge_batch_size | 300 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | DESLIGADO |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | DESLIGADO |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | DESLIGADO |
| innodb_rollback_segments | 128 |
| innodb_sched_priority_cleaner | 19 |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | LIGADO |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | DESLIGADO |
| innodb_stats_persistent | LIGADO |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | DESLIGADO |
| innodb_status_output_locks | DESLIGADO |
| innodb_strict_mode | DESLIGADO |
| innodb_support_xa | LIGADO |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | LIGADO |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_track_changed_pages | DESLIGADO |
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_atomic_writes | DESLIGADO |
| innodb_use_fallocate | DESLIGADO |
| innodb_use_global_flush_log_at_trx_commit | LIGADO |
| innodb_use_native_aio | LIGADO |
| innodb_use_stacktrace | DESLIGADO |
| innodb_use_sys_malloc | LIGADO |
| innodb_version | 5.6.17-65.0 |
| innodb_write_io_threads | 4 |
+-------------------------------------------+----- -------------------+
143 linhas em conjunto (0,02 seg)

O número de núcleos da máquina é 8, é um

Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHza partir de/proc/cpuinfo

One last note: Ran the queries with the indexes suggested by RolandoMYSQLDBA, and the queries took about 11-20s each. I do want to point out that it is crucial for me (this is the main table of a bulletin board) that the first query about a threadid returns in less than a second, as there are more than 60.000 threads and google-bots constantly crawl these threads.

Eu tenho uma tabela existente:

CREATE TABLE dbo.ProofDetails
(
    ProofDetailsID int NOT NULL 
        CONSTRAINT PK_ProofDetails 
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , ProofID int NULL
    , IDShownToUser int NULL
    , UserViewedDetails bit NOT NULL 
        CONSTRAINT DF_ProofDetails_UserViewedDetails 
        DEFAULT ((0))
);

Esta tabela tem 150.000.000 linhas. O sistema está em operação 24x7x365, portanto, não há janelas de manutenção que ocorrem regularmente.

Desejo adicionar um índice à tabela e, com a edição Enterprise do SQL Server, devo poder fazer isso sem bloquear o acesso de gravação à tabela. O comando que usei foi:

CREATE INDEX IX_ProofDetails_ProofID_Etc 
ON dbo.ProofDetails (ProofID, IDShownToUser)
INCLUDE (UserViewedDetails)
WITH (ONLINE=ON
    , ALLOW_ROW_LOCKS=ON
    , ALLOW_PAGE_LOCKS=ON
    , FILLFACTOR=100
    , MAXDOP=4
);

Executei a instrução sozinha no SSMS, pressionando F5. Ele funcionou por mais de um minuto, depois começou a bloquear outras sessões. Cancelei imediatamente o CREATE INDEXcomando, pois não posso bloquear outras sessões.

Durante o primeiro minuto, nada estava bloqueando meu CREATE INDEXcomando, sys.dm_exec_requestsmostrou o processo com um tipo de espera CXPACKET- é claro. Eu não acho que isso seja uma coisa ruim, já que a operação foi paralelizada.

Eu não tive muito tempo para inspecionar a saída do sys.dm_exec_requests. Houve apenas uma única linha retornada da consulta WHERE session_id = xxx. As sessões bloqueadas estavam tentando inserir linhas na tabela de destino.

Não sei quanto tempo os bloqueios duraram, exceto para dizer que cancelei a execução da instrução cerca de 2 minutos após o início. Os bloqueios estavam ocorrendo por cerca de um minuto naquele ponto.

Estou entendendo mal a implementação de WITH (ONLINE=ON)? Ou há algo mais que eu preciso estar ciente?

O servidor é uma máquina bastante robusta, com 2 processadores quad-core Xeon E5-2643 de 3,3 Ghz, 192 GB de RAM e armazenamento SAN com capacidade para mais de 5.000 iops. A CPU está normalmente abaixo de 20%, a RAM é 93% utilizada, principalmente pelo SQL Server. Não há mais nada em execução na caixa, apenas Windows Server 2012 e SQL Server 2012.

De acordo com um comentário sobre esta pergunta que fiz sobre conexões ociosas no PostgreSQL 9.2 , algumas transações não confirmadas (possivelmente relacionadas a algumas dessas conexões ociosas) podem causar alguns problemas de desempenho.

Qual é uma boa maneira de determinar se há transações não confirmadas (pontos de bônus se houver uma maneira de saber se a conexão em que estão está ociosa ou não)?

Muito obrigado!

Eu adicionei o usuário myuserao Postgres.

Em seguida, adicionei o banco de dados mydatabasena GUI do pgAdmin III e restaurei a partir de um arquivo de backup. Portanto, o proprietário de mydatabaseé o superusuário postgres.

Então tentei dar todos os direitos de acesso e modificação mydatabasepara myuser. Entrei psqlcomo usuário postgres:

psql -d template1 -U postgres  

e então eu executei esta consulta:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser

Agora eu posso usar myuserpara logar, mas se eu tentar uma consulta simples, recebo este erro:

ERROR:  permission denied for relation table_name

Estou esquecendo de algo? Você pode me ajudar a resolver isso?

Eu fiz um rascunho de aplicativo remoto em cima da libpq para PostrgreSQL . Ele se comporta bem, mas eu perfilei o funcionamento geral do aplicativo. Para cada resultado final de negócio que eu produzo, acontece que eu chamo algo como 40 cláusula select (sobre tcpip).

Tenho reminiscências do SQL-Server me lembrando de minimizar o número de interações entre meu aplicativo remoto e o banco de dados. Tendo analisado meus selects, acho que poderia reduzir esse número para 3 SELECTcláusulas, usando joins. Mas não me lembro da sintaxe para usar o resultado de a SELECTem outro arquivo SELECT.

Por exemplo:

SELECT * FROM individual
INNER JOIN publisher
ON individual.individual_id = publisher.individual_id
WHERE individual.individual_id = 'here I would like to use the results of a another select'

Este outro SELECTseria simplesmente do tipo:

SELECT identifier FROM another_table WHERE something='something'

Aqui está o layout das tabelas simplificado, declinado várias vezes para diferentes item_types ... (3 tipos totalmente diferentes, daí as 3 consultas SQL se otimizadas).

table passage
  id_passage PK
  business_field_passage bytea

table item
  id_item PK
  id_passage FK
  business_field_item text

table item_detail
  id_item_detail PK
  id_item FK
  business_field_item_detail text
  image_content bytea

Existem vários id_itempara um id_passage.
Existem vários id_item_detailpara um id_item.

Como você escreveria isso?
Qual é o nome para descrever a ação de redirecionar um select para outro (se houver)?

É possível renomear f1, f2, f3...nomes padrão ao usar row_to_jsona função apenas para algumas colunas?

eu posso fazer

row_to_json(customers)

retornando

{"id_customer":2,"first_name":"bla","last_name":"second_bla"}

Mas se eu quiser apenas nomes sem id_customer, tenho que usar

row_to_json(row(first_name, last_name))

e então eu recebo

{"f1":"bla","f2":"second_bla"}

E eu gostaria de obter esse resultado com nomes de coluna padrão ou com meus próprios. Eu sei que posso criar meu próprio tipo composto e usar

row_to_json(row(first_name, last_name))::my_custom_type

mas não é possível fazer isso direito na consulta sem criar esse tipo?

Recentemente, postei uma resposta a uma pergunta sobre mysql.db .

Então, comecei a pensar que deveria fazer a todos esta pergunta:

Tenho notado há anos que, após a instalação do MySQL 5.0+, mysql.dbé preenchido com duas entradas que permitem que os bancos de dados de teste sejam acessados ​​por usuários anônimos.

Você pode vê-lo executando esta consulta:

mysql> select * from mysql.db where SUBSTR(db,1,4) = 'test'\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
2 rows in set (0.00 sec)

Essas entradas mysql.dbrepresentam um risco de segurança e, em caso afirmativo, por que são adicionadas por padrão a uma nova instalação?

ATUALIZAÇÃO 2013-06-14 10:13 EDT

Esta manhã, alguém votou negativamente na minha pergunta, o que eu realmente não entendo. À luz desse evento, aqui está o motivo pelo qual dediquei um tempo para fazer uma refutação:

Eu instalei o MySQL 5.6.12 para um cliente esta semana em seu Staging Cluster. Decidi verificar se esse ainda era um problema contínuo:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

mysql> select db,user,host from mysql.db where LEFT(db,4)='test';
+---------+------+------+
| db      | user | host |
+---------+------+------+
| test    |      | %    |
| test\_% |      | %    |
+---------+------+------+
2 rows in set (0.10 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-06-14 10:10:13 |
+---------------------+
1 row in set (0.00 sec)

mysql>

Adivinha? Isso ainda é um problema até hoje !!!

MORAL DA HISTÓRIA: Verifique mysql.dbimediatamente após a instalação e remova os logins anônimos e apague essas entradas de teste mysql.dbsem demora.

Muitos dos meus bancos de dados têm campos definidos como varchars. Isso não tem sido um grande problema desde que moro e trabalho na América (onde a única língua que existe é "americano". ahem )

Depois de trabalhar com bancos de dados por cerca de 5 anos, descobri que eventualmente tive problemas com a natureza limitada do campo varchar e tive que modificar meus campos para armazenar dados como nvarchars. Depois de fazer outra atualização em uma tabela, convertendo um campo varchar em um nvarchar, pensei-- por que ainda estamos fazendo dessa maneira? Há muito tempo tomei a decisão mental de definir todos os meus novos campos de texto para nvarchar, em vez de varchar, que é o que aprendi a fazer em meus livros didáticos quando estava na escola, 10 anos atrás.

É 2011 e houve uma nova versão do SQL Server no ano passado. Por que continuamos a oferecer suporte a um tipo de dados varchar quando podemos/devemos usar nvarchar?

Eu sei que muitas vezes é argumentado que nvarchars são "duas vezes maiores" que varchars, então o uso do espaço de armazenamento pode ser um argumento para manter varcars.

No entanto, os usuários de hoje podem definir seus nvarchars para armazenar os dados como UTF-8 em vez do padrão UTF-16 se quiserem economizar espaço de armazenamento. Isso permitiria a codificação de 8 bits, se isso for principalmente desejável, ao mesmo tempo em que garante que o raro caractere de 2 a 8 bytes que é inserido em seu banco de dados não quebraria nada.

Estou esquecendo de algo? Existe uma boa razão para isso não ter mudado nos últimos 15-20 anos?

Em sua resposta para Qual é o melhor: colunas de identidade ou valores de id únicos gerados? mrdenny disse:

Quando o SQL Denali for lançado, ele oferecerá suporte a sequências que serão mais eficientes que a identidade, mas você não pode criar algo mais eficiente sozinho.

Eu não tenho tanta certeza. Conhecendo as sequências do Oracle , tenho que criar um gatilho para inserção, encapsular cada inserção em uma chamada de procedimento armazenado ou rezar para não esquecer de usar corretamente a sequência quando fizer uma inserção ad-hoc.

Duvido que as vantagens das sequências sejam tão óbvias.