Professional Web Applications Themes

mysql performance issues too many connections - MySQL

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

  1. #1

    Default mysql performance issues too many connections

    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)

    marcfischman@gmail.com Guest

  2. #2

    Default Re: mysql performance issues too many connections

    com wrote: 

    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.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: mysql performance issues too many connections

    com wrote:
     
    <snip> 
    +-------+------+-----------+-------+---------+------+-------+-----------------------+ 
    <snip>


    All your connection are sleeping. Turn off persistant connections and your
    problem should go away.

    I don't think the load will be an issue unless your queries are particularly
    intensive or are badly optimized. We get about 2-3 times the traffic you
    mentioned and our MySQL serves about 6.5m queries per day, MySQL's CPU
    usage averages below 15% most of the time (though admittedly the hardware
    is beefy).


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

Similar Threads

  1. Flash Performance issues
    By reach4thelasers in forum Macromedia Flash Player
    Replies: 17
    Last Post: February 19th, 02:30 PM
  2. CFMX7 Performance Issues
    By jeff_gombala in forum Coldfusion Server Administration
    Replies: 1
    Last Post: July 18th, 08:38 PM
  3. Performance issues
    By Marcus in forum Coldfusion Server Administration
    Replies: 4
    Last Post: February 28th, 07:21 PM
  4. Informix Performance Issues
    By Ajay Gopinath in forum Informix
    Replies: 1
    Last Post: August 4th, 03:13 AM
  5. Oracle 8.1.7.4 Performance Issues
    By Burkhard Kiesel in forum Oracle Server
    Replies: 4
    Last Post: December 16th, 10:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139