[SDBUG] Mysql 5.0 Performance!!!
Pedro Moreno
pmoreno at oakwest.com
Fri Apr 3 00:53:57 PDT 2009
Hi guys.
I thinking the same thing, because I have test this with 2 machines
and the bottleneck is the same, I will check with the programmer this
and I will give a read the book Ben point in the last post, I have been
in that site.
Thanks guys I will let u know the status. See u latter!!!
Matthew Szubrycht wrote:
> 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
>
> _______________________________________________
> SDBUG mailing list
> SDBUG at sdbug.org
> http://lists.sdbug.org/mailman/listinfo/sdbug
More information about the SDBUG
mailing list