473,698 Members | 2,609 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

mysql too many connections performance issues

Please help. I have a website running on a linux/apache/mysql/php
server. I receive about 8,000-10,000 visitors a day with about 200,000
to 300,000 page views. The server is a RedHat Linux server running PHP
5.x, MySQL 5.x, Apache 2.x

We have been suffering from a number of performance issues. Our
hosting company has set our max connections to 100, and we are using
persistent connections in PHP. At times the mysqld process takes 100%
of the CPU. We have also been suffering from
mysql_pconnect( ): Too many connections errors. What can I do to fix
these issues?

When I run a top on the server I see this ...
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5567 mysql 16 0 169m 42m 4236 S 81.2 4.2 3078:09 mysqld
32539 apache 15 0 42676 25m 7708 S 1.0 2.5 0:03.40 httpd
32572 apache 15 0 42680 25m 7708 S 1.0 2.5 0:02.98 httpd
32608 apache 15 0 42680 25m 7704 S 1.0 2.5 0:00.72 httpd
32542 apache 15 0 42704 25m 7712 S 0.7 2.5 0:03.38 httpd
32561 apache 15 0 42732 25m 7712 S 0.7 2.5 0:02.61 httpd
32567 apache 15 0 42680 25m 7708 S 0.7 2.5 0:02.60 httpd
32591 apache 16 0 42672 25m 7708 S 0.7 2.5 0:02.06 httpd
32596 apache 15 0 42680 25m 7708 S 0.7 2.5 0:01.90 httpd
32602 apache 15 0 42680 25m 7704 S 0.7 2.5 0:01.05 httpd
32606 apache 15 0 42676 25m 7692 S 0.7 2.5 0:00.62 httpd
32612 apache 15 0 42672 25m 7708 S 0.7 2.5 0:00.81 httpd
32627 apache 15 0 42668 25m 7696 S 0.7 2.5 0:00.09 httpd
32534 apache 15 0 42704 25m 7712 S 0.3 2.5 0:03.66 httpd
32552 apache 15 0 42700 25m 7712 S 0.3 2.5 0:02.68 httpd
32560 apache 15 0 42680 25m 7708 S 0.3 2.5 0:02.61 httpd
32562 apache 15 0 42680 25m 7708 S 0.3 2.5 0:02.95 httpd
32564 apache 15 0 42704 25m 7712 S 0.3 2.5 0:02.95 httpd
32566 apache 15 0 42680 25m 7708 S 0.3 2.5 0:02.87 httpd
32574 apache 15 0 42708 25m 7708 S 0.3 2.5 0:02.59 httpd
32586 apache 15 0 42680 25m 7708 S 0.3 2.5 0:02.16 httpd
32590 apache 15 0 42708 25m 7700 S 0.3 2.5 0:01.93 httpd
32593 apache 15 0 42680 25m 7712 S 0.3 2.5 0:02.08 httpd
32604 apache 15 0 42668 25m 7700 S 0.3 2.5 0:00.77 httpd
32614 apache 15 0 42704 25m 7696 S 0.3 2.5 0:00.63 httpd
32616 apache 15 0 42680 25m 7708 S 0.3 2.5 0:00.72 httpd
32622 apache 15 0 42676 25m 7688 S 0.3 2.5 0:00.24 httpd
1 root 16 0 1712 460 428 S 0.0 0.0 0:06.87 init
2 root RT 0 0 0 0 S 0.0 0.0 0:01.02
migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:14.49
ksoftirqd/0
4 root RT 0 0 0 0 S 0.0 0.0 0:00.57
migration/1
5 root 34 19 0 0 0 S 0.0 0.0 0:00.61
ksoftirqd/1
6 root 5 -10 0 0 0 S 0.0 0.0 0:00.61 events/0
7 root 5 -10 0 0 0 S 0.0 0.0 0:00.52 events/1
8 root 7 -10 0 0 0 S 0.0 0.0 0:00.00 khelper

These are some of the mysql variables, please tell me if you need to
see more.

