MySQL 4.0.12 has been released as stable, a vast improvement over any of its predecessors. This how-to outlines some of the basic steps necessary to upgrade successfully to MySQL 4.0.12 from 3.23, and how to optimize MySQL 4.0.12 after installing it on your server. If you have already upgraded, you should have noticed a great improvement in speed and performance. MySQL 4.0.xx coupled with phpMyAdmin 2.4.0 makes it a nice transition that our users have really appreciated. Performance has increased dramatically and our system loads are way down compared to other versions.

Pros and cons: Vast improvement in speed and performance, on the downside, we have noted slight incompatibilities with boards such as Invisions and phpbb. One site in particular that relies on Invisions did experience a problem where one of the db tables suddenly locked up. We spent the latter part of 1hr trying to correct the problem. By entering the site though phpMyAdmin, we were able to repair the table. Some users have complained about table lockups especially amongst Invision board users, and a repair of the table has corrected the problem so far. We have only experienced one such case among many sites. We don’t know if this was just a coincidence or not but if your going to upgrade, you should consider some of the facts before doing so.

After installing MySQL 4.0.12 there are some very important configuration changes that need to be performed and I will go through those in this HOWTO. Don’t even think about upgrading to 4.0.12 if your intention is to go back a version if you decide you don’t like it. You cannot go back a version if you get into trouble because the basic structure of MySQL 4.0.12 is vastly different then 3.23. Doing so without doing it properly could render all your databases useless. There is a way to go back a version however its really a big deal and this HOWTO does not cover that.

The first action we need to perform after installation of MySQL 4.0.12, is to update and add the new privileges and features to the MySQL privilege tables. As many saw with the installation of MySQL 4.0.12 on cPanel, there was a problem after the upgrade where all users on the server were able to view everyone else’s databases though phpMyAdmin. This could be have been a security issue in itself, and occurred because so many didn’t update the privileges tables after installation.

Log in to your box as root and execute the following command;

mysql_fix_privilege_tables

This command will upgrade the MySQL privilege tables and set all permissions properly. Ignore the errors and warnings. Those are normal and you will see these messages after executing the command.

Next, you should optimize all your databases. If you have never done this before, now is the time. This command will go through all your databases and optimize all your database tables for optimal performance;

mysqlcheck -o -u root -p --all-databases

Provide the root password when prompted then wait till optimization completes.

Its time to visit my.cnf. For best optimization, it is recommended that you upgrade your existing STARUP SQL VARIABLES in my.cnf with the new SQL VARIABLE names that have been added with this release. You don’t have to do that if you dont want however by not doing so, the old names still work in MySQL 4.0, but are deprecated. Why not spend a few minutes changing those? The following SQL VARIABLES have changed with this release;

myisam_bulk_insert_tree_size changed to bulk_insert_buffer_size
query_cache_startup_type change to query_cache_type
record_buffer changed to read_buffer_size
record_rnd_buffer changed to read_rnd_buffer_size
sort_buffer changed to sort_buffer_size
warnings changed to log-warnings
err-log changed to --log-error (for mysqld_safe)

SQL_BIG_TABLES changed to BIG_TABLES
SQL_LOW_PRIORITY_UPDATES changed to LOW_PRIORITY_UPDATES
SQL_MAX_JOIN_SIZE changed to MAX_JOIN_SIZE
SQL_QUERY_CACHE_TYPE changed to QUERY_CACHE_TYPE

Most of us at Rackshack will not be using the second set of SQL variables, not so as much as the first set.

Pico or Vi /etc/my.cnf

Look for any OLD SQL variable names in your my.cnf, compare and change them to reflect the new variable names. In my set, I found i had to change:
record_buffer to read_buffer_size
sort_buffer to sort_buffer_size.
You may find others that need changing in your configuration.

The most important addition for optimal performance is the addition of three new SQL variables. I HIGHLY recommend that you add them to my.cnf if your running any type of busy server. Those SQL variables are;

query_cache_limit
query_cache_size
query_cache_type

These additions have dropped my server loads dramatically. You can look up the meaning of these variable on the MySQL site and i will not attempt to explain them here. All i know is they work wonders.

[mysqld]
skip-innodb
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

Continued .....

Continued ...

Add it under [mysqld] as indicated above.

One other change which should be done to your my.cnf file is the removal of set-variable.

Find all occurances of set-variable and remove it from my.cnf. Its not needed!

Here is my copy of my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2 <--- Try 4 for dual pentiums
myisam_sort_buffer_size=64M
log-bin
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid <-- Not necessary
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

This may be good starting point for you if you dont have your my.cnf setup. You can try mine out on your server or modify it to suit your own server and memory requirements.

Once you have made all of the above changes its time to save the file and restart MySQL. I recommend that you view your mysqld.log file after restarting MySQL to make sure there are no errors being logged.

I highly recommend that everyone view this link for more information upgrading from version 3.23 to 4.0.

http://www.mysql.com/doc/en/Upgrading-from-3.23.html

I have certainly not covered everthing and there may be other information here that may benefit you. Depending on the type of database you host there are additional commands to run for innodb type db's and other optimization methods to consider in preparation for the release of MySQL 4.1 and 5.0.

Happy SQL'ing!

Hello,

great HOW TO .

However please can you tell me for what system
do you suggest the mysql config file above ?

Suppose a Pentium 4 server with 100 users , 1 GB RAM
and 512 Swap . The config file suggested above it
still ok ?

The configuration above should be just fine. If you find your loads are too high you may adjust the above config. Give it a whirl. I am also running the above configuration on a number of P4's.

*BUMP* people are asking questions about their priv tables that are clearly outlined here so by bumping this im hoping they will see it!

Some thoughts ...

I personally suggest the following my.cnf changes for people with a single-processor server and 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

For people with 1 GB ram:

thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
thread_concurrency=2


Larger read buffer sizes are more wasteful than helpful. Under your settings, each mysql client will use 6 MB of ram. With just 80 clients and apache running, you will run out of physical memory on a 512MB server and start using swap. On a busy server, that's a one-way trip to 200+ load and a server reboot ticket.

Note that key_buffer is shared once among all clients, and a large value is recommended to improve performance on tables with unique keys (like a primary id number).

uhm ... I think that we need an HOW TO calculate those MB values ....

Those parameters depends on

number of cpu (1 or 2)
quantity of phisical RAM

Anyone can write an HOW TO calculate those values ?

After trying that config on a Cpanel 6.2 server - Celeron 1.3 ghz with 512 mb ram I received this:


030411 08:01:57 mysqld started
Unknown suffix ' ' used for variable 'thread_concurrency' (value '2 <--- Try 4 for dual pentiums')
/usr/sbin/mysqld: Error while setting value '2 <--- Try 4 for dual pentiums' to 'thread_concurrency'
030411 08:01:58 mysqld ended


in mysql.log



Quick edit of my.cnf and I removed the <--- Try for dual pentiums

Thanks for the tweaking tips!

Its thread_concurrency=2 not

'thread_concurrency' (value '2 <--- Try 4 for dual pentiums')

Those were only commens i put in the file. I though you knew that you are supposed to remove comment.

I have been trying every HowTo I can find to try and fix a problem one of my clients is having. I just tried aussie's my.cnf on my P4 2gig with 1.5 megs and mysql failed to restart.

I removed the entries and it restarted.


:confused:

Thanks aussie. Put those settings in my my.cnf and things sped up a good bit....

Now I need to figure out how to push images a little faster. On to Apache searching I guess....

