Master-Key

Vivendo e aprendendo J. Neto. Criamos o relacionamento para o STATUS, mas não obtivemos uma ganho significativo. Ótima a idéia de Dividir para Conquistar. Já repassei ao nosso programador para que analise a possibilidade. Teremos de criar uma cópia do DB, porque não podemos realizar no DB original, devido à necessidade de bloquear os acessos dos usuários e Clientes. Como a tarefa será demorada, quando tivermos um comparativo das situações, estarei postando o resultado. Obrigado pela orientação.

Primeiramente, desculpem-nos a extensão do tópico, mas não vemos como não o ser. Temos uma DB, com 29 tabelas(MyISAM), 702.773 registros e 182,4 Mbytes - NOW. O sistema basicamente opera com cadastro e gerenciamento de Usuários e Clientes: Os Clientes realizam cadastro e compras que, geram Pedidos e Produtos (controlado via CB - código de barras - único de 24 caracteres). Temos 10 níveis de Usuários, que conforme seus privilégios gerenciam os Clientes e seus Pedidos, recebem os produtos comprados (pelos Clientes) e os Liquidam (conciliam o Produto recebido contra nossa DB). O controle central é realizado sobre a variação (atualização) dos campos STATUS dos Pedidos e dos Produtos (via CB). Temos uma tabela BASE dos Produtos e uma outra dos Produtos_Conciliados. À medida que novos produtos são comprados, inserimos na tabela base. À medida que são Conciliados (retornam), atualizamos a tabela base e inserimos na Produtos Conciliados. O gerenciamento envolve obviamente o controle de criação, pagamento, liberação, coleta, entrega, circulação e liquidação - com seus respectivos STATUS e datas (DATE e/ou time Stamps). Basica e grosseiramente, é este o resumo do sistema. Sabemos que nosso Calcanhar de Aquiles é o campo CB (char(24)) que se interelaciona com o campo STATUS(varchar(15). Pelo tamanho do CB, somos obrigados a utilizar como texto, provocando table scan - memo indexado. Ocorre lentidão tanto na criação dos registros, como atualização dos STATUS destes, em ambas as tabelas principais. Não usamos JOIN, as clausulas WHERE dentro dos SELECTs estão com os índices apropriados, utilizamos alguns INSERT junto com os SELECTS, possuimos uma tabela de expurgo para dados mais antigos etc; tudo conferido. Como conversado com o Denis, não podemos fornecer muitas informações consideradas privativas, mas certamente podemos apresentar a situação do ambiente e algumas básicas. 1- Nosso maior problema é na leitura do CB (via leitora ou manual), quando o CB é verificado na tabela base e existindo, seus dados são mostrados (via grid) e é efetuado o REFRESH do campo; para a leitura do CB seguinte. Chega a demorar 30 segundos esta operação, tornando impossível a sua utilização em tempo real. 2- Paliativamente, foi criada um aplicação em Delphi(FB-DBExp-BDE) que baixa os dados necessários do DB, realiza a captura do CB e sobe os dados para o DB. 3- Na execução das rotinas de Download e Upload dos dados, o MySQL efetua um loched nas tabelas, de maneira que o ambiente torna-se ainda mais lento (quase instável) - sem falar no alto volume de SLEEP. Mas está ajudando. 4- Operamos em 24 x 7 x 365 - com Usuários e Clientes acessando simultâneamente. Vamos lá. Nosso Sever - VPS- é novo e não está adequadamente configurado... já sabemos disto. Configuração básica: On this screen you can see the container resources consumption overview. Please pay attention to the resources nearing their maximum. CPU Usage CPU 0.18% Load Average 0.02, 0.01, 0.0 Memory Usage Resource Used Free Total Memory 174.9 MB 17.08% 849.1 MB 1 GB Disk Usage Resource Used Free Total Disk Space 3.89 GB 4.86% 76.11 GB 80.00 GB Disk Inodes 172,353 12.31% 1,227,647 1,400,000 ========= Serviços====== crond Running httpd Stopped iptables Running mysql Running sshd Running xinetd Running ======================= Tráfico do servidor: Essas tabelas mostram as estatísticas do tráfico da rede neste servidor MySQL desde o início. Tráfego ø por hora Recebido 15 MB 732 KB Enviar 555 MB 27 MB Total 570 MB 28 MB Conexões ø por hora % máx. de conexões concorrentes 9 --- --- Tentativas falharam 0 0.00 0.00% Abortado 6 0.00 k 0.18% Total 3,408 166.23 100.00% ======================== SHOW VARIABLES LIKE '%query_cache%'; have_query_cache YES query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type ON query_cache_wlock_invalidate OFF ===================== SHOW STATUS LIKE '%qcache%'; Qcache_free_blocks 0 Qcache_free_memory 0 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Qcache_queries_in_cache 0 Qcache_total_blocks 0 ===================== SHOW STATUS ; Variable_name Value Aborted_clients 6 Aborted_connects 0 Binlog_cache_disk_use 0 Binlog_cache_use 0 Bytes_received 507 Bytes_sent 7960 Com_admin_commands 0 Com_alter_db 0 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_call_procedure 0 Com_change_db 0 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 0 Com_create_user 0 Com_dealloc_sql 0 Com_delete 0 Com_delete_multi 0 Com_do 0 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 0 Com_drop_user 0 Com_execute_sql 0 Com_flush 0 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 0 Com_insert_select 0 Com_kill 0 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize 0 Com_preload_keys 0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_rename_table 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 0 Com_savepoint 0 Com_select 2 Com_set_option 4 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_charsets 1 Com_show_collations 1 Com_show_column_types 0 Com_show_create_db 0 Com_show_create_table 0 Com_show_databases 1 Com_show_errors 0 Com_show_fields 0 Com_show_grants 1 Com_show_innodb_status 0 Com_show_keys 0 Com_show_logs 0 Com_show_master_status 0 Com_show_ndb_status 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_privileges 0 Com_show_processlist 0 Com_show_slave_hosts 0 Com_show_slave_status 0 Com_show_status 1 Com_show_storage_engines 0 Com_show_tables 0 Com_show_triggers 0 Com_show_variables 3 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 0 Com_stmt_execute 0 Com_stmt_fetch 0 Com_stmt_prepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Variable_name Value Com_unlock_tables 0 Com_update 0 Com_update_multi 0 Com_xa_commit 0 Com_xa_end 0 Com_xa_prepare 0 Com_xa_recover 0 Com_xa_rollback 0 Com_xa_start 0 Compression OFF Connections 3390 Created_tmp_disk_tables 0 Created_tmp_files 2 Created_tmp_tables 7 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 0 Handler_delete 0 Handler_discover 0 Handler_prepare 0 Handler_read_first 0 Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 180 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 0 Handler_write 306 Innodb_buffer_pool_pages_data 0 Innodb_buffer_pool_pages_dirty 0 Innodb_buffer_pool_pages_flushed 0 Innodb_buffer_pool_pages_free 0 Innodb_buffer_pool_pages_misc 0 Innodb_buffer_pool_pages_total 0 Innodb_buffer_pool_read_ahead_rnd 0 Innodb_buffer_pool_read_ahead_seq 0 Innodb_buffer_pool_read_requests 0 Innodb_buffer_pool_reads 0 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 0 Innodb_data_fsyncs 0 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 0 Innodb_data_reads 0 Innodb_data_writes 0 Innodb_data_written 0 Innodb_dblwr_pages_written 0 Innodb_dblwr_writes 0 Innodb_log_waits 0 Innodb_log_write_requests 0 Innodb_log_writes 0 Innodb_os_log_fsyncs 0 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 0 Innodb_page_size 0 Innodb_pages_created 0 Innodb_pages_read 0 Innodb_pages_written 0 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 0 Innodb_row_lock_time_avg 0 Innodb_row_lock_time_max 0 Innodb_row_lock_waits 0 Innodb_rows_deleted 0 Innodb_rows_inserted 0 Innodb_rows_read 0 Innodb_rows_updated 0 Key_blocks_not_flushed 0 Key_blocks_unused 6944 Key_blocks_used 7245 Key_read_requests 2099159 Key_reads 31755 Key_write_requests 20925 Key_writes 14258 Last_query_cost 0.000000 Max_used_connections 9 Ndb_cluster_node_id 0 Ndb_config_from_host Ndb_config_from_port 0 Ndb_number_of_data_nodes 0 Not_flushed_delayed_rows 0 Open_files 107 Open_streams 0 Open_tables 54 Opened_tables 0 Prepared_stmt_count 0 Qcache_free_blocks 0 Qcache_free_memory 0 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Variable_name Value Qcache_queries_in_cache 0 Qcache_total_blocks 0 Queries 199523 Questions 14 Rpl_status NULL Select_full_join 0 Select_full_range_join 0 Select_range 0 Select_range_check 0 Select_scan 7 Slave_open_temp_tables 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 0 Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 Table_locks_immediate 178672 Table_locks_waited 5 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 0 Threads_connected 1 Threads_created 3389 Threads_running 1 Uptime 73586 Uptime_since_flush_status 199523 ======================== Variáveis e configurações do servidor - ocultando informações privadas.... Variáveis Valor da sessão / Valor global auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 (Valor global) latin1 character set connection utf8 (Valor global) latin1 character set database latin1 character set filesystem binary character set results utf8 (Valor global) latin1 character set server latin1 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_unicode_ci (Valor global) latin1_swedish_ci collation database latin1_swedish_ci collation server latin1_swedish_ci completion type 0 concurrent insert 1 connect timeout 10 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 keep files on create OFF engine condition pushdown OFF expire logs days 0 flush OFF flush time 0 ft boolean syntax + -><()~*:""&| ft max word len 84 ft min word len 4 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine YES have compress YES have crypt YES have csv YES have dynamic loading YES have example engine YES have federated engine YES have geometry YES have innodb DISABLED have isam NO have merge engine YES have ndbcluster DISABLED have openssl NO have ssl NO have query cache YES have raid NO have rtree keys YES have symlink YES hostname xxx.yyyyy.com init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path innodb data home dir innodb adaptive hash index ON innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb rollback on timeout OFF innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,384,512 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 low priority updates OFF lower case file system OFF lower case table names 0 max allowed packet 1,048,576 max binlog cache size 4,294,963,200 max binlog size 1,073,741,824 max connect errors 10 max connections 100 max delayed threads 20 max error count 64 max heap table size 16,777,216 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1,024 max prepared stmt count 16,382 max relay log size 0 max seeks for key 4,294,967,295 max sort length 1,024 max sp recursion depth 0 max tmp tables 32 max user connections 0 max write lock count 4,294,967,295 multi range count 256 myisam data pointer size 6 myisam max sort file size 2,146,435,072 myisam recover options OFF myisam repair threads 1 myisam sort buffer size 8,388,608 myisam stats method nulls_unequal ndb autoincrement prefetch sz 1 ndb force send ON ndb use exact count ON ndb use transactions ON ndb cache check time 0 ndb connectstring net buffer length 16,384 net read timeout 30 net retry count 10 net write timeout 60 new OFF old passwords ON open files limit 1,024 optimizer prune level 1 optimizer search depth 62 pid file /var/lib/mysql/xxx.yyyyy.com.pid plugin dir port 3,306 preload buffer size 32,768 profiling OFF profiling history size 15 protocol version 10 query alloc block size 8,192 query cache limit 1,048,576 query cache min res unit 4,096 query cache size 0 query cache type ON query cache wlock invalidate OFF query prealloc size 8,192 range alloc block size 4,096 read buffer size 131,072 read only OFF read rnd buffer size 262,144 relay log relay log index relay log info file relay-log.info relay log purge ON relay log space limit 0 rpl recovery rank 0 secure auth OFF secure file priv server id 0 skip external locking ON skip networking OFF skip show database OFF slave compressed protocol OFF slave load tmpdir /tmp/ slave net timeout 3,600 slave skip errors OFF slave transaction retries 10 slow launch time 2 socket /var/lib/mysql/mysql.sock sort buffer size 2,097,144 sql big selects ON sql mode sql notes ON sql warnings OFF ssl ca ssl capath ssl cert ssl cipher ssl key storage engine MyISAM sync binlog 0 sync frm ON system time zone BRT table cache 64 table lock wait timeout 50 table type MyISAM thread cache size 0 thread stack 196,608 time format %H:%i:%s time zone SYSTEM timed mutexes OFF tmp table size 33,554,432 tmpdir /tmp/ transaction alloc block size 8,192 transaction prealloc size 4,096 tx isolation REPEATABLE-READ updatable views with limit YES version 5.0.81-community version comment MySQL Community Edition (GPL) version compile machine i686 version compile os pc-linux-gnu wait timeout 28,800 ====================== O arquivo My.cnf esta na forma mais básica possível... ======================= Estrutura da Tabale BASE: Campo Tipo Collation Atrib Nulo Padrão Extra cod_vale char(5) latin1_general_ci Não cod_ped char(5) latin1_general_ci Não cod_it varchar(3) latin1_general_ci Não data_emissao datetime Não 0000-00-00 00:00:00 data_val datetime Não 0000-00-00 00:00:00 data_conc datetime Não 0000-00-00 00:00:00 empresa_var varchar(50) latin1_general_ci Não end_entrega varchar(100) latin1_general_ci Não tel char(10) latin1_general_ci Não contato varchar(50) latin1_general_ci Não cb char(24) latin1_general_ci Não valor varchar(4) latin1_general_ci Não extenso varchar(36) latin1_general_ci Não mensagem varchar(25) latin1_general_ci Não val char(6) latin1_general_ci Não valor_total varchar(9) latin1_general_ci Não status varchar(15) latin1_general_ci Sim NULL liquidante varchar(50) latin1_general_ci Sim NULL mat varchar(5) latin1_general_ci Sim NULL --------------------------- Índices: Nome chave Tipo Cardinalidade Ação Campo cb INDEX 346781 cb status INDEX 1 status ----------------- Uso do espaço Tipo Uso Dados 132.7 MB Índice 8,231.0 KB Total 140.7 MB Estatísticas do registros Comandos Valor Formato dinâmico Collation latin1_general_ci Registros 346,781 Tamanho do registro ø 401 Tamanho do registro ø 426 Bytes Criação Jun 22, 2009 as 08:46 PM Última atualização Jul 01, 2009 as 06:40 PM Última verificação Jul 01, 2009 as 06:40 PM ================== Exemplos de Comandos - somente para ter uma idéia. Capturamos o CB, verificamos se existe na tabela BASE e seu STATUS. Se existir, cfe o STUATUS é gravado nas tabelas de controle. Se existir e ainda não estiver liquidado, a tabela BASE é atualizada, grava-se os dados na tabela de controle movimentação, prepara-se outras tabelas de controle de pagamento e provisão financeira, comissões etc. Segue pequeno trecho da rotina que efetua a liquidação do produto que está em circulação: //Verificar se CB já foi conciliado $status200 = 'Conciliado'; $sql_CB_check71 = mysql_query("SELECT COUNT(cb) FROM base WHERE cb='{$cb}' and status='{$status200}'"); $uReg71 = mysql_fetch_array($sql_CB_check71,MYSQL_NUM); $cb_check71 = $uReg71[0]; if(($cb_check71 > 0) && ($v == 1)) { echo "&lt;script>alert('Já Conciliado!')</script>"; $sql18 = mysql_query("INSERT INTO rejeitados (cod_ped_conc, cb, status,usuario,data) VALUES('$lote', '$cb', '$status200','$usuario',now())") or die( mysql_error() ); $v = 0; } =============== //Após ler e encontrar CB e estando OK, os dados serão salvos na tabela conc_temp $sql4 = mysql_query("INSERT INTO conc_temp (cod_ped, cod_prod_conc, cb, num_ped, num_prod, num_item, status, valor,data_val,dt_ent) VALUES('$lote', '$cod_val', '$cb', '$numped', '$numprod', '$numitem', '$status', '$vr_val','$dt_val',now())") or die( mysql_error() ); $qdte_prod_lido = $qdte_prod_lido + 1; $qtde_prod_inval = $qtde_prod_inval + 1; } ---------- uma vez gravado na temp e atualizada a base, ocorre o REFRESH.... que é bem demorado... ======== Agradeço-lhes quaisquer ajuda e/ou sugestões, estando pronto para maiores informações.