NEED HELP - Anyone Here a MySQL Expert?
 

See here:

http://www.vbulletin.com/forum/showthread.php?p=542231#post542231

and let me know if you have any suggestions...

-Wayne

Its been a while since I've used MySQL/PHP for anything, but when I was it was for systems with what I'd assume was higher query rates than even a fairly popular forum would generate.

Without knowing any more details about configuration of things, that really smells like a PHP issue, not a MySQL issue...

I always had bad luck with PHP+IIS on Windows, but that was a few years ago. Have you tried changing PHP versions? There were a few cases back then (this was four or five years ago at this point) where I chased bugs for ages that turned out to be PHP-related.

I sent email to my PHP/MySQL expert. My guess is he'll say "we don't have those problems...we run on a real OS." I'll let you know if he has anything productive to say.

I told you to buy the X-Serve ;)

Sounds like an index issue to me.

Do you use a FULLTEXT type index and MATCH() on the target
select table?

Look at how the select statements for full post search and title on search differ.
It is possible that the full post search is properly using an index and title search is not, resulting in the dramatically different response time.

To help isolate the problem run the same select that your application runs directly against the DB (not through the app (PHP) if it runs slowly in a direct sql call it is likely an index issue.

Comments on similiar problem

From: Bill Fumerola <billf@m...>
Date: Wed Mar 27, 2002 8:34 pm
Subject: Re: keystone-users Re: Recommended PHP version?

On Wed, Mar 27, 2002 at 12:31:45PM -0700, Nieltawee, Lake wrote:
> Oppss! .sorry I hit the Enter key by accident...Part II
>
> First of all, thank you responding to my post. Next, if you get Keystone
> working on PHP4, than I must do something wrong. But I keep getting bunch
> of syntex errors, like:

keystone runs on php4. these are warnings, not syntax errors.

> Warning: Use of undefined constant bghigh - assumed 'bghigh' in
> c:\apache\htdocs\keystone\conf\colors.conf.default on line 30
> Warning: Use of undefined constant bgmed - assumed 'bgmed' in
> c:\apache\htdocs\keystone\conf\colors.conf.default on line 31
> Warning: Use of undefined constant bglow - assumed 'bglow' in
> c:\apache\htdocs\keystone\conf\colors.conf.default on line 32
> Warning: Use of undefined constant back - assumed 'back' in
> c:\apache\htdocs\keystone\conf\colors.conf.default on line 33
> Warning: Use of undefined constant bgwhite - assumed 'bgwhite' in
> c:\apache\htdocs\keystone\conf\colors.conf.default on line 34
> Warning: Use of undefined constant link - assumed 'link' in
> c:\apache\htdocs\keystone\conf\colors.conf.default on line 37

these come from arrays being referenced like:

$foo[BAR]
and not
$foo['BAR']

these are reasonably harmless warnings.

> or after I log-in:
> Warning: mysql is deprecated; use mysql_select_db() and mysql_query()
> instead in c:\apache\htdocs\keystone\mysql.php3 on line 22

mysql.php3 still uses some php3-era mysql library calls. php provides
backwards compatability, so its not a big deal.

> Warning: Use of undefined constant passwd - assumed 'passwd' in
> c:\apache\htdocs\keystone\login.php3 on line 66

see above.

> Did you get these error? Problably not. Do you have any ideas why do I get
> these error?

your php.conf may be configured to report more warnings.

Boy, give a MechE a computer and look what he does... :D

Just kiddin', Wayne.

i'd suspect index trouble as well, how did you migrate your DB to the new server??
do you still have the old one around to compare settings?

I'm sure there is a good technical reason why Bosch didn't use MySQL for the DME in my Porsche... Just trying to keep it on topic! :D

I've got some mysql databases that have multi-million records, but not on W2K.

Some ideas :

1) Mysql tuning:

set-variable = key_buffer=2048M

Key buffer - 2GB? How much RAM do you have? Can you look at the my-huge.cf that came with MySQL - NT? I've got some different values.

2) How much RAM is the mysql process using? Are you swapping (high disk I/O)? NT/2K has pretty good tools for monitoring performance of CPU & disk. (perfmon). If mysql process is using more than physical RAM, you'll swap a lot. Swapping on IDE drives will be slow. Perfmon should be able to monitor I/O. I'd watch physical disk & page file utilization.

I've got an I/O problem on one of my MySQL databases, where during normal operation the I/O uses 100% of the SCSI bus, so MySQL can't read/write its tables and indexes fast enough. I need another SCSI card & need to split the data tables across multiple cards, or add more RAM & tune my.cnf.

3) An index problem? Can you dissect the PHP code enought to figure out what query is used by the slow search, and then type it into a mysql> command prompt, but type 'explain' first.

.i.e instead of
select blah from blah where...
do
explain select blah from blah where...

Then look at MySQL docs to figure out if the quey is using indexes or not, or if the query is using the wrong index. If you are not using an index or the right index, you'll 'sweep' the table, reading every record. Lots of I/O.

4) 'optimize tables'

5) Hmm... these may be full text searches, which have special considerations. Probably have to search mysql.com for info on how they are indexed.

These articles look like good intro's to MySQL tuning:

http://www.databasejournal.com/features/mysql/article.php/1382791

http://www.databasejournal.com/features/mysql/article.php/10897_1402311_2

They give an intro of how to interpret 'show status;'

--Mike