The problem could be anything as simple and a typo or invalid startup variable. What does you /var/log/mysql.log say? Have you looked? Your problem is mysql was not shutdown properly therefore the pids left over and still running did not allow mysql to restart after your changes. Also, you should shutdown mysql from the command line, not from WHM because WHM does not shut it down but rather tries to restart it and thats not what you want to do. After you make the changes to my.cnf you need to shutdown mysql from the command line /etc/rc.d/init.d/mysql stop then you need to execute killall -9 mysqld to make sure there are no old processes running. Do a ps aux to make sure all your [mysqld] prcess have been removed. If there are old processes running, your never going to be able to restart mysql with the new changes. Then you need to load your mysql log file so that you can monitor the restart and watch for errors. If you execute tail -f /var/log/mysqld.log you can watch the log in real time while you restart mysql from WHM. The tail command loads the log file and places it to the last record so you can watch the status of error msgs during the restart. While watching the log restart mysql from WHM. If there are any errors then you will know what those are and you can correct them in my.cnf. If there are other reasons why it fails to restart, you will also know the reasons why. To exit the log file type cntrl-x or [cntrl-c[/b] whichever works.

Works great!

On an ENSIM 3.1.10 box, is this file known as /etc/my.cnf.admin_appl

or do I need a separate /etc/my.cnf

On Ensim its also in /etc/my.cnf.

Sorry for the dealy in responding. Exim went south for a few days and although I couldn't figure out why, it magically healed itself. (may have had something to do with my trying to use your RBL HowTo )

Anyway, now that my clients have stopped screaming at me, I'll have another run at it.

What would you suggest for someone with 256 MB ram?

Excellent 'How to Optimize' for MySQL4.

Thanks aussie.

mysql_fix_privilege_tables



This scripts updates the mysql.user, mysql.db, mysql.host and the
mysql.func tables to MySQL 3.22.14 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION or want to use the more secure passwords in 3.23

If you get 'Access denied' errors, you should run this script again
and give the MySQL root user password as an argument!
Converting all privilege tables to MyISAM format
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

If your tables are already up to date or partially up to date you will
get some warnings about 'Duplicated column name'. You can safely ignore these!
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Creating Grant Alter and Index privileges if they don't exists
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Adding columns needed by GRANT .. REQUIRE (openssl)
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Creating the new table and column privilege tables
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
Changing name of columns_priv.Type -> columns_priv.Column_priv
You can ignore any Unknown column errors from this
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Fixing the func table
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Adding new fields used by MySQL 4.0.2 to the privilege tables
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)



PLEASE HELP

You need to add your root password to the end of the script as follows:

mysql_fix_privilege_tables 'password'

I went to my /etc/my.cnf and the only thing in there at ALL is:

[mysqld]
set-variable = max_connections=500

Nothing else at all. Is that normal? Can I just add your suggestions?

That normal, its what cPanel supplies and nothing more. Go ahead and optimize it.

Any number change suggestions for 1.5 gig of ram?

Thanks Aussie!

Just a quick note, no need to have both "record_buffer" and "read_buffer_size" variables. read_buffer_size replaced record_buffer, so ONLY use read_buffer_size in your my.cnf.

I was reccommended the following my.cnf - this is on a dual xeon setup with 2 gig of ram but you shouldn't need to change the values if you have only a gig of Ram



MYSQL performance is now out of this world.

HTH

Well.. your sort,read and join buffers are too big.. if you had alot of mysql processes going you would suck up all your ram so I'd recomend you change those to..

join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M

and increase the key buffer..

key_buffer = 150M


I have the same server, with 2 gigs of ram also. I use those settings and mysql performance is still great. Now if you don't have a ton of mysql usage on that server it will be fine, but you are allocating a ton of memory to each mysql client. My server has tons of concurrent mysql connections, if I had it set as yours was, I'd run out of ram and start swapping. So be carefull when allocating so much memory to those buffers.

I'm using less than a quarter of real memory and doing about 18 queries a second:

http://www.cpfc.org/php/mysql.php

I think I'll keep the bufferes the way they are.

