performance

[bagnallc]

i am having a bit of a problem at the moment with my pc.

i have been designing a website just using localhost (php/mysql written in notepad) and i have never had any problems with it. as i have done each step i have always being testing and viewing as i go along.

now though all of a sudden whenever i click a link or try and load a page with any database content (even pages which always worked fine), my pc just hangs and when i go into task manager mysqld-nt.exe is always a high number.

are there any settings i need to change or is there something that may have gone amiss?

many thanks

[shoz]

I can't guarantee that I can help, but you can try adding the following to your my.cnf

[code]
log-slow-queries
[/code]
This should be "under [mysqld]
eg
[code]
[mysqld]
log-slow-queries
[/code]
If you don't have a my.cnf, create it and put it in the "data" directory under mysql's installation directory(If you're using MYSQL 4.1 or less). If you're using MYSQl 5 put it in the root of the installation directory. Restart the server.

Load one of the pages that accesses the database and let it run until it's finished (I'm assuming it doesn't completely bring your computer down). Look in the "data" directory for a logfile named something like "host-slow.log" and post the contents if any.

Btw, if you manually query the database using simple queries do you have the same problem? Post your MYSQL version as well.
[code]
SELECT VERSION();
[/code]

[fenway]

Assuming it doesn't hang completely, you can also SHOW PROCESSLIST as well.

[bagnallc]

Hi guys,

thanks for your help.
certainly some of the queries i run (such as the one in the slowlog listed below), are indeed very large ones, but whats troubling me is that in the past this query and many others would run no problem.

it is still the case now that when i initially log in at first, or if i reboot - the pages load instantly.

just once in a while the pc starts to hang and when i go in to task manager i see this mysqld-nt.exe with cpu usage very high. rest of the time its as normal

i guess it does look like it may be the large queries,its just confusing because as i say, previously there was never the same problem.

anyway in the hope you may be able to suggest something the slow log file is as follows

C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt, Version: 5.0.19-nt-log. started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
# Time: 060725 17:22:59
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 81  Lock_time: 0  Rows_sent: 2  Rows_examined: 1868379
use horse;
SELECT distinct ifnull(rt_1.rt_1_id, 2) as id, ifnull(rt_1.rt_1_description, 'Non Hand') as rt1
FROM numbers
LEFT JOIN rt_1 ON numbers.rt1_id=rt_1.rt_1_id
JOIN all_details ON numbers.no_id=all_details.no_id

[fenway]

Could you post the EXPLAIN output of this query, and the table structures?

[bagnallc]

i have just done a slight restructure on the tables and also ran a few optimize and analyze statements. part of my problem may have been that some tables were myisam and others innodb.

anyway for now i wont take up any more of your time as it seems to have resolved problems

many thanks once again for showing an interest

cheers

[fenway]

Yup... ANALYZE is quite important.