mysql too many connections performance issues

mysql too many connections performance issues

am 07.09.2006 19:41:01 von marcfischman

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.

mysql> show 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 |
+----------------------------+------------+

mysql> show 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)

mysql> show 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)

Re: mysql too many connections performance issues

am 08.09.2006 01:47:57 von gordonb.17xgv

>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).