Well I reduced those 3 buffers (primarilily the read buffer) on one server that was going out of control at peak. After reducing the read and sort buffers the server has not gone out of control, and all the sites load faster. Anyway though, I am just throwing this out there in case anyone runs into any issues, so they know whats eating up all their ram. If yours is running well, then I'm gald for you and hopes it stays that way lol.. I'm not out to prove anyone wrong

btw, what script is that you use for mysql info, very detailed, i'd like to get a copy.

[PHP]l

 

// stat.php - Last modified: 03/18/02  13:10:00  P.Kelly 



/**************************************************  ************/ 

/* Set-up default parameters                                  */ 

/* Override them using get vars eg. stats.php?ns=0              */ 

/* Or override the constant settings below                       */ 

/**************************************************  ************/ 

$ns  = isset($ns)  ? $ns  : 1; // allow netstat 

$htp = isset($htp) ? $htp : 1; // allow http process count 

$msp = isset($msp) ? $msp : 1; // allow mysql process count 

$tp  = isset($tp)  ? $tp  : 1; // allow top information 

$mss = isset($mss) ? $mss : 1; // allow mysql status information 

/**************************************************  ************/ 



/*** 

  Configure statistic gathering programs; specify path if necessary 

***/ 



$netstat = 'netstat -a -n|grep -E "^(tcp)"| cut -c 68-|sort|uniq -c|sort -n'; 

$http = "ps -auxww | grep -c http"; 

$mysql = "ps -auxww | grep -c mysql"; 

$top = "top"; 

$uptime = "uptime"; 

$free = "free"; 

$mysqladmin = "mysqladmin"; 

$mysqllogin = "-uusername -ppassword"; 





function SecToDHMS($secs) { 

  $showSecs = $uptime == 0; 

  $s = ""; 

  if ($secs >= 86400) { 

    $n = (int) ($secs/86400); 

    $s = $n." day".($n>1?"s":""); 

    $secs %= 86400; 

  } 

  if ($secs >= 3600) { 

    $n = (int) ($secs/3600); 

    $s .= " ".$n." hr".($n>1?"s":""); 

    $secs %= 3600; 

  } 

  if ($secs >= 60) { 

    $n = (int) ($secs/60); 

    $s .= " ".$n." min".($n>1?"s":""); 

    $secs %= 60; 

  } 

  if ($secs || $showSecs == 1) 

    $s .= " ".$secs." sec".($secs>1?"s":""); 

  return trim ($s); 

} 







/*** 

  Display current date time 

***/ 

system ("date"); 

echo "nn"; 









/*** 

  Display "top" information if available; otherwise display similar information 

***/ 

if($tp) 

{ 

    if (isset ($top) && $top != "") 

    { 

        $fp = popen ("$top -i -n 1 -b", "r"); 

        $buffer = ""; 

        while (!feof ($fp) && $buffer == "")  // skip any blank header lines 

        $buffer = rtrim (fgets ($fp, 1024)); 



        if (!feof ($fp) && $buffer != "") 

        { 

            echo "$buffern"; 

            while (!feof ($fp) && $buffer != "") 

            {  // display all except CPU details 

                $buffer = rtrim (fgets ($fp, 1024)); 

                if (substr ($buffer, 0, 11) != "CPU states:") 

                    echo "$buffern"; 

            } 

        } 

        // display details; filter to show only "nobody" and "mysql" users excluding "top" process 

        while (!feof ($fp)) 

        { 

            $buffer = rtrim (fgets ($fp, 1024)); 

            $user = rtrim (substr ($buffer, 6, 9)); 

            if ($user == "USER") 

                $header = $buffer; 

            if (($user == "nobody" || $user == "mysql") && substr ($buffer, 67, 3) != "top") 

            { 

                if ($header != "") 

                { 

                    echo "$headern"; 

                    $header = ""; 

                } 

                echo "$buffern"; 

            } 

        } 

        pclose ($fp); 

        echo "n"; 

        } 

        else { 

        if (isset ($uptime) && $uptime != "") 

            system ("$uptime"); 

        if (isset ($free) && $free != "") 

            system ("$free"); 

        echo "n"; 

    } 

} 









