By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,857 Members | 1,813 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,857 IT Pros & Developers. It's quick & easy.

mysql too many connections performance issues

P: n/a
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_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 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_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 21370536 |
+-------------------------+----------+
9 rows in set (0.00 sec)

Sep 7 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
>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 discussion thread is closed

Replies have been disabled for this discussion.