Databases
StackOverflow2013
Postsdbo
POslow inserts mysql
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POslow inserts mysql
    primarykey
    data
    text
    <p>I have very slow insert to my InnoDb table (2 500 000 notes). But why? Here is debug information:</p> <p>Server: Intel® Core™ i7-920 24 GB DDR3 RAMFestplatten2 x 750 GB SATA 3 Gb/s HDD 168.02 query/sec </p> <pre><code>CREATE TABLE IF NOT EXISTS `bysr_customers` ( `customerID` int(11) NOT NULL AUTO_INCREMENT, `OdnoklasID` varchar(255) NOT NULL, `Login` varchar(32) DEFAULT NULL, `cust_password` varchar(255) NOT NULL, `Email` varchar(255) DEFAULT NULL, `first_name` varchar(255) DEFAULT NULL, `last_name` varchar(255) DEFAULT NULL, `kopilka` int(110) NOT NULL DEFAULT '0', `subscribed4news` int(11) DEFAULT NULL, `custgroupID` int(11) DEFAULT NULL, `addressID` int(11) DEFAULT NULL, `reg_datetime` datetime DEFAULT NULL, `ActivationCode` varchar(16) NOT NULL DEFAULT '', `CID` int(11) DEFAULT NULL, `affiliateID` int(11) NOT NULL, `affiliateEmailOrders` int(11) NOT NULL DEFAULT '1', `affiliateEmailPayments` int(11) NOT NULL DEFAULT '1', `actions` text NOT NULL, `area` text NOT NULL, `city` text NOT NULL, `street` text NOT NULL, `house` text NOT NULL, `build` text NOT NULL, `room` text NOT NULL, `phone` text NOT NULL, `fake` int(10) NOT NULL, PRIMARY KEY (`customerID`), UNIQUE KEY `ok_index` (`OdnoklasID`), KEY `AFFILIATEID` (`affiliateID`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3504023 ; INSERT INTO bysr_customers (OdnoklasID, first_name, last_name, custgroupID, reg_datetime, CID, affiliateEmailOrders, affiliateEmailPayments, fake) VALUES ('fake_4', '2', '3', '1', '2012-02-22 16:42:54', '1', '1', '1', '0'); mysql&gt; SHOW profile FOR QUERY 7; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000043 | | checking permissions | 0.000007 | | Opening tables | 0.000044 | | System lock | 0.000004 | | Table lock | 0.000004 | | init | 0.000018 | | update | 7.521551 | | end | 0.000011 | | query end | 0.000006 | | freeing items | 2.573892 | | logging slow query | 0.000013 | | logging slow query | 0.000035 | | cleaning up | 0.000006 | +----------------------+----------+ 13 rows in set (0.00 sec) auto_increment_increment 1 auto_increment_offset 1 autocommit ON automatic_sp_privileges ON back_log 50 basedir /usr/ big_tables OFF binlog_cache_size 32768 binlog_direct_non_transactional_updates OFF binlog_format STATEMENT bulk_insert_buffer_size 8388608 character_set_client utf8 character_set_connection utf8 character_set_database cp1251 character_set_filesystem binary character_set_results utf8 character_set_server cp1251 character_set_system utf8 character_sets_dir /usr/share/mysql/charsets/ collation_connection utf8_general_ci collation_database cp1251_general_ci collation_server cp1251_general_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 1000 div_precision_increment 4 engine_condition_pushdown ON error_count 0 event_scheduler OFF expire_logs_days 10 flush OFF flush_time 0 foreign_key_checks ON ft_boolean_syntax + -&gt;&lt;()~*:""&amp;| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) general_log OFF general_log_file /var/lib/mysql/Ubuntu-1104-natty-64-minimal.log group_concat_max_len 1024 have_community_features YES have_compress YES have_crypt YES have_csv YES have_dynamic_loading YES have_geometry YES have_innodb YES have_ndbcluster NO have_openssl DISABLED have_partitioning YES have_query_cache YES have_rtree_keys YES have_ssl DISABLED have_symlink YES hostname Ubuntu-1104-natty-64-minimal identity 0 ignore_builtin_innodb OFF init_connect init_file init_slave innodb_adaptive_hash_index ON innodb_additional_mem_pool_size 1048576 innodb_autoextend_increment 8 innodb_autoinc_lock_mode 1 innodb_buffer_pool_size 10737418240 innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir 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_buffer_size 1048576 innodb_log_file_size 5242880 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_stats_on_metadata ON innodb_support_xa ON innodb_sync_spin_loops 20 Variable_name Value innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 innodb_use_legacy_cardinality_algorithm ON insert_id 0 interactive_timeout 28800 join_buffer_size 131072 keep_files_on_create OFF key_buffer_size 2147483648 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 language /usr/share/mysql/english/ large_files_support ON large_page_size 0 large_pages OFF last_insert_id 0 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_bin_trust_routine_creators OFF log_error /var/log/mysql/error.log log_output FILE log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries ON log_warnings 1 long_query_time 2.000000 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 16777216 max_binlog_cache_size 18446744073709547520 max_binlog_size 104857600 max_connect_errors 10 max_connections 151 max_delayed_threads 20 max_error_count 64 max_heap_table_size 1073741824 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 18446744073709551615 min_examined_row_limit 0 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 9223372036853727232 myisam_mmap_size 18446744073709551615 myisam_recover_options BACKUP myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal myisam_use_mmap OFF net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old OFF old_alter_table OFF old_passwords OFF open_files_limit 1024 optimizer_prune_level 1 optimizer_search_depth 62 optimizer_switch index_merge=on,index_merge_union=on,index_merge_so... pid_file /var/lib/mysql/Ubuntu-1104-natty-64-minimal.pid plugin_dir /usr/lib/mysql/plugin port 3306 preload_buffer_size 32768 profiling ON profiling_history_size 15 protocol_version 10 pseudo_thread_id 79658 query_alloc_block_size 8192 query_cache_limit 10485760 query_cache_min_res_unit 4096 query_cache_size 314572800 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 rand_seed1 rand_seed2 range_alloc_block_size 4096 read_buffer_size 131072 read_only OFF read_rnd_buffer_size 262144 relay_log relay_log_index relay_log_info_file relay-log.info Variable_name Value relay_log_purge ON relay_log_space_limit 0 report_host report_password report_port 3306 report_user rpl_recovery_rank 0 secure_auth OFF secure_file_priv server_id 0 skip_external_locking ON skip_name_resolve OFF skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_exec_mode STRICT slave_load_tmpdir /tmp slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slow_launch_time 2 slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log socket /var/run/mysqld/mysqld.sock sort_buffer_size 268435456 sql_auto_is_null ON sql_big_selects ON sql_big_tables OFF sql_buffer_result OFF sql_log_bin ON sql_log_off OFF sql_log_update ON sql_low_priority_updates OFF sql_max_join_size 18446744073709551615 sql_mode sql_notes ON sql_quote_show_create ON sql_safe_updates OFF sql_select_limit 18446744073709551615 sql_slave_skip_counter 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 MSK table_definition_cache 256 table_lock_wait_timeout 50 table_open_cache 64 table_type MyISAM thread_cache_size 8 thread_handling one-thread-per-connection thread_stack 196608 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF timestamp 1329916096 tmp_table_size 1073741824 tmpdir /tmp transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ unique_checks ON updatable_views_with_limit YES version 5.1.54-1ubuntu4-log version_comment (Ubuntu) version_compile_machine x86_64 version_compile_os debian-linux-gnu wait_timeout 28800 warning_count 0 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNick Garvey
    1. USsergeyne
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POslow inserts mysql
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POslow inserts mysql
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POslow inserts mysql
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo you have a lot of indexes on that table?
      singulars
      1. POslow inserts mysql
      1. USTyler Eaves
    2. COI have 3 indexes: 1.primary (int) 2.unique (varchar) 3.index (int)
      singulars
      1. POslow inserts mysql
      1. USsergeyne
    3. COPost the output of `SHOW VARIABLES LIKE '%innodb%';`
      singulars
      1. POslow inserts mysql
      1. USN.B.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload