Re: High load average (MySQL 5.6.16)
Posted by: Peter Wells
Date: February 26, 2014 08:48AM
Date: February 26, 2014 08:48AM
Hello again Rick. Thanks for your time.
I will split my answer because the forum says the body is too large.
After some reviews, when I said the updates were executed in batch, I was wrong. There are some batch inserts, but there are always less than 100 rows.
The inserts in the table STATS_DC_T_1 were performed using a CachedThreadPool, that means many parallel connections.
We've replaced it with a a single thread executing inserts one by one and load average has decreased.
It's now between 3-7 with random peaks of 10-11. I really don't know what is the normal load average in a dual CPU machine with 12 physical cores.
We can modify the thread to wait until it has N (100) inserts pending and then do them in batch.
We still don't know which queries cause the high load average.
STATS_DC_T_1 stores visits to a website.
The load average doesn't match server load in terms of users. Sometimes at night, when there's less traffic we see load average of 12.
We suspect it could be Googlebot crawling the site.
We have modified the application so in case of crawlers, the modifications triggered by these crawlers are performed in the background.
> That smells like SHOW STATUS; it needs to be SHOW GLOBAL STATUS; I checked what I could; nothing is seriously mis-configured.
mysql> show global status;
+-----------------------------------------------+---------------+
| Variable_name | Value |
+-----------------------------------------------+---------------+
| Aborted_clients | 310 |
| Aborted_connects | 22331 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 131656105617 |
| Bytes_sent | 740803009499 |
| Com_admin_commands | 31058 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 7 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 16 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 2674 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 34 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 1 |
| Com_dealloc_sql | 0 |
| Com_delete | 572450 |
| Com_delete_multi | 63 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 9 |
| Com_insert | 13702191 |
| Com_insert_select | 82154 |
| Com_install_plugin | 0 |
| Com_kill | 10 |
| Com_load | 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_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 6209 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 836943882 |
| Com_set_option | 9905990 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 19893 |
| Com_show_collations | 995025 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 370 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 1 |
| Com_show_events | 20 |
| Com_show_errors | 0 |
| Com_show_fields | 44 |
| Com_show_function_code | 0 |
| Com_show_function_status | 20 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 20 |
| Com_show_processlist | 1163 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 40 |
| Com_show_storage_engines | 6 |
| Com_show_table_status | 203 |
| Com_show_tables | 3 |
| Com_show_triggers | 20 |
| Com_show_variables | 3296422 |
| 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_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 13011011 |
| Com_update_multi | 7757 |
| 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 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 3318759 |
| Created_tmp_disk_tables | 12774 |
| Created_tmp_files | 129007 |
| Created_tmp_tables | 16240640 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 413459194 |
| Handler_delete | 1872751 |
| Handler_discover | 0 |
| Handler_external_lock | 1106212558 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 225499 |
| Handler_read_key | 11781266236 |
| Handler_read_last | 1142624 |
| Handler_read_next | 23349326250 |
| Handler_read_prev | 8606543001 |
| Handler_read_rnd | 149400977 |
| Handler_read_rnd_next | 1143747338 |
| Handler_rollback | 4096600 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 57628957 |
| Handler_write | 494765326 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 1249341 |
| Innodb_buffer_pool_bytes_data | 20469202944 |
| Innodb_buffer_pool_pages_dirty | 14664 |
| Innodb_buffer_pool_bytes_dirty | 240254976 |
| Innodb_buffer_pool_pages_flushed | 27593957 |
| Innodb_buffer_pool_pages_free | 2034 |
| Innodb_buffer_pool_pages_misc | 59344 |
| Innodb_buffer_pool_pages_total | 1310719 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 3289683 |
| Innodb_buffer_pool_read_ahead_evicted | 128 |
| Innodb_buffer_pool_read_requests | 134920666369 |
| Innodb_buffer_pool_reads | 190539128 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 490951318 |
| Innodb_data_fsyncs | 8275682 |
| Innodb_data_pending_fsyncs | 1 |
| Innodb_data_pending_reads | 4 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 3259453329408 |
| Innodb_data_reads | 198953204 |
| Innodb_data_writes | 52182978 |
| Innodb_data_written | 959632901632 |
| Innodb_dblwr_pages_written | 27593957 |
| Innodb_dblwr_writes | 1076435 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 79 |
| Innodb_log_write_requests | 106597380 |
| Innodb_log_writes | 23379141 |
| Innodb_os_log_fsyncs | 640222 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 55402967552 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 1507283 |
| Innodb_pages_read | 198954142 |
| Innodb_pages_written | 27593956 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 881085 |
| Innodb_row_lock_time_avg | 25 |
| Innodb_row_lock_time_max | 28064 |
| Innodb_row_lock_waits | 34153 |
| Innodb_rows_deleted | 1872364 |
| Innodb_rows_inserted | 42049968 |
| Innodb_rows_read | 43806472590 |
| Innodb_rows_updated | 18376940 |
| Innodb_num_open_files | 498 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 405421 |
| Key_blocks_used | 23728 |
| Key_read_requests | 289029110 |
| Key_reads | 22044 |
| Key_write_requests | 44841631 |
| Key_writes | 62922 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 103 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 161 |
| Open_streams | 0 |
| Open_table_definitions | 606 |
| Open_tables | 1799 |
| Opened_files | 181163 |
| Opened_table_definitions | 684 |
| Opened_tables | 1963 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 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 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 882079425 |
| Questions | 881645330 |
| Select_full_join | 681 |
| Select_full_range_join | 0 |
| Select_range | 13938967 |
| Select_range_check | 0 |
| Select_scan | 6223631 |
| Slave_heartbeat_period | |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | |
| Slave_retried_transactions | |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 123163 |
| Sort_merge_passes | 75281 |
| Sort_range | 3183441 |
| Sort_rows | 2568368567 |
| Sort_scan | 464636 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 552180850 |
| Table_locks_waited | 267 |
| Table_open_cache_hits | 552252238 |
| Table_open_cache_misses | 1892 |
| Table_open_cache_overflows | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 87 |
| Threads_connected | 16 |
| Threads_created | 103 |
| Threads_running | 10 |
| Uptime | 571675 |
| Uptime_since_flush_status | 571675 |
+-----------------------------------------------+---------------+
341 rows in set (0.33 sec)
> You have a good long_query_time; what is in the slowlog?
This type of query is performed a lot. They are usually under 1 second but there are also many of them in the slowlog over 1 sec.
DESCRIPCIONES_367 is a manually partitioned table. It has 29 million rows and it's 38,6 GB in size. We are thinking on partitioning it again manually by country (does MySQL partition work well?)
Inserts in this table tend to be slow, here is mysqldumpslow summary
Count: 130 Time=59.31s (7710s) Lock=0.00s (0s) Rows=0.0 (0), []@[127.0.0.1]
INSERT INTO DESCRIPCIONES_367 ( ID,ID_OBJETO,ELEMENTO,IDIOMA,NOMBRE,DESCRIPCION_CORTA,DESCRIPCION_LARGA,OBSERVACIONES,CONDICIONES,OTROS,KEYWORDS,CLAVE,GRUPO,USU_CREA,USU_MOD,ESTADO,ID_PADRE,TIPO_CONTENIDO,MODO_ESTATICO,MODO_KEYWORDS,ESTILO_HIJOS,TIPO_TRADUCCION,NUMERO_ELEMENTOS,ORDEN,FEC_CREA,FEC_MOD ) VALUES ( N , N , N , 'S' , null , null , 'S' , 'S' , 'S' , null , 'S' , null , null , 'S' , null , N , N , N , N , N , N , N , N , N.N , 'S' , null )
# Query_time: 1.042077 Lock_time: 0.000264 Rows_sent: 15 Rows_examined: 76
SET timestamp=1393279317;
select V.NUMERO_REFERENCIA,V.ESTADO_ACTO,V.FECHA_PUBLICACION,V.CARDINAL,V.ID_COD,V.ID_REG_ACTO,V.ID_ACTO , D.ID AS ID_DESCRIPCION,D.`DESCRIPCION_LARGA` as DESCRIPCION_CORTA,D2.DESCRIPCION_LARGA, D.`KEYWORDS` from v_inscripciones_actos_detalle_notext V LEFT JOIN descripciones_375 D on (D.id_objeto=V.id_reg_acto and d.elemento=375 and d.idioma='es' ) LEFT JOIN descripciones_367 D2 on (D2.id_objeto=V.id_reg_acto and d2.elemento=367 and d2.idioma='es' ) where 0=0 and ( V.ID_SOCIEDAD=2244245 ) and V.DESCRIBIBLE=1 ORDER BY V.FECHA_PUBLICACION DESC LIMIT 0,20;
+----+-------------+-----------------+------+----------------------------------------------------------------------+-----------------------+---------+-------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+----------------------------------------------------------------------+-----------------------+---------+-------------------------------+------+-------------+
| 1 | SIMPLE | inscripciones | ref | PRIMARY,INS_EMP_IDX,INS_FEC_PUB_IDSOC_IDX | INS_FEC_PUB_IDSOC_IDX | 4 | const | 19 | Using where |
| 1 | SIMPLE | actos_inscritos | ref | ACT_UK,ACT_ID_INS_IDX,ID_INSCRIPCION_ID_SOCIEDAD_ID_ACTO,DESCRIBIBLE | ACT_UK | 8 | inscripciones.ID | 1 | Using where |
| 1 | SIMPLE | D | ref | ELEMENTO_IDX,IDIOMA_DESCRIPCION_LARGA | ELEMENTO_IDX | 18 | const,const,func | 1 | Using where |
| 1 | SIMPLE | D2 | ref | ELEMENTO_IDX,IDIOMA_DESCRIPCION_LARGA | ELEMENTO_IDX | 20 | const,const,func | 1 | Using where |
+----+-------------+-----------------+------+----------------------------------------------------------------------+-----------------------+---------+-------------------------------+------+-------------+
mysql> show create table v_inscripciones_actos_completa_notext;
| v_inscripciones_actos_completa_notext | CREATE ALGORITHM=UNDEFINED DEFINER=``@`%` SQL SECURITY DEFINER VIEW `v_inscripciones_actos_completa_notext` AS select `inscripciones`.`ID` AS `ID`,`inscripciones`.`ID_SOCIEDAD` AS `ID_SOCIEDAD`,`inscripciones`.`NOMBRE_SOCIEDAD` AS `NOMBRE_SOCIEDAD`,`inscripciones`.`FECHA_INSCRIPCION` AS `FECHA_INSCRIPCION`,`inscripciones`.`FECHA_PUBLICACION` AS `FECHA_PUBLICACION`,`inscripciones`.`NUMERO_REFERENCIA` AS `NUMERO_REFERENCIA`,`inscripciones`.`NUMERO_ACTOS` AS `NUMERO_ACTOS`,`inscripciones`.`BOLETIN` AS `BOLETIN`,`inscripciones`.`YEAR` AS `YEAR`,`inscripciones`.`NUMERO_INSCRIPCION` AS `NUMERO_INSCRIPCION`,`inscripciones`.`TOMO` AS `TOMO`,`inscripciones`.`LIBRO` AS `LIBRO`,`inscripciones`.`FOLIO` AS `FOLIO`,`inscripciones`.`SECCION` AS `SECCION`,`inscripciones`.`HOJA` AS `HOJA`,`inscripciones`.`INSCRIPCION_TEXTO_COR` AS `INSCRIPCION_TEXTO_COR`,`inscripciones`.`USU_CREA` AS `USU_CREA`,`inscripciones`.`USU_MOD` AS `USU_MOD`,`inscripciones`.`FEC_CREA` AS `FEC_CREA`,`inscripciones`.`FEC_MOD` AS `FEC_MOD`,`inscripciones`.`ESTADO` AS `ESTADO`,`inscripciones`.`ACTOS` AS `ACTOS`,`inscripciones`.`ID_PROVINCIA` AS `ID_PROVINCIA`,`inscripciones`.`NUMERO_PROCESOS` AS `NUMERO_PROCESOS`,`inscripciones`.`FEC_PROCESO` AS `FEC_PROCESO`,`inscripciones`.`REVISION` AS `REVISION`,`inscripciones`.`URL_DATOS` AS `URL_DATOS`,`inscripciones`.`URL_PDF` AS `URL_PDF`,`inscripciones`.`URL_REFERENCIA` AS `URL_REFERENCIA`,`inscripciones`.`TIPO` AS `TIPO`,`actos_inscritos`.`ID` AS `id_acto_objeto`,`actos_inscritos`.`ID_ACTO` AS `ID_ACTO`,`actos_inscritos`.`ACTO` AS `ACTO`,`actos_inscritos`.`CARDINAL` AS `cardinal`,`actos_inscritos`.`ESTADO` AS `estado_acto` from (`inscripciones` join `actos_inscritos` on((`actos_inscritos`.`ID_INSCRIPCION` = `inscripciones`.`ID`))) | utf8 | utf8_general_ci |
mysql> show create table inscripciones;
| inscripciones | CREATE TABLE `inscripciones` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ID_SOCIEDAD` int(10) unsigned NOT NULL,
`ID_COD` varchar(32) CHARACTER SET ascii DEFAULT NULL,
`NOMBRE_SOCIEDAD` varchar(255) DEFAULT NULL,
`FECHA_INSCRIPCION` datetime NOT NULL,
`FECHA_PUBLICACION` datetime NOT NULL,
`NUMERO_REFERENCIA` bigint(20) unsigned NOT NULL,
`NUMERO_ACTOS` int(10) unsigned NOT NULL,
`BOLETIN` int(10) unsigned DEFAULT NULL,
`YEAR` int(10) unsigned NOT NULL,
`INSCRIPCION_TEXTO` longtext,
`NUMERO_INSCRIPCION` varchar(64) DEFAULT NULL,
`TOMO` mediumint(10) DEFAULT NULL,
`LIBRO` mediumint(10) DEFAULT NULL,
`FOLIO` mediumint(10) DEFAULT NULL,
`SECCION` mediumint(10) unsigned DEFAULT NULL,
`HOJA` varchar(45) DEFAULT NULL,
`INSCRIPCION_TEXTO_COR` longtext,
`USU_CREA` varchar(64) NOT NULL,
`USU_MOD` varchar(64) DEFAULT NULL,
`FEC_CREA` datetime NOT NULL,
`FEC_MOD` datetime DEFAULT NULL,
`ESTADO` tinyint(4) NOT NULL DEFAULT '0',
`ACTOS` varchar(255) DEFAULT NULL,
`ID_PROVINCIA` int(11) NOT NULL DEFAULT '0',
`NUMERO_PROCESOS` mediumint(9) DEFAULT NULL,
`FEC_PROCESO` datetime DEFAULT NULL,
`REVISION` smallint(6) DEFAULT NULL,
`URL_DATOS` varchar(255) DEFAULT NULL,
`URL_PDF` varchar(255) DEFAULT NULL,
`URL_REFERENCIA` varchar(512) DEFAULT NULL,
`TIPO` tinyint(4) DEFAULT NULL,
`EDITADA` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `INS_UK` (`BOLETIN`,`NUMERO_REFERENCIA`,`YEAR`,`TIPO`),
KEY `INS_FEC_PUB_IDX` (`FECHA_PUBLICACION`),
KEY `INS_GET_IDX_1` (`NUMERO_REFERENCIA`,`BOLETIN`,`YEAR`,`TIPO`),
KEY `INS_FEC_INS_IDX` (`FECHA_INSCRIPCION`),
KEY `INS_EMP_IDX` (`ID_SOCIEDAD`),
KEY `INS_TIPO_IDX` (`TIPO`),
KEY `INS_USU_CREA_IDX` (`USU_CREA`),
KEY `INS_NOM_SOC_IDX` (`NOMBRE_SOCIEDAD`),
KEY `ID_COD` (`ID_COD`),
KEY `INS_FEC_PUB_IDSOC_IDX` (`ID_SOCIEDAD`,`FECHA_PUBLICACION`)
) ENGINE=InnoDB AUTO_INCREMENT=29279960 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
mysql> show create table actos_inscritos;
| actos_inscritos | CREATE TABLE `actos_inscritos` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ID_INSCRIPCION` bigint(20) unsigned NOT NULL,
`ID_COD` varchar(32) DEFAULT NULL,
`ID_SOCIEDAD` int(20) unsigned NOT NULL,
`ACTO` varchar(255) DEFAULT NULL,
`TEXTO_ACTO` longtext NOT NULL,
`CARDINAL` int(10) unsigned NOT NULL,
`USU_CREA` varchar(64) DEFAULT NULL,
`USU_MOD` varchar(64) DEFAULT NULL,
`FEC_CREA` datetime NOT NULL,
`FEC_MOD` datetime DEFAULT NULL,
`ESTADO` tinyint(3) NOT NULL DEFAULT '0',
`ID_ACTO` int(11) NOT NULL DEFAULT '-1',
`DESCRIBIBLE` tinyint(4) NOT NULL DEFAULT '0',
`FECHA_INSCRIPCION` datetime DEFAULT NULL,
`FECHA_PUBLICACION` datetime DEFAULT NULL,
`NUMERO_REFERENCIA` bigint(20) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ACT_UK` (`ID_INSCRIPCION`,`CARDINAL`),
KEY `ACT_ID_INS_IDX` (`ID_INSCRIPCION`),
KEY `ACT_IDACT` (`ID_ACTO`),
KEY `ACT_SOC_IDX` (`ID_SOCIEDAD`),
KEY `Index 7` (`ID_ACTO`,`CARDINAL`),
KEY `ID_INSCRIPCION_ID_SOCIEDAD_ID_ACTO` (`ID_INSCRIPCION`,`ID_SOCIEDAD`,`ID_ACTO`),
KEY `ID_COD` (`ID_COD`),
KEY `FECHA_INSCRIPCION` (`FECHA_INSCRIPCION`),
KEY `FECHA_PUBLICACION` (`FECHA_PUBLICACION`),
KEY `NUMERO_REFERENCIA` (`NUMERO_REFERENCIA`),
KEY `DESCRIBIBLE` (`DESCRIBIBLE`)
) ENGINE=InnoDB AUTO_INCREMENT=55709002 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
mysql> show create table descripciones_367;
| descripciones_367 | CREATE TABLE `descripciones_367` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ELEMENTO` smallint(11) DEFAULT NULL,
`IDIOMA` varchar(2) DEFAULT NULL,
`ESTADO` smallint(11) DEFAULT NULL,
`USU_CREA` varchar(64) NOT NULL,
`USU_MOD` varchar(64) DEFAULT NULL,
`FEC_CREA` datetime NOT NULL,
`FEC_MOD` datetime DEFAULT NULL,
`ID_OBJETO` bigint(20) NOT NULL,
`NOMBRE` varchar(255) DEFAULT NULL,
`DESCRIPCION_CORTA` varchar(255) DEFAULT NULL,
`DESCRIPCION_LARGA` varchar(4096) DEFAULT NULL,
`OBSERVACIONES` varchar(255) DEFAULT NULL,
`CONDICIONES` varchar(255) DEFAULT NULL,
`OTROS` varchar(255) DEFAULT NULL,
`KEYWORDS` varchar(255) DEFAULT NULL,
`TIPO_CONTENIDO` smallint(6) DEFAULT NULL,
`ID_PADRE` int(9) DEFAULT NULL,
`MODO_ESTATICO` tinyint(4) DEFAULT NULL,
`MODO_KEYWORDS` tinyint(4) DEFAULT NULL,
`ESTILO_HIJOS` tinyint(4) DEFAULT NULL,
`GRUPO` varchar(128) DEFAULT NULL,
`CLAVE` varchar(128) DEFAULT NULL,
`TIPO_TRADUCCION` tinyint(4) DEFAULT NULL,
`ORDEN` float DEFAULT NULL,
`NUMERO_ELEMENTOS` smallint(6) DEFAULT NULL,
`ID_INSCRIPCION` bigint(20) DEFAULT NULL,
`CARDINAL` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ELEMENTO_IDX` (`ELEMENTO`,`IDIOMA`,`ID_OBJETO`),
UNIQUE KEY `IDIOMA_ID_INSCRIPCION_CARDINAL` (`ID_INSCRIPCION`,`CARDINAL`,`IDIOMA`),
KEY `DES_ESTADO_IDX` (`ESTADO`),
KEY `DES_ESTATIC_IDX` (`MODO_ESTATICO`),
KEY `KEY_IDIOMA` (`CLAVE`,`IDIOMA`),
KEY `ID_PADRE` (`ID_PADRE`),
KEY `IDIOMA_DESCRIPCION_LARGA` (`IDIOMA`,`DESCRIPCION_LARGA`(300))
) ENGINE=InnoDB AUTO_INCREMENT=53593574 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
I will split my answer because the forum says the body is too large.
After some reviews, when I said the updates were executed in batch, I was wrong. There are some batch inserts, but there are always less than 100 rows.
The inserts in the table STATS_DC_T_1 were performed using a CachedThreadPool, that means many parallel connections.
We've replaced it with a a single thread executing inserts one by one and load average has decreased.
It's now between 3-7 with random peaks of 10-11. I really don't know what is the normal load average in a dual CPU machine with 12 physical cores.
We can modify the thread to wait until it has N (100) inserts pending and then do them in batch.
We still don't know which queries cause the high load average.
STATS_DC_T_1 stores visits to a website.
The load average doesn't match server load in terms of users. Sometimes at night, when there's less traffic we see load average of 12.
We suspect it could be Googlebot crawling the site.
We have modified the application so in case of crawlers, the modifications triggered by these crawlers are performed in the background.
> That smells like SHOW STATUS; it needs to be SHOW GLOBAL STATUS; I checked what I could; nothing is seriously mis-configured.
mysql> show global status;
+-----------------------------------------------+---------------+
| Variable_name | Value |
+-----------------------------------------------+---------------+
| Aborted_clients | 310 |
| Aborted_connects | 22331 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 131656105617 |
| Bytes_sent | 740803009499 |
| Com_admin_commands | 31058 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 7 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 16 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 2674 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 34 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 1 |
| Com_dealloc_sql | 0 |
| Com_delete | 572450 |
| Com_delete_multi | 63 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 9 |
| Com_insert | 13702191 |
| Com_insert_select | 82154 |
| Com_install_plugin | 0 |
| Com_kill | 10 |
| Com_load | 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_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 6209 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 836943882 |
| Com_set_option | 9905990 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 19893 |
| Com_show_collations | 995025 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 370 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 1 |
| Com_show_events | 20 |
| Com_show_errors | 0 |
| Com_show_fields | 44 |
| Com_show_function_code | 0 |
| Com_show_function_status | 20 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 20 |
| Com_show_processlist | 1163 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 40 |
| Com_show_storage_engines | 6 |
| Com_show_table_status | 203 |
| Com_show_tables | 3 |
| Com_show_triggers | 20 |
| Com_show_variables | 3296422 |
| 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_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 13011011 |
| Com_update_multi | 7757 |
| 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 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 3318759 |
| Created_tmp_disk_tables | 12774 |
| Created_tmp_files | 129007 |
| Created_tmp_tables | 16240640 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 413459194 |
| Handler_delete | 1872751 |
| Handler_discover | 0 |
| Handler_external_lock | 1106212558 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 225499 |
| Handler_read_key | 11781266236 |
| Handler_read_last | 1142624 |
| Handler_read_next | 23349326250 |
| Handler_read_prev | 8606543001 |
| Handler_read_rnd | 149400977 |
| Handler_read_rnd_next | 1143747338 |
| Handler_rollback | 4096600 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 57628957 |
| Handler_write | 494765326 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 1249341 |
| Innodb_buffer_pool_bytes_data | 20469202944 |
| Innodb_buffer_pool_pages_dirty | 14664 |
| Innodb_buffer_pool_bytes_dirty | 240254976 |
| Innodb_buffer_pool_pages_flushed | 27593957 |
| Innodb_buffer_pool_pages_free | 2034 |
| Innodb_buffer_pool_pages_misc | 59344 |
| Innodb_buffer_pool_pages_total | 1310719 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 3289683 |
| Innodb_buffer_pool_read_ahead_evicted | 128 |
| Innodb_buffer_pool_read_requests | 134920666369 |
| Innodb_buffer_pool_reads | 190539128 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 490951318 |
| Innodb_data_fsyncs | 8275682 |
| Innodb_data_pending_fsyncs | 1 |
| Innodb_data_pending_reads | 4 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 3259453329408 |
| Innodb_data_reads | 198953204 |
| Innodb_data_writes | 52182978 |
| Innodb_data_written | 959632901632 |
| Innodb_dblwr_pages_written | 27593957 |
| Innodb_dblwr_writes | 1076435 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 79 |
| Innodb_log_write_requests | 106597380 |
| Innodb_log_writes | 23379141 |
| Innodb_os_log_fsyncs | 640222 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 55402967552 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 1507283 |
| Innodb_pages_read | 198954142 |
| Innodb_pages_written | 27593956 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 881085 |
| Innodb_row_lock_time_avg | 25 |
| Innodb_row_lock_time_max | 28064 |
| Innodb_row_lock_waits | 34153 |
| Innodb_rows_deleted | 1872364 |
| Innodb_rows_inserted | 42049968 |
| Innodb_rows_read | 43806472590 |
| Innodb_rows_updated | 18376940 |
| Innodb_num_open_files | 498 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 405421 |
| Key_blocks_used | 23728 |
| Key_read_requests | 289029110 |
| Key_reads | 22044 |
| Key_write_requests | 44841631 |
| Key_writes | 62922 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 103 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 161 |
| Open_streams | 0 |
| Open_table_definitions | 606 |
| Open_tables | 1799 |
| Opened_files | 181163 |
| Opened_table_definitions | 684 |
| Opened_tables | 1963 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 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 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 882079425 |
| Questions | 881645330 |
| Select_full_join | 681 |
| Select_full_range_join | 0 |
| Select_range | 13938967 |
| Select_range_check | 0 |
| Select_scan | 6223631 |
| Slave_heartbeat_period | |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | |
| Slave_retried_transactions | |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 123163 |
| Sort_merge_passes | 75281 |
| Sort_range | 3183441 |
| Sort_rows | 2568368567 |
| Sort_scan | 464636 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 552180850 |
| Table_locks_waited | 267 |
| Table_open_cache_hits | 552252238 |
| Table_open_cache_misses | 1892 |
| Table_open_cache_overflows | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 87 |
| Threads_connected | 16 |
| Threads_created | 103 |
| Threads_running | 10 |
| Uptime | 571675 |
| Uptime_since_flush_status | 571675 |
+-----------------------------------------------+---------------+
341 rows in set (0.33 sec)
> You have a good long_query_time; what is in the slowlog?
This type of query is performed a lot. They are usually under 1 second but there are also many of them in the slowlog over 1 sec.
DESCRIPCIONES_367 is a manually partitioned table. It has 29 million rows and it's 38,6 GB in size. We are thinking on partitioning it again manually by country (does MySQL partition work well?)
Inserts in this table tend to be slow, here is mysqldumpslow summary
Count: 130 Time=59.31s (7710s) Lock=0.00s (0s) Rows=0.0 (0), []@[127.0.0.1]
INSERT INTO DESCRIPCIONES_367 ( ID,ID_OBJETO,ELEMENTO,IDIOMA,NOMBRE,DESCRIPCION_CORTA,DESCRIPCION_LARGA,OBSERVACIONES,CONDICIONES,OTROS,KEYWORDS,CLAVE,GRUPO,USU_CREA,USU_MOD,ESTADO,ID_PADRE,TIPO_CONTENIDO,MODO_ESTATICO,MODO_KEYWORDS,ESTILO_HIJOS,TIPO_TRADUCCION,NUMERO_ELEMENTOS,ORDEN,FEC_CREA,FEC_MOD ) VALUES ( N , N , N , 'S' , null , null , 'S' , 'S' , 'S' , null , 'S' , null , null , 'S' , null , N , N , N , N , N , N , N , N , N.N , 'S' , null )
# Query_time: 1.042077 Lock_time: 0.000264 Rows_sent: 15 Rows_examined: 76
SET timestamp=1393279317;
select V.NUMERO_REFERENCIA,V.ESTADO_ACTO,V.FECHA_PUBLICACION,V.CARDINAL,V.ID_COD,V.ID_REG_ACTO,V.ID_ACTO , D.ID AS ID_DESCRIPCION,D.`DESCRIPCION_LARGA` as DESCRIPCION_CORTA,D2.DESCRIPCION_LARGA, D.`KEYWORDS` from v_inscripciones_actos_detalle_notext V LEFT JOIN descripciones_375 D on (D.id_objeto=V.id_reg_acto and d.elemento=375 and d.idioma='es' ) LEFT JOIN descripciones_367 D2 on (D2.id_objeto=V.id_reg_acto and d2.elemento=367 and d2.idioma='es' ) where 0=0 and ( V.ID_SOCIEDAD=2244245 ) and V.DESCRIBIBLE=1 ORDER BY V.FECHA_PUBLICACION DESC LIMIT 0,20;
+----+-------------+-----------------+------+----------------------------------------------------------------------+-----------------------+---------+-------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+----------------------------------------------------------------------+-----------------------+---------+-------------------------------+------+-------------+
| 1 | SIMPLE | inscripciones | ref | PRIMARY,INS_EMP_IDX,INS_FEC_PUB_IDSOC_IDX | INS_FEC_PUB_IDSOC_IDX | 4 | const | 19 | Using where |
| 1 | SIMPLE | actos_inscritos | ref | ACT_UK,ACT_ID_INS_IDX,ID_INSCRIPCION_ID_SOCIEDAD_ID_ACTO,DESCRIBIBLE | ACT_UK | 8 | inscripciones.ID | 1 | Using where |
| 1 | SIMPLE | D | ref | ELEMENTO_IDX,IDIOMA_DESCRIPCION_LARGA | ELEMENTO_IDX | 18 | const,const,func | 1 | Using where |
| 1 | SIMPLE | D2 | ref | ELEMENTO_IDX,IDIOMA_DESCRIPCION_LARGA | ELEMENTO_IDX | 20 | const,const,func | 1 | Using where |
+----+-------------+-----------------+------+----------------------------------------------------------------------+-----------------------+---------+-------------------------------+------+-------------+
mysql> show create table v_inscripciones_actos_completa_notext;
| v_inscripciones_actos_completa_notext | CREATE ALGORITHM=UNDEFINED DEFINER=``@`%` SQL SECURITY DEFINER VIEW `v_inscripciones_actos_completa_notext` AS select `inscripciones`.`ID` AS `ID`,`inscripciones`.`ID_SOCIEDAD` AS `ID_SOCIEDAD`,`inscripciones`.`NOMBRE_SOCIEDAD` AS `NOMBRE_SOCIEDAD`,`inscripciones`.`FECHA_INSCRIPCION` AS `FECHA_INSCRIPCION`,`inscripciones`.`FECHA_PUBLICACION` AS `FECHA_PUBLICACION`,`inscripciones`.`NUMERO_REFERENCIA` AS `NUMERO_REFERENCIA`,`inscripciones`.`NUMERO_ACTOS` AS `NUMERO_ACTOS`,`inscripciones`.`BOLETIN` AS `BOLETIN`,`inscripciones`.`YEAR` AS `YEAR`,`inscripciones`.`NUMERO_INSCRIPCION` AS `NUMERO_INSCRIPCION`,`inscripciones`.`TOMO` AS `TOMO`,`inscripciones`.`LIBRO` AS `LIBRO`,`inscripciones`.`FOLIO` AS `FOLIO`,`inscripciones`.`SECCION` AS `SECCION`,`inscripciones`.`HOJA` AS `HOJA`,`inscripciones`.`INSCRIPCION_TEXTO_COR` AS `INSCRIPCION_TEXTO_COR`,`inscripciones`.`USU_CREA` AS `USU_CREA`,`inscripciones`.`USU_MOD` AS `USU_MOD`,`inscripciones`.`FEC_CREA` AS `FEC_CREA`,`inscripciones`.`FEC_MOD` AS `FEC_MOD`,`inscripciones`.`ESTADO` AS `ESTADO`,`inscripciones`.`ACTOS` AS `ACTOS`,`inscripciones`.`ID_PROVINCIA` AS `ID_PROVINCIA`,`inscripciones`.`NUMERO_PROCESOS` AS `NUMERO_PROCESOS`,`inscripciones`.`FEC_PROCESO` AS `FEC_PROCESO`,`inscripciones`.`REVISION` AS `REVISION`,`inscripciones`.`URL_DATOS` AS `URL_DATOS`,`inscripciones`.`URL_PDF` AS `URL_PDF`,`inscripciones`.`URL_REFERENCIA` AS `URL_REFERENCIA`,`inscripciones`.`TIPO` AS `TIPO`,`actos_inscritos`.`ID` AS `id_acto_objeto`,`actos_inscritos`.`ID_ACTO` AS `ID_ACTO`,`actos_inscritos`.`ACTO` AS `ACTO`,`actos_inscritos`.`CARDINAL` AS `cardinal`,`actos_inscritos`.`ESTADO` AS `estado_acto` from (`inscripciones` join `actos_inscritos` on((`actos_inscritos`.`ID_INSCRIPCION` = `inscripciones`.`ID`))) | utf8 | utf8_general_ci |
mysql> show create table inscripciones;
| inscripciones | CREATE TABLE `inscripciones` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ID_SOCIEDAD` int(10) unsigned NOT NULL,
`ID_COD` varchar(32) CHARACTER SET ascii DEFAULT NULL,
`NOMBRE_SOCIEDAD` varchar(255) DEFAULT NULL,
`FECHA_INSCRIPCION` datetime NOT NULL,
`FECHA_PUBLICACION` datetime NOT NULL,
`NUMERO_REFERENCIA` bigint(20) unsigned NOT NULL,
`NUMERO_ACTOS` int(10) unsigned NOT NULL,
`BOLETIN` int(10) unsigned DEFAULT NULL,
`YEAR` int(10) unsigned NOT NULL,
`INSCRIPCION_TEXTO` longtext,
`NUMERO_INSCRIPCION` varchar(64) DEFAULT NULL,
`TOMO` mediumint(10) DEFAULT NULL,
`LIBRO` mediumint(10) DEFAULT NULL,
`FOLIO` mediumint(10) DEFAULT NULL,
`SECCION` mediumint(10) unsigned DEFAULT NULL,
`HOJA` varchar(45) DEFAULT NULL,
`INSCRIPCION_TEXTO_COR` longtext,
`USU_CREA` varchar(64) NOT NULL,
`USU_MOD` varchar(64) DEFAULT NULL,
`FEC_CREA` datetime NOT NULL,
`FEC_MOD` datetime DEFAULT NULL,
`ESTADO` tinyint(4) NOT NULL DEFAULT '0',
`ACTOS` varchar(255) DEFAULT NULL,
`ID_PROVINCIA` int(11) NOT NULL DEFAULT '0',
`NUMERO_PROCESOS` mediumint(9) DEFAULT NULL,
`FEC_PROCESO` datetime DEFAULT NULL,
`REVISION` smallint(6) DEFAULT NULL,
`URL_DATOS` varchar(255) DEFAULT NULL,
`URL_PDF` varchar(255) DEFAULT NULL,
`URL_REFERENCIA` varchar(512) DEFAULT NULL,
`TIPO` tinyint(4) DEFAULT NULL,
`EDITADA` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `INS_UK` (`BOLETIN`,`NUMERO_REFERENCIA`,`YEAR`,`TIPO`),
KEY `INS_FEC_PUB_IDX` (`FECHA_PUBLICACION`),
KEY `INS_GET_IDX_1` (`NUMERO_REFERENCIA`,`BOLETIN`,`YEAR`,`TIPO`),
KEY `INS_FEC_INS_IDX` (`FECHA_INSCRIPCION`),
KEY `INS_EMP_IDX` (`ID_SOCIEDAD`),
KEY `INS_TIPO_IDX` (`TIPO`),
KEY `INS_USU_CREA_IDX` (`USU_CREA`),
KEY `INS_NOM_SOC_IDX` (`NOMBRE_SOCIEDAD`),
KEY `ID_COD` (`ID_COD`),
KEY `INS_FEC_PUB_IDSOC_IDX` (`ID_SOCIEDAD`,`FECHA_PUBLICACION`)
) ENGINE=InnoDB AUTO_INCREMENT=29279960 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
mysql> show create table actos_inscritos;
| actos_inscritos | CREATE TABLE `actos_inscritos` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ID_INSCRIPCION` bigint(20) unsigned NOT NULL,
`ID_COD` varchar(32) DEFAULT NULL,
`ID_SOCIEDAD` int(20) unsigned NOT NULL,
`ACTO` varchar(255) DEFAULT NULL,
`TEXTO_ACTO` longtext NOT NULL,
`CARDINAL` int(10) unsigned NOT NULL,
`USU_CREA` varchar(64) DEFAULT NULL,
`USU_MOD` varchar(64) DEFAULT NULL,
`FEC_CREA` datetime NOT NULL,
`FEC_MOD` datetime DEFAULT NULL,
`ESTADO` tinyint(3) NOT NULL DEFAULT '0',
`ID_ACTO` int(11) NOT NULL DEFAULT '-1',
`DESCRIBIBLE` tinyint(4) NOT NULL DEFAULT '0',
`FECHA_INSCRIPCION` datetime DEFAULT NULL,
`FECHA_PUBLICACION` datetime DEFAULT NULL,
`NUMERO_REFERENCIA` bigint(20) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ACT_UK` (`ID_INSCRIPCION`,`CARDINAL`),
KEY `ACT_ID_INS_IDX` (`ID_INSCRIPCION`),
KEY `ACT_IDACT` (`ID_ACTO`),
KEY `ACT_SOC_IDX` (`ID_SOCIEDAD`),
KEY `Index 7` (`ID_ACTO`,`CARDINAL`),
KEY `ID_INSCRIPCION_ID_SOCIEDAD_ID_ACTO` (`ID_INSCRIPCION`,`ID_SOCIEDAD`,`ID_ACTO`),
KEY `ID_COD` (`ID_COD`),
KEY `FECHA_INSCRIPCION` (`FECHA_INSCRIPCION`),
KEY `FECHA_PUBLICACION` (`FECHA_PUBLICACION`),
KEY `NUMERO_REFERENCIA` (`NUMERO_REFERENCIA`),
KEY `DESCRIBIBLE` (`DESCRIBIBLE`)
) ENGINE=InnoDB AUTO_INCREMENT=55709002 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
mysql> show create table descripciones_367;
| descripciones_367 | CREATE TABLE `descripciones_367` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ELEMENTO` smallint(11) DEFAULT NULL,
`IDIOMA` varchar(2) DEFAULT NULL,
`ESTADO` smallint(11) DEFAULT NULL,
`USU_CREA` varchar(64) NOT NULL,
`USU_MOD` varchar(64) DEFAULT NULL,
`FEC_CREA` datetime NOT NULL,
`FEC_MOD` datetime DEFAULT NULL,
`ID_OBJETO` bigint(20) NOT NULL,
`NOMBRE` varchar(255) DEFAULT NULL,
`DESCRIPCION_CORTA` varchar(255) DEFAULT NULL,
`DESCRIPCION_LARGA` varchar(4096) DEFAULT NULL,
`OBSERVACIONES` varchar(255) DEFAULT NULL,
`CONDICIONES` varchar(255) DEFAULT NULL,
`OTROS` varchar(255) DEFAULT NULL,
`KEYWORDS` varchar(255) DEFAULT NULL,
`TIPO_CONTENIDO` smallint(6) DEFAULT NULL,
`ID_PADRE` int(9) DEFAULT NULL,
`MODO_ESTATICO` tinyint(4) DEFAULT NULL,
`MODO_KEYWORDS` tinyint(4) DEFAULT NULL,
`ESTILO_HIJOS` tinyint(4) DEFAULT NULL,
`GRUPO` varchar(128) DEFAULT NULL,
`CLAVE` varchar(128) DEFAULT NULL,
`TIPO_TRADUCCION` tinyint(4) DEFAULT NULL,
`ORDEN` float DEFAULT NULL,
`NUMERO_ELEMENTOS` smallint(6) DEFAULT NULL,
`ID_INSCRIPCION` bigint(20) DEFAULT NULL,
`CARDINAL` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ELEMENTO_IDX` (`ELEMENTO`,`IDIOMA`,`ID_OBJETO`),
UNIQUE KEY `IDIOMA_ID_INSCRIPCION_CARDINAL` (`ID_INSCRIPCION`,`CARDINAL`,`IDIOMA`),
KEY `DES_ESTADO_IDX` (`ESTADO`),
KEY `DES_ESTATIC_IDX` (`MODO_ESTATICO`),
KEY `KEY_IDIOMA` (`CLAVE`,`IDIOMA`),
KEY `ID_PADRE` (`ID_PADRE`),
KEY `IDIOMA_DESCRIPCION_LARGA` (`IDIOMA`,`DESCRIPCION_LARGA`(300))
) ENGINE=InnoDB AUTO_INCREMENT=53593574 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.