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.