/*** 

  Display current number of http processes 

***/ 

if($htp) 

{ 

echo "nHttp processes currently running = "; 

system ($http); 

} 





/*** 

  Display current number of http processes 

***/ 

if($msp) 

{ 

echo "Mysql processes currently running = "; 

system ($mysql); 

} 



/*** 

  Display current number of http processes 

***/ 

if($ns) 

{ 

echo "nNetstat information summaryn"; 

system ($netstat); 

echo "n"; 

} 







/*** 

  Display MySQL extended-status (with special metric calculations to follow) 

***/ 

if($mss) 

{ 

    if (isset($mysqladmin) && $mysqladmin != "") { 



    // Get MySql Variables. 



    exec("$mysqladmin $mysqllogin variables",$mysqlrawvar); 

    for($i = 0;$i < count($mysqlrawvar);$i++) 

        { 

        $line = array(); 

        $line = explode("|",$mysqlrawvar[$i]); 

        if(count($line) > 2); $mysqlvar[trim($line[1])] = trim($line[2]); 

    //    echo $mysqlrawvar[$i]."n"; 

        } 



    //  echo "nnMySql Variables Arraynn"; 

    //  while (list ($key, $val) = each ($mysqlvar))        echo "$key => $val
"; 



    // Get MySql Status. 



    exec("$mysqladmin $mysqllogin extended-status",$mysqlrawstat); 

    for($i = 0;$i < count($mysqlrawstat);$i++) 

        { 

        $line = array(); 

        $line = explode("|",$mysqlrawstat[$i]); 

        if(count($line) > 2); 

            { 

            $param = trim($line[1]); 

            $var   = trim($line[2]); 

            $mysqlstat[$param] = $var; 

            $buffer = trim($mysqlrawstat[$i]); 



            if ($param == "Key_blocks_used" && isset ($mysqlvar[key_buffer])) { 

              $temp = sprintf ("%.0f", $var/($mysqlvar["key_buffer"]>>10)*100); 

              echo $buffer," Approx. $temp% of key_buffer in use"; 

            } 

            else if ($param == "Max_used_connections" && $var == $mysqlvar["max_connections"]) 

              echo $buffer," Max. connections reachedn"; 

            else if ($param == "Open_tables" && isset ($mysqlvar["table_cache"])) { 

              $temp = sprintf("%.0f", $var/$mysqlvar["table_cache"]*100); 

              echo $buffer," $temp% of table_cache in use"; 

            } 

            else if ($param == "Slow_queries" && isset ($mysqlvar["long_query_time"])) 

              echo $buffer." (execution time > ".$mysqlvar["long_query_time"]." secs)"; 

            else if ($param == "Uptime") 

              echo $buffer." ".SecToDHMS($var); 

            else 

              echo $buffer; 



            echo "n"; 

            } 

        } 





    //  echo "nnMySql Extended Status Arraynn"; 

    //  while (list ($key, $val) = each ($mysqlstat))        echo "$key => $val
"; 



      echo "nn"; 

      if (isset ($mysqlstat[Key_read_requests]) && isset ($mysqlstat[Key_reads]) && $mysqlstat[Key_read_requests]) { 

        $temp = sprintf ("%.6f", $mysqlstat[Key_reads]/$mysqlstat[Key_read_requests]); 

        echo "Key Reads/Key Read Requests = $temp  (Cache hit = ",100-$temp,"%)n"; 

      } 

      if (isset ($mysqlstat[Key_write_requests]) && isset ($mysqlstat[Key_writes]) && $mysqlstat[Key_write_requests]) { 

        $temp = sprintf ("%.6f", $mysqlstat[Key_writes]/$mysqlstat[Key_write_requests]); 

        echo "Key Writes/Key Write Requests = $tempn"; 

      } 

      if (isset ($mysqlstat[Uptime])) { 

        $uptime = $mysqlstat[Uptime]; 

        if ($uptime) { 

          if (isset ($mysqlstat[Connections])) { 

            $temp = $mysqlstat[Connections]/$uptime; 

            echo "Connections/second = ",sprintf ("%.3f", $temp), " (/hour = ",sprintf ("%.3f", $temp*3600),")n"; 

          } 

          if (isset ($mysqlstat[Bytes_received])) { 

            $temp = ($mysqlstat[Bytes_received] >> 10)/$uptime; 

            echo "KB received/second = ",sprintf ("%.3f", $temp), " (/hour = ",sprintf ("%.3f", $temp*3600),")n"; 

          } 

          if (isset ($mysqlstat[Bytes_sent])) { 

            $temp = ($mysqlstat[Bytes_sent] >> 10)/$uptime; 

            echo "KB sent/second = ",sprintf ("%.3f", $temp), " (/hour = ",sprintf ("%.3f", $temp*3600),")n"; 

          } 

          if (isset ($mysqlstat[Created_tmp_tables])) { 

            $temp = $mysqlstat[Created_tmp_tables]/$uptime; 

            echo "Temporary Tables Created/second = ",sprintf ("%.3f", $temp)," (/hour = ",sprintf ("%.3f", $temp*3600),")n"; 

          } 

          if (isset ($mysqlstat[Opened_tables])) { 

            $temp = $mysqlstat[Opened_tables]/$uptime; 

            echo "Opened Tables/second = ",sprintf ("%.3f", $temp)," (/hour = ",sprintf ("%.3f", $temp*3600),")n"; 

          } 

          if (isset ($mysqlstat[Slow_queries])) { 

            $temp = $mysqlstat[Slow_queries]/$uptime; 

            echo "Slow Queries/second = ",sprintf ("%.3f", $temp)," (/hour = ",sprintf ("%.3f", $temp*3600),")n"; 

          } 

          if (isset ($mysqlstat[Questions])) { 

            echo "% of slow queries = ",sprintf ("%.3f%%", $mysqlstat[Slow_queries]/$mysqlstat[Questions]*100),"n"; 

            $temp = $mysqlstat[Questions]/$uptime; 

            echo "Queries/second = ",sprintf ("%.3f", $temp)," (/hour = ",sprintf ("%.3f", $temp*3600),")n"; 

          } 

          if (isset ($mysqlvar[flush_time]) && $mysqlvar[flush_time] != 0) 

            echo "Next automatic buffers flush in ",SecToDHMS($mysqlvar[flush_time]-($uptime % $mysqlvar[flush_time])); 



# work out how much is served from the Query cache



 if (isset ($mysqlstat[Qcache_hits])) { 

$total = $mysqlstat[Qcache_inserts]+$mysqlstat[Qcache_hits]+$mysqlstat[Qcache_not_cached]
; 

$percentage = ($mysqlstat[Qcache_hits]/$total)*100; 

echo "MySQL Query Cache hits = $mysqlstat[Qcache_hits]/$total(",sprintf ("%.0f",$percentage), "%)n"; 

} 









        } 

      } 

    } 

} 

