mysql performance issues too many connections

#1 (**permalink**) 09-07-2006

#2 (**permalink**) 09-07-2006

marcfischman@gmail.com wrote:
> 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)
>

First of all, don't use persistent connections. They aren't needed, and
are affecting your performance. Rather, use non-persistent
connections and close them when you're done. This will immediately free
up MySQL resources.

In your case you have 100 connections going all the time, even if you
only need one or two. Using non-persistent connections means you have
to connect each time, but this overhead isn't as bad as using all the
extra resources unnecessarily.

300K page views is a fair amount, but MySQL should be able to handle it
just fine. Even if every page used MySQL (probably not, but I don't
know your site), that's only 3.4 connections per second. During heavy
times you might hit 4x that, but that's still not bad.

You also didn't say if you're on shared hosting. But if you are, that
can be a rather heavy load on the server. Other sites on the same
server may be slowing things down enough to affect your site. It just
depends on what those other sites are doing. You may be better off with
a dedicated server; you're pushing the limit on most shared hosting.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

#3 (**permalink**) 09-07-2006

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode