mysql caching

"Michael Fesser" <netizen@gmx.de> wrote in message
news:rflds4hkjj3vosncm96vj2m4nt2l774mls@4ax.com...
> 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.

that's what I thought as well, but when I contacted my host, they said it
was a mySQL issue. And the odd thing now is that the issue has not reared
its head again for at least 48 hours.

> 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?

it's a freeBSD box running a VPS. The command line stuff returns in a timely
manner.

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.

thanks for your advice/help, Micha! I've always appreciated your help :) I'm
honestly thinking this is something eating up CPU from another site on the
VPS (i'm only running 2 sites of my own in my VPS)... I'll investigate
further with the host.

Hunter

.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

"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.

>
>>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).

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?

> 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?

> 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
idx_contentTitleShort `Program` Normal
idx_contentActor02 `Actor2` Normal
idx_contentActor03 `Actor3` Normal
idx_contentActor04 `Actor4` Normal
idx_contentActor05 `Actor5` Normal
idx_contentCharacter01 `Character1`
idx_contentCharacter02 `Character2`
idx_contentCharacter03 `Character3`
idx_contentCharacter04 `Character4`
idx_contentCharacter05 `Character5`
idx_contentCategory01 `Category1`
idx_contentCategory02 `Category2`
idx_contentCategory03 `Category3`
idx_contentCategory04 `Category4`
idx_contentIconALT `MediaALT` Normal
idx_contentTitle `ProgramTitle` 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

> Fiddling with cache settings should be the last thing, if at all (in
> fact I've never done that, and in my applications queries across 8-12
> tables are quite common!) The DB is smart enough to choose the optimal
> strategy for answering the query itself. In a typical web application
> you would never really have to mess with caching, because usually the
> performance problems are simply caused by things like mentioned above.

well, then I'm sure I munged up the db design - any good references you can
refer me to read?

.oO(Hunter Elliott)

>I'm having some significant slowness on a site.

What does slow mean in this context? Milliseconds? Seconds?

>All of my "monthly" pages have queries in this style:
>SELECT SQL_CACHE *
>FROM vw_Monthly
>WHERE Airdate LIKE 'YYYY-MM%'
>ORDER BY Airdate DESC
>(the YYYY-MM would have an actual date)
>
>I *think* my issue is coming from the query on my main page.
>SELECT MediaALT, MediaIcon, FilePath, FileName, BGsound, Airdate,
>ProgramTitle, ContentText, Actor1, Actor2, Actor3, Actor4, Actor5,
>Character1, Character2, Character3, Character4, Character5, InMemoriam,
>MemoriamText, Program
>FROM tblContent
>WHERE MONTH(Airdate) = MONTH(CURRENT_DATE) AND YEAR(Airdate) =
>YEAR(CURRENT_DATE) AND Airdate <= CURRENT_DATE() ORDER BY Airdate DESC

It doesn't make much sense to try to fix something which you *think*
might be a problem. You have to make sure that it actually _is_ the
problem. Premature optimization would just make things worse.

Is it really the query that's running slow or the script? How does the
query perform if run directly on MySQL's command line client? How much
time does it take there?

>I just changed this main page query to
>$thisMonth = date('Y-m');
>$thisDate = date('Y-m-d');
>SELECT SQL_CACHE * FROM vw_MainPage WHERE Airdate >= '$thisMonth.''-01' AND
>Airdate <= '$thisDate'
>
>the goal is to show any entry for the current month only and not show any
>entries that are scheduled for later in the month.

Pretty trivial and fast, so the real problem lies somewhere else/deeper.

>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. Your tables
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. 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.

Fiddling with cache settings should be the last thing, if at all (in
fact I've never done that, and in my applications queries across 8-12
tables are quite common!) The DB is smart enough to choose the optimal
strategy for answering the query itself. In a typical web application
you would never really have to mess with caching, because usually the
performance problems are simply caused by things like mentioned above.

Micha