Max connections being used every 10-12 day.
Hi list, I have a rather serious problem that i really dont know how to solve, Every 8-12 day my mysql server stops responding and i get the error code that indicates that max_connections are full, the problem is that i have checked all of my code over and over again to se that every connections are closed properly and they are. No persistent connections are being used and the max connections error allways occures at night 02:00-03:00, my httpd logs shows no unnormal amount of traffic at the time. The last time this happend i tuned the wait_timeout down to 15 seconds to se if that helped, but no effect :( The server is running NetBSD 1.6.2 and mysql 4.0.21 I really need help on this one because i dont know what is causing max_connections to be used all at once or how to reproduce the error, i only know that it happens very periodicly and 'show full processlist' hardly ever shows any connections not even the day/hours before the error. The server has about 4-5 queries / seconds. According to the manual the max_connections have one connection reserved for the superuser but i have never been able to use that extra connection to se which user that is eating upp all the connections. // Fredrik Carlsson # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock skip-locking key_buffer = 280M max_allowed_packet = 32M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections = 200 wait_timeout = 15 connect_timeout = 5 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
Hi there a are a couple of things that I have found cause occasional lock ups. running out of temp space - MySql builds temp files on bigger queries and if it runs out of temp disk space it grinds to a halt, which causes all the following queries to queue up until max_connections is exceeded. the second cause is one massive query - big tables, lots of joins etc - that takes so much processing power that there is nothing left. MySql and perhaps the server slows down to the point that it cannot process any more queries, and again the queue builds until max_connections is exceeded. if the lockup occurs overnight this might be the time the server is doing housekeeping - apache log analysis for example - so it may be the server is loaded too heavily to manage MySql queries too, so again the queue builds up. Just a few thoughts - hope it helps. Peter -Original Message- From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] Sent: 01 January 2005 10:37 To: mysql@lists.mysql.com Subject: Max connections being used every 10-12 day. Hi list, I have a rather serious problem that i really dont know how to solve, Every 8-12 day my mysql server stops responding and i get the error code that indicates that max_connections are full, the problem is that i have checked all of my code over and over again to se that every connections are closed properly and they are. No persistent connections are being used and the max connections error allways occures at night 02:00-03:00, my httpd logs shows no unnormal amount of traffic at the time. The last time this happend i tuned the wait_timeout down to 15 seconds to se if that helped, but no effect :( The server is running NetBSD 1.6.2 and mysql 4.0.21 I really need help on this one because i dont know what is causing max_connections to be used all at once or how to reproduce the error, i only know that it happens very periodicly and 'show full processlist' hardly ever shows any connections not even the day/hours before the error. The server has about 4-5 queries / seconds. According to the manual the max_connections have one connection reserved for the superuser but i have never been able to use that extra connection to se which user that is eating upp all the connections. // Fredrik Carlsson # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock skip-locking key_buffer = 280M max_allowed_packet = 32M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections = 200 wait_timeout = 15 connect_timeout = 5 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
mysql has about 50GB of temp space to work with so thats not the problem. Its strange because the server is not that loaded and around 12-04 at night no cronjobs that affects mysql or general server performance are being run. Is is possible to se how many queued up questions mysql has at the moment? Peter Lovatt wrote: Hi there a are a couple of things that I have found cause occasional lock ups. running out of temp space - MySql builds temp files on bigger queries and if it runs out of temp disk space it grinds to a halt, which causes all the following queries to queue up until max_connections is exceeded. the second cause is one massive query - big tables, lots of joins etc - that takes so much processing power that there is nothing left. MySql and perhaps the server slows down to the point that it cannot process any more queries, and again the queue builds until max_connections is exceeded. if the lockup occurs overnight this might be the time the server is doing housekeeping - apache log analysis for example - so it may be the server is loaded too heavily to manage MySql queries too, so again the queue builds up. Just a few thoughts - hope it helps. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to determine a field is part of the primary key in a table?
Hi list, I m writing a perl program and would like to use it (wiht mysql command) to determine whether a field name is (or is part of) a primary key of a table. If you know how to handle this, please drop me a line. Thanks and Happy New Year. Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update a Field in a table to Uppercase
Does anyone have a sample of a simple update statement I can run to update a field in a table to all uppercase values. Any help or web references are appreciated.
Re: How to determine a field is part of the primary key in a table?
Sam For columns in a statement $sth, DBD:mysql maintains a boolean array named is_pri_key. PB - - Original Message - From: sam wun To: mysql@lists.mysql.com Sent: Saturday, January 01, 2005 7:58 AM Subject: How to determine a field is part of the primary key in a table? Hi list, I m writing a perl program and would like to use it (wiht mysql command) to determine whether a field name is (or is part of) a primary key of a table. If you know how to handle this, please drop me a line. Thanks and Happy New Year. Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: use of soundex in queries
Raphael Matthias Krug wrote:
Hi Shawn,
I printed the manual, but as I am not such a database guru I was not
able to transfer this knowlegde into an select-statement. Thanks for
your realtimehelp.
Is it possible to do an select-query with soundex like the following
examble?
select nn from table where nn !=''
my database has the field name (nn), I need to select every
namefield, which is not empty. So soundex should give me an result
with a list of names. How to do?
Thanks
Raphael
Am 31 Dec 2004 um 11:00 hat [EMAIL PROTECTED] geschrieben:
Did you think to check the manual?
English:
http://dev.mysql.com/doc/mysql/en/String_functions.html
German
http://dev.mysql.com/doc/mysql/de/String_functions.html
French:
http://dev.mysql.com/doc/mysql/fr/String_functions.html
and your query would look like
SELECT ...
FROM ...
WHERE SOUNDEX(field1) = SOUNDEX(field2)
because you want to compare the soundof both fields to see if they
are nearly the same.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Raphael Matthias Krug [EMAIL PROTECTED]wrote on 12/31/2004
10:46:06 AM:
Hi,
I need to compare names from different tables and therefore I need
to know the proper use of soundex. I googled for it, but could not
find
anything useful. And select soundex('text') is no help for me.
The query should look like this:
select field from table where field like [soundexquery inserted]
As result there should be a list with the names. The comparison of
the table can also be done with php, so mainly it would be great to
know how to do resultqueries as described.
Thanks for help! Happy new year
Raphael
Raphael:
I am not sure I am quite understanding what you are trying to do, but from what
I've read it looks like you want to query the names of the fields rather than
their values. MySQL does not store field names in tables, and this makes
impossible to use any SQL functions on field names, only on the values they contain.
The best way to acccomplish this is to programmatically (eg. from PHP) execute
SHOW FIELDS FROM tbl_name, and then post-process the output.
P.S. I have a theory that a habit of printing computer documentation is a road
block to becoming a guru. At least, I have not yet encountered a guru that
printed much, while at the same time it seems like a struggling user prints a
lot. You cannot be 100% sure about the cause and effect relationship, though,
but trying to go printless might activate something that speeds up skill
acquisition.
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] phpMyAdmin w/ winXP - IIS w/PHP 4.3 w/mysql 4.1.8
It would be nice if phpMyAdmin would kindly note that on their website... Also, when I run a phpInfo()... it says i have the 3.23.49 could this be a contributing factor? On Sat, 1 Jan 2005 15:55:27 +0700, Willy Sudiarto Raharjo [EMAIL PROTECTED] wrote: Has anyone had any problems installing phpMyAdmin with the above configuration? I get an error about the mySql client and authentication methods? MySQL Error: 1251 : Client does not support authentication protocol requested by server MySQL 4.1.x is using a different authentication protocols so it may break phpmyadmin functionality. Use 4.0.x if you want to use phpmyadmin clearly or maybe you should wait for the next release -- Willy Sudiarto Raharjo Registered Linux User : 336579 Public-key : http://www.informatix.or.id/willy/public-key.txt Blog : http://willysr.blogspot.com OOo Documentation Project (ID) : http://project.informatix.or.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [PHP] phpMyAdmin w/ winXP - IIS w/PHP 4.3 w/mysql 4.1.8
The problem is not with phpmyadmin, the problem is with php. If you install 4.3 of php it will not work with mysql 4.1.8 or any version mysql 4.1 or 5.0. It will only work if you turn on the short passwords option in 4.1. I've not tried it on 5.0 lately. You can get it installed but it takes a little work. This is not a phpmyadmin bug it's all, it's not really a php, and it's not a mysql bug. I reported it to both phpmyadmin and php.net over a year ago. Think this is a problem, wait until you get a $40k 64 bit machine and try to install php on it via source because you want to use php 4.3 and mysql 4.1 and you find out you can't install anything because 64 bit installs stuff in different places than php is expecting it. And the php devel team has no plans on fix it. So you have to hack the config script to get it to work. Donny -Original Message- From: GH [mailto:[EMAIL PROTECTED] Sent: Saturday, January 01, 2005 1:15 PM To: Willy Sudiarto Raharjo; php-general; mysql@lists.mysql.com Subject: Re: [PHP] phpMyAdmin w/ winXP - IIS w/PHP 4.3 w/mysql 4.1.8 It would be nice if phpMyAdmin would kindly note that on their website... Also, when I run a phpInfo()... it says i have the 3.23.49 could this be a contributing factor? On Sat, 1 Jan 2005 15:55:27 +0700, Willy Sudiarto Raharjo [EMAIL PROTECTED] wrote: Has anyone had any problems installing phpMyAdmin with the above configuration? I get an error about the mySql client and authentication methods? MySQL Error: 1251 : Client does not support authentication protocol requested by server MySQL 4.1.x is using a different authentication protocols so it may break phpmyadmin functionality. Use 4.0.x if you want to use phpmyadmin clearly or maybe you should wait for the next release -- Willy Sudiarto Raharjo Registered Linux User : 336579 Public-key : http://www.informatix.or.id/willy/public-key.txt Blog : http://willysr.blogspot.com OOo Documentation Project (ID) : http://project.informatix.or.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
What kind of box is this? According to you're my.cnf it looks like it's a either a dual with hyperthreading or a quad box. I don't see that you have your slow query log turned on, this should be the first thing you should do in my opinion. This is what mine looks like. ### Slow Query Information ### log-long-format log-slow-queries log-queries-not-using-indexes set-variable= long_query_time=3 Then go in and fix all of those that are showing up in the slow query log. With 4-5 queries per second, you should NEVER fill up the 200 connections unless you just have some awful queries or you have some tables that are getting corrupted and are being repaired during that time. Donny -Original Message- From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] Sent: Saturday, January 01, 2005 4:37 AM To: mysql@lists.mysql.com Subject: Max connections being used every 10-12 day. Hi list, I have a rather serious problem that i really dont know how to solve, Every 8-12 day my mysql server stops responding and i get the error code that indicates that max_connections are full, the problem is that i have checked all of my code over and over again to se that every connections are closed properly and they are. No persistent connections are being used and the max connections error allways occures at night 02:00-03:00, my httpd logs shows no unnormal amount of traffic at the time. The last time this happend i tuned the wait_timeout down to 15 seconds to se if that helped, but no effect :( The server is running NetBSD 1.6.2 and mysql 4.0.21 I really need help on this one because i dont know what is causing max_connections to be used all at once or how to reproduce the error, i only know that it happens very periodicly and 'show full processlist' hardly ever shows any connections not even the day/hours before the error. The server has about 4-5 queries / seconds. According to the manual the max_connections have one connection reserved for the superuser but i have never been able to use that extra connection to se which user that is eating upp all the connections. // Fredrik Carlsson # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock skip-locking key_buffer = 280M max_allowed_packet = 32M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections = 200 wait_timeout = 15 connect_timeout = 5 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: use of soundex in queries
Sasha P.S. I have a theory that a habit of printing computer documentation is a road block to becoming a guru. At least, I have not yet encountered a guru that printed much, while at the same time it seems like a struggling user prints a lot. You cannot be 100% sure about the cause and effect relationship, though, but trying to go printless might activate something that speeds up skill acquisition. I just printed the soundex-parts. This was ten lines :-). For understanding my problem, see the text below. Shawn Sasha I am working with medieval sources, so called taxbooks. They contain names, taxamounts and other administrative entries. For my research I took nine of these taxbooks. One of my aims is to find out, if many taxpayers died or moved or simply stayed, e.g. with diseases. For this purpose, I inserted every taxbook in one table. To compare the persons in this book, a friend created a php-script/file which takes from one book the names and compares them with the other books using right now a normal select-statement. The result is on the left a name and then as a table for each taxbook a row and if the name appears a 1. From a colleague I heard now about soundex. This would make it easier to compare these odd-writen names (so to say; the same person can appear as Myer, Mair, Meyer etc.). Therefore I asked how to implement this into a select-statement. Do you see any better ideas doing this, then with a php-file? The script right now has the disadvantage, that it does not check, if the names show up in the order. But I think, this could be fixed with an additional select-statement in the php-file. Sorry, it got a bit long. Thanks in advance Raphael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
correcting the booklet
Dear Sir, I have written a paper on Mysql http://en.wikibooks.org/wiki/Programming:MySQL Will you please go through it and make corrections where ever necessary? Shantanu Oak [EMAIL PROTECTED] http://oksoft.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master will insist on running as a SLAVE if data/master.info exists.
Mikael Fridh wrote: Kevin A. Burton wrote: This is a bug. Feature. Putting system configuration information on a unix machine in /var.. .yeah... thats not a feature. In fact, you don't need those configuration statements in the first place. You could just as well initiated the slave replication by issuing CHANGE MASTER TO... Yes... I realize Slave is always started unless my.cnf says skip-slave-start. Anyway, WITH skip-slave-start you will still have the slave information (binlog positions etc.) initiated but it will just not start replicating. The information in master.info overrides anything in my.cnf. Again... and this file is in /var... Whats the point of /etc/my.cnf... why not just store everything in /var? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