mysqlshow variables like 'max%';
+----------------------------+------------+
| Variable_name | Value |
+----------------------------+------------+
| max_allowed_pac ket | 1048576 |
| max_binlog_cach e_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_err ors | 10 |
| max_connections | 100 |
| max_delayed_thr eads | 20 |
| max_error_count | 64 |
| max_heap_table_ size | 16777216 |
| max_insert_dela yed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_ sort_data | 1024 |
| max_relay_log_s ize | 0 |
| max_seeks_for_k ey | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursio n_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connec tions | 0 |
| max_write_lock_ count | 4294967295 |
+----------------------------+------------+

mysqlshow full processlist;
+-------+------+-----------+-------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+-------+------+-----------+-------+---------+------+-------+-----------------------+
| 10993 | root | localhost | database | Sleep | 0 | | NULL
|
| 10994 | root | localhost | database | Sleep | 2 | | NULL
|
| 10995 | root | localhost | database | Sleep | 2 | | NULL
|
| 10996 | root | localhost | database | Sleep | 2 | | NULL
|
| 10997 | root | localhost | database | Query | 0 | NULL | show
full processlist |
| 10998 | root | localhost | database | Sleep | 2 | | NULL
|
| 10999 | root | localhost | database | Sleep | 6 | | NULL
|
| 11000 | root | localhost | database | Sleep | 1 | | NULL
|
| 11001 | root | localhost | database | Sleep | 1 | | NULL
|
| 11002 | root | localhost | database | Sleep | 1 | | NULL
|
| 11003 | root | localhost | database | Sleep | 0 | | NULL
|
| 11004 | root | localhost | database | Sleep | 4 | | NULL
|
| 11005 | root | localhost | database | Sleep | 1 | | NULL
|
| 11006 | root | localhost | database | Sleep | 2 | | NULL
|
| 11007 | root | localhost | database | Sleep | 2 | | NULL
|
| 11008 | root | localhost | database | Sleep | 2 | | NULL
|
| 11009 | root | localhost | database | Sleep | 28 | | NULL
|
| 11010 | root | localhost | database | Sleep | 5 | | NULL
|
| 11011 | root | localhost | database | Sleep | 0 | | NULL
|
| 11012 | root | localhost | database | Sleep | 3 | | NULL
|
| 11013 | root | localhost | database | Sleep | 44 | | NULL
|
| 11014 | root | localhost | database | Sleep | 2 | | NULL
|
| 11015 | root | localhost | database | Sleep | 1 | | NULL
|
| 11016 | root | localhost | database | Sleep | 8 | | NULL
|
| 11017 | root | localhost | database | Sleep | 8 | | NULL
|
| 11018 | root | localhost | database | Sleep | 15 | | NULL
|
| 11019 | root | localhost | database | Sleep | 2 | | NULL
|
| 11020 | root | localhost | database | Sleep | 4 | | NULL
|
| 11021 | root | localhost | database | Sleep | 25 | | NULL
|
| 11022 | root | localhost | database | Sleep | 2 | | NULL
|
| 11023 | root | localhost | database | Sleep | 0 | | NULL
|
| 11024 | root | localhost | database | Sleep | 2 | | NULL
|
| 11025 | root | localhost | database | Sleep | 2 | | NULL
|
| 11026 | root | localhost | database | Sleep | 1 | | NULL
|
| 11027 | root | localhost | database | Sleep | 0 | | NULL
|
| 11028 | root | localhost | database | Sleep | 2 | | NULL
|
| 11029 | root | localhost | database | Sleep | 2 | | NULL
|
| 11030 | root | localhost | database | Sleep | 2 | | NULL
|
| 11031 | root | localhost | database | Sleep | 0 | | NULL
|
| 11032 | root | localhost | database | Sleep | 1 | | NULL
|
| 11033 | root | localhost | database | Sleep | 1 | | NULL
|
| 11034 | root | localhost | database | Sleep | 0 | | NULL
|
| 11035 | root | localhost | database | Sleep | 2 | | NULL
|
| 11036 | root | localhost | database | Sleep | 3 | | NULL
|
| 11037 | root | localhost | database | Sleep | 2 | | NULL
|
| 11038 | root | localhost | database | Sleep | 1 | | NULL
|
| 11039 | root | localhost | database | Sleep | 18 | | NULL
|
| 11040 | root | localhost | database | Sleep | 3 | | NULL
|
| 11041 | root | localhost | database | Sleep | 3 | | NULL
|
| 11042 | root | localhost | database | Sleep | 2 | | NULL
|
| 11043 | root | localhost | database | Sleep | 3 | | NULL
|
| 11044 | root | localhost | database | Sleep | 7 | | NULL
|
+-------+------+-----------+-------+---------+------+-------+-----------------------+
52 rows in set (0.00 sec)

