Hello.
> the problem is that after around 160,000 rows inserted with success , >then i get "Out of memory (Needed 8164 bytes)" error many times. Does your program report this error or MySQL Server? > Can someone give me an advice with some optimization or if something >is wrong in mysql server configuration? At least check that memory which might be used by MySQL Server can't be more than amount of your RAM. See: http://dev.mysql.com/doc/mysql/en/memory-use.html Use a formula similar to this: min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer thread_stack net_buffers and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool In case Andrei wrote: > Hi list, > > i have a linux server with mysql 4.1.14 > > and i'm trying to execute a program wich read a file with 100's tohusands > lines and for every line must do a SELECT and then an INSERT . > > the SELECT use 2 InnoDB tables and the INSERT use a MyISAM table. > > the problem is that after around 160,000 rows inserted with success , then i > get "Out of memory (Needed 8164 bytes)" error many times. > > i will try to increase the RAM size but i don't know if this is the problem > (for sure it will help , but ... ) > > so, i suspect that after these 160,000 rows ... the SELECT for the following > records is not successfully ending but the INSERT is ok. > > Can someone give me an advice with some optimization or if something is wrong > in mysql server configuration? > > #free > total used free shared buffers cached > Mem: 514460 238972 275488 0 28824 131024 > -/+ buffers/cache: 79124 435336 > Swap: 1975912 20052 1955860 > > VARIABLES: > +---------------------------------+---------------------------------------------+ > | Variable_name | Value > +---------------------------------+---------------------------------------------+ > | back_log | 50 > | basedir | /opt/sql/mysql-4.1.14/ > | binlog_cache_size | 32768 > | bulk_insert_buffer_size | 8388608 > | character_set_client | latin1 > | character_set_connection | latin1 > | character_set_database | latin1 > | character_set_results | latin1 > | character_set_server | latin1 > | character_set_system | utf8 > | character_sets_dir > | /opt/sql/mysql-4.1.14/share/mysql/charsets/ > | collation_connection | latin1_swedish_ci > | collation_database | latin1_swedish_ci > | collation_server | latin1_swedish_ci > | concurrent_insert | ON > | connect_timeout | 5 > | datadir | /opt/sql/mysql-data/ > | 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 > | 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 | 1024 > | have_archive | NO > | have_bdb | NO > | have_blackhole_engine | NO > | have_compress | YES > | have_crypt | YES > | have_csv | NO > | have_example_engine | NO > | have_geometry | YES > | have_innodb | YES > | have_isam | NO > | have_ndbcluster | NO > | have_openssl | NO > | have_query_cache | YES > | have_raid | NO > | have_rtree_keys | YES > | have_symlink | YES > | init_connect | > | init_file | > | init_slave | > | innodb_additional_mem_pool_size | 20971520 > | innodb_autoextend_increment | 8 > | innodb_buffer_pool_awe_mem_mb | 0 > | innodb_buffer_pool_size | 167772160 > | innodb_data_file_path | ibdata1:10M:autoextend > | innodb_data_home_dir | /opt/sql/mysql-data/ > | innodb_fast_shutdown | ON > | 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 | /opt/sql/mysql-data/ > | innodb_log_archive | OFF > | innodb_log_buffer_size | 8388608 > | innodb_log_file_size | 67108864 > | innodb_log_files_in_group | 2 > | innodb_log_group_home_dir | /opt/sql/mysql-data/ > | innodb_max_dirty_pages_pct | 90 > | innodb_max_purge_lag | 0 > | innodb_mirrored_log_groups | 1 > | innodb_open_files | 300 > | innodb_table_locks | ON > | innodb_thread_concurrency | 8 > | interactive_timeout | 28800 > | join_buffer_size | 131072 > | key_buffer_size | 134217728 > | key_cache_age_threshold | 300 > | key_cache_block_size | 1024 > | key_cache_division_limit | 100 > | language | /opt/sql/mysql-4.1.14/share/mysql/english/ > | large_files_support | ON > | license | GPL > | local_infile | ON > | locked_in_memory | OFF > | log | OFF > | log_bin | ON > | log_error | /opt/sql/mysql-data/v-02.err > | log_slave_updates | OFF > | log_slow_queries | OFF > | log_update | OFF > | log_warnings | 2 > | long_query_time | 10 > | low_priority_updates | OFF > | lower_case_file_system | OFF > | lower_case_table_names | 0 > | max_allowed_packet | 1047552 > | max_binlog_cache_size | 4294967295 > | max_binlog_size | 1073741824 > | max_connect_errors | 10 > | max_connections | 100 > | max_delayed_threads | 20 > | max_error_count | 64 > | max_heap_table_size | 16777216 > | max_insert_delayed_threads | 20 > | max_join_size | 18446744073709551615 > | max_length_for_sort_data | 1024 > | max_relay_log_size | 0 > | max_seeks_for_key | 4294967295 > | max_sort_length | 1024 > | max_tmp_tables | 32 > | max_user_connections | 0 > | max_write_lock_count | 4294967295 > | myisam_data_pointer_size | 4 > | myisam_max_extra_sort_file_size | 2147483648 > | myisam_max_sort_file_size | 2147483647 > | myisam_recover_options | OFF > | myisam_repair_threads | 1 > | myisam_sort_buffer_size | 33554432 > | net_buffer_length | 32768 > | net_read_timeout | 30 > | net_retry_count | 10 > | net_write_timeout | 60 > | new | OFF > | old_passwords | ON > | open_files_limit | 1024 > | pid_file | /opt/sql/mysql-data//v-02.pid > | port | 3306 > | preload_buffer_size | 32768 > | protocol_version | 10 > | query_alloc_block_size | 8192 > | query_cache_limit | 2097152 > | query_cache_min_res_unit | 4096 > | query_cache_size | 33554432 > | query_cache_type | ON > | query_cache_wlock_invalidate | OFF > | query_prealloc_size | 8192 > | range_alloc_block_size | 2048 > | read_buffer_size | 1044480 > | read_only | OFF > | read_rnd_buffer_size | 2093056 > | relay_log_purge | ON > | relay_log_space_limit | 0 > | rpl_recovery_rank | 0 > | secure_auth | OFF > | server_id | 190 > | skip_external_locking | ON > | skip_networking | OFF > | skip_show_database | OFF > | slave_net_timeout | 3600 > | slave_transaction_retries | 0 > | slow_launch_time | 2 > | socket | /tmp/mysql.sock > | sort_buffer_size | 1048568 > | sql_mode | > | storage_engine | MyISAM > | sql_notes | ON > | sql_warnings | ON > | sync_binlog | 0 > | sync_replication | 0 > | sync_replication_slave_id | 0 > | sync_replication_timeout | 0 > | sync_frm | ON > | system_time_zone | EEST > | table_cache | 128 > | table_type | MyISAM > | thread_cache_size | 3 > | thread_stack | 196608 > | time_format | %H:%i:%s > | time_zone | SYSTEM > | tmp_table_size | 33554432 > | tmpdir | > | transaction_alloc_block_size | 8192 > | transaction_prealloc_size | 4096 > | tx_isolation | REPEATABLE-READ > | version | 4.1.14-log > | version_comment | Source distribution > | version_compile_machine | i686 > | version_compile_os | pc-linux-gnu > | wait_timeout | 28800 > +---------------------------------+------------ > 184 rows in set (0.00 sec) > > > AND STATUS AFTER ERROR: > +----------------------------+-----------+ > | Variable_name | Value | > +----------------------------+-----------+ > | Aborted_clients | 0 | > | Aborted_connects | 0 | > | Binlog_cache_disk_use | 0 | > | Binlog_cache_use | 0 | > | Bytes_received | 218282566 | > | Bytes_sent | 781475261 | > | Com_admin_commands | 0 | > | Com_alter_db | 0 | > | Com_alter_table | 0 | > | Com_analyze | 0 | > | Com_backup_table | 0 | > | Com_begin | 0 | > | Com_change_db | 1374 | > | 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_dealloc_sql | 0 | > | Com_delete | 4 | > | 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 | 450460 | > | Com_insert_select | 0 | > | Com_kill | 0 | > | Com_load | 0 | > | Com_load_master_data | 0 | > | Com_load_master_table | 0 | > | Com_lock_tables | 44 | > | 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 | 467544 | > | Com_set_option | 1938 | > | Com_show_binlog_events | 0 | > | Com_show_binlogs | 0 | > | Com_show_charsets | 0 | > | Com_show_collations | 0 | > | Com_show_column_types | 0 | > | Com_show_create_db | 0 | > | Com_show_create_table | 1892 | > | Com_show_databases | 1 | > | Com_show_errors | 1 | > | Com_show_fields | 1940 | > | Com_show_grants | 0 | > | Com_show_innodb_status | 0 | > | Com_show_keys | 0 | > | Com_show_logs | 0 | > | Com_show_master_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 | 28 | > | Com_show_storage_engines | 0 | > | Com_show_tables | 1938 | > | Com_show_variables | 1 | > | Com_show_warnings | 1 | > | Com_slave_start | 0 | > | Com_slave_stop | 0 | > | Com_stmt_prepare | 0 | > | Com_stmt_execute | 0 | > | Com_stmt_send_long_data | 0 | > | Com_stmt_reset | 0 | > | Com_stmt_close | 0 | > | Com_truncate | 0 | > | Com_unlock_tables | 44 | > | Com_update | 31422 | > | Com_update_multi | 0 | > | Connections | 1278 | > | Created_tmp_disk_tables | 0 | > | Created_tmp_files | 3817 | > | Created_tmp_tables | 206483 | > | Delayed_errors | 0 | > | Delayed_insert_threads | 0 | > | Delayed_writes | 0 | > | Flush_commands | 1 | > | Handler_commit | 44 | > | Handler_delete | 0 | > | Handler_discover | 0 | > | Handler_read_first | 341868 | > | Handler_read_key | 1291007 | > | Handler_read_next | 9595996 | > | Handler_read_prev | 0 | > | Handler_read_rnd | 737 | > | Handler_read_rnd_next | 35686820 | > | Handler_rollback | 1260 | > | Handler_update | 0 | > | Handler_write | 570268 | > | Key_blocks_not_flushed | 0 | > | Key_blocks_unused | 103157 | > | Key_blocks_used | 12823 | > | Key_read_requests | 6741758 | > | Key_reads | 22028 | > | Key_write_requests | 2284340 | > | Key_writes | 2284340 | > | Max_used_connections | 14 | > | Not_flushed_delayed_rows | 0 | > | Open_files | 35 | > | Open_streams | 0 | > | Open_tables | 101 | > | Opened_tables | 112 | > | Qcache_free_blocks | 3295 | > | Qcache_free_memory | 9077184 | > | Qcache_hits | 230953 | > | Qcache_inserts | 465226 | > | Qcache_lowmem_prunes | 254590 | > | Qcache_not_cached | 423 | > | Qcache_queries_in_cache | 7510 | > | Qcache_total_blocks | 18317 | > | Questions | 1190852 | > | Rpl_status | NULL | > | Select_full_join | 0 | > | Select_full_range_join | 0 | > | Select_range | 206349 | > | Select_range_check | 0 | > | Select_scan | 257304 | > | Slave_open_temp_tables | 0 | > | Slave_running | OFF | > | Slave_retried_transactions | 0 | > | Slow_launch_threads | 0 | > | Slow_queries | 0 | > | Sort_merge_passes | 0 | > | Sort_range | 134 | > | Sort_rows | 804 | > | Sort_scan | 134 | > | Table_locks_immediate | 949628 | > | Table_locks_waited | 2 | > | Threads_cached | 1 | > | Threads_connected | 13 | > | Threads_created | 14 | > | Threads_running | 1 | > | Uptime | 158850 | > +----------------------------+-----------+ > 162 rows in set (0.02 sec) > > > Thank's, > Andrei > > > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
