[SDBUG] Mysql 5.0 Performance!!!
Matthew Szubrycht
matts at bmihosting.com
Thu Apr 2 23:20:46 PDT 2009
Pedro,
A database this size should fly like a breeze on a box you describe,
unless the box is _really_ busy with other jobs.
This part: "Using temporary; Using filesort" smells to me like an
indexing issue, so I think I would give table indexing a closer look
and some love; Look for things like missing indexes or severely over-
indexed table design.
Cheers,
Matthew Szubrycht
On Apr 2, 2009, at 7:49 PM, Pedro Moreno wrote:
>
> Hi people.
>
> I have a problem with mysql, we develop a .NET app with mysql 5.0
> running on FreeBSD 7.0-p6, this machine is a P4 2.8Ghz 1GB RAM. I
> use the my-large mysql file to tune mysql. The system is a
> deployment of 6 machines in the factory each one with a barcode,
> they read about 300-500 items each one a day.
>
> That part is working 1-2 seconds between each read is acceptable, we
> are going to add more barcode machines, but we are facing some
> problems, exist some views that are eating mysql performance. Right
> now the database size is around "2.3MB" 38 Tables and 24559 records.
>
> Is not to big for my opinion, well this DB will handle all our
> warehouse items, will be around 300MB by the end of year I think.
>
> The problem is this, each time we use a module of the app, the
> system do this:
>
> SELECT * FROM EtiquetaModelo WHERE
> UPC='0000000266200063' and EstadosEtiquetaID=4
> 7 Init DB ow
> 7 Query select * from CargaDetalle where
> CargaID =13 and Modelo ='2662'
> 7 Init DB ow
> 7 Query SELECT * FROM EtiquetaModelo WHERE
> UPC='P265561054' and EstadosEtiquetaID=4
> 7 Init DB ow
> 7 Query select * from CargaDetalle where
> CargaID =13 and Modelo ='2655'
> 7 Init DB ow
> 7 Query INSERT INTO
> CargaDetalleEtiqueta(CargaID, UPC, Modelo) value(13, 'P265561054',
> '2655');update EtiquetaModelo set EstadosEtiquetaID = 5 Where
> 7 Init DB ow
> 7 Query SELECT * FROM
> vDetalleCargaRegistrado WHERE CargaID=13
>
> (I just enable the log to get this info, in my production systems is
> disable)
>
> This module receive the info from a bardcode "0000000266200063",
> then do all the stuff.
> Every time that reach the "SELECT * FROM vDetalleCargaRegistrado
> WHERE CargaID=13" I see this:
> Copying to tmp table
> Is copying the view data to some tmp_table, this is where
> bottleneck is, my cpu goes to 50 ID for a while, the user time is
> the one who eat all that CPU power.
> Exist about 4 views that have this behavior, the developer is
> checking each one to see if he made a mistake or something to
> tuneup, my part is to tuneup mysql for freebsd, I play all this
> morning with mysql, read a lot of pages about how to fix this.
> I still don't understand the views, but went I see the server, is
> like each time is reading the 1220 records and saving in that tmp
> table, I'm wrong ?
> Reading the views with phpMyAdmin I see this for those nasty views:
>
> id select_type table type possible_keys key key_len ref rows
> Extra
> 1 PRIMARY CargaDetalle ALL RefCarga11 /NULL/ /NULL/ /NULL/
> 117 1 PRIMARY Carga eq_ref PRIMARY,RefAnden21 PRIMARY 8
> ow.CargaDetalle.CargaID 1 Using where
> 1 PRIMARY Contenedor eq_ref PRIMARY PRIMARY 8
> ow.Carga.ContenedorID 1 Using where
> 1 PRIMARY Anden eq_ref PRIMARY PRIMARY 8 ow.Carga.AndenID 1
> Using where
> 1 PRIMARY EstadoCarga eq_ref PRIMARY PRIMARY 4
> ow.Carga.EstadoCargaID 1 1 PRIMARY <derived3> ALL /NULL/ /
> NULL/ /NULL/ /NULL/ 41 3 DERIVED CargaDetalleEtiqueta ALL /
> NULL/ /NULL/ /NULL/ /NULL/ 1174 Using temporary; Using filesort
> 3 DERIVED EtiquetaModelo ALL /NULL/ /NULL/ /NULL/ /NULL/
> 10133 Using where
>
>
> "Using temporary, Using filesort"
>
> He already knows that it have to use a tmp table to receive all that
> data, I'm correct?
>
> I have increase my tmp_table_size from 32M to 384M but nothing
> happen. U can see my my.cnf file and see the current value.
> Now check this other view:
>
> id select_type table type possible_keys key key_len ref rows
> Extra
> 1 PRIMARY CargaDetalle ALL RefCarga11 /NULL/ /NULL/ /NULL/
> 117 1 PRIMARY Carga eq_ref PRIMARY,RefAnden21 PRIMARY 8
> ow.CargaDetalle.CargaID 1 Using where
> 1 PRIMARY Contenedor eq_ref PRIMARY PRIMARY 8
> ow.Carga.ContenedorID 1 Using where
> 1 PRIMARY Anden eq_ref PRIMARY PRIMARY 8 ow.Carga.AndenID 1
> Using where
> 1 PRIMARY EstadoCarga eq_ref PRIMARY PRIMARY 4
> ow.Carga.EstadoCargaID 1 1 PRIMARY <derived3> ALL /NULL/ /
> NULL/ /NULL/ /NULL/ 41 3 DERIVED CargaDetalleEtiqueta ALL /
> NULL/ /NULL/ /NULL/ /NULL/ 1174 Using temporary; Using filesort
> 3 DERIVED EtiquetaModelo ALL /NULL/ /NULL/ /NULL/ /NULL/
> 10133 Using where
>
>
> (2) tmp tables.
>
> Now check this output from vmstat went I input data:
>
> procs memory page disks faults
> cpu
> r b w avm fre flt re pi po fr sr ad4 ad6 in sy cs
> us sy id
> 1 0 0 612580 329980 2 0 0 0 0 0 0 0 30670 30746
> 2268 39 3 58*
> 1 0 0 612580 329980 0 0 0 0 0 0 0 0 30753 120
> 2243 49 1 50
> 1 0 0 612580 329976 1 0 0 0 0 0 0 0 30662 125
> 2238 49 1 50
> 1 0 0 614220 329968 2 0 0 0 0 0 0 0 30688 129
> 2265 49 1 50
> 1 0 0 614220 329964 1 0 0 0 0 0 1 1 30672 128
> 2256 49 1 50
> 1 0 0 614220 329964 0 0 0 0 0 0 0 0 30662 120
> 2238 49 1 50
>
> another input:
> 1 0 0 610940 329988 0 0 0 0 0 0 0 0 30668 30687
> 2257 16 4 80*
> 1 0 0 612580 329980 2 0 0 0 0 0 0 0 31145 130
> 2274 50 0 50
> 1 0 0 612580 329980 0 0 0 0 0 0 0 0 30665 124
> 2242 49 1 50
> 1 0 0 614220 329968 3 0 0 0 0 0 0 0 30664 132
> 2243 49 1 50
> 1 0 0 614220 329964 1 0 0 0 0 0 0 0 30662 120
> 2239 49 1 50
> 1 0 0 614220 329964 0 0 0 0 0 0 0 0 30662 120
> 2239 49 1 50
> 1 0 0 614220 329964 0 0 0 0 0 0 0 0 30664 129
> 2241 49 1 50
> Is reading something, I don't exactly understand this, but reading
> this is doing a "Read" operation, the 30746 is the amount of data is
> reading from my virtual memory?
> Now, this is iostat went I input data:
>
> tty ad4
> ad6 cd0 cpu
> tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy
> in id
> 77 11.62 4 0.04 12.12 4 0.05 0.00 0 0.00 18 0 3 0 79*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.50 1 0.00 0.50 1 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 43 0 0
> 0 57*
> tin tout KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s us ni sy
> in id
> 0 76 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 0 0 0
> 0 100
> 0 77 0.50 1 0.00 0.50 1 0.00 0.00 0 0.00 19 0 3
> 0 79*
> 0 77 12.56 8 0.10 12.07 7 0.08 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 50 0 0
> 0 50*
> 0 77 0.50 1 0.00 0.50 1 0.00 0.00 0 0.00 43 0 0
> 0 57*
> 0 75 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 0 0 0
> 0 100
> 0 76 0.00 0 0.00 0.00 0 0.00 0.00 0 0.00 0 0 0
> 0 100
> My system have a raid-1 setup with geom(gmirror).
>
> There u see the amount of time of each operation. Now multiply this
> by 50?
>
> The network data we transfer on each input is < 1Kb.
> KB/s in KB/s out
> 0.06 0.13
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.87 2.17
> 0.34 0.40
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.06 0.12
> 0.11 0.78
> 0.06 0.12
>
> Is all the info I see that tell me that Mysql is not well optimized,
> this is the config file(my.cnf):
> # The following options will be passed to all MySQL clients
> [client]
> #password = your_password
> port = 3306
> socket = /tmp/mysql.sock
>
> # Here follows entries for some specific programs
>
> # The MySQL server
> [mysqld]
> port = 3306
> socket = /tmp/mysql.sock
> skip-locking
> key_buffer = 256M
> max_allowed_packet = 1M
> table_cache = 256
> sort_buffer_size = 1M
> read_buffer_size = 1M
> read_rnd_buffer_size = 4M
> myisam_sort_buffer_size = 64M
> thread_cache_size = 8
> query_cache_size= 16M
> # Try number of CPU's*2 for thread_concurrency
> thread_concurrency = 8
> skip-bdb
> log
> tmp_table_size=384M
> max_heap_table_size=32M
> skip-federated
> [mysqldump]
> quick
> max_allowed_packet = 16M
> [mysql]
> no-auto-rehash
> [isamchk]
> key_buffer = 128M
> sort_buffer_size = 128M
> read_buffer = 2M
> write_buffer = 2M
> [myisamchk]
> key_buffer = 128M
> sort_buffer_size = 128M
> read_buffer = 2M
> write_buffer = 2M
> [mysqlhotcopy]
> interactive-timeout
>
> I'm using MyIsam engine.
>
>
> This is my extended-status:
> +-----------------------------------+-----------+
> | Variable_name | Value |
> +-----------------------------------+-----------+
> | Aborted_clients | 1 |
> | Aborted_connects | 3 |
> | Binlog_cache_disk_use | 0 |
> | Binlog_cache_use | 0 |
> | Bytes_received | 5425235 |
> | Bytes_sent | 867362995 |
> | Com_admin_commands | 89 |
> | Com_alter_db | 0 |
> | Com_alter_table | 48 |
> | Com_analyze | 0 |
> | Com_backup_table | 0 |
> | Com_begin | 0 |
> | Com_call_procedure | 0 |
> | Com_change_db | 502 |
> | Com_change_master | 0 |
> | Com_check | 0 |
> | Com_checksum | 0 |
> | Com_commit | 0 |
> | Com_create_db | 0 |
> | Com_create_function | 0 |
> | Com_create_index | 8 |
> | Com_create_table | 54 |
> | Com_create_user | 0 |
> | Com_dealloc_sql | 0 |
> | Com_delete | 32 |
> | Com_delete_multi | 0 |
> | Com_do | 0 |
> | Com_drop_db | 0 |
> | Com_drop_function | 0 |
> | Com_drop_index | 0 |
> | Com_drop_table | 76 |
> | 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 | 14517 |
> | Com_insert_select | 32 |
> | Com_kill | 0 |
> | Com_load | 0 |
> | Com_load_master_data | 0 |
> | Com_load_master_table | 0 |
> | Com_lock_tables | 42 |
> | 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 | 352 |
> | Com_set_option | 801 |
> | Com_show_binlog_events | 0 |
> | Com_show_binlogs | 14 |
> | Com_show_charsets | 2 |
> | Com_show_collations | 7 |
> | Com_show_column_types | 0 |
> | Com_show_create_db | 0 |
> | Com_show_create_table | 54 |
> | Com_show_databases | 20 |
> | Com_show_errors | 0 |
> | Com_show_fields | 169 |
> | Com_show_grants | 7 |
> | Com_show_innodb_status | 0 |
> | Com_show_keys | 93 |
> | Com_show_logs | 0 |
> | Com_show_master_status | 0 |
> | Com_show_ndb_status | 0 |
> | Com_show_new_master | 0 |
> | Com_show_open_tables | 0 |
> | Com_show_privileges | 0 |
> | Com_show_processlist | 1491 |
> | Com_show_slave_hosts | 0 |
> | Com_show_slave_status | 0 |
> | Com_show_status | 13 |
> | Com_show_storage_engines | 0 |
> | Com_show_tables | 140 |
> | Com_show_triggers | 42 |
> | Com_show_variables | 27 |
> | 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_reset | 0 |
> | Com_stmt_send_long_data | 0 |
> | Com_truncate | 0 |
> | Com_unlock_tables | 42 |
> | Com_update | 120 |
> | Com_update_multi | 0 |
> | 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 |
> | Connections | 1718 |
> | Created_tmp_disk_tables | 321 |
> | Created_tmp_files | 5 |
> | Created_tmp_tables | 1871 |
> | Delayed_errors | 0 |
> | Delayed_insert_threads | 0 |
> | Delayed_writes | 0 |
> | Flush_commands | 1 |
> | Handler_commit | 0 |
> | Handler_delete | 14683 |
> | Handler_discover | 0 |
> | Handler_prepare | 0 |
> | Handler_read_first | 44 |
> | Handler_read_key | 190420 |
> | Handler_read_next | 103226 |
> | Handler_read_prev | 0 |
> | Handler_read_rnd | 4709 |
> | Handler_read_rnd_next | 10460346 |
> | Handler_rollback | 0 |
> | Handler_savepoint | 0 |
> | Handler_savepoint_rollback | 0 |
> | Handler_update | 141574 |
> | Handler_write | 121118 |
> | Innodb_buffer_pool_pages_data | 19 |
> | Innodb_buffer_pool_pages_dirty | 0 |
> | Innodb_buffer_pool_pages_flushed | 0 |
> | Innodb_buffer_pool_pages_free | 493 |
> | Innodb_buffer_pool_pages_misc | 0 |
> | Innodb_buffer_pool_pages_total | 512 |
> | Innodb_buffer_pool_read_ahead_rnd | 1 |
> | Innodb_buffer_pool_read_ahead_seq | 0 |
> | Innodb_buffer_pool_read_requests | 77 |
> | Innodb_buffer_pool_reads | 12 |
> | Innodb_buffer_pool_wait_free | 0 |
> | Innodb_buffer_pool_write_requests | 0 |
> | Innodb_data_fsyncs | 3 |
> | Innodb_data_pending_fsyncs | 0 |
> | Innodb_data_pending_reads | 0 |
> | Innodb_data_pending_writes | 0 |
> | Innodb_data_read | 2494464 |
> | Innodb_data_reads | 25 |
> | Innodb_data_writes | 3 |
> | Innodb_data_written | 1536 |
> | Innodb_dblwr_pages_written | 0 |
> | Innodb_dblwr_writes | 0 |
> | Innodb_log_waits | 0 |
> | Innodb_log_write_requests | 0 |
> | Innodb_log_writes | 1 |
> | Innodb_os_log_fsyncs | 3 |
> | Innodb_os_log_pending_fsyncs | 0 |
> | Innodb_os_log_pending_writes | 0 |
> | Innodb_os_log_written | 512 |
> | Innodb_page_size | 16384 |
> | Innodb_log_writes | 1 |
> | Innodb_os_log_fsyncs | 3 |
> | Innodb_os_log_pending_fsyncs | 0 |
> | Innodb_os_log_pending_writes | 0 |
> | Innodb_os_log_written | 512 |
> | Innodb_page_size | 16384 |
> | Innodb_pages_created | 0 |
> | Innodb_pages_read | 19 |
> | Innodb_pages_written | 0 |
> | Innodb_row_lock_current_waits | 0 |
> | Innodb_row_lock_time | 0 |
> | Innodb_row_lock_time_avg | 0 |
> | Innodb_row_lock_time_max | 0 |
> | Innodb_row_lock_waits | 0 |
> | Innodb_rows_deleted | 0 |
> | Innodb_rows_inserted | 0 |
> | Innodb_rows_read | 0 |
> | Innodb_rows_updated | 0 |
> | Key_blocks_not_flushed | 0 |
> | Key_blocks_unused | 225376 |
> | Key_blocks_used | 6584 |
> | Key_read_requests | 950504 |
> | Key_reads | 7188 |
> | Key_write_requests | 140745 |
> | Key_writes | 4427 |
> | Last_query_cost | 0.000000 |
> | Max_used_connections | 4 |
> | Not_flushed_delayed_rows | 0 |
> | Open_files | 137 |
> | Open_streams | 0 |
> | Open_tables | 80 |
> | Opened_tables | 244 |
> | Prepared_stmt_count | 0 |
> | Qcache_free_blocks | 11 |
> | Qcache_free_memory | 16694312 |
> | Qcache_hits | 270 |
> | Qcache_inserts | 215 |
> | Qcache_lowmem_prunes | 0 |
> | Qcache_not_cached | 632 |
> | Qcache_queries_in_cache | 41 |
> | Qcache_total_blocks | 117 |
> | Queries | 20936 |
> | Questions | 20936 |
> | Rpl_status | NULL |
> | Select_full_join | 100 |
> | Select_full_range_join | 0 |
> | Select_range | 0 |
> | Select_range_check | 0 |
> | Select_scan | 1038 |
> | Slave_open_temp_tables | 0 |
> | Slave_retried_transactions | 0 |
> | Slave_running | OFF |
> | Slow_launch_threads | 0 |
> | Slow_queries | 4 |
> | Sort_merge_passes | 0 |
> | Sort_range | 26 |
> | Sort_rows | 4717 |
> | Sort_scan | 93 |
> | Table_locks_immediate | 1038 |
> | Table_locks_waited | 0 |
> | Tc_log_max_pages_used | 0 |
> | Tc_log_page_size | 0 |
> | Tc_log_page_waits | 0 |
> | Threads_cached | 2 |
> | Threads_connected | 2 |
> | Threads_created | 4 |
> | Threads_running | 1 |
> | Uptime | 23002 |
> | Uptime_since_flush_status | 20936 |
> +-----------------------------------+-----------+
>
> I don't know why is showing me Innodb info, I'm not using that
> engine yet.
>
> This are my variables:
>
> +---------------------------------
> +------------------------------------------------------------+
> | Variable_name |
> Value |
> +---------------------------------
> +------------------------------------------------------------+
> | auto_increment_increment |
> 1 |
> | auto_increment_offset |
> 1 |
> | automatic_sp_privileges |
> ON |
> | back_log |
> 50 |
> | basedir | /usr/
> local/ |
> | bdb_cache_size |
> 8384512 |
> | bdb_home
> | |
> | bdb_log_buffer_size |
> 262144 |
> | 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_filesystem |
> binary |
> | character_set_results |
> latin1 |
> | character_set_server |
> latin1 |
> | character_set_system |
> utf8 |
> | character_sets_dir | /usr/local/share/mysql/
> charsets/ |
> | collation_connection |
> latin1_swedish_ci |
> | collation_database |
> latin1_swedish_ci |
> | collation_server |
> latin1_swedish_ci |
> | completion_type |
> 0 |
> | concurrent_insert |
> 1 |
> | connect_timeout |
> 10 |
> | datadir | /var/db/
> 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 |
> | keep_files_on_create |
> OFF |
> | engine_condition_pushdown |
> OFF |
> | 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 |
> DISABLED |
> | have_blackhole_engine |
> NO |
> | have_compress |
> YES |
> | have_crypt |
> YES |
> | have_csv |
> NO |
> | have_dynamic_loading |
> YES |
> | have_blackhole_engine |
> NO |
> | have_compress |
> YES |
> | have_crypt |
> YES |
> | have_csv |
> NO |
> | have_dynamic_loading |
> YES |
> | have_example_engine |
> NO |
> | have_federated_engine |
> NO |
> | have_geometry |
> YES |
> | have_innodb |
> YES |
> | have_isam |
> NO |
> | have_merge_engine |
> YES |
> | have_ndbcluster |
> NO |
> | have_openssl |
> NO |
> | have_ssl |
> NO |
> | have_query_cache |
> YES |
> | have_raid |
> NO |
> | have_rtree_keys |
> YES |
> | have_symlink |
> YES |
> | hostname |
> bacula.oakwest.com |
> | 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 |
> 8388608 |
> | innodb_checksums |
> ON |
> | innodb_commit_concurrency |
> 0 |
> | innodb_concurrency_tickets |
> 500 |
> | innodb_data_file_path |
> ibdata1:10M:autoextend |
> | innodb_data_home_dir
> | |
> | innodb_adaptive_hash_index |
> ON |
> | 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_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_rollback_on_timeout |
> OFF |
> | innodb_support_xa |
> ON |
> | innodb_sync_spin_loops |
> 20 |
> | innodb_table_locks |
> ON |
> | innodb_thread_concurrency |
> 8 |
> | innodb_thread_sleep_delay |
> 10000 |
> | innodb_support_xa |
> ON |
> | innodb_sync_spin_loops |
> 20 |
> | innodb_table_locks |
> ON |
> | innodb_thread_concurrency |
> 8 |
> | innodb_thread_sleep_delay |
> 10000 |
> | interactive_timeout |
> 28800 |
> | join_buffer_size |
> 131072 |
> | key_buffer_size |
> 268435456 |
> | key_cache_age_threshold |
> 300 |
> | key_cache_block_size |
> 1024 |
> | key_cache_division_limit |
> 100 |
> | language | /usr/local/share/mysql/
> english/ |
> | large_files_support |
> ON |
> | large_page_size |
> 0 |
> | large_pages |
> OFF |
> | lc_time_names |
> en_US |
> | license |
> GPL |
> | local_infile |
> ON |
> | locked_in_memory |
> OFF |
> | log |
> ON |
> | log_bin |
> OFF |
> | log_bin_trust_function_creators |
> OFF |
> | log_error
> | |
> | log_queries_not_using_indexes |
> OFF |
> | log_slave_updates |
> OFF |
> | log_slow_queries |
> 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 |
> 1048576 |
> | max_binlog_cache_size |
> 4294963200 |
> | max_binlog_size |
> 1073741824 |
> | max_connect_errors |
> 10 |
> | max_connections |
> 100 |
> | max_delayed_threads |
> 20 |
> | max_error_count |
> 64 |
> | max_heap_table_size |
> 33554432 |
> | max_insert_delayed_threads |
> 20 |
> | max_join_size |
> 4294967295 |
> | max_length_for_sort_data |
> 1024 |
> | max_prepared_stmt_count |
> 16382 |
> | max_relay_log_size |
> 0 |
> | max_seeks_for_key |
> 4294967295 |
> | max_sort_length |
> 1024 |
> | max_sp_recursion_depth |
> 0 |
> | max_tmp_tables |
> 32 |
> | max_user_connections |
> 0 |
> | max_write_lock_count |
> 4294967295 |
> | multi_range_count |
> 256 |
> | myisam_data_pointer_size |
> 6 |
> | myisam_max_sort_file_size |
> 2146435072 |
> | myisam_recover_options |
> OFF |
> | myisam_repair_threads |
> 1 |
> | myisam_sort_buffer_size |
> 67108864 |
> | myisam_max_sort_file_size |
> 2146435072 |
> | myisam_recover_options |
> OFF |
> | myisam_repair_threads |
> 1 |
> | myisam_sort_buffer_size |
> 67108864 |
> | myisam_stats_method |
> nulls_unequal |
> | net_buffer_length |
> 16384 |
> | net_read_timeout |
> 30 |
> | net_retry_count |
> 1000000 |
> | net_write_timeout |
> 60 |
> | new |
> OFF |
> | old_passwords |
> OFF |
> | open_files_limit |
> 11095 |
> | optimizer_prune_level |
> 1 |
> | optimizer_search_depth |
> 62 |
> | pid_file | /var/db/mysql/
> bacula.oakwest.com.pid |
> | plugin_dir
> | |
> | port |
> 3306 |
> | preload_buffer_size |
> 32768 |
> | profiling |
> OFF |
> | profiling_history_size |
> 15 |
> | protocol_version |
> 10 |
> | query_alloc_block_size |
> 8192 |
> | query_cache_limit |
> 1048576 |
> | query_cache_min_res_unit |
> 4096 |
> | query_cache_size |
> 16777216 |
> | query_cache_type |
> ON |
> | query_cache_wlock_invalidate |
> OFF |
> | query_prealloc_size |
> 8192 |
> | range_alloc_block_size |
> 4096 |
> | read_buffer_size |
> 1048576 |
> | read_only |
> OFF |
> | read_rnd_buffer_size |
> 4194304 |
> | relay_log
> | |
> | relay_log_index
> | |
> | relay_log_info_file | relay-
> log.info |
> | relay_log_purge |
> ON |
> | relay_log_space_limit |
> 0 |
> | rpl_recovery_rank |
> 0 |
> | secure_auth |
> OFF |
> | secure_file_priv
> | |
> | server_id |
> 1 |
> | skip_external_locking |
> ON |
> | skip_networking |
> OFF |
> | skip_show_database |
> OFF |
> | slave_compressed_protocol |
> OFF |
> | slave_load_tmpdir | /var/
> tmp/ |
> | slave_net_timeout |
> 3600 |
> | slave_skip_errors |
> OFF |
> | slave_transaction_retries |
> 10 |
> | slow_launch_time |
> 2 |
> | socket | /tmp/
> mysql.sock |
> | sort_buffer_size |
> 1048576 |
> | sql_big_selects |
> ON |
> | sql_mode
> | |
> | sql_notes |
> ON |
> | 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 |
> PDT |
> | table_cache |
> 256 |
> | table_lock_wait_timeout |
> 50 |
> | table_type |
> MyISAM |
> | thread_cache_size |
> 8 |
> | thread_stack |
> 196608 |
> | time_format | %H:%i:
> %s |
> | time_zone |
> SYSTEM |
> | timed_mutexes |
> OFF |
> | tmp_table_size |
> 402653184 |
> | tmpdir | /var/
> tmp/ |
> | transaction_alloc_block_size |
> 8192 |
> | transaction_prealloc_size |
> 4096 |
> | tx_isolation | REPEATABLE-
> READ |
> | updatable_views_with_limit |
> YES |
> | version | 5.0.77-
> log |
> | version_bdb | Sleepycat Software: Berkeley DB
> 4.1.24: (January 29, 2009) |
> | version_comment | FreeBSD port: mysql-
> server-5.0.77_1 |
> | version_compile_machine |
> i386 |
> | version_compile_os | portbld-
> freebsd6.1 |
> | wait_timeout |
> 28800 |
> +---------------------------------
> +------------------------------------------------------------+
>
> I start this, we begin running mysql 5.1, after we start getting
> this problem we switch to 5.0, the performance mailist of freebsd
> suggest to use the 5.0 branch, this is the reason I switch, I was
> thinking that maybe the problem will be fix but no.
>
> FreeBSD 7.0 p6
> P4 2.8Ghz Raid-1 Sata disks.
> 1GB RAM
> Just running mysql.
>
> Hope someone could point me why I have this problem with my views
> and what way I could follow, u can see that we still don't have to
> much records yet, thanks all for your time, if u need more info
> please just let me know, see latter people.
>
> _______________________________________________
> SDBUG mailing list
> SDBUG at sdbug.org
> http://lists.sdbug.org/mailman/listinfo/sdbug
More information about the SDBUG
mailing list