Re: Problems with: MySQL 5.0.21 - 64bit
Spoken too soon... Yesterday MySQL died again... there is nothing in the log files... (be it mysqld.log, .err, or /var/log/messages). The hosting provider is running an application called 'big-brother' and a lot of sef-faults appear in the logs from this. I don't know if thins is the cause... if this application is faulting mysql also... The behaviour: the whole machine freezes... all that can be done is to hit the RESET button on the machine ! Friday we downgraded from 5.0 to 4.1. At first we compiled the MySQL server ourselvs... next we deleted it and installed a RPM version... none worked... It appears we hit bug 15815... (http://bugs.mysql.com/bug.php?id=15815)... So be aware with innodb_thread_concurency on 64bit machines... Below are some values as an orientation... for innodb_thread_concurency value per machine workload (at arround 600 simultaneous connections... all used): innodb_thread_concurency / CPU-workload 8 / 90% 4 / 75 - 85% 3 / 60 - 70% 2 / 25% So set it lower... lower... lower... when I left the office friday my colleagues were still working at this... I'll keep you all updated ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with: MySQL 5.0.21 - 64bit
Hi Gabriel, Yesterday MySQL died again... there is nothing in the log files... (be it mysqld.log, .err, or /var/log/messages). The hosting provider is running an application called 'big-brother' and a lot of sef-faults appear in the logs from this. I don't know if thins is the cause... if this application is faulting mysql also... The behaviour: the whole machine freezes... all that can be done is to hit the RESET button on the machine ! If the whole machine freezes, this sounds like bad memory, bad cpu, or similar, rather than a MySQL problem. Regardless of how poorly an application could be written, it should not be possible for it to freeze the machine. Regards, Jeremy -- high performance mysql consulting www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with: MySQL 5.0.21 - 64bit
Yup... For now the problem stopped... These humongos values were because they were initialized at MAX_INT on that 64bit machine... In the my.cnf file they were not mentioned at all ! 2^64 - 1 == 18446744073709551615 Now look at the values below ! -- Gabriel PREDA Senior Web Developer On 6/22/06, Dan Buettner [EMAIL PROTECTED] wrote: Gabriel, in your SHOW VARIABLES, I see a handful of settings that are much, much larger than normal: | max_binlog_cache_size | 18446744073709551615 | max_join_size | 18446744073709551615 | max_seeks_for_key | 18446744073709551615 | max_write_lock_count| 18446744073709551615 | myisam_max_sort_file_size | 9223372036854775807 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with: MySQL 5.0.21 - 64bit
Hi list, Since we bought a better hardware for our dedicated MySQL Server we have been running into some problems. We are using: - Fedora Core 3 - 64bit version - Kernel: 2.6.9-1.667smp - x86_64 - MySQL 5.0.21-standard - for 64bit - RAM: 4 GB - RAID 5 matrix with 3 SCSI disks at 15k rotations We are using InnoDB tables (with one or 2 exceptions... for some FullText indexes)... We are not using transactions... yet ! I'll drop config. details lower... Still at given moments MySQL hangs... it does not accept connections anymore... We can't kill the process... with KILL command... the only thing we can do is ask the hosting provider to do a HARDWARE reset... and someone goes to the machine and pushes the reset button... this hppens at least once a week... Does this happened to someone else ? What was the problem ? Thanx in advance ! -- Gabriel PREDA Senior Web Developer - --- CONFIG DATA -- - - PROCESORS from 0 to 7: processor : 0 (up to processor 7 the specs. are the same) vendor_id : GenuineIntel cpu family : 15 model : 4 model name : Intel(R) Xeon(TM) CPU 2.80GHz stepping : 8 cpu MHz : 2793.261 cache size : 16 KB physical id : 0 siblings : 4 fpu : yes fpu_exception : yes cpuid level : 5 wp : yes flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm pni monitor ds_cpl est cid cx16 xtpr ts bogomips : 5537.79 clflush size : 64 cache_alignment : 128 address sizes : 36 bits physical, 48 bits virtual TOP output: top - 00:59:44 up 10:14, 1 user, load average: 0.46, 0.54, 0.64 Tasks: 82 total, 1 running, 81 sleeping, 0 stopped, 0 zombie Cpu0 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu1 : 0.3% us, 0.3% sy, 0.0% ni, 99.3% id, 0.0% wa, 0.0% hi, 0.0% si Cpu2 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu4 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu5 : 6.3% us, 1.7% sy, 0.0% ni, 91.4% id, 0.0% wa, 0.3% hi, 0.3% si Cpu6 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Cpu7 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 4038412k total, 1614864k used, 242 Mem: 4038412k total, 1617176k used, 2421236k free, 135060k buffers Swap: 2048276k total,0k used, 2048276k free, 367664k cached MY.CNF: [mysqld] set-variable = max_connections=900 safe-show-database set-variable = max_heap_table_size=64M set-variable = tmp_table_size=64M set-variable = query-cache-size=500M set-variable = query_cache_limit=30M set-variable = long_query_time=3 set-variable = table_cache=600 set-variable = thread_cache_size=32 set-variable = thread_concurrency=8 set-variable = key_buffer_size=32M set-variable = interactive_timeout=60 set-variable = wait_timeout=60 set-variable = max_allowed_packet=3M set-variable = sort_buffer_size=6M set-variable = ft_min_word_len=3 set-variable = binlog_cache_size=0 set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_thread_concurrency=8 set-variable = innodb_buffer_pool_size=400M set-variable = innodb_flush_log_at_trx_commit=0 set-variable = innodb_autoextend_increment=50M set-variable = innodb_fast_shutdown=0 set-variable = innodb_log_buffer_size=4M set-variable = innodb_max_dirty_pages_pct=75 set-variable = innodb_status_file SHOW VARIABLES: +-+---+ | Variable_name | Value | +-+---+ | automatic_sp_privileges | ON | | back_log| 50 | | basedir | / | | binlog_cache_size | 4096 | | bulk_insert_buffer_size | 8388608 | | completion_type | 0 | | concurrent_insert | 1 | | connect_timeout | 5 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4 | | engine_condition_pushdown | OFF | | expire_logs_days| 0 | | flush | OFF | | flush_time | 0 | | group_concat_max_len| 1024 | | have_archive| YES | | have_compress | YES
Re: Problems with: MySQL 5.0.21 - 64bit
Hi, Please let us know what all variables you have configured in your my.cnf files. what is your total size of memory in box. eg : max_connections key_buffer_size sort_buffer_size. Gabriel PREDA wrote: Hi list, Since we bought a better hardware for our dedicated MySQL Server we have been running into some problems. We are using: - Fedora Core 3 - 64bit version - Kernel: 2.6.9-1.667smp - x86_64 - MySQL 5.0.21-standard - for 64bit - RAM: 4 GB - RAID 5 matrix with 3 SCSI disks at 15k rotations We are using InnoDB tables (with one or 2 exceptions... for some FullText indexes)... We are not using transactions... yet ! I'll drop config. details lower... Still at given moments MySQL hangs... it does not accept connections anymore... We can't kill the process... with KILL command... the only thing we can do is ask the hosting provider to do a HARDWARE reset... and someone goes to the machine and pushes the reset button... this hppens at least once a week... Does this happened to someone else ? What was the problem ? Thanx in advance ! -- Thanks Regards, Dilipkumar DBA Support ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch India vs. England LIVE, Hot videos and more only on Sify Max! Click Here. www.sifymax.com Get to see what's happening in your favourite City on Bangalore Live! www.bangalorelive.in -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with: MySQL 5.0.21 - 64bit
Gabriel, in your SHOW VARIABLES, I see a handful of settings that are much, much larger than normal: | max_binlog_cache_size | 18446744073709551615 | max_join_size | 18446744073709551615 | max_seeks_for_key | 18446744073709551615 | max_write_lock_count| 18446744073709551615 | myisam_max_sort_file_size | 9223372036854775807 These values appear insanely large to me, especially given you've got just 4 GB RAM. I'd adjust these down and see what effect that has. Also, I noticed that your threads_created value seems fairly large (2701) for about 10 hours of uptime. You might consider upping your thread_cache size a bit. Doubt this is causing the hangs though. When the MySQL software hangs, other processes on the machine are still responsive? Anything of interest in your system log? You mention this is new hardware ... wonder if you could have a problem with it. Dan Gabriel PREDA wrote: Hi list, Since we bought a better hardware for our dedicated MySQL Server we have been running into some problems. We are using: - Fedora Core 3 - 64bit version - Kernel: 2.6.9-1.667smp - x86_64 - MySQL 5.0.21-standard - for 64bit - RAM: 4 GB - RAID 5 matrix with 3 SCSI disks at 15k rotations We are using InnoDB tables (with one or 2 exceptions... for some FullText indexes)... We are not using transactions... yet ! I'll drop config. details lower... Still at given moments MySQL hangs... it does not accept connections anymore... We can't kill the process... with KILL command... the only thing we can do is ask the hosting provider to do a HARDWARE reset... and someone goes to the machine and pushes the reset button... this hppens at least once a week... Does this happened to someone else ? What was the problem ? Thanx in advance ! -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
