Cool, I like this info. (especially key_buffer_size stuff)
However, I was running my numbers against what you said would be good. And this is
what I came up with...
The Key_reads/Key_read_request = 0.002 (much less than you suggest so that is good...
I think)
However this one worries me a bit...
Key_write/Key_write_requests = 0.087 (way lower than 1)
Does this mean that I am sending WAY to many un-needed UPDATE requests?
----- Original Message -----
From: "Jim Beigel" <[EMAIL PROTECTED]>
To: "Ryan Hadley" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, February 07, 2001 6:32 AM
Subject: RE: Performance issues.
> Ryan:
>
> 1.) ...WHERE word LIKE '%word%' will never be fast.
>
> 2.) Since you said you have indexed the field, if you can limit your
> searches to ...WHERE word LIKE 'word%', then you might want to look at
> indexing on a subset of the field if it's a big one.
>
> 3.) You will get better performance if you perform routine maintenance on
> your tables. Try running OPTIMIZE TABLE .... Or, if you can bring down
> mysqld, try the myisamchk (-a -S) equivalents.
>
> 4.) Lastly, in addition to the other comments, I'd take a look at changing
> some of the parameters in my.cnf. Here's a pertinent snip from the manual:
>
> join_buffer_size
> ----------------
> The size of the buffer that is used for full joins (joins that do not use
> indexes). The buffer is allocated one time for each full join between two
> tables. Increase this value to get a faster full join when adding indexes is
> not possible. (Normally the best way to get fast joins is to add indexes.)
>
> key_buffer_size
> ---------------
> Index blocks are buffered and are shared by all threads. key_buffer_size is
> the size of the buffer used for index blocks. Increase this to get better
> index handling (for all reads and multiple writes) to as much as you can
> afford; 64M on a 256M machine that mainly runs MySQL is quite common. If
> you, however, make this too big (more than 50% of your total memory?) your
> system may start to page and become REALLY slow. Remember that because MySQL
> does not cache data read, that you will have to leave some room for the OS
> filesystem cache. You can check the performance of the key buffer by doing
> show status and examine the variables Key_read_requests, Key_reads,
> Key_write_requests, and Key_writes. The Key_reads/Key_read_request ratio
> should normally be < 0.01. The Key_write/Key_write_requests is usually near
> 1 if you are using mostly updates/deletes but may be much smaller if you
> tend to do updates that affect many at the same time or if you are using
> delay_key_write. See section 7.28 SHOW Syntax (Get Information About Tables,
> Columns,...). To get even more speed when writing many rows at the same
> time, use LOCK TABLES. See section 7.32 LOCK TABLES/UNLOCK TABLES Syntax.
>
>
>
>
> Jim Beigel
> Director of Software Development
> Alabanza Corporation
> [EMAIL PROTECTED]
> 740-282-2971 x.5205
>
> > -----Original Message-----
> > From: Ryan Hadley [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, February 06, 2001 6:09 PM
> > To: [EMAIL PROTECTED]
> > Subject: RE: Performance issues.
> >
> >
> > Thanks for the quick response.
> >
> > The response time is slow... and the mysqld processes are what is
> > hogging up
> > the system.
> >
> > We do have indexes on the fields, but from what I understand,
> > when you use a
> > "LIKE" statement, it rarely uses an index.
> >
> > -Ryan
> >
> > -----Original Message-----
> > From: Kent Hoover [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, February 06, 2001 5:42 PM
> > To: [EMAIL PROTECTED]
> > Subject: Performance issues.
> >
> >
> > Ryan:
> >
> > If your response time for this query is slow, it is likely that an INDEX
> > will help
> > you. (Read about CREATE INDEX in the MySQL manual.
> >
> > If you don't already have an INDEX on the keyWord column, create one.
> >
> > If you can induce your customer/users to type more characters, that
> > would help.
> > WHERE keyWord LIKE 'salomi%' is much better for you than
> > WHERE keyWord LIKE 's%' .
> >
> > NOTE, that if your response time to this query is good, there could be
> > something
> > other than MySQL running on your machine that is sucking your CPU dry.
> > You might be able to spot it by running 'top' 'ps -ef' or whatever
> > command is
> > available for your machine.
> >
> > Cheers,
> >
> > Kent Hoover
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <[EMAIL PROTECTED]>
> > To unsubscribe, e-mail
> > <[EMAIL PROTECTED]>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
