Christoph Klünter a écrit : > Hi List, > > We have a mysql-Server with 8G of Ram. But mysql doesn't use this ram. > But we get following error: > > May 14 22:56:11 sql mysqld[5875]: 070514 22:56:10 [ERROR] /usr/sbin/mysqld: > Got error 12 from storage engine > May 14 22:56:11 sql mysqld[5875]: 070514 22:56:10 [ERROR] /usr/sbin/mysqld: > Sort aborted > > I have set the sort_buffer_size to 1G but even this doesn't help. > Any hints ? Should we try a 64Bit-OS ? > > Regards > Christoph > > > Kernel is a 2.6.18-3-686-bigmem from debian-etch. > > mysql> show status; > +----------------------------+------------+ > | Variable_name | Value | > +----------------------------+------------+ > | Aborted_clients | 103352 | > | Aborted_connects | 3 | > | Binlog_cache_disk_use | 0 | > | Binlog_cache_use | 0 | > | Bytes_received | 2985193088 | > | Bytes_sent | 3725769917 | > | Com_admin_commands | 5377515 | > | Com_alter_db | 0 | > | Com_alter_table | 8 | > | Com_analyze | 0 | > | Com_backup_table | 0 | > | Com_begin | 0 | > | Com_change_db | 5434526 | > | 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 | 154176 | > | 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 | 2 | > | Com_grant | 0 | > | Com_ha_close | 0 | > | Com_ha_open | 0 | > | Com_ha_read | 0 | > | Com_help | 0 | > | Com_insert | 473672 | > | 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 | 2 | > | 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 | 14627137 | > | Com_set_option | 450 | > | Com_show_binlog_events | 0 | > | Com_show_binlogs | 13 | > | Com_show_charsets | 112 | > | Com_show_collations | 112 | > | Com_show_column_types | 0 | > | Com_show_create_db | 0 | > | Com_show_create_table | 42 | > | Com_show_databases | 10 | > | Com_show_errors | 0 | > | Com_show_fields | 111 | > | Com_show_grants | 46 | > | Com_show_innodb_status | 0 | > | Com_show_keys | 56 | > | 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 | 4930 | > | Com_show_storage_engines | 7 | > | Com_show_tables | 250 | > | Com_show_variables | 4708 | > | Com_show_warnings | 0 | > | Com_slave_start | 0 | > | Com_slave_stop | 0 | > | Com_truncate | 1 | > | Com_unlock_tables | 0 | > | Com_update | 1088621 | > | Com_update_multi | 0 | > | Connections | 61722 | > | Created_tmp_disk_tables | 6036 | > | Created_tmp_files | 8 | > | Created_tmp_tables | 287594 | > | Delayed_errors | 0 | > | Delayed_insert_threads | 0 | > | Delayed_writes | 0 | > | Flush_commands | 1 | > | Handler_commit | 0 | > | Handler_delete | 339394 | > | Handler_discover | 0 | > | Handler_read_first | 40913 | > | Handler_read_key | 76429100 | > | Handler_read_next | 643328453 | > | Handler_read_prev | 33596837 | > | Handler_read_rnd | 33146948 | > | Handler_read_rnd_next | 2840985720 | > | Handler_rollback | 0 | > | Handler_update | 1086372 | > | Handler_write | 28076366 | > | Key_blocks_not_flushed | 0 | > | Key_blocks_unused | 1944154 | > | Key_blocks_used | 151175 | > | Key_read_requests | 347151518 | > | Key_reads | 160286 | > | Key_write_requests | 3334152 | > | Key_writes | 2277303 | > | Max_used_connections | 639 | > | Not_flushed_delayed_rows | 0 | > | Open_files | 788 | > | Open_streams | 0 | > | Open_tables | 716 | > | Opened_tables | 2103 | > | Qcache_free_blocks | 7890 | > | Qcache_free_memory | 57390144 | > | Qcache_hits | 4027006 | > | Qcache_inserts | 14379458 | > | Qcache_lowmem_prunes | 34882 | > | Qcache_not_cached | 189319 | > | Qcache_queries_in_cache | 9026 | > | Qcache_total_blocks | 25979 | > | Questions | 25868482 | > | Rpl_status | NULL | > | Select_full_join | 0 | > | Select_full_range_join | 0 | > | Select_range | 339495 | > | Select_range_check | 0 | > | Select_scan | 515306 | > | 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 | 1000576 | > | Sort_rows | 181854952 | > | Sort_scan | 620651 | > | Table_locks_immediate | 12510467 | > | Table_locks_waited | 4698993 | > | Threads_cached | 136 | > | Threads_connected | 37 | > | Threads_created | 2167 | > | Threads_running | 5 | > | Uptime | 134426 | > +----------------------------+------------+ > 157 rows in set (0.00 sec) > > mysql> show variables; > +---------------------------------+----------------------------------------------------------+ > | Variable_name | Value > | > +---------------------------------+----------------------------------------------------------+ > | back_log | 50 > | > | basedir | /usr/ > | > | bdb_cache_size | 8388600 > | > | bdb_home | > | > | bdb_log_buffer_size | 0 > | > | bdb_logdir | > | > | bdb_max_lock | 10000 > | > | bdb_shared_data | OFF > | > | bdb_tmpdir | > | > | 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 | /usr/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 | /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 > | > | 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 | YES > | > | have_bdb | DISABLED > | > | have_blackhole_engine | NO > | > | have_compress | YES > | > | have_crypt | YES > | > | have_csv | YES > | > | have_example_engine | NO > | > | have_geometry | YES > | > | have_innodb | YES > | > | have_isam | YES > | > | have_ndbcluster | DISABLED > | > | have_openssl | NO > | > | have_query_cache | YES > | > | have_raid | YES > | > | have_rtree_keys | YES > | > | have_symlink | YES > | > | init_connect | > | > | init_file | > | > | init_slave | > | > | innodb_additional_mem_pool_size | 1048576 > | > | innodb_autoextend_increment | 8 > | > | innodb_buffer_pool_awe_mem_mb | 0 > | > | innodb_buffer_pool_size | 209715200 > | > | innodb_data_file_path | ibdata1:10M:autoextend > | > | innodb_data_home_dir | > | > | 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 | > | > | innodb_log_archive | 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_table_locks | ON > | > | innodb_thread_concurrency | 8 > | > | interactive_timeout | 28800 > | > | join_buffer_size | 131072 > | > | key_buffer_size | 4294963200 > | > | key_cache_age_threshold | 300 > | > | key_cache_block_size | 1024 > | > | key_cache_division_limit | 100 > | > | language | /usr/share/mysql/english/ > | > | large_files_support | ON > | > | license | GPL > | > | local_infile | ON > | > | locked_in_memory | OFF > | > | log | OFF > | > | log_bin | ON > | > | log_error | > | > | log_slave_updates | OFF > | > | log_slow_queries | ON > | > | log_update | 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 | 33553408 > | > | max_binlog_cache_size | 4294967295 > | > | max_binlog_size | 104857600 > | > | max_connect_errors | 10 > | > | max_connections | 2000 > | > | max_delayed_threads | 20 > | > | max_error_count | 64 > | > | max_heap_table_size | 16777216 > | > | max_insert_delayed_threads | 20 > | > | max_join_size | 4294967295 > | > | max_length_for_sort_data | 1024 > | > | max_relay_log_size | 0 > | > | max_seeks_for_key | 4294967295 > | > | max_sort_length | 1024 > | > | max_tmp_tables | 200 > | > | 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 | 134217728 > | > | ndb_autoincrement_prefetch_sz | 32 > | > | ndb_force_send | ON > | > | ndb_use_exact_count | ON > | > | ndb_use_transactions | ON > | > | net_buffer_length | 16384 > | > | net_read_timeout | 30 > | > | net_retry_count | 10 > | > | net_write_timeout | 60 > | > | new | OFF > | > | old_passwords | ON > | > | open_files_limit | 10010 > | > | pid_file | /var/run/mysqld/mysqld.pid > | > | port | 3306 > | > | preload_buffer_size | 32768 > | > | protocol_version | 10 > | > | query_alloc_block_size | 8192 > | > | query_cache_limit | 1048576 > | > | query_cache_min_res_unit | 4096 > | > | query_cache_size | 67108864 > | > | query_cache_type | ON > | > | query_cache_wlock_invalidate | OFF > | > | query_prealloc_size | 8192 > | > | range_alloc_block_size | 2048 > | > | read_buffer_size | 8384512 > | > | read_only | OFF > | > | read_rnd_buffer_size | 16773120 > | > | relay_log_purge | ON > | > | relay_log_space_limit | 0 > | > | rpl_recovery_rank | 0 > | > | secure_auth | OFF > | > | server_id | 1 > | > | skip_external_locking | ON > | > | skip_networking | OFF > | > | skip_show_database | OFF > | > | slave_net_timeout | 3600 > | > | slave_transaction_retries | 0 > | > | slow_launch_time | 2 > | > | socket | /var/run/mysqld/mysqld.sock > | > | sort_buffer_size | 1073741760 > | > | 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 | CEST > | > | table_cache | 2048 > | > | table_type | MyISAM > | > | thread_cache_size | 150 > | > | thread_stack | 131072 > | > | time_format | %H:%i:%s > | > | time_zone | SYSTEM > | > | tmp_table_size | 33554432 > | > | tmpdir | /tmp > | > | transaction_alloc_block_size | 8192 > | > | transaction_prealloc_size | 4096 > | > | tx_isolation | REPEATABLE-READ > | > | version | 4.1.11-Debian_4sarge7-log > | > | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: > (April 1, 2005) | > | version_comment | Source distribution > | > | version_compile_machine | i386 > | > | version_compile_os | pc-linux-gnu > | > | wait_timeout | 28800 > | > +---------------------------------+----------------------------------------------------------+ > 196 rows in set (0.01 sec) > >
Hi, yeah, apparenlty you're running into the 32 bits memory liimt. Note thta some memory is allocated for the OS so you don't even have the full 4GB of ram you can technically adressesed. The 64 bits os would increase this limit to 64gb++ (on 64 bits hardware) Good luck! -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
