On Thu, 2005-06-16 at 21:06, Richard Dale wrote:
> > So we have recently started stress testing Mysql on an Opteron dual
> CPU
> > machine running Ubuntu Hoary. We are using the 64-bit GCC
> > 4.0.24-standard binary from mysql. The stress test that I'm currently
> > running on it involves inserting a large database (from a mysqldump)
> > ...
So I don't think this has anything to do memory consumption anymore.
I've been able to get the problem to repeat with mysqld's memory
footprint being about 800+megs (545M resident). I also was able to get
it to happen with the ubuntu stock mysqld my.cnf aswell... which uses
ridiculously conservative memory settings. (The key cache is so small
it takes 4x as long to run the test). With an identical configuration
as my normal setup, running the 32bit binary doesn't appear to trigger
the problem.
I'll try 64bit with linuxthreads next.
Somebody asked for a show processlist and show status... Here's the data
pretty close to when it happened...
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 4 |
| Aborted_connects | 0 |
| Bytes_received | 3638957151 |
| Bytes_sent | 18427266 |
| Com_admin_commands | 5 |
| Com_alter_table | 10996 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 9 |
| Com_change_master | 0 |
| Com_check | 193 |
| Com_commit | 0 |
| Com_create_db | 57 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 5505 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_drop_db | 57 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 3 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 334612 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 5505 |
| Com_optimize | 0 |
| Com_purge | 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_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 2 |
| Com_show_fields | 0 |
| Com_show_grants | 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_processlist | 2 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 9 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 5491 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Connections | 182 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 43 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 9 |
| Handler_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 617450860 |
| Key_blocks_used | 478697 |
| Key_read_requests | 1216631922 |
| Key_reads | 47181747 |
| Key_write_requests | 1533549063 |
| Key_writes | 196897433 |
| Max_used_connections | 4 |
| Not_flushed_key_blocks | 477382 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 11 |
| Open_files | 25 |
| Open_streams | 0 |
| Opened_tables | 16714 |
| Questions | 362618 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_free_memory | 268417560 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 530 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 16700 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 181 |
| Threads_connected | 4 |
| Threads_running | 4 |
| Uptime | 229473 |
+--------------------------+------------+
133 rows in set (0.31 sec)
mysql> show processlist;
+-----+------+-----------+-------------+---------+------+----------------------+-------------------------------------------+
| Id | User | Host | db | Command | Time |
State | Info |
+-----+------+-----------+-------------+---------+------+----------------------+-------------------------------------------+
| 162 | root | localhost | abc1 | Query | 8815 | Repair with keycache
| /*!40000 ALTER TABLE Score ENABLE KEYS */ |
| 168 | root | localhost | abc2 | Query | 7752 | Repair with keycache
| /*!40000 ALTER TABLE Score ENABLE KEYS */ |
| 180 | root | localhost | abc3 | Query | 7603 | Repair with keycache
| /*!40000 ALTER TABLE Score ENABLE KEYS */ |
| 181 | root | localhost | NULL | Query | 0 |
NULL | show processlist |
+-----+------+-----------+-------------+---------+------+----------------------+-------------------------------------------+
(obviously this was from a snapshot of the one that takes 4x as long to
run)
-Joe
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
