Tweaking MySQL Variables 1

[Tama]

Hi there

I'm troubleshooting performance problems with my dynamic website that's run off MySQL. The MySQL variables are default and I'm wondering if I should look at changing any of them.

My Server:
2.4Ghz Celeron (400FSB)
512meg RAM (DDR)
40gig H/D (7200rpm)

Apache 2.0 Fedora
MySQL 4.0.20

Site Traffic:
Average Users Online: 67
Peak Users Online: 119 (average across hour)
Average Queries to generate page: 104
Average Pages per hour: 700
Peak pages per hour: 2653
Average Query execution time: 5.42ms

All MySQL queries are carried out through one user on localhost via PHP scripting.

Keeping in mind that I'm currently using the default MySQL variables is there changes I should look at making (especially in regards to maximum user connections.)

Any comments, advice etc. is most appreciated.

Thank you in advance

Tama

I do my sums on fingers and thumbs.

 

[Bastien]

You are not mentioning any specific problems so its tough to suggest things...

Some things to consider:
1. Are you using indeces? Do they get used by the engine? Look at the explain plans to find out

2. Do those indeces make sense? indeces on queries that return more than 30% of the rows are ignored.

3. why not enable 'slow query log' to see if any of the queries need tuning?

4. Perhaps more memory into the machine?

5. Are there places where you can substitute an array for a query? Like static listbox elements (ex. state names in a form).

Bastien

Cat, the other other white meat

 

[Tama]

You are not mentioning any specific problems so its tough to suggest things...

Sorry, I forgot to state my problem. I'm using a chat script and page generation times noticeably rise with users logged on the chat. For example, over 1 hour when an average of 9 people were on Chat the page generation times increased twentyfold.

I've posted a question on the chat script's support forum after doing a search there. No one else seems to be suffering from my problem. I haven't had anyone reply to my post yet.

I suppose I'm also interested in what people think of my hardware spec versus what I'm asking it to do. I would have thought that the server would of been capable of handling lots more traffic than what's being thrown at it.

Some things to consider:
1. Are you using indeces? Do they get used by the engine? Look at the explain plans to find out

2. Do those indeces make sense? indeces on queries that return more than 30% of the rows are ignored.

3. why not enable 'slow query log' to see if any of the queries need tuning?

Earlier in the year I ran a slow query log and then analysed my slow queries, and tweaked the queries themselves or added indeces as neccessary. I don't think I've made that many changes since then, but you're right, I should start my slow query log again.

4. Perhaps more memory into the machine?
The machine is with a hosting company but this is something I can check on. The actual cost of another 512meg would be minimal, but I'm not sure whether the motherboard/ chassis can handle it. If the server is a blade style (and I honestly don't know what it is) it might only take 1 slab of RAM - a 1gig stick could get pricey.

5. Are there places where you can substitute an array for a query? Like static listbox elements (ex. state names in a form).
That I take totally static data and insert it into the php script rather than having it pulled from the MySQL DB? Or did you have something trickier in mind?

Thanks alot for your feedback Bastien, I'm off to find my slow query log code right now.

Cheers
Tama

I do my sums on fingers and thumbs.

 

[Tama]

OK - I've been running the slow query log for a while and it's picked up three queries that obviously run into problems:

1. Is part of a search routine that scans 3.5+ million rows so I'm not to bugged by this.

2. Confuses the hell out of me. It's an UPDATE command that updates two MEDINT values in a table with a bit over 2000 rows and matches off the PRIMARY key. The only thing I can think of is that it happens a lot (everyone a registered user views a page) so could there be some sort of table locking action happening?

3. Is very similar to 2 - again an UPDATE command again matching off a PRIMARY key. Again this happens a lot, everytime a user views a topic on the sites forums.

Does anyone have any suggestions for how to approach 2 and 3? Am I right in thinking that it could be to do with the table locking and some sort of queue/ buffer happening?

Cheers
Tama

I do my sums on fingers and thumbs.

 

[Bastien]

Yeah, its locking the tables. Question: what engine are you using(MyISAM or InnoDB?) InnoDb would lock the rows and not the table level...

I guess,depending on what the site does, you could consider replication. Then set the site to query off the replicated server and all updates are run against the master...

You might want to pick up high performance mysql book. Its easily one of the best tech books I've read.

Bastien

Cat, the other other white meat

 

[Tama]

The tables at MyISAM, but there seems to be an option to change tables to INNO DB.

I've just had a read of:

http://dev.mysql.com/doc/mysql/en/Converting_tables_to_InnoDB.html

and it seems like a worthwhile change to investigate. Does anyone have experience with changing MyISAM to Inno DB and know of any pitfalls?

The site is heavily reliant on phpBB forums which recieve 500-1500 posts a day, replication of some tables might be possible, but the tables that are locking the most are also the tables recieving constant updates.

I'm a big fan of O'Reilly books and will look at getting the one you suggest. I really appreciate your help on this, especially the Inno DB tip.

Cheers
Tama

I do my sums on fingers and thumbs.

 

[Tama]

Oh crap - since moving 3 of my high use MyISAM tables to Inno DB page generation has gone through the roof! Queries using the Inno DB tables are showing pathetically slow times on my slow query log.

ARGH! No, I'm not panicking - well, perhaps a wee bit...

...any ideas? Anyone?

Tama

I do my sums on fingers and thumbs.