Slow database withing a Jail (8.1)

G

ghostcorps

Hi Guys

We have recently noticed that our website is being displayed very, very slowly. We suspect that the database is being slow. I can not provide a link as it is in development and not properly secured.

I am running FreeBSD 8.1 with two jails, one for the webserver (Apache/PHP5) and one for the database server (MySQL) . Aside from restricting database connections to only the webserver, the database is stock. Are there any known issues with such a setup?

Otherwise what would be the best recommendation for testing the speed of the database from the webserver?



Thanks :)

Update: Seems to be taking forever to login to the database server. What is a good query to test the speed with once I am in? And what is a good time for the results?

Update 2: I suspect this has to do with routing since the traceroute I am running from the host is upto 13 timedout hops and counting! How can this happen?
 
If you have concurrent inserts and selects you should convert your tables to InnoDB. Do note that you will have to store those tables on a filesystem with filecaching turned off.

You also need to tune MySQL. Specifically certain settings. The tuning script at http://www.day32.com/MySQL/ can help with that.

databases/mytop might also be helpful.
 
Hello again.

I have confirmed that some but not all of the database tables are using InnoDB with this query:

Code: 
SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';

Is it worth trying to convert them all? What is the best method via commandline?


Even When Mytop shows that there is no traffic, it still takes about 15 seconds just to log into the database. If I open the page the Key Efficiency jumps to 100%, that seems bad...


Code: 
MySQL on DATABASE (5.1.49)                                                         up 11+18:10:23 [01:39:26]
 Queries: 7.5k   qps:    0 Slow:     0.0         Se/In/Up/De(%):    72/03/07/05
             qps now:    1 Slow qps: 0.0  Threads:    1 (   1/   0) 00/00/00/00
 Key Efficiency: 52.1%  Bps in/out:   2.7/356.6   Now in/out:  21.1/ 1.4k

      Id      User         Host/IP         DB      Time    Cmd Query or State
      --      ----         -------         --      ----    --- ----------
     468       USER            HOST      DATABASE      0  Query show full processlist


The script is an excellent suggestion. I have worked through the few suggestions it made. But while I am sure these helped avoid future issues, they haven't fixed this particular problem.

I am convinced this has to do with the initial connection to the database because the only delay I can find is at login, after that the querys run just as fas on the webserver as they do directly from the database server.


Following up on the other suggestion:
How do I turn off disk Caching? I have looked all over the place, but the pages I have found speak of it as though I should already know :(



[solved] I had to add an entry into the hosts file, eventhough the database server exists only within the jail and I was connecting to it via ip only... odd but there y'go :) Thanks for the assists
 
ghostcorps said:
Is it worth trying to convert them all?
Click to expand...
That entirely depends on the table's usage. If you do an insert on a MyISAM table the whole table will be locked. Any selects on that table will have to wait until the insert is completed and the table lock is removed. InnoDB only locks a row during an insert. Selects can still operate on the rest of the table.

If I open the page the Key Efficiency jumps to 100%, that seems bad...
Click to expand...
No, that's actually good. You want the key efficiency to be as high as possible.

How do I turn off disk Caching?
Click to expand...
Good question. Actually I only know how to do that on Solaris :r
 
Thankyou SirDice

It is a Wordpress website, we expect some heavy traffic, so I think InnoDB on all tables sounds like the way to go. I don't spose you know of a quick and easy way to do this?
 
You must log in or register to reply here.
Back
Top