MySQL hangs unexpedtedly
Hi. These last days, after some months of running relatively smoothly, I've been having some trouble with MySQL. Sometimes (yesterday 4 times) it just goes astray and virtually hangs. Load goes up to +200%, all connections are occupied and it just doesn't do anything. The only way to stop it is to killall -9 mysql because a mysqladmin shutdown won't do. Looking though the error file I get this: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, [...] key_buffer_size=402649088 record_buffer=8384512 sort_buffer=33554424 max_used_connections=352 max_connections=400 threads_connected=287 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 4192696 K bytes of memory Hope that's ok, if not, decrease some variables in the equation (Is it ok? Box is a Dual 1Ghz, 1Gb ram, Linux 2.4.16 dedicated only to MySQL) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x806ede4 0x8120148 0x814ba77 0x80a147f 0x807561a 0x8073f97 [...] Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=1733 Everytime the box hangs I get the same stack backtrace, I have resolved it and it's: 0x806ede4 pack__12Field_stringPcPCcUi + 20 0x8120148 regatoi + 8 0x814ba6f __printf_fp + 5719 0x80a147f join_read_const__FP13st_join_table + 35 0x807561a push_front__t4List1Z3KeyP3Key + 18 0x8073f97 prepare__13select_exportRt4List1Z4Item + 443 ...but when I try to find the thread that caused it (1733 in this case) in the binary log it doesn't appear so I'm unable to determine what's causing the error. I thought at first that it could be a load peak issue, but this error is from today at 6am, and this isn't a busy hour. Everytime I get this i run myisamchk -r *.MYI. It seems that after some time (+30 minutes usually) mysql restarts and starts running again. Any thoughts on this? Anything I can do to resolve it? Thanks. -- L - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL hangs unexpedtedly
Hardware? Operating System and version? MySql version? Precompiled, you compiled? Which compiler? Intel Dual 1Ghz, 1Gb RAM, RedHat Linux 6.2, kernel 2.4.16, MySQL 3.23.52 from rpms (happened the same with 3.23.46). As I said before, this only happens the last few days, we were running for almost a year without much problems. Anyone can explain/understand the stack trace? Cheers -- L [EMAIL PROTECTED] wrote: Hi. These last days, after some months of running relatively smoothly, I've been having some trouble with MySQL. Sometimes (yesterday 4 times) it just goes astray and virtually hangs. Load goes up to +200%, all connections are occupied and it just doesn't do anything. The only way to stop it is to killall -9 mysql because a mysqladmin shutdown won't do. Looking though the error file I get this: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, [...] key_buffer_size=402649088 record_buffer=8384512 sort_buffer=33554424 max_used_connections=352 max_connections=400 threads_connected=287 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 4192696 K bytes of memory Hope that's ok, if not, decrease some variables in the equation (Is it ok? Box is a Dual 1Ghz, 1Gb ram, Linux 2.4.16 dedicated only to MySQL) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x806ede4 0x8120148 0x814ba77 0x80a147f 0x807561a 0x8073f97 [...] Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at (nil) is invalid pointer thd-thread_id=1733 Everytime the box hangs I get the same stack backtrace, I have resolved it and it's: 0x806ede4 pack__12Field_stringPcPCcUi + 20 0x8120148 regatoi + 8 0x814ba6f __printf_fp + 5719 0x80a147f join_read_const__FP13st_join_table + 35 0x807561a push_front__t4List1Z3KeyP3Key + 18 0x8073f97 prepare__13select_exportRt4List1Z4Item + 443 ...but when I try to find the thread that caused it (1733 in this case) in the binary log it doesn't appear so I'm unable to determine what's causing the error. I thought at first that it could be a load peak issue, but this error is from today at 6am, and this isn't a busy hour. Everytime I get this i run myisamchk -r *.MYI. It seems that after some time (+30 minutes usually) mysql restarts and starts running again. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL over NFS
Also, what we have done is used a replicating box as a select only server. This requires you to write your code in such a way that only selects get sent to the slave. A simple function wrapper in PHP is all we needed. I've been thinking of this idea too but it's a bit of a problem because we open one connection for every page request and it makes from 10 to 100 querys (selects and updates). So it would be either opening and closing the connection for each query (not good I guess) or opening two connections, one to each server, at the beginning and then randomly serving them to both boxes if they're selects or to one of them if they're updates. Anyway if anyone has more insights about this I'd love to hear them. Cheers. -- L - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to access MySql using Perl. Help
I am new to MySql. I have MySql installed in my Linux PC. How do I connect
to MySql using Perl script and assign the result of a query to an array?
Where could I find a sample code? Thank you.
man DBD::mysql
Cheers.
--
Luis Calero Muñoz
$email{luis} = '[EMAIL PROTECTED]'
$who{luis} = 'sysadm at ociojoven dot com'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.32 way faster than 3.23.46
Hi. I've been testing some querys with this two versions of mysql and found weird results. First we try with 3.23.32: (foo)-/usr/local/mysql_3.23.32/bin# ./mysql -uroot publish Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 to server version: 3.23.32-log mysql SELECT DISTINCT Article.ID as ArticleID FROM eZArticle_Article AS Article, eZArticle_ArticleCategoryLink as Link, eZArticle_ArticlePermission AS Permission, eZArticle_Category AS Category WHERE ( ( ( ( ( Permission.GroupID='-1' ) AND Permission.ReadPermission='1') ) AND ( Permission.GroupID='-1') AND Permission.ReadPermission='1' ) ) AND Article.IsPublished = 'true' AND Permission.ObjectID=Article.ID AND Link.ArticleID=Article.ID AND Category.ID=Link.CategoryID AND Category.ExcludeFromSearch = 'false' ORDER BY Article.Published DESC, Article.Puntos DESC LIMIT 220,10; +---+ | ArticleID | +---+ | 5665 | [...] | 7311 | +---+ 10 rows in set (54.19 sec) And then with 3.23.46: (foo)-/usr/local/mysql_3.23.46/bin# mysql -uroot publish Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.46-log mysql SELECT DISTINCT Article.ID as ArticleID FROM eZArticle_Article AS Article, eZArticle_ArticleCategoryLink as Link, eZArticle_ArticlePermission AS Permission, eZArticle_Category AS Category WHERE ( ( ( ( ( Permission.GroupID='-1' ) AND Permission.ReadPermission='1') ) AND ( Permission.GroupID='-1') AND Permission.ReadPermission='1' ) ) AND Article.IsPublished = 'true' AND Permission.ObjectID=Article.ID AND Link.ArticleID=Article.ID AND Category.ID=Link.CategoryID AND Category.ExcludeFromSearch = 'false' ORDER BY Article.Published DESC, Article.Puntos DESC LIMIT 220,10; +---+ | ArticleID | +---+ | 5665 | [...] | 7311 | +---+ 10 rows in set (1 min 26.06 sec) Almost 30 seconds more with the same query!! We've found out that the main problem are the Permission checks in the WHERE clause, because taking them out makes the query execution down to 0.30 secs, but why the older version takes much less time? (I've tried this with 3.23.42 with the same results). Any way one of the tables has around 1 elements and the other ones around 5000, isn't 1 minute too much time? Any hints to optmize the query? FYI it's running on a dedicated Dual 800Mhz, Linux server w/ 1Gb RAM. Thanks in advance. Cheers. -- Luis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Database system architecture
Hi. We're in the process of making a database driven website for a client and I'd like to clear one doubt. There are going to be two frontend machines w/ apache on linux and one shared NAS on the back for the all data (possibly a Netapp box). My question is: is it a good idea to have the database files on a NAS mounted partition on each frontend and a MySQL server on each too? This way both servers are going ot access the same set of data, but I was fearing of file locking errors or something like that. BTW, DB use on inserts/updates is not going to be very heavy. If this isn't a good idea... any insights about how to make this setup to be optimal? Thanks. -- Luis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication implementation.
can anybody please desc
from the scratch (user rights)
of configuring 2 mysql servers in
master/slave fashion for replicatiom.
http://www.mysql.com/doc/R/e/Replication.html
Not that difficult to find...
--
Luis Calero Muñoz
$email{luis} = '[EMAIL PROTECTED]'
$who{luis} = 'sysadm at ociojoven dot com'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AW: MySQL is hogging my box
I assume you've done the slow-query-log review thing... making sure all your
queries are fast, yes?
Yes, you're right. After I did the myisamchk, the box has been more or
less stable with loads from 10 to 15. Still seems a lot ot me. I've
checked slow-query-log and only seemed to have one slow query every 4 or 5
minutes.
--
Luis Calero Muñoz
$email{luis} = '[EMAIL PROTECTED]'
$who{luis} = 'sysadm at ociojoven dot com'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL is hogging my box
Hi all, this is my first question on this list and I hope that you can help me. I've got one dedicated box for MySQL and it's going downhill just by starting the DB. The box is a dual PIII 800Mhz, 1 Ghz RAM, Linux RedHat 6.2 w/ kernel 2.4.6SMP, SCSI Drives and plugged to a 100Mb internal network. MySQL is latest rpm version (3.23.42). There are 3 frontends (apache 1.3.20 + php4 / running ezPublish) shooting questions at this box. This evening it has started to raise the load of the machine (even over 50) and it's going useless. Here is the main configuration of MySQL: /etc/my.cnf [mysqld] log-bin server-id=1 skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 set-variable= wait_timeout=300 set-variable= max_connect_errors=1000 set-variable= max_connections=300 log-slow From SHOW VARIABLES | max_allowed_packet | 1047552 | max_connections | 300 | max_connect_errors | 1000 | max_delayed_threads | 20 | max_heap_table_size | 16777216 | max_join_size | 4294967295 | max_sort_length | 1024 | max_user_connections | 0 | max_tmp_tables | 32 | max_write_lock_count | 4294967295 | myisam_sort_buffer_size | 67108864 | net_buffer_length | 16384 | record_buffer | 1044480 | record_rnd_buffer | 1044480 | query_buffer_size | 0 | sort_buffer | 1048568 | table_cache | 256 | table_type | MYISAM | thread_cache_size | 8 | thread_stack | 65536 | tmp_table_size | 33554432 | version | 3.23.42-log | wait_timeout | 300 From SHOW extended-status +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 11| | Aborted_connects | 0 | | Bytes_received | 3276773 | | Bytes_sent | 12091463 | | Connections | 508 | | Created_tmp_disk_tables | 21| | Created_tmp_tables | 1187 | | Created_tmp_files| 32| | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_delete | 0 | | Handler_read_first | 149 | | Handler_read_key | 546266| | Handler_read_next| 140968| | Handler_read_prev| 80| | Handler_read_rnd | 24511 | | Handler_read_rnd_next| 536386110 | | Handler_update | 202796| | Handler_write| 204321| | Key_blocks_used | 845 | | Key_read_requests| 310986| | Key_reads| 834 | | Key_write_requests | 1214 | | Key_writes | 993 | | Max_used_connections | 174 | | Not_flushed_key_blocks | 6 | | Not_flushed_delayed_rows | 0 | | Open_tables | 256 | | Open_files | 325 | | Open_streams | 0 | | Opened_tables| 340 | | Questions| 23070 | | Select_full_join | 1533 | | Select_full_range_join | 0 | | Select_range | 366 | | Select_range_check | 0 | | Select_scan | 10150 | | Slave_running| OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 1 | | Slow_queries | 16| | Sort_merge_passes| 16| | Sort_range | 328 | | Sort_rows| 2566631 | | Sort_scan| 1625 | | Table_locks_immediate| 21913 | | Table_locks_waited | 3026 | | Threads_cached | 0 | | Threads_created | 175 | | Threads_connected| 175 | | Threads_running | 100 | | Uptime | 417 | +--+---+ After 10 minutes running this is the output of top and vmstat 1: 6:54pm up 2:04, 5 users, load average: 51.63, 42.91, 36.94 273 processes: 236 sleeping, 37 running, 0 zombie, 0 stopped CPU states: 59.3% user, 40.6% system, 0.0% nice, 0.0% idle Mem: 1028968K av, 313836K used, 715132K free, 0K shrd, 13408K buff Swap: 2064376K av, 0K used, 2064376K free 227364K cached PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND 5089 mysql 15 0 66872 65M 1584 S 0 3.5 6.4 0:01 mysqld 4926 mysql 13 0 40796 39M 1584 S 0 3.3 3.9 0:06 mysqld 5046 mysql 20 0 88076 86M 1584 R 0 3.3 8.5 0:01 mysqld 4906 mysql 20 0 74476 72M 1584 R 0 3.2 7.2 0:07 mysqld 4919 mysql 11 0 40672 39M 1584 S 0 3.2 3.9
Re: AW: MySQL is hogging my box
There are some others with a similar problem(including me). The question
still remain,
if it is all caused by the same bug(?).
What table-types do you use? (MyISAM,Merge,BDB,...)
MyISAM
How many entries are in the tables?
Up to 145000 in some of the tables. But its not the usual stuff...
normal tables have around 15000 entries.
How many queries/second?
Based on the numbers on my system right now:
| Questions| 526358 |
| Uptime | 2910 |
That gives like 180 queries/sec, right? Isn't it a real big number???
Do the queries use indices?
Yes they do...
Ok, after asking I've run myisamchk on the tables and then after
restarting it has started to go better. Not 50 load but 12, and slowly
rising.
Besides when I look at the processlist I see many (5 to 10 at a time)
processes Locked for around 4 or 5 seconds. Any insights on this?
Some guy has given me an insight about installing an older version of
MySQL. All my problems have been with 3.23.38 and 3.23.42. I'll try the
older version and would tell you what happens.
Cheers.
--
Luis Calero Muñoz
$email{luis} = '[EMAIL PROTECTED]'
$who{luis} = 'sysadm at ociojoven dot com'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