mysqlshow status like 'q%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blo cks | 0 |
| Qcache_free_mem ory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_p runes | 0 |
| Qcache_not_cach ed | 0 |
| Qcache_queries_ in_cache | 0 |
| Qcache_total_bl ocks | 0 |
| Questions | 21370536 |
+-------------------------+----------+
9 rows in set (0.00 sec)

Sep 7 '06 #1
1 13677
>Please help. I have a website running on a linux/apache/mysql/php
>server. I receive about 8,000-10,000 visitors a day with about 200,000
to 300,000 page views. The server is a RedHat Linux server running PHP
5.x, MySQL 5.x, Apache 2.x

We have been suffering from a number of performance issues. Our
hosting company has set our max connections to 100, and we are using
persistent connections in PHP.
Use of persistent connections is likely to cause problems with too
many connections. If the maximum number of instances of Apache is
200, eventually you will get 200 persistent connections *FOR EACH*
login/password combination used by scripts on the page. You may
well run out of connections even if most of them are idle, even if
you never have more than one page being processed at one time.
>At times the mysqld process takes 100%
of the CPU. We have also been suffering from
mysql_pconnect (): Too many connections errors. What can I do to fix
these issues?
Drop the persistent connections. That should stop the "too many
connections" errors. Reduced memory in the server may reduce swapping
in the MySQL server and speed things up. Or, you may have to throw
more hardware at the problem (say, put the database on a separate
server from the web server).
Sep 7 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1517
by: Kayra Otaner | last post by:
Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when
0
1673
by: kayra | last post by:
Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when...
33
5577
by: Joshua D. Drake | last post by:
Hello, I think the below just about says it all: http://www.commandprompt.com/images/mammoth_versus_dolphin_500.jpg Sincerely, Joshua Drake
39
8402
by: Mairhtin O'Feannag | last post by:
Hello, I have a client (customer) who asked the question : "Why would I buy and use UDB, when MySql is free?" I had to say I was stunned. I have no experience with MySql, so I was left sort of stammering and sputtering, and managed to pull out something I heard a couple of years back - that there was no real transaction safety in MySql. In flight transactions could be lost.
1
2825
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work. Despite entering the required lines to "my.ini" (the new my.cnf) through notepad AND MySQL Administrator, the cache does not work. So, today I took a peek at the 'Health' tab in MySQL Administrator.
8
2358
by: Fred | last post by:
Hello, Our website is currently developed in ASP/Mysql 4. The dedicated servers on which it is currently hosted arrive at saturation. Here is their configuration: - 1 server PIV 2,8Ghz 1GB RAM with IIS 5 on Windows 2000 - 1 server Bi-xeon 3Ghz, 512 MB with MySQL 4 on Windows 2003 The website makes approximately 10.000.000 of pages seen and 310.000
4
6061
by: elyob | last post by:
Not really tried going two ways at once, but I have an include_once connection to a mysql_database, now I need to retrieve info from a second mysql_database .. My mysql_connects are getting confused. So, (I've had a couple of beers), am I opening up too many at once? I'll look again in the morning, but am thinking that it is a bad idea to leave two connections open all the time ...
7
3250
by: Martien van Wanrooij | last post by:
I have been faced a couple of times with the situation that I wanted to write a script and was worried about a too frequent opening and closing mysql connections. To give some examples: 1)I am trying to develop a photoalbum (also posted a topic called "array / mysql question"). Of course I can use some wonderful open source albums but at the same time I want to get more familiar with all the ins and outs so my idea is a list of...
11
13208
by: Kurda Yon | last post by:
Hi, I got this warning: mysql_query(): 4 is not a valid MySQL-Link resource. The line which cause this warning is: mysql_query("insert into $tablename (id,priority) values('$id', '0.00')", $link2); As far as understand the problem is that $link2 is "not a valid MySQL-
0
8676
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8608
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9164
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9029
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8870
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6524
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4370
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2332
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.