MySQL Forums
Forum List  »  General

Mysql takes long time to save data and sometimes does not save
Posted by: Danielle Ballester
Date: September 15, 2008 02:04PM

dear all,

When I start mysql service, it works very well, but after about 15 minutes, it takes a long time to save, and most time does not save.
Please find attached my my.ini file and the "show status" at this time.

- using innodb tables;

My.ini
------

[client]
port=3306

[mysql]
default-character-set=latin1
quick

[mysqld]
port=3306


basedir="C:/Arquivos de programas/MySQL/MySQL Server 5.0/"
datadir="C:/Arquivos de programas/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB

sql-mode="REAL_AS_FLOAT"

max_connections=800

query_cache_size=165M
query_cache_type = 1

table_cache=2048

tmp_table_size=96M

thread_cache_size=160

#*** MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=12M

key_buffer_size=3000M

read_buffer_size=1M
read_rnd_buffer_size=1M


#*** INNODB Specific options ***

innodb_table_locks=0

innodb_additional_mem_pool_size=10M

innodb_flush_log_at_trx_commit=2

innodb_log_buffer_size=1M

innodb_buffer_pool_size=1000M

innodb_log_file_size=38M

innodb_thread_concurrency=8

#Allow big result sets by saving all temporary sets on file (Solves most 'table full' errors).
big-tables

#Paths to individual datafiles and their sizes.
#innodb_data_file_path=ibdata1:807403520:autoextend
#This option makes InnoDB to store each created table into its own .ibd file.
innodb_file_per_table
#The common part of the directory path for all InnoDB datafiles. Leave this empty if you want to split the data files onto different drives.
#innodb_data_home_dir=C:/Arquivos de programas/MySQL/MySQL Server 5.0/tbs
#This option makes InnoDB to store each created table into its own .ibd file.
innodb_file_per_table

#Default transaction isolation level
transaction-isolation=REPEATABLE-READ

#Max packetlength to send/receive from to server.
max_allowed_packet=10M

log-bin=C:\Arquivos de programas\MySQL\MySQL Server 5.0\Data\erdadosone-bin

#server-id=1
#Tells the slave to log the updates from the slave thread to the binary log. You will need to turn it on if you plan to daisy-chain the slaves.
#log-slave-updates

#The number of seconds the server waits for activity on a connection before closing it
wait_timeout=28800


#Don't cache results that are bigger than this.
query_cache_limit=30M

#Using this option will cause most temporary files created to use a small set of names, rather than a unique name for each new file.
temp-pool


#Each thread that needs to do a sort allocates a buffer of this size.
sort_buffer_size=8M


#Don't cache host names.
skip-host-cache
#Don't resolve hostnames. All hostnames are IP's or 'localhost'.
skip-name-resolve

#The number of seconds the mysqld server is waiting for a connect packet before responding with 'Bad handshake'
connect_timeout=320
#Enter a name for the query log file. Otherwise a default name will be used.
#log=general
#Enter a name for the error log file. Otherwise a default name will be used.
log-error=error
#Enter a name for the slow query log. Otherwise a default name will be used.
#log-slow-queries=slow
#Log some not critical warnings to the log file.
log-warnings
#Logs will be rotated after expire-log-days days
expire_logs_days=3
#The size of the cache to hold the SQL statements for the binary log during a transaction. If you often use big, multi-statement transactions you can increase this to get more performance.
binlog_cache_size=256k
#Don't allow creation of heap tables bigger than this.
max_heap_table_size=32M

--------



Show Status at the moment mysql takes long time and does not save
-----------------------------------------------------------------

Variable_name Value
Aborted_clients 96
Aborted_connects 0
Binlog_cache_disk_use 1
Binlog_cache_use 86
Bytes_received 161
Bytes_sent 336
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_call_procedure 0
Com_change_db 0
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_create_user 0
Com_dealloc_sql 0
Com_delete 0
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 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 0
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 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 1
Com_set_option 1
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 0
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
Com_show_innodb_status 0
Com_show_keys 0
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 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 1
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 0
Com_update 0
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 612
Created_tmp_disk_tables 2
Created_tmp_files 6
Created_tmp_tables 2
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 2
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 133
Innodb_buffer_pool_pages_data 22855
Innodb_buffer_pool_pages_dirty 5
Innodb_buffer_pool_pages_flushed 1759
Innodb_buffer_pool_pages_free 39764
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 1381
Innodb_buffer_pool_pages_total 64000
Innodb_buffer_pool_read_ahead_rnd 5
Innodb_buffer_pool_read_ahead_seq 354
Innodb_buffer_pool_read_requests 103212389
Innodb_buffer_pool_reads 15001
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 12523
Innodb_data_fsyncs 1500
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 376033280
Innodb_data_reads 22837
Innodb_data_writes 3042
Innodb_data_written 58981376
Innodb_dblwr_pages_written 1759
Innodb_dblwr_writes 172
Innodb_log_waits 0
Innodb_log_write_requests 1546
Innodb_log_writes 978
Innodb_os_log_fsyncs 942
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 1274368
Innodb_page_size 16384
Innodb_pages_created 37
Innodb_pages_read 22818
Innodb_pages_written 1759
Innodb_row_lock_current_waits 3
Innodb_row_lock_time 604812
Innodb_row_lock_time_avg 31832
Innodb_row_lock_time_max 51375
Innodb_row_lock_waits 19
Innodb_rows_deleted 0
Innodb_rows_inserted 2125
Innodb_rows_read 120773508
Innodb_rows_updated 117
Key_blocks_not_flushed 0
Key_blocks_unused 113910
Key_blocks_used 91
Key_read_requests 2952073
Key_reads 2576
Key_write_requests 29767
Key_writes 0
Last_query_cost 2.399000
Max_used_connections 95
Not_flushed_delayed_rows 0
Open_files 23
Open_streams 0
Open_tables 37
Opened_tables 0
Prepared_stmt_count 0
Qcache_free_blocks 10
Qcache_free_memory 536821040
Qcache_hits 190
Qcache_inserts 232
Qcache_lowmem_prunes 0
Qcache_not_cached 4078
Qcache_queries_in_cache 29
Qcache_total_blocks 72
Questions 26843
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 2
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects 0
Ssl_connect_renegotiates 0
Ssl_ctx_verify_depth 0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size 0
Ssl_session_cache_timeouts 0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries 0
Ssl_verify_depth 0
Ssl_verify_mode 0
Ssl_version
Table_locks_immediate 11238
Table_locks_waited 0
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 21
Threads_connected 74
Threads_created 95
Threads_running 4
Uptime 2622
Uptime_since_flush_status 2622

---------------------------------------



Thanks for any information about.

Danielle

Options: ReplyQuote


Subject
Written By
Posted
Mysql takes long time to save data and sometimes does not save
September 15, 2008 02:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.