Find the answer to your Linux question:
Results 1 to 2 of 2
Please use the below mentined script for MySQL tunning and optimization: http://www.day32.com/MySQL/tuning-primer.sh This script takes information from "SHOW STATUS LIKE..." and "SHOW VARIABLES LIKE..." to produce sane recomendations for tuning ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Jun 2006
    Location
    (.)
    Posts
    69

    MySQL tunning and optimization


    Please use the below mentined script for MySQL tunning and optimization:

    http://www.day32.com/MySQL/tuning-primer.sh

    This script takes information from "SHOW STATUS LIKE..." and "SHOW VARIABLES LIKE..."
    to produce sane recomendations for tuning server variables.
    It is compatable with all versions of MySQL 3.23 and higher (including 5.1).

    # Currently it handles recomendations for the following: Slow Query Log
    # Max Connections
    # Worker Threads
    # Key Buffer
    # Query Cache
    # Sort Buffer
    # Joins
    # Temp Tables
    # Table (Open & Definition) Cache
    # Table Locking
    # Table Scans (read_buffer)
    # Innodb Status

    Thanks,

  2. #2
    Linux Newbie
    Join Date
    Dec 2006
    Posts
    105
    Please refer following discussion to optimise MySQL to handle a high traffic website.

    Tim
    CTO, Digital Content Solutions

    MySQL settings, many concurrent users.
    I run a site for a client that has over 3000 users that log in for about 5-7 hours
    per day each. So, at peak times, we have to handle about 2000 concurrent users.
    When configured correctly, PHP and MySQL can handle this load wonderfully on fairly
    cheap Intel architecture. First off, hardware.

    1) It is better to have 2 separate servers for Apache/PHP and MySQL with the Linux of your choice.
    2) Try not to run too much else on either box; leave the resources for Apache/PHP and MySQL.

    Here are the specs on each box in my config:
    1) Apache/PHP: Pentium 3, 600 MHZ, 512 megs ram.
    2) MySQL: Dual Pentium 3, 750 MHZ (1500 MHZ total), 2 gigs ram.

    The reason for this configuration is that it is very database heavy;
    it is a members only web site with username and password required for login, fully personalized.
    It is an online school, so each student has their suite of tools for attending school,
    their courses, report cards, time logging, and much more. Teachers have web based tools to create
    their courses, including lessons, text to speech audio, and more.

    1) PHP coding: be sure to use persistent connections!
    Opening and closing a connection from your Apache/PHP box to your MySQL box is a very heavy load.
    By using persistent connections, a high capacity site will open connections and share them to
    exchange data rather than opening a connection on each page request, sending the data, then closing,
    and repeating that process at least once for every user click!
    Be sure to use "mysql_pconnect" instead of "mysql_connect" and also that appropriate changes are made
    in "php.ini" or overridden by using the command "ini_set".
    You can find more documentation on doing this at the php web site.

    2) Apache set up ("httpd.conf"): I've changed these various settings, and played with them until they
    seem to keep the most "idle %" reported in "top".
    MinSpareServers 10
    MaxSpareServers 20
    StartServers 70
    MaxClients 255

    3) Mysql set up ("my.cnf"). The MySQL config file, my.cnf.
    Here is what to add under the [mysqld] heading.
    The two lines, "max_connections" and "max_user_connections" are where the magic happens.
    Since your Apache/PHP box is connecting to MySQL, it appears as a single user.
    MySQL defaults to 1 max connection, with 1 max connection per user. The following lines make
    it so your Apache/PHP box can connect to your MySQL box up to the number you have set "MaxClients"
    to in the Apache config above. By using persistent connections, you can pretty much get Apache up,
    have it connect to MySQL upon start up, and just use the persistent connections to pass data between
    the two boxes rather than opening connections. Its much more efficient that way.
    set-variable = max_connections = 300
    (this must be higher than "MaxClients" set in Apache, or you won't fully maximize use)
    set-variable = max_user_connections = 300
    set-variable = table_cache=1200
    (max number of tables in join multiplied by max_user_connections)

    A few other MySQL tunings:
    set-variable = max_allowed_packet=1M (sanity check to stop runaway queries)
    set-variable = max_connect_errors=999999
    (stop mysqld from shutting down if there are connect errors - this defaults to 1 error and mysqld stops!)
    Unix, Linux tips...

Posting Permissions

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