Mysql connections not released
Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: . When running ' show processlist', the amount of processes were increasing the whole time. . From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. . From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo
Re: Mysql connections not released
On Thu, Jul 1, 2010 at 11:58 PM, Machiel Richards machi...@rdc.co.zawrote: Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: · When running ‘ show processlist’, the amount of processes were increasing the whole time. · From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. · From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] My first guess would be a networking problem. If you run ifconfig repeatedly do you notice the number of errors increasing? It would be good to look at both the database server and the web server. Also, until you figure the issue out you should be able to mitigate it by setting the connect timeout to a more aggressive value. -- Rob Wultsch wult...@gmail.com
RE: Mysql connections not released
Hi From a thread found on the net it also suggests that it might be network or DNS related. The connections building up is the following: *** 1346. row *** Id: 903175 User: unauthenticated user Host: ip:51798 db: NULL Command: Connect Time: NULL State: login Info: NULL The servers however only uses ip connections and no DNS related lookups. From what I could find on the web though it seems that there is one session that is unable to authenticate and the authentication process does not complete causing it to get stuck This then causes all other requests to wait for the transaction to complete. Everyone who replied to these threads with the same issue stated that when they kill that one problem connection it brings everything back to normal again, however the problem happens regularly and if not picked up immediately eventually causes an inability to access the database to even kill processes, etc... So what I know now is that the problem is caused by a connection / transaction not completing. However what I need to find out now is why and how to resolve this. Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo From: Rob Wultsch [mailto:wult...@gmail.com] Sent: 02 July 2010 9:42 AM To: Machiel Richards Cc: mysql@lists.mysql.com Subject: Re: Mysql connections not released On Thu, Jul 1, 2010 at 11:58 PM, Machiel Richards machi...@rdc.co.za wrote: Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: . When running ' show processlist', the amount of processes were increasing the whole time. . From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. . From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting Error! Filename not specified. My first guess would be a networking problem. If you run ifconfig repeatedly do you notice the number of errors increasing? It would be good to look at both the database server and the web server. Also, until you figure the issue out you should be able to mitigate it by setting the connect timeout to a more aggressive value. -- Rob Wultsch wult...@gmail.com
Re: Mysql connections not released
A bad assumption I make these days is that everyone has skip resolve off. If that is the case DNS issues will not impact you. Do you have that setting? Do you see errors on the interface? On Fri, Jul 2, 2010 at 1:05 AM, Machiel Richards machi...@rdc.co.za wrote: Hi From a thread found on the net it also suggests that it might be network or DNS related. The connections building up is the following: *** 1346. row *** Id: 903175 User: unauthenticated user Host: ip:51798 db: NULL Command: Connect Time: NULL State: login Info: NULL The servers however only uses ip connections and no DNS related lookups. From what I could find on the web though it seems that there is one session that is unable to authenticate and the authentication process does not complete causing it to get “stuck” This then causes all other requests to wait for the transaction to complete. Everyone who replied to these threads with the same issue stated that when they kill that one problem connection it brings everything back to normal again, however the problem happens regularly and if not picked up immediately eventually causes an inability to access the database to even kill processes, etc... So what I know now is that the problem is caused by a connection / transaction not completing. However what I need to find out now is why and how to resolve this. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] *From:* Rob Wultsch [mailto:wult...@gmail.com] *Sent:* 02 July 2010 9:42 AM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: Mysql connections not released On Thu, Jul 1, 2010 at 11:58 PM, Machiel Richards machi...@rdc.co.za wrote: Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: · When running ‘ show processlist’, the amount of processes were increasing the whole time. · From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. · From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting *Error! Filename not specified.* My first guess would be a networking problem. If you run ifconfig repeatedly do you notice the number of errors increasing? It would be good to look at both the database server and the web server. Also, until you figure the issue out you should be able to mitigate it by setting the connect timeout to a more aggressive value. -- Rob Wultsch wult...@gmail.com -- Rob Wultsch wult...@gmail.com
RE: Mysql connections not released
We do not use that setting at present no specifically because all hosts use ip's to connect and not dns / hostnames. We can't seem to find any errors thus the reason for truggling to pinpoint the exact cause. Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo From: Rob Wultsch [mailto:wult...@gmail.com] Sent: 02 July 2010 10:46 AM To: Machiel Richards Cc: mysql@lists.mysql.com Subject: Re: Mysql connections not released A bad assumption I make these days is that everyone has skip resolve off. If that is the case DNS issues will not impact you. Do you have that setting? Do you see errors on the interface? On Fri, Jul 2, 2010 at 1:05 AM, Machiel Richards machi...@rdc.co.za wrote: Hi From a thread found on the net it also suggests that it might be network or DNS related. The connections building up is the following: *** 1346. row *** Id: 903175 User: unauthenticated user Host: ip:51798 db: NULL Command: Connect Time: NULL State: login Info: NULL The servers however only uses ip connections and no DNS related lookups. From what I could find on the web though it seems that there is one session that is unable to authenticate and the authentication process does not complete causing it to get stuck This then causes all other requests to wait for the transaction to complete. Everyone who replied to these threads with the same issue stated that when they kill that one problem connection it brings everything back to normal again, however the problem happens regularly and if not picked up immediately eventually causes an inability to access the database to even kill processes, etc... So what I know now is that the problem is caused by a connection / transaction not completing. However what I need to find out now is why and how to resolve this. Machiel Richards MySQL DBA Relational Database Consulting RDC_Logo From: Rob Wultsch [mailto:wult...@gmail.com] Sent: 02 July 2010 9:42 AM To: Machiel Richards Cc: mysql@lists.mysql.com Subject: Re: Mysql connections not released On Thu, Jul 1, 2010 at 11:58 PM, Machiel Richards machi...@rdc.co.za wrote: Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: . When running ' show processlist', the amount of processes were increasing the whole time. . From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. . From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting Error! Filename not specified. My first guess would be a networking problem. If you run ifconfig repeatedly do you notice the number of errors increasing? It would be good to look at both the database server and the web server. Also, until you figure the issue out you should be able to mitigate it by setting the connect timeout to a more aggressive value. -- Rob Wultsch wult...@gmail.com -- Rob Wultsch wult...@gmail.com
Re: Mysql connections not released
I do not know that part of the mysql source code well, but I am very sure you will take the dns hit even if all of your grants are ip based. I suggest you put put that setting into your cnf and bounce the instance. You will also never run into issues with max connect error if you set skip name resolve. It might be worthwhile to start watching a continuous ping from the webserver to the db server. Are you losing any packets? On Fri, Jul 2, 2010 at 1:48 AM, Machiel Richards machi...@rdc.co.za wrote: We do not use that setting at present no specifically because all hosts use ip’s to connect and not dns / hostnames. We can’t seem to find any errors thus the reason for truggling to pinpoint the exact cause. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] *From:* Rob Wultsch [mailto:wult...@gmail.com] *Sent:* 02 July 2010 10:46 AM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: Mysql connections not released A bad assumption I make these days is that everyone has skip resolve off. If that is the case DNS issues will not impact you. Do you have that setting? Do you see errors on the interface? On Fri, Jul 2, 2010 at 1:05 AM, Machiel Richards machi...@rdc.co.za wrote: Hi From a thread found on the net it also suggests that it might be network or DNS related. The connections building up is the following: *** 1346. row *** Id: 903175 User: unauthenticated user Host: ip:51798 db: NULL Command: Connect Time: NULL State: login Info: NULL The servers however only uses ip connections and no DNS related lookups. From what I could find on the web though it seems that there is one session that is unable to authenticate and the authentication process does not complete causing it to get “stuck” This then causes all other requests to wait for the transaction to complete. Everyone who replied to these threads with the same issue stated that when they kill that one problem connection it brings everything back to normal again, however the problem happens regularly and if not picked up immediately eventually causes an inability to access the database to even kill processes, etc... So what I know now is that the problem is caused by a connection / transaction not completing. However what I need to find out now is why and how to resolve this. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] *From:* Rob Wultsch [mailto:wult...@gmail.com] *Sent:* 02 July 2010 9:42 AM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: Mysql connections not released On Thu, Jul 1, 2010 at 11:58 PM, Machiel Richards machi...@rdc.co.za wrote: Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: · When running ‘ show processlist’, the amount of processes were increasing the whole time. · From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. · From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting *Error! Filename not specified.* My first guess would be a networking problem. If you run ifconfig repeatedly do you notice the number of errors increasing? It would be good to look at both the database server and the web server. Also, until you figure the issue out you should be able to mitigate it by setting the connect timeout to a more aggressive value. -- Rob Wultsch wult...@gmail.com -- Rob Wultsch wult...@gmail.com -- Rob Wultsch wult...@gmail.com
Re: Slow mysql connections
Do you have any other services running on the server, such as mail or
web? How long does it take to connect to those services ('telnet
server 25', or telnet server 80')?
What are you using to connect to mysql? how long does 'telnet
server 3306' take (assuming you are on the default port and have
firewall access)? How long does it take to connect from a local
server session?
When you are doing the above tests, do them with the server name and
with the IP address.
The above should let you rule out your client, and any server related
issues such as DNS or firewall. So you will know if it is a mysql
problem or not.
cheers,
Doug
On 13 Aug 2008, at 05:39, Krishna Chandra Prajapati wrote:
Hi all,
Connecting to mysql server (Production) is taking 5 to 6 seconds.
Production
has 16Gb ram. Previously it was using only 6GB ram. The details are as
follows. DNS looks fine.
free -m
total used free sharedbuffers
cached
Mem: 15899 15877 21 0 97
13913
-/+ buffers/cache: 1865 14033
Swap:0 0 0
---
vmstat
procs ---memory-- ---swap-- -io -system--
cpu
r b swpd free buff cache si sobibo in cs us
sy id
wa
0 0 0 26792 100600 142428680089 15000
7 1
92 0
1
-
cat /proc/meminfo
MemTotal: 16281204 kB
MemFree: 26692 kB
Buffers:100916 kB
Cached: 14243248 kB
SwapCached: 0 kB
Active:8338968 kB
Inactive: 7679900 kB
HighTotal: 0 kB
HighFree:0 kB
LowTotal: 16281204 kB
LowFree: 26692 kB
SwapTotal: 0 kB
SwapFree:0 kB
Dirty: 224 kB
Writeback: 0 kB
AnonPages: 1674640 kB
Mapped: 11616 kB
Slab: 215140 kB
PageTables: 4232 kB
NFS_Unstable:0 kB
Bounce: 0 kB
CommitLimit: 8140600 kB
Committed_AS: 2182136 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 252 kB
VmallocChunk: 34359738043 kB
--
ps aux (The below process is only process using cpu and memory)
mysql 3963 10.6 10.2 2190136 1671816 ? Sl Mar28 21019:10
/usr/sbin/mysqld --basedir=/usr --datadir=/v
cpu usage 10.6
memory usage 10.2
Please tell how what are the reasons and how can i solve this
problem.
Krishna Chandra Prajapati
MySQL DBA,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow mysql connections
On Wed, Aug 13, 2008 at 1:30 PM, Doug Bridgens [EMAIL PROTECTED] wrote:
Do you have any other services running on the server, such as mail or web?
How long does it take to connect to those services ('telnet server 25',
or telnet server 80')?
No
What are you using to connect to mysql? how long does 'telnet server
3306' take (assuming you are on the default port and have firewall access)?
How long does it take to connect from a local server session?
mysql client 5.0.19
When you are doing the above tests, do them with the server name and with
the IP address.
Tried with both
The above should let you rule out your client, and any server related issues
such as DNS or firewall. So you will know if it is a mysql problem or not.
cheers,
Doug
On 13 Aug 2008, at 05:39, Krishna Chandra Prajapati wrote:
Hi all,
Connecting to mysql server (Production) is taking 5 to 6 seconds.
Production
has 16Gb ram. Previously it was using only 6GB ram. The details are as
follows. DNS looks fine.
free -m
total used free sharedbuffers cached
Mem: 15899 15877 21 0 97 13913
-/+ buffers/cache: 1865 14033
Swap:0 0 0
---
vmstat
procs ---memory-- ---swap-- -io -system--
cpu
r b swpd free buff cache si sobibo in cs us sy id
wa
0 0 0 26792 100600 142428680089 15000 7 1
92 0
1
-
cat /proc/meminfo
MemTotal: 16281204 kB
MemFree: 26692 kB
Buffers:100916 kB
Cached: 14243248 kB
SwapCached: 0 kB
Active:8338968 kB
Inactive: 7679900 kB
HighTotal: 0 kB
HighFree:0 kB
LowTotal: 16281204 kB
LowFree: 26692 kB
SwapTotal: 0 kB
SwapFree:0 kB
Dirty: 224 kB
Writeback: 0 kB
AnonPages: 1674640 kB
Mapped: 11616 kB
Slab: 215140 kB
PageTables: 4232 kB
NFS_Unstable:0 kB
Bounce: 0 kB
CommitLimit: 8140600 kB
Committed_AS: 2182136 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 252 kB
VmallocChunk: 34359738043 kB
--
ps aux (The below process is only process using cpu and memory)
mysql 3963 10.6 10.2 2190136 1671816 ? Sl Mar28 21019:10
/usr/sbin/mysqld --basedir=/usr --datadir=/v
cpu usage 10.6
memory usage 10.2
Please tell how what are the reasons and how can i solve this problem.
Krishna Chandra Prajapati
MySQL DBA,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Re: Slow mysql connections
You should run into out of memory scenaria. OS is keeping to free some
memory that delay mysql connection.
On 8/13/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote:
On Wed, Aug 13, 2008 at 1:30 PM, Doug Bridgens [EMAIL PROTECTED] wrote:
Do you have any other services running on the server, such as mail or web?
How long does it take to connect to those services ('telnet server 25',
or telnet server 80')?
No
What are you using to connect to mysql? how long does 'telnet server
3306' take (assuming you are on the default port and have firewall access)?
How long does it take to connect from a local server session?
mysql client 5.0.19
When you are doing the above tests, do them with the server name and with
the IP address.
Tried with both
The above should let you rule out your client, and any server related issues
such as DNS or firewall. So you will know if it is a mysql problem or not.
cheers,
Doug
On 13 Aug 2008, at 05:39, Krishna Chandra Prajapati wrote:
Hi all,
Connecting to mysql server (Production) is taking 5 to 6 seconds.
Production
has 16Gb ram. Previously it was using only 6GB ram. The details are as
follows. DNS looks fine.
free -m
total used free sharedbuffers cached
Mem: 15899 15877 21 0 97 13913
-/+ buffers/cache: 1865 14033
Swap:0 0 0
---
vmstat
procs ---memory-- ---swap-- -io -system--
cpu
r b swpd free buff cache si sobibo in cs us sy id
wa
0 0 0 26792 100600 142428680089 15000 7 1
92 0
1
-
cat /proc/meminfo
MemTotal: 16281204 kB
MemFree: 26692 kB
Buffers:100916 kB
Cached: 14243248 kB
SwapCached: 0 kB
Active:8338968 kB
Inactive: 7679900 kB
HighTotal: 0 kB
HighFree:0 kB
LowTotal: 16281204 kB
LowFree: 26692 kB
SwapTotal: 0 kB
SwapFree:0 kB
Dirty: 224 kB
Writeback: 0 kB
AnonPages: 1674640 kB
Mapped: 11616 kB
Slab: 215140 kB
PageTables: 4232 kB
NFS_Unstable:0 kB
Bounce: 0 kB
CommitLimit: 8140600 kB
Committed_AS: 2182136 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 252 kB
VmallocChunk: 34359738043 kB
--
ps aux (The below process is only process using cpu and memory)
mysql 3963 10.6 10.2 2190136 1671816 ? Sl Mar28 21019:10
/usr/sbin/mysqld --basedir=/usr --datadir=/v
cpu usage 10.6
memory usage 10.2
Please tell how what are the reasons and how can i solve this problem.
Krishna Chandra Prajapati
MySQL DBA,
--
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: Slow mysql connections
In infinite wisdom Krishna Chandra Prajapati [EMAIL PROTECTED] spoke thus: Hi all, Connecting to mysql server (Production) is taking 5 to 6 seconds. Production has 16Gb ram. Previously it was using only 6GB ram. The details are as follows. DNS looks fine. Is it that the database you are connecting to has too many tables or columns and the mysql client spends some time making the autocomplete list? Try connecting with -A option. If that does not help, from your client box, do the following strace -o mysql.strace -T mysql -u root -h host -p (disconnect immediately after getting a connection) This will record you all the syscalls mysql client has made along with the ime spent in each syscall (-T option) in the mysql.strace file (-o option). Review that file to see which syscall takes the maximum amount of time. -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow mysql connections
Hi Raj, In which path the trace file would be created. regards anandkl On 8/13/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: In infinite wisdom Krishna Chandra Prajapati [EMAIL PROTECTED] spoke thus: Hi all, Connecting to mysql server (Production) is taking 5 to 6 seconds. Production has 16Gb ram. Previously it was using only 6GB ram. The details are as follows. DNS looks fine. Is it that the database you are connecting to has too many tables or columns and the mysql client spends some time making the autocomplete list? Try connecting with -A option. If that does not help, from your client box, do the following strace -o mysql.strace -T mysql -u root -h host -p (disconnect immediately after getting a connection) This will record you all the syscalls mysql client has made along with the ime spent in each syscall (-T option) in the mysql.strace file (-o option). Review that file to see which syscall takes the maximum amount of time. -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow mysql connections
Thank u very much. regards anandkl On 8/13/08, Raj Shekhar [EMAIL PROTECTED] wrote: in infinite wisdom Ananda Kumar spoke thus On 08/13/2008 04:37 PM: Hi Raj, In which path the trace file would be created. In your current path itself. Or just do strace -o /tmp/mysql.strace -T mysql -u root -h host -p The mysql -u root -h host -ppassword is the command which you want to strace. -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it!
Re: Slow mysql connections
in infinite wisdom Ananda Kumar spoke thus On 08/13/2008 04:37 PM: Hi Raj, In which path the trace file would be created. In your current path itself. Or just do strace -o /tmp/mysql.strace -T mysql -u root -h host -p The mysql -u root -h host -ppassword is the command which you want to strace. -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow mysql connections
Hi all, Connecting to mysql server (Production) is taking 5 to 6 seconds. Production has 16Gb ram. Previously it was using only 6GB ram. The details are as follows. DNS looks fine. free -m total used free sharedbuffers cached Mem: 15899 15877 21 0 97 13913 -/+ buffers/cache: 1865 14033 Swap:0 0 0 --- vmstat procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 0 0 0 26792 100600 142428680089 15000 7 1 92 0 1 - cat /proc/meminfo MemTotal: 16281204 kB MemFree: 26692 kB Buffers:100916 kB Cached: 14243248 kB SwapCached: 0 kB Active:8338968 kB Inactive: 7679900 kB HighTotal: 0 kB HighFree:0 kB LowTotal: 16281204 kB LowFree: 26692 kB SwapTotal: 0 kB SwapFree:0 kB Dirty: 224 kB Writeback: 0 kB AnonPages: 1674640 kB Mapped: 11616 kB Slab: 215140 kB PageTables: 4232 kB NFS_Unstable:0 kB Bounce: 0 kB CommitLimit: 8140600 kB Committed_AS: 2182136 kB VmallocTotal: 34359738367 kB VmallocUsed: 252 kB VmallocChunk: 34359738043 kB -- ps aux (The below process is only process using cpu and memory) mysql 3963 10.6 10.2 2190136 1671816 ? Sl Mar28 21019:10 /usr/sbin/mysqld --basedir=/usr --datadir=/v cpu usage 10.6 memory usage 10.2 Please tell how what are the reasons and how can i solve this problem. Krishna Chandra Prajapati MySQL DBA,
Re: mysql connections - how to stop flooding?
My first guess would be that you have a MyISAM table that gets hit with a long running query which locks the table. When that happens, all other queries start queueing up and connections will rise as new queries come in. Once the long running query finishes, all the other queued queries run and finish and then connections drop. I'm sure why Apache hits 100% cpu usage, although if my first guess is correct, then the Apache box has just as many connections open as the db box. - Original Message - From: hydn79 [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, April 29, 2007 3:38 PM Subject: mysql connections - how to stop flooding? I have an issue. Both with mysql pconnect enabled and disabled. Randomly, between every 5 mins or ever few hours the connections will jump by as much as 100 connections of more in the span of a a few seconds (5 - 15 seconds usually) then return back to normal. However, during this peak/flood of connections. Apache (on seperate box) hits 100% cpu usage until the connects normalize. Also the apache httpd.exe RAM usuage increases at the same time. Question. 1) Does this sound like a mysql, php or apache issue? 2) Is there a config in either my.ini, php.ini or httpd.conf to stop this flooding? Related Specs Two identical servers one with apache and the other DB Both with 3GB RAM. Max RAM usage around 35% 2x 5140 P4 dual core 3x 36GB 15k SCSI RAID 1 (one drive runs OS and 2 drives on Raid) My.ini -- [client] port=3306 [mysql] default-character-set=latin1 safe-user-create skip-show-database [mysqld] port=3306 basedir=D:/mysql/ datadir=D:/mysql/Data/ default-character-set=latin1 default-storage-engine=myisam max_connections=600 connect_timeout=10 wait_timeout=10 max_connect_errors=10 key_buffer_size=256M tmp_table_size=256M table_cache=2048 max_allowed_packet=16M myisam_max_sort_file_size=100k myisam_max_extra_sort_file_size=100k thread_cache_size=256 myisam_sort_buffer_size=64M read_buffer_size=2M join_buffer_size=1M sort_buffer_size=1M read_rnd_buffer_size=1M query_cache_limit=8M query_cache_size=128M query_cache_type=1 query_prealloc_size=163840 query_alloc_block_size=32768 innodb_additional_mem_pool_size=10M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=6M innodb_buffer_pool_size=128M innodb_log_file_size=100M innodb_thread_concurrency=4 innodb_file_io_threads=4 open_files_limit=2048 bulk_insert_buffer_size=8M max_heap_table_size=256M safe-user-create skip-show-database old-passwords #Don't cache host names. skip-host-cache #Don't resolve hostnames. All hostnames are IP's or 'localhost'. skip-name-resolve [mysqldump] quick max_allowed_packet = 32M [myisamchk] key_buffer = 128M sort_buffer = 128M read_buffer = 64M write_buffer = 64M [mysqlhotcopy] interactive-timeout Thanks, -- View this message in context: http://www.nabble.com/mysql-connections---how-to-stop-flooding--tf3666522.html#a10244744 Sent from the MySQL - General mailing list archive at Nabble.com. -- 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]
mysql connections - how to stop flooding?
I have an issue. Both with mysql pconnect enabled and disabled. Randomly, between every 5 mins or ever few hours the connections will jump by as much as 100 connections of more in the span of a a few seconds (5 - 15 seconds usually) then return back to normal. However, during this peak/flood of connections. Apache (on seperate box) hits 100% cpu usage until the connects normalize. Also the apache httpd.exe RAM usuage increases at the same time. Question. 1) Does this sound like a mysql, php or apache issue? 2) Is there a config in either my.ini, php.ini or httpd.conf to stop this flooding? Related Specs Two identical servers one with apache and the other DB Both with 3GB RAM. Max RAM usage around 35% 2x 5140 P4 dual core 3x 36GB 15k SCSI RAID 1 (one drive runs OS and 2 drives on Raid) My.ini -- [client] port=3306 [mysql] default-character-set=latin1 safe-user-create skip-show-database [mysqld] port=3306 basedir=D:/mysql/ datadir=D:/mysql/Data/ default-character-set=latin1 default-storage-engine=myisam max_connections=600 connect_timeout=10 wait_timeout=10 max_connect_errors=10 key_buffer_size=256M tmp_table_size=256M table_cache=2048 max_allowed_packet=16M myisam_max_sort_file_size=100k myisam_max_extra_sort_file_size=100k thread_cache_size=256 myisam_sort_buffer_size=64M read_buffer_size=2M join_buffer_size=1M sort_buffer_size=1M read_rnd_buffer_size=1M query_cache_limit=8M query_cache_size=128M query_cache_type=1 query_prealloc_size=163840 query_alloc_block_size=32768 innodb_additional_mem_pool_size=10M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=6M innodb_buffer_pool_size=128M innodb_log_file_size=100M innodb_thread_concurrency=4 innodb_file_io_threads=4 open_files_limit=2048 bulk_insert_buffer_size=8M max_heap_table_size=256M safe-user-create skip-show-database old-passwords #Don't cache host names. skip-host-cache #Don't resolve hostnames. All hostnames are IP's or 'localhost'. skip-name-resolve [mysqldump] quick max_allowed_packet = 32M [myisamchk] key_buffer = 128M sort_buffer = 128M read_buffer = 64M write_buffer = 64M [mysqlhotcopy] interactive-timeout Thanks, -- View this message in context: http://www.nabble.com/mysql-connections---how-to-stop-flooding--tf3666522.html#a10244744 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql - connections question
hi shawn... mysql permits you to set/modify the number of simultaneous connections via the my.cnf file my assumption is that this is for all the databases for a mysql instance. is this correct? also, what issues might one run into if you have multiple copies of mysql running on a single server? i'm looking at possibly needing multiple mysql instances running so that i can handle the databases, and the connections that i'm going to be dealing with. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Killing MySQL connections on a specific interface from a specific host
I am not really sure why you would need this , but I am just throwing in a possible solution.. First I would say since you need to kill connections on a specific interface (eth0), It would be fair to assume that you have more than one interface, if thats the case and you don't have the --bind-address option set in my.cnf, then your mysqld daemon would listen on all the available IP's on all interfaces, ACK. And that's what I need. then it is vey difficult to know on what IP did mysql serve a specific connection, unless you would do a netstat, then correspond that IP to the clients IP in show processlist, etc etc , so pretty cumbersume... That's what I've done so far ... I can list and grep all connections on the interface and get the client IP. I can also get the corresponding MySQL Thread-IDs from the mysql-processlist. But I could not find a way to only select connections from a specific Client IP to a specific Server IP, as the Processlist only shows the Client-address. But unless there is a real need , you can just have the deamon to listen only on one specific IP residing on eth0, like this --bind-address= xxx.xxx.xx.x ( this IP resides on eth0) The Server has to listen on all (two) interfaces and clients can connect to both. If this is feasible in your setup, then killing threads from a specific IP should be easy, if you need to kill threads manually then use a toll like mytop (http://jeremy.zawodny.com/mysql/mytop/ http://jeremy.zawodny.com/mysql/mytop/), or if you want it automated then you could easily write a perl script which would parse the output of show full processlist, get all the connections from a specific client IP, and KILL them ... Well, that's what I've done ... But it also kills connections from the client to another interface. I know this is somewhat special and it would take quite a while to explain why exactly I need this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Killing MySQL connections on a specific interface from a specific host
Hi I'd like to know if there is anything to kill connections from a specific client ip that came to the server on a specific interface. I do not want to block them on layer 2 (which could easily be done with netfilter), I would like to be able to kill active connections. For example: I would like to kill connections from 192.168.50.3 that came in on interface eth0. Connections from that client IP to another interface should not be affected. Of course one could script something using lsof -i or netstat and the mysql processlist, but that would end in some nasty shellscript and I don't know how to only kill connections for one interface as the mysql processlist only shows the client ip, not the ip, the client connected to. Thanks for any ideas Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Killing MySQL connections on a specific interface from a specific host
On 4/28/06, Dominik Klein [EMAIL PROTECTED] wrote: Hi I'd like to know if there is anything to kill connections from a specific client ip that came to the server on a specific interface. I do not want to block them on layer 2 (which could easily be done with netfilter), I would like to be able to kill active connections. For example: I would like to kill connections from 192.168.50.3 that came in on interface eth0. Connections from that client IP to another interface should not be affected. Of course one could script something using lsof -i or netstat and the mysql processlist, but that would end in some nasty shellscript and I don't know how to only kill connections for one interface as the mysql processlist only shows the client ip, not the ip, the client connected to. Thanks for any ideas Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I am not really sure why you would need this , but I am just throwing in a possible solution.. First I would say since you need to kill connections on a specific interface (eth0), It would be fair to assume that you have more than one interface, if thats the case and you don't have the --bind-address option set in my.cnf, then your mysqld daemon would listen on all the available IP's on all interfaces, then it is vey difficult to know on what IP did mysql serve a specific connection, unless you would do a netstat, then correspond that IP to the clients IP in show processlist, etc etc , so pretty cumbersume... But unless there is a real need , you can just have the deamon to listen only on one specific IP residing on eth0, like this --bind-address= xxx.xxx.xx.x ( this IP resides on eth0) If this is feasible in your setup, then killing threads from a specific IP should be easy, if you need to kill threads manually then use a toll like mytop (http://jeremy.zawodny.com/mysql/mytop/), or if you want it automated then you could easily write a perl script which would parse the output of show full processlist, get all the connections from a specific client IP, and KILL them ... Hope this helps Kishore Jalleda http://kjalleda.googlepages.com/projects
Re: Log mysql connections
António Fernandes wrote: Hello, I'm am trying to make MySQL Server to log connections (attempts, successes, failures) to a Syslog. I know that it's possible to log all queries but I just want the connections. Has anyone already done this? Is there a patch file that I can use? Wanring - I've never done this myself, I've just seen the option and the code in the source. So take my words with a bit of a grain of salt. Make sure libwrap is installed, and then build MySQL using --with-libwrap option to configure. -- 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]
Log mysql connections
Hello, I'm am trying to make MySQL Server to log connections (attempts, successes, failures) to a Syslog. I know that it's possible to log all queries but I just want the connections. Has anyone already done this? Is there a patch file that I can use? Thank you, António Fernandes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
maximum number of mysql connections and process
I have some general questions: #1 How many simultaneoulsy connection can mysql server handle? #2 Is there a limitation in how many processes can mysql handle? Thanks _ Keep up with high-tech trends here at Hook'd on Technology. http://special.msn.com/msnbc/hookedontech.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum number of mysql connections and process
In the last episode (Feb 09), Mike Mapsnac said: I have some general questions: #1 How many simultaneoulsy connection can mysql server handle? #2 Is there a limitation in how many processes can mysql handle? As many as your OS's threads implementation can handle. --- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to pass mysql connections across exec()
Hi,
I have a requirement where I should be able to open connection to mysql from a server
which is running continuously and pass the connection to a child of this server after
it
does a exec to a totally different executable. This will enable me to reuse a mysql
connection
initiated by one process and used by another process - which though is a child of the
first process but is a totally different executable. Thus, I will be able to initiate
connection pooling for a daemon like smtp without keeping on initiating
mysql_real_connect()
on each and every mail.
I have done this by doing mysql_init() and mysql_real_connect() in the parent.
In the exec'ed process, I do mysql_init() followed my vio_new() and my_net_init().
The above logic works for me, But i am not sure if I am doing the right thing. Also
there is a difference
in the some of the values of the mysql structure in parent and the child.
Also enclosing two programs try.c and try1.c, which demonstrate the above. try does a
mysql_real_connect()
to open a mysql_connection with socket descriptor 3. The program try1 the uses this
same descriptor to
restablish the connection to the mysqld server without initiating a new socket
connection.
The output of the mysql structure by try is
hostlocalhost
uservpopmail
passssh-1.5-
sock/tmp/mysql.sock
vers4.0.0-alpha-log
Host Info Localhost via UNIX socket
info(null)
db vpopmail
Affected Rows 0
Insert id 0
extra_info 0
thread_id 103
packet_length 0
port3306
client_flag 8333
server_capabilities 44
Protocol version10
Field Count 0
Server Status 2
Server Language 8
Mysql Status0
Free me 0
Reconnect 1
Scramble0Q,$n~OR
The output of the mysql structure by try1 is
hostlocalhost
uservpopmail
passssh-1.5-
sock/tmp/mysql.sock
vers
Host Info Localhost via UNIX socket
info(null)
db (null)
Affected Rows 0
Insert id 0
extra_info 0
thread_id 0
packet_length 0
port3306
client_flag 8333
server_capabilities 0
Protocol version10
Field Count 0
Server Status 2
Server Language 0
Mysql Status0
Free me 0
Reconnect 1
Scramble
As you can see the following variables are not getting set in the exec'ed process.
mysql.server_version
mysql.thread_id
mysql.server_language
mysql.scramble_buff
Is there anyting i am missing? (However, the program works fine and the query also
works fine.
The mysql show process list also shows that the connection is being reused. However I
am not
sure, if this method could cause any problems).
The programs try.c and try1.c are below
/* try.c */
#include stdio.h
#include fcntl.h
#include mysql.h
main()
{
MYSQL mysql;
mysql_init(mysql);
if (!(mysql_real_connect(mysql, localhost, vpopmail, ***,
vpopmail, 3306, 0, 0)))
{
fprintf(stderr, mysql_real_connect: %s\n, mysql_error(mysql));
return(1);
}
printf(host%s\n, mysql.host);
printf(user%s\n, mysql.user);
printf(pass%s\n, mysql.passwd);
printf(sock%s\n, mysql.unix_socket);
printf(vers%s\n, mysql.server_version);
printf(Host Info %s\n, mysql.host_info);
printf(info%s\n, mysql.info);
printf(db %s\n, mysql.db);
printf(Affected Rows %d\n, mysql.affected_rows);
printf(Insert id %d\n, mysql.insert_id);
printf(extra_info %d\n, mysql.extra_info);
printf(thread_id %d\n, mysql.thread_id);
printf(packet_length %d\n, mysql.packet_length);
printf(port%d\n, mysql.port);
printf(client_flag %d\n, mysql.client_flag);
printf(server_capabilities %d\n, mysql.server_capabilities);
printf(Protocol version%d\n, mysql.protocol_version);
printf(Field Count %d\n, mysql.field_count);
printf(Server Status %d\n, mysql.server_status);
printf(Server Language %d\n, mysql.server_language);
printf(Mysql Status%d\n, mysql.status);
printf(Free me %d\n, mysql.free_me);
printf(Reconnect %d\n, mysql.reconnect);
printf(Scramble%s\n, mysql.scramble_buff);
getchar();
execl(/tmp/try1, /tmp/try1, 0);
}
/* try1.c */
#include stdio.h
#include stdlib.h
#include string.h
#include pwd.h
RE: How to pass mysql connections across exec()
have you tried forking the process, then terminating the parent? All open
file desciptors, including sockets, would be available to the child. The
child would contain the code in try1.c.
-Original Message-
From: Manvendra Bhangui [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 11, 2002 5:22 AM
To: [EMAIL PROTECTED]
Subject: How to pass mysql connections across exec()
Hi,
I have a requirement where I should be able to open connection to mysql from
a server
which is running continuously and pass the connection to a child of this
server after it
does a exec to a totally different executable. This will enable me to reuse
a mysql connection
initiated by one process and used by another process - which though is a
child of the
first process but is a totally different executable. Thus, I will be able to
initiate
connection pooling for a daemon like smtp without keeping on initiating
mysql_real_connect()
on each and every mail.
I have done this by doing mysql_init() and mysql_real_connect() in the
parent.
In the exec'ed process, I do mysql_init() followed my vio_new() and
my_net_init().
The above logic works for me, But i am not sure if I am doing the right
thing. Also there is a difference
in the some of the values of the mysql structure in parent and the child.
Also enclosing two programs try.c and try1.c, which demonstrate the above.
try does a mysql_real_connect()
to open a mysql_connection with socket descriptor 3. The program try1 the
uses this same descriptor to
restablish the connection to the mysqld server without initiating a new
socket connection.
The output of the mysql structure by try is
hostlocalhost
uservpopmail
passssh-1.5-
sock/tmp/mysql.sock
vers4.0.0-alpha-log
Host Info Localhost via UNIX socket
info(null)
db vpopmail
Affected Rows 0
Insert id 0
extra_info 0
thread_id 103
packet_length 0
port3306
client_flag 8333
server_capabilities 44
Protocol version10
Field Count 0
Server Status 2
Server Language 8
Mysql Status0
Free me 0
Reconnect 1
Scramble0Q,$n~OR
The output of the mysql structure by try1 is
hostlocalhost
uservpopmail
passssh-1.5-
sock/tmp/mysql.sock
vers
Host Info Localhost via UNIX socket
info(null)
db (null)
Affected Rows 0
Insert id 0
extra_info 0
thread_id 0
packet_length 0
port3306
client_flag 8333
server_capabilities 0
Protocol version10
Field Count 0
Server Status 2
Server Language 0
Mysql Status0
Free me 0
Reconnect 1
Scramble
As you can see the following variables are not getting set in the exec'ed
process.
mysql.server_version
mysql.thread_id
mysql.server_language
mysql.scramble_buff
Is there anyting i am missing? (However, the program works fine and the
query also works fine.
The mysql show process list also shows that the connection is being reused.
However I am not
sure, if this method could cause any problems).
The programs try.c and try1.c are below
/* try.c */
#include stdio.h
#include fcntl.h
#include mysql.h
main()
{
MYSQL mysql;
mysql_init(mysql);
if (!(mysql_real_connect(mysql, localhost, vpopmail, ***,
vpopmail, 3306, 0, 0)))
{
fprintf(stderr, mysql_real_connect: %s\n,
mysql_error(mysql));
return(1);
}
printf(host%s\n, mysql.host);
printf(user%s\n, mysql.user);
printf(pass%s\n, mysql.passwd);
printf(sock%s\n, mysql.unix_socket);
printf(vers%s\n, mysql.server_version);
printf(Host Info %s\n, mysql.host_info);
printf(info%s\n, mysql.info);
printf(db %s\n, mysql.db);
printf(Affected Rows %d\n, mysql.affected_rows);
printf(Insert id %d\n, mysql.insert_id);
printf(extra_info %d\n, mysql.extra_info);
printf(thread_id %d\n, mysql.thread_id);
printf(packet_length %d\n, mysql.packet_length);
printf(port%d\n, mysql.port);
printf(client_flag %d\n, mysql.client_flag);
printf(server_capabilities %d\n, mysql.server_capabilities);
printf(Protocol version%d\n, mysql.protocol_version);
printf(Field Count %d\n, mysql.field_count);
printf(Server Status %d\n, mysql.server_status);
printf(Server Language %d\n, mysql.server_language);
printf
Re: How to pass mysql connections across exec()
Rick,
I have tried it and it works and there is no problem in executing any sql queries in
the child. my application also is
working fine. But the issue I am facing is the value of the four variables in the
MYSQL structure, which
I presume gets set only when doing mysql_real_connect(). I am worried that I could
cause mysqld to crash because this is not the way to connect to mysqld. These four
variables are not available to the child (I am thinking
of setting these four variables as env variables in the parent so that the child can
pick it up and set it forcibly). In
fact the function mysqlreconnect() in try1.c is a hacked version of
mysql_real_connect() from libmysql.c minus the
socket() and connect() system calls.
The purpose behind my experimentations is to modify the inetd server, open the
connection to mysqld in inetd and
the child of inetd should be able to reuse the socket connected to mysqld.
mysql.server_version
mysql.thread_id
mysql.server_language
mysql.scramble_buff
Any help in figuring out how the mysql structure, the NET structure and the VIO
structure gets set or can be set without doing mysql_real_connect() would be helpful
to me.
Regards Manvendra
On Mon, 11 Mar 2002 15:04:47 -0600
Rick Emery [EMAIL PROTECTED] wrote:
have you tried forking the process, then terminating the parent? All open
file desciptors, including sockets, would be available to the child. The
child would contain the code in try1.c.
-Original Message-
From: Manvendra Bhangui [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 11, 2002 5:22 AM
To: [EMAIL PROTECTED]
Subject: How to pass mysql connections across exec()
Hi,
I have a requirement where I should be able to open connection to mysql from
a server
which is running continuously and pass the connection to a child of this
server after it
does a exec to a totally different executable. This will enable me to reuse
a mysql connection
initiated by one process and used by another process - which though is a
child of the
first process but is a totally different executable. Thus, I will be able to
initiate
connection pooling for a daemon like smtp without keeping on initiating
mysql_real_connect()
on each and every mail.
I have done this by doing mysql_init() and mysql_real_connect() in the
parent.
In the exec'ed process, I do mysql_init() followed my vio_new() and
my_net_init().
The above logic works for me, But i am not sure if I am doing the right
thing. Also there is a difference
in the some of the values of the mysql structure in parent and the child.
Also enclosing two programs try.c and try1.c, which demonstrate the above.
try does a mysql_real_connect()
to open a mysql_connection with socket descriptor 3. The program try1 the
uses this same descriptor to
restablish the connection to the mysqld server without initiating a new
socket connection.
The output of the mysql structure by try is
hostlocalhost
uservpopmail
passssh-1.5-
sock/tmp/mysql.sock
vers4.0.0-alpha-log
Host Info Localhost via UNIX socket
info(null)
db vpopmail
Affected Rows 0
Insert id 0
extra_info 0
thread_id 103
packet_length 0
port3306
client_flag 8333
server_capabilities 44
Protocol version10
Field Count 0
Server Status 2
Server Language 8
Mysql Status0
Free me 0
Reconnect 1
Scramble0Q,$n~OR
The output of the mysql structure by try1 is
hostlocalhost
uservpopmail
passssh-1.5-
sock/tmp/mysql.sock
vers
Host Info Localhost via UNIX socket
info(null)
db (null)
Affected Rows 0
Insert id 0
extra_info 0
thread_id 0
packet_length 0
port3306
client_flag 8333
server_capabilities 0
Protocol version10
Field Count 0
Server Status 2
Server Language 0
Mysql Status0
Free me 0
Reconnect 1
Scramble
As you can see the following variables are not getting set in the exec'ed
process.
mysql.server_version
mysql.thread_id
mysql.server_language
mysql.scramble_buff
Is there anyting i am missing? (However, the program works fine and the
query also works fine.
The mysql show process list also shows that the connection is being reused.
However I am not
sure, if this method could cause any problems).
The programs try.c and try1.c are below
/* try.c */
#include stdio.h
#include fcntl.h
#include mysql.h
main()
{
MYSQL mysql;
mysql_init(mysql);
if (!(mysql_real_connect(mysql
MySQL Connections
Does anyone know how to allow other hosts to connect to a MySQL database? I have a pretty good GUI (DBTools) that will supposedly let you do this, but whenever I try to connect to a MySQL Server that is not local, is says that access is denied. I cant find it anywhere in Xinetd so I am assuming it is in the MySQL config somewhere. Am I wrong? - 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 Connections
Erich, You need to create special root or superuser account so that you can connect from your PC. This will allow you full access from DBtools. This is what I had to do in order to make the connection. You'll need to log in as the 'root' or 'superuser' to execute your mysql client. %mysql grant all on *.* to some_user_name identified by 'some_password' with grant option; %mysql flush privileges; This is will produce the % in the Host column in the mysql user table Hope this helps :) On Mon, 9 Jul 2001 17:48:59 -0500, Erich Kolb [EMAIL PROTECTED] wrote: Does anyone know how to allow other hosts to connect to a MySQL database? I have a pretty good GUI (DBTools) that will supposedly let you do this, but whenever I try to connect to a MySQL Server that is not local, is says that access is denied. I cant find it anywhere in Xinetd so I am assuming it is in the MySQL config somewhere. Am I wrong? Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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
Problem with mysql connections.
Dear Sir, We installed MySql server on one system. I have the test database, and My own database named Gomoos. When i using to connect to the database using java program with a jdbc driver i am not able to connect to my database Gomoos .But i am able to connect to test database. When i am using localhost insted of IPaddress then i amable to connect to any database on local system. But from a remote system iam able to connect to onlytest database. I am getting an error saying access denied for user: '@myIPaddress' Please give me a soultion to resolve this problem. Thanks Regards Niranjan Babu.P - 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: Problem with mysql connections.
see GRANT in the manual. Snehalata wrote: Dear Sir, We installed MySql server on one system. I have the test database, and My own database named Gomoos. When i using to connect to the database using java program with a jdbc driver i am not able to connect to my database Gomoos .But i am able to connect to test database. When i am using localhost insted of IPaddress then i amable to connect to any database on local system. But from a remote system iam able to connect to onlytest database. I am getting an error saying access denied for user: '@myIPaddress' Please give me a soultion to resolve this problem. Thanks Regards Niranjan Babu.P - 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 - 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 Connections
Hi, I am currently working on a project in which we are using MySQL to store data. The database server (basic installation) is installed on Windows NT version 4. We are currently doing some load testing on the software and have encountered some problems. The database server is hanging. Our intention is eventually to create a farm of application servers to host the system for scalability reasons. So, at the moment we want to find the limits of a single app server. 1. We have employed the Singleton pattern to control the connection to that database, which is a point of concern. Will the load testing strain the single connection thus hanging the Mysql Server? 2. Should each request create a new database connection or is this too slow and costly 3. Should connection pooling be considered and if so which app server do you recommend 4. Could antone tell me how many connections can be open at any one time? 5. Are there any know reasons why the database server should hang? 6. Are there any know reasons why a connection to the database should hang thus crippling the database server? Any comments or suggestion as to wh this is happening would be most useful. Cheers Daire Treanor. - 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: Balanced mysql-connections
Kilian Looser wrote: Hi, i've the following setup: LoadBalancer - five Webserver with Apache and mysql - Database Now i got a problem. i've user's they insert records in the database. Now the insert is going throu, for example, webserver1. Now i'm doing a select throu webserver2 and i didn't get the information inserted by webserver1. i've to wait for about two or three seconds, till the tupel is present in the database. Is this a cache problem? any idea? thx Kilian If you are saying that you have 5 mysql server pointing to a single set of data files then this is not good mysql is not design to work that way.. the reason you are having this problem is that each of the fiver servers have an OS disk cache that has something in it different that what is actually in the file, mysql itself doesn't have a data cache, it expects the file system to do that. You really should have 5 replicas of the database and send all updates through one server and use all the others for reads. The scheme that you mention above (assuming NFS or something pointing to the single data files?) works fine but only in READ ONLY situations. In addition to the dirty read problem you see above you will probably corrupt yoru data in short order. - 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: Balanced mysql-connections
Kilian Looser wrote: Hi, i've the following setup: LoadBalancer - five Webserver with Apache and mysql - Database Now i got a problem. i've user's they insert records in the database. Now the insert is going throu, for example, webserver1. Now i'm doing a select throu webserver2 and i didn't get the information inserted by webserver1. i've to wait for about two or three seconds, till the tupel is present in the database. Is this a cache problem? any idea? thx Kilian Hi, Are you using INSERT with either DELAYED or LOW PRIORITY options ? Hope this helps -- Joseph Bueno NetClub/Trader.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
Limit mysql connections by database user?
Hello, is there a way to limit the connection which the mysql user can do?? I have a server with 12 database users, and sometimes, one of the users makes large number of connections because of a bulletin board he is using, is there a way to limit the number of connections that he can do? Regards Issam - 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
