.oO(Hunter Elliott)
>"Michael Fesser" <netizen@gmx.de> wrote in message
>news:8tucs45h569h35ul51abnmtcpajc8vitml@4ax.com...
>> .oO(Hunter Elliott)
>>
>>>I'm having some significant slowness on a site.
>>
>> What does slow mean in this context? Milliseconds?
Seconds?
>
>many, many seconds. After, say, a restart of the server,
the page would load
>for me in about 1 to 2 seconds. After a while the
response time would be
>somewhere between 20 to 60 seconds.
1 to 2s is quite long already. My own applications with some
dozen
loaded files, hundreds of created objects and half a dozen DB
queries on
each(!) page request usually take less than a second to
create and
return a single page - and that's on my own local 1.2GHz
machine, which
is not really comparable to a real web server.
OK, it's acceptable for now. But 20s or more is definitely a
problem,
which shouldn't happen.
>>>Does my approach look
>>>like I'm going in the right direction?
>>
>> Unfortunately not. First there's a big problem with
the table designs.
>> Column names like Actor1, Actor2 etc. almost always
indicate a poor
>> table structure, which causes redundancy and
inflexibility.
>
>Well, I need to be able to list up to 5 people for each
quote that I have. I
>need to list both the actor's name and the name of the
character they
>portray.
>
>I have a table that holds the actor names and another
table that holds the
>character names. (in addition, there's a table that holds
the name of the
>film/tv show, a table that holds a "type" for the various
media the clips
>come from).
OK, this sounds a bit better, which you can further build
upon.
>How would you parse out these various things? I.e., if
you have to have up
>to 5 actors/characters per quote, what would you do
different than having
>fields to hold 5 different actors and characters?
Since I don't know all the details about your tables and the
task you're
trying to solve, this is just a more general comment.
In DB theory this is called an m:n relationship - m items
from one table
can be related to n items from another table in any possible
number and
combination. In this case you have your table with quotes or
whatever,
and a second one with actor informations. Preferably each
actor and each
quote should have a numeric primary key. With these actor and
quote IDs
you can easily setup another two-column table, which connects
these two
by using their IDs as FOREIGN KEYs.
This allows to assign any number of actors to a quote or
whatever you're
working with.
>> could need some normalization. Second it's not
recommended to use SELECT
>> * for anything else than debugging or quick tests.
Such sloppy queries
>> don't belong to production code.
>
>well, since I had defined a view that only holds the
specific fields I need
>(I thought that would have been clear since the Select
was from a "vw"
>instead of a "tbl"), what would be the purpose of
restating said fields
>again?
It wasn't clear (at least not to me) that you were using a
view. I'm not
a fan of hungarian notation, so here a vw_* table could also
have been a
table containing data about a German automobile manufacturer.
;-) [1]
The main points about not using SELECT * are that
1) it will often return more data than necessary (especially
when
storing binary data in BLOBs)
2) it may cause ambiguities when JOINing multiple table
3) the order of the returned columns is more or less random
OK, the latter shouldn't be an issue usually, because an
application
should not rely on a particular order of columns in the
result set.
But sometimes a defined column order can be helpful.
>> Third you didn't tell anything about
>> the actual table structure, their column types and
-most important- the
>> indexes. It often also helps to look at EXPLAIN
SELECT to see what the
>> DB really has to do in order to answer the request,
which indexes it
>> uses (if any), if there are full table scans and
things like that.
>idx_ContentID `ContentID` Unique
>idx_contentActor01 `Actor1` Normal
>idx_contentIcon `MediaIcon` Normal
>[...]
>
>EXPLAIN SELECT says I'm using a select_type of Simple,
referencing my
>content table, type is All, extra is using a Where and
filesort
OK.
What confuses me - you said that after a server restart the
first page
request works well (more or less), but after a while the
performance
goes down. To me this looks as if something is eating up CPU
power, for
example scripts running into an endless loop or something
like that. If
you request such pages multiple times, it may keep more and
more server
processes busy, slowing down the system. Just a rough guess,
of course.
Is it your own local test server? What OS - Windows? Linux?
Does the CPU
load increase with time? What about using these queries on
the command
line client - do they perform well?
>well, then I'm sure I munged up the db design - any good
references you can
>refer me to read?
Not really at the moment. I'm running out of ideas, because
it's an
issue I've never encountered myself yet. I just can say from
my own
experiences that even with your current table setup such a
performance
loss shouldn't happen. IMHO there's something screwed up on
the system.
Micha
[1]
http://en.wikipedia.org/wiki/Vw
4
Replies
AdChoices