?> 

[/PHP]

Looks like a gret scripts, but very little displays for me. I get this:

Sat May 31 16:31:15 EDT 2003


4:31pm up 14 days, 4:29, 2 users, load average: 0.01, 0.01, 0.00
90 processes: 89 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1548256K av, 1478288K used, 69968K free, 0K shrd, 101660K buff
Swap: 1020116K av, 19172K used, 1000944K free 1140608K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
23559 nobody 15 0 924 924 712 R 1.9 0.0 0:00 top


Http processes currently running = 13
Mysql processes currently running = 16

Netstat information summary
11 ESTABLISHED
13 TIME_WAIT
38 LISTEN

But that is it. No MySQL stuff.

Oh, and there are a couple of comment errors that needed to be fixed.

Any thoughts as to why the MySQL data is not showing up?

Thanks!

Jon

did you put you username and password in the script?

Uh........ duh......

Excellent script Dave#. Thanks.

I thought ensim and mysql 4.0 weren't compatible with one another. Am I mistaken?

Eeek, i tried every combination of possible passwords and could not get this script to display everthing.

$mysqladmin = "???";
$mysqllogin = "-uroot -pmypassword";

you need to leave the first line as is

$mysqladmin = "mysqladmin";
$mysqllogin = "-uusername -ppassword";

ic, thanks

I ran this script and tested my.cnf setup;

Key Reads/Key Read Requests = 0.068543 (Cache hit = 99.931457%)
Key Writes/Key Write Requests = 0.307692
Connections/second = 1.241 (/hour = 4467.016)
KB received/second = 0.990 (/hour = 3562.304)
KB sent/second = 210.670 (/hour = 758412.565)
Temporary Tables Created/second = 0.068 (/hour = 245.026)
Opened Tables/second = 0.393 (/hour = 1413.613)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 15.534 (/hour = 55922.513)
MySQL Query Cache hits = 433/1054(41%)

But then I'm not running a dual on this box, nice speed!

those figures are mental - really high KB sent/second but low % query cache . . . are you serving images ot blobs or something other than text?

Some of our mysql db's serve mega images. One forum comes to mind. They have images everywhere. Every avatar and every footer contains some major graphics, or animated graphic, per msg. This box has 150 sites on it at the moment. The only thing i see increasing here is;

| Open_tables | 387 | 33% of table_cache in use

Yes, the KB sent is pretty high. Almost seems inaccurate.

Mon Jun 2 16:05:23 PDT 2003


4:05pm up 20 days, 17:59, 0 users, load average: 0.66, 0.71, 0.69
179 processes: 175 sleeping, 3 running, 1 zombie, 0 stopped
Mem: 1031244K av, 929812K used, 101432K free, 0K shrd, 71840K buff
Swap: 1024120K av, 60616K used, 963504K free 528288K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
23364 nobody 16 0 1100 1096 816 R 12.0 0.1 0:00 top


Http processes currently running = 67
Mysql processes currently running = 19

Netstat information summary
1 CLOSE_WAIT
1 LAST_ACK
3 FIN_WAIT1
8 CLOSING
9 TIME_WAIT
38 LISTEN
70 ESTABLISHED

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 76 |
| Aborted_connects | 0 |
| Bytes_received | 9811417 |
| Bytes_sent | 4267385009 |
| Com_admin_commands | 193 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 16517 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 9 |
| Com_delete | 10842 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 1524 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 496 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 56 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 2 |
| Com_select | 38780 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 4 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 24 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 19 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 16 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 10518 |
| Connections | 14591 |
| Created_tmp_disk_tables | 378 |
| Created_tmp_tables | 866 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 1093 |
| Handler_read_first | 3574 |
| Handler_read_key | 400551 |
| Handler_read_next | 2280319 |
| Handler_read_prev | 13279 |
| Handler_read_rnd | 36644 |
| Handler_read_rnd_next | 4188380 |
| Handler_rollback | 0 |
| Handler_update | 97583 |
| Handler_write | 138462 |
| Key_blocks_used | 8364 |
| Key_read_requests | 1474318 |
| Key_reads | 7008 |
| Key_write_requests | 121010 |
| Key_writes | 4541 |
| Max_used_connections | 12 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 676 | 45% of table_cache in use
| Open_files | 1344 |
| Open_streams | 0 |
| Opened_tables | 1253 |
| Questions | 128098 |
| Qcache_queries_in_cache | 2447 |
| Qcache_inserts | 38632 |
| Qcache_hits | 33799 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2129 |
| Qcache_free_memory | 17779512 |
| Qcache_free_blocks | 520 |
| Qcache_total_blocks | 6263 |
| Rpl_status | NULL |
| Select_full_join | 47 |
| Select_full_range_join | 97 |
| Select_range | 371 |
| Select_range_check | 0 |
| Select_scan | 12517 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 1019 |
| Sort_rows | 36761 |
| Sort_scan | 1032 |
| Table_locks_immediate | 67363 |
| Table_locks_waited | 25 |
| Threads_cached | 12 |
| Threads_created | 13 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 6926 | 1 hr 55 mins 26 secs
+--------------------------+------------+


