The MU forums have moved to WordPress.org

Apache and MySQL optimisations (17 posts)

  1. amanzi
    Member
    Posted 14 years ago #

    Hi all,

    I've been running my WPMU site from a shared host for the last couple of years but am finally moving to a dedicated server. I'd like to know if anyone would like to share any of the optimisations they have configured on Apache or MySQL, or if you've got any other tips that relate to running your own WPMU-specific server. I currently have about 1000 blogs, so not a huge number but still significant.

    Thanks - Stuart.

  2. VentureMaker
    Member
    Posted 14 years ago #

    Well, if you have a good server you won't need many tweaks done for Apache/MySQL/PHP.

    If you're planning a rapid extension - from 1k to 10k blogs - then yes, I can share some ideas :)

  3. Ovidiu
    Member
    Posted 14 years ago #

    can I just jump the wagon here?

    having one problem with mysql which I can't get rid of:

    Connections ø per hour %
    max. concurrent connections 9 --- ---
    Failed attempts 683 61.94 9.86%
    Aborted 0 0.00 0.00%
    Total 6,925 628.02 100.00%

    any hints where all these failed attempts come from and what to try and debug it?

    Otherwise the server is running perfectly. Might be a certain timeout I have set to low?

    Just in case the above text is unreadable I am having 9.86% of Failed attempts.

  4. VentureMaker
    Member
    Posted 14 years ago #

    Ovidiu, your mysql log would be helpful :)

  5. SteveAtty
    Member
    Posted 14 years ago #

    It could be something silly like web sessions that close down unexpectedly or even packets that are too big.

    Back to the original topic I found a nice little script that analyses your DB and comes back with suggestions for tweaking mySQL's memory usage.

    But at the end of the day memory is important for MySQL caches and things like eAccelerator (if you're using it)

  6. cafespain
    Member
    Posted 14 years ago #

    @amanzi: If you are going to your own dedicated server, consider something other than Apache (nginx, litespeed, lighttpd) - I always point people (who ask) here :
    http://articles.slicehost.com/

    Ususally some good tips on setting things up on the server side.

    Look here for mysql tips:
    http://www.mysqlperformanceblog.com/

  7. Ovidiu
    Member
    Posted 14 years ago #

    for mysql:

    check these tools: http://hackmysql.com/MySQLreport and http://wiki.mysqltuner.com/MySQLTuner but both don't give any advice concerning failed connections.

    will get back to you with log files.

  8. tdjcbe
    Member
    Posted 14 years ago #

    Well, if you have a good server you won't need many tweaks done for Apache/MySQL/PHP.

    Don't let those who provide paid support for MySQL hear you. :)

    Thanks for the tools by the way. Haven't seen those before.

  9. andrea_r
    Moderator
    Posted 14 years ago #

    This is good too:
    http://kb.mediatemple.net/questions/246/(dv)+HOWTO%3A+Basic+Apache+performance+tuning+(httpd)
    Applies to any Apache setup.

    Also, been playing with Nginx lately. *sweet*

  10. cafespain
    Member
    Posted 14 years ago #

    andrea_r: Nginx going on new slice (when I have time to build it), it'll be a good chance to test it against Litespeed - though I will miss a web based configuration system.

  11. Ovidiu
    Member
    Posted 14 years ago #

    @VentureMaker:

    what log files would be interesting?
    here is an excerpt from my my.cnf:

    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    #log = /var/log/mysql.log
    #log = /var/log/mysql/mysql.log
    #
    # Error logging goes to syslog. This is a Debian improvement :)
    #
    # Here you can see queries with especially long duration
    log-slow-queries = /var/log/mysql/mysql-slow.log
    #
    # The following can be used as easy to replay backup logs or for replication.
    #server-id = 1
    # log-bin = /var/log/mysql/mysql-bin.log
    # See /etc/mysql/debian-log-rotate.conf for the number of files kept.
    # max_binlog_size = 104857600
    #binlog-do-db = include_database_name
    #binlog-ignore-db = include_database_name

    I have enabled only the slow queries logging. shall I also enable this one:

    log = /var/log/mysql.log

    ? doing a cat /var/log/syslog | grep mysql doesn't show anything.

  12. VentureMaker
    Member
    Posted 14 years ago #

    Ovidiu, slow queries logging should be enabled all the time :) It won't eat many server resources, but it's extremely useful.

    And yes, enable mysql.log for some time. It's a 'killer' on a loaded server, but useful to debug your problem with failed attempts.

  13. Ovidiu
    Member
    Posted 14 years ago #

    @VentureMaker
    logging has been running for a couple of days now, what should I look for in the log files? They are huge so randomly browsing around is pointless :-(

  14. VentureMaker
    Member
    Posted 14 years ago #

    OK, put them somewhere I can get them from :)

  15. Ovidiu
    Member
    Posted 14 years ago #

    please email me your contact details to ovizii {at} zice [dot] ro

  16. VentureMaker
    Member
    Posted 14 years ago #

    Dropped you a line.

  17. Ovidiu
    Member
    Posted 13 years ago #

    guys, can anyone name some possible reasons for my mysql to have 7.56% Failed attempts?

    Connections  	ø per hour  	%
    max. concurrent connections 	11 	--- 	---
    Failed attempts 	6,989 	61.98 	7.56%
    Aborted 	12 	0.00 k 	0.01%
    Total 	92 k 	819.58 	100.00%

    I tried increasing all timeout and wait variables I might have possibly modified to no avail. I am at a loss. What should I look for in the logs, would there be anything that could point me in the right direction?

About this Topic