Re: Database cache corrupted
Can you send us the exact zabbix error? On Sun, Apr 27, 2008 at 6:18 PM, Sergio Belkin <[EMAIL PROTECTED]> wrote: > So anyone had ever had any problem with database cache? :) > > 2008/4/25 Sergio Belkin <[EMAIL PROTECTED]>: > > > > Hi, I am using zabbix (monitoring software) with mysql. zabbix goes > > zombie and complains with messages suggesting that Database cache > > perhaps is corrupted. How can I check and fix it? I am using Centos > > 5.1, cpu Intel(R) Xeon(R) CPU and 1 Gb of RAM. > > > > > > my.cnf is as follows: > > > > [client] > > port= 3306 > > socket = /var/lib/mysql/mysql.sock > > [mysqld] > > port= 3306 > > socket = /var/lib/mysql/mysql.sock > > back_log = 50 > > max_connections = 100 > > max_connect_errors = 10 > > table_cache = 1024 > > max_allowed_packet = 16M > > binlog_cache_size = 1M > > max_heap_table_size = 128M > > sort_buffer_size = 8M > > join_buffer_size = 3M > > thread_cache_size = 8 > > thread_concurrency = 8 > > query_cache_size = 64M > > query_cache_limit = 2M > > ft_min_word_len = 4 > > default_table_type = InnoDB > > thread_stack = 192K > > transaction_isolation = REPEATABLE-READ > > tmp_table_size = 64M > > log_slow_queries = /var/log/mysqld/slow-query-log > > long_query_time = 5 > > log_long_format > > tmpdir = /tmp > > log_queries_not_using_indexes = /var/log/mysqld/not-indexes.log > > expire_logs_days = 2 > > server-id = 1 > > key_buffer_size = 8M > > read_buffer_size = 2M > > read_rnd_buffer_size = 16M > > bulk_insert_buffer_size = 64M > > myisam_sort_buffer_size = 128M > > myisam_max_sort_file_size = 10G > > myisam_max_extra_sort_file_size = 10G > > myisam_repair_threads = 1 > > myisam_recover > > skip-bdb > > innodb_additional_mem_pool_size = 16M > > innodb_buffer_pool_size = 600M > > innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M > > innodb_file_io_threads = 4 > > innodb_thread_concurrency = 16 > > innodb_flush_log_at_trx_commit = 1 > > innodb_log_buffer_size = 8M > > innodb_log_file_size = 256M > > innodb_log_files_in_group = 3 > > innodb_max_dirty_pages_pct = 90 > > innodb_lock_wait_timeout = 120 > > [mysqldump] > > quick > > max_allowed_packet = 16M > > [mysql] > > no-auto-rehash > > [isamchk] > > key_buffer = 512M > > sort_buffer_size = 512M > > read_buffer = 8M > > write_buffer = 8M > > [myisamchk] > > key_buffer = 512M > > sort_buffer_size = 512M > > read_buffer = 8M > > write_buffer = 8M > > [mysqlhotcopy] > > interactive-timeout > > [mysqld_safe] > > open-files-limit = 8192 > > > > EOF > > > > Thanks in advance! > > > > -- > > -- > > Open Kairos http://www.openkairos.com > > Watch More TV http://sebelk.blogspot.com > > Sergio Belkin - > > > > > > -- > -- > Open Kairos http://www.openkairos.com > Watch More TV http://sebelk.blogspot.com > Sergio Belkin - > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- high performance mysql consulting. http://provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database cache corrupted
So anyone had ever had any problem with database cache? :) 2008/4/25 Sergio Belkin <[EMAIL PROTECTED]>: > Hi, I am using zabbix (monitoring software) with mysql. zabbix goes > zombie and complains with messages suggesting that Database cache > perhaps is corrupted. How can I check and fix it? I am using Centos > 5.1, cpu Intel(R) Xeon(R) CPU and 1 Gb of RAM. > > > my.cnf is as follows: > > [client] > port= 3306 > socket = /var/lib/mysql/mysql.sock > [mysqld] > port= 3306 > socket = /var/lib/mysql/mysql.sock > back_log = 50 > max_connections = 100 > max_connect_errors = 10 > table_cache = 1024 > max_allowed_packet = 16M > binlog_cache_size = 1M > max_heap_table_size = 128M > sort_buffer_size = 8M > join_buffer_size = 3M > thread_cache_size = 8 > thread_concurrency = 8 > query_cache_size = 64M > query_cache_limit = 2M > ft_min_word_len = 4 > default_table_type = InnoDB > thread_stack = 192K > transaction_isolation = REPEATABLE-READ > tmp_table_size = 64M > log_slow_queries = /var/log/mysqld/slow-query-log > long_query_time = 5 > log_long_format > tmpdir = /tmp > log_queries_not_using_indexes = /var/log/mysqld/not-indexes.log > expire_logs_days = 2 > server-id = 1 > key_buffer_size = 8M > read_buffer_size = 2M > read_rnd_buffer_size = 16M > bulk_insert_buffer_size = 64M > myisam_sort_buffer_size = 128M > myisam_max_sort_file_size = 10G > myisam_max_extra_sort_file_size = 10G > myisam_repair_threads = 1 > myisam_recover > skip-bdb > innodb_additional_mem_pool_size = 16M > innodb_buffer_pool_size = 600M > innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M > innodb_file_io_threads = 4 > innodb_thread_concurrency = 16 > innodb_flush_log_at_trx_commit = 1 > innodb_log_buffer_size = 8M > innodb_log_file_size = 256M > innodb_log_files_in_group = 3 > innodb_max_dirty_pages_pct = 90 > innodb_lock_wait_timeout = 120 > [mysqldump] > quick > max_allowed_packet = 16M > [mysql] > no-auto-rehash > [isamchk] > key_buffer = 512M > sort_buffer_size = 512M > read_buffer = 8M > write_buffer = 8M > [myisamchk] > key_buffer = 512M > sort_buffer_size = 512M > read_buffer = 8M > write_buffer = 8M > [mysqlhotcopy] > interactive-timeout > [mysqld_safe] > open-files-limit = 8192 > > EOF > > Thanks in advance! > > -- > -- > Open Kairos http://www.openkairos.com > Watch More TV http://sebelk.blogspot.com > Sergio Belkin - > -- -- Open Kairos http://www.openkairos.com Watch More TV http://sebelk.blogspot.com Sergio Belkin - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database cache corrupted
Hi, I am using zabbix (monitoring software) with mysql. zabbix goes zombie and complains with messages suggesting that Database cache perhaps is corrupted. How can I check and fix it? I am using Centos 5.1, cpu Intel(R) Xeon(R) CPU and 1 Gb of RAM. my.cnf is as follows: [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock back_log = 50 max_connections = 100 max_connect_errors = 10 table_cache = 1024 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 128M sort_buffer_size = 8M join_buffer_size = 3M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default_table_type = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log_slow_queries = /var/log/mysqld/slow-query-log long_query_time = 5 log_long_format tmpdir = /tmp log_queries_not_using_indexes = /var/log/mysqld/not-indexes.log expire_logs_days = 2 server-id = 1 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover skip-bdb innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 600M innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192 EOF Thanks in advance! -- -- Open Kairos http://www.openkairos.com Watch More TV http://sebelk.blogspot.com Sergio Belkin - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database cache /
Thanks for the leads. I'll double check my indices and check out the following links. > http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html > http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database cache /
At 2:45 PM +1000 7/3/07, Daniel Kasak wrote: On Mon, 2007-07-02 at 21:19 -0700, Ed Lazor wrote: I have a 400mb database. The first query to tables takes about 90 seconds. Additional queries take about 5 seconds. I wait a while and run a query again; it takes about 90 seconds for the first one and the rest go quickly. I'm guessing data is being loaded into memory which is why things speed up. Does this sound right? Is there a way to keep the table in memory? Nothing is changing in the data. You probably have the query cache enabled already if it's behaving like this. See: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html The same behavior might be observed without the query cache being enabled. The key buffer caches MyISAM index blocks, filesystem caching is used for data blocks, etc. The query cache only works for the current connection, so if you open a connection and execute a query, it's only cached for *that* connection. It's not cached only for that connection, actually. But you should get MUCH better performance than what you're currently getting anyway. You need to look at your queries, and put indexes on appropriate fields. Generally you want them on fields used in joins, and fields in your 'where' clause. Always a good idea. Also, you might want to increase your server buffer sizes as appropriate. See, for example: http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database cache /
On Mon, 2007-07-02 at 21:19 -0700, Ed Lazor wrote: > I have a 400mb database. The first query to tables takes about 90 seconds. > Additional queries take about 5 seconds. I wait a while and run a query > again; it takes about 90 seconds for the first one and the rest go quickly. > I'm guessing data is being loaded into memory which is why things speed up. > > Does this sound right? Is there a way to keep the table in memory? Nothing > is changing in the data. You probably have the query cache enabled already if it's behaving like this. See: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html The query cache only works for the current connection, so if you open a connection and execute a query, it's only cached for *that* connection. But you should get MUCH better performance than what you're currently getting anyway. You need to look at your queries, and put indexes on appropriate fields. Generally you want them on fields used in joins, and fields in your 'where' clause. If you post your query and the output of 'show create table ' for each table involved, someone might suggest which fields to add indexes to. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database cache /
I have a 400mb database. The first query to tables takes about 90 seconds. Additional queries take about 5 seconds. I wait a while and run a query again; it takes about 90 seconds for the first one and the rest go quickly. I'm guessing data is being loaded into memory which is why things speed up. Does this sound right? Is there a way to keep the table in memory? Nothing is changing in the data. Thanks, -Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
