[Jax.PM] MySQL ?

[Aaron Johnson]

On Thu, 2002-10-17 at 10:45, William C (Bill) Jones wrote:
> [a jax.PM member posting]
> >> What values would you give
> >> 
> >> KEY_BUFFER=???
> >> TABLE_CACHE=???
> >> JOIN_BUFFER=???
> >> RECORD_BUFFER=???
> >> SORT_BUFFER=???
> >> MAX_CONNECTIONS=300
> >> TMP_TABLE_SIZE=???
> >> 
> >> Knowing you have 6GB RAM 6GB Swap and want to handle 300 connections and be
> >> very very fast (heck, I got 4 CPUs dedicated to this :/  :)
> > 
> > just from experience, you'll probably gain more performance
> > by concentrating on your table design, and how to handle
> > expensive queries, and table locking, than from the variables.
> 
> Unfortunately I had no design control over the system (Blackboard) - but was
> definitely interested in what other are doing, etc.
> 
> Maybe I should say I want to handle 250 httpd and 300-500 mysql connections
> per second (simultaneously -- whatever that means these days.)
> 
> This is what I am doing in production:
> KEY_BUFFER=128M
> TABLE_CACHE=1024
> JOIN_BUFFER=30M
> RECORD_BUFFER=4096k
> SORT_BUFFER=20M
> MAX_CONNECTIONS=300
> TMP_TABLE_SIZE=60M
> WAIT_TIMEOUT=31536000
> 
> 
> And Test:
> KEY_BUFFER=512M
> TABLE_CACHE=512
> JOIN_BUFFER=30M
> RECORD_BUFFER=4M
> SORT_BUFFER=20M
> MAX_CONNECTIONS=100
> TMP_TABLE_SIZE=100M
> WAIT_TIMEOUT=31536000
> 
> 
> Both are about the same speed - except the test server is a single CPU
> machine with 2GB RAM...
> 

How big are your tables?
How many tables do you have?
What type of queries are you doing?
Are you running under mod_perl?
If you are under mod_perl have you put Apache::DBI in your startup.pl?

If it is a CGI model app have you looked at
CGI::Speedyhttp://search.cpan.org/author/HORROCKS/CGI-SpeedyCGI-2.21/lib/CGI/SpeedyCGI.pm

I have used CGI::Speedy in a couple of apps and seen an improvement in
performance, but not sure if it would reuse DBI connection, most likely
they would be reestablished on each call so not sure how that would help
your MySQL specific question.

Aaron Johnson

> Does that help?
> -Bill-  :]
> _Sx____________________
>   ('>    iudicium ferat
>   //\   Have Computer -
>   v_/_    Will Hack...
> 
> 
> _______________________________________________
> Jacksonville-pm mailing list
> Jacksonville-pm at mail.pm.org
> http://mail.pm.org/mailman/listinfo/jacksonville-pm