MYSQL Ops

Sayian

Hi, im seeking some help getting mysql to run better

I run a site, that has a dedicated server, 3.2ghz HT p4, 2 GB of Ram, 80 GB hdd

Apache / Mysql / Red Hat Ent 3

I serve alot of static images, and run alot of SQL querys

Sometimes at night the load exceeds 14-17 and the server slows to a halt

I know about my.cnf, but when i open the file theirs only a cpl lines in their for the connection, i heard you could optimize this.

How, and how would i know what values to put in there?

Thanks guys for any assistance you could offer me

rincewind456

You could start here http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/

Sayian

Thanks, im looking over that now.

Sayian

The ANALYSE procedure can help you find the optimal types for a table: SELECT * FROM table_name PROCEDURE ANALYSE()

How could i run this from a script and print the results?? i dont have access to ssh right now

Sayian

INSERT and SELECT can run concurrently.

Could someone also provide me an example of what this means and how to use it?

rincewind456

Sayian wrote:
The ANALYSE procedure can help you find the optimal types for a table: SELECT * FROM table_name PROCEDURE ANALYSE()

How could i run this from a script and print the results?? i dont have access to ssh right now

Like so:

<?php
$dbname = 'xxxx';
$dbpass = 'xxxx';
$dbuser = 'xxxx';
$conn = mysql_connect( 'localhost', $dbuser, $dbpass );
if ( ! $conn ) die( "Couldn't connect to MySQL" );
mysql_select_db($dbname) or die ( "Couldn't open $dbname: ".mysql_error() );

$sql = "SELECT * FROM table_name PROCEDURE ANALYSE()";

$result =mysql_query($sql);
print '<table border="1" cellpadding="2">';

while ($row = mysql_fetch_assoc($result)){
	foreach ($row as $key => $value)
	print '<tr><td>'.$key.'</td><td>'.$value.'</td></tr>';
}
print '</table>';
?>

Sxooter

Yeah, the first rule of optimizing your database is to don't, the second rule is examine your queries with explain / analyze...

There are probably some simple fixes like indexing that will speed things right up.

rincewind456

Sayian wrote:
INSERT and SELECT can run concurrently.

Could someone also provide me an example of what this means and how to use it?

Here

Sayian

Thanks for all your help, i am running PROCEDURE ANALYSE on my tables right now to better the data type.

1 More question tho, are INDEX's supposed to be used for when you

SELECT col FROM table WHERE <- INDEX ->

In the example above, having an index on the WHERE clause would help things yes? Or when should index be used?

jayant

At the mysql prompt, type

SELECT @@global.thread_cache_size;

If the value returned is 0, you may want to change that value to something like 30 or 40.
Do not set it too high. 40 should be normally enough. Type the following at the mysql prompt

SET GLOBAL thread_cache_size=40;

Sayian

=/ i dont have access to ssh right now, but i wrote that down jayant! thanks