Key Reads/Key Read Requests = 0.004753 (Cache hit = 99.995247%)
Key Writes/Key Write Requests = 0.037526
Connections/second = 2.107 (/hour = 7584.118)
KB received/second = 1.383 (/hour = 4980.017)
KB sent/second = 302.794 (/hour = 1090058.273)
Temporary Tables Created/second = 0.125 (/hour = 450.130)
Opened Tables/second = 0.181 (/hour = 651.285)
Slow Queries/second = 0.000 (/hour = 0.000)
% of slow queries = 0.000%
Queries/second = 18.495 (/hour = 66582.847)
MySQL Query Cache hits = 33799/74560(45%)

Howcome your table cache is still at 4%? After 8hrs mine reached 100%.

I think thats because I reuse the same tables all the time ie3 there oisn't that many different tables being read into cache

After installing MySQL 4, the mysqld process no longer runs as 'nice'

i.e. it shows up with the S flag on ps, not like before with SN flags:

mysql 5867 0.0 3.2 122200 67420 pts/0 S 03:35 0:13 /usr/sbin/mysqld

Anyone knows to make it run as nice like before?

this is cel 1,7 1 gb ram

is it ok ?

yes

mtop is good for watching your sql database. Output looks something like this (and you can watch it in real time):

127.0.0.1 mysqld 4.0.13 up 0 day(s), 23:40 hrs
12 threads: 1 running, 11 cached. Queries/slow: 22.3M/4 Cache Hit: 99.99%
Opened tables: 299 RRN: 144.6M TLW: 1.1M SFJ: 28 SMP: 0 QPS: 218

ID USER HOST DB TIME COMMAND STATE INFO
73 DELAYED tmp 3 Delayed Waiting for


................


If you haven't already, I highly recommend using phpmyadmin..

This MySQL server has been running for 0 days, 23 hours, 40 minutes and 57 seconds. It started up on Jul 02, 2003 at 05:40 PM.
Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic ø per hour
Received 3,406 MB 147,271 KB
Sent 2,579 MB 111,521 KB
Total 5,985 MB 258,793 KB
Connections ø per hour %
Failed attempts 0 0.00 0.00 %
Aborted 9,304 392.86 25.04 %
Total 37,158 1,569.01 100,00 %

Total ø per hour ø per minute ø per second
23,350,299 985,972.72 16,432.88 273.88

aussie, I'm curious why you suggest using the log-bin directive, in your my.cnf? I admit, I blindly used your provided my.cnf file, tweaking a few of the caching directives, but I never looked long and hard at the rest of the options. log-bin is a neat directive, if you are involved in mysql replication, but for the majority of users reading this guide, I believe it is unnecessary.

Note, I found this unnecessary option, while spelunking around /var/lib/mysql, and discovering a 1 gigabyte file full of these UPDATE transactions! The CPU time spent writing to this file could have been used for other tasks.

Great guide, I just want to shed some light on that one option.

Blacks

Sorry for jumping in, but I've been reading everything I could get on tweaking mysql for almost two years now, and even Mysql A.B. recommends using binary logging for performance. I believe it has to do with the amount of work involved in converting to text, per query.

The only file i got is /etc/my.cnf.admin_appl
when i do a locate my.cnf i only see this file
Do i have to make a /etc/my.cnf myself or do i need to edit to file located /etc/my.cnf.admin_appl

Thanks

Flemming