MySQL queries


#1

When making static pages and simple php pages, files load quite fast on dreamhost, but my blog and image gallery, which uses mysql queries, loads real slow. I’ve already put in optimized settings, but it is still very slow in loading.

is there any other way to speed up mysql queries? or are dreamhost’s database servers just slow?


#2

How slow is slow? Also, if you give us your url, we can try it out to make sure that it’s slow for us as well.

Free unique IP and $67 off with code [color=#CC0000]LMIP67[/color] or use [color=#CC0000]LM97[/color] for $97 off. Click for more promo code discounts


#3

my main page isn’t so bad, loading around 2-4 seconds, but my image gallery and the other programs that use mysql are rather slow.

my site: http://bakaelite.net

thanks in advanced!


#4

As I’ve said before in another thread, the MySQL slowness is extremely annoying and strange. The MySQL server I’m on gets around 15-20 queries/second which is not very much, especially for a dedicated MySQL server. But the MySQL is still the bottleneck here at DH. I’ve got databases on dedicated MySQL servers which get 200-300 queries/second and they are really fast. Requests are nearly instant and pages load fast. So why is it that the MySQL server has 15-20 queries/second is very slow and another server has so many more queries and is faster???

Jan

Promo-Code: [color=#CC0000]SAVEMONEY97[/color] - Save [color=#CC0000]$97[/color] on your first year of hosting.
Use [color=#CC0000]SAVE2DOMAIN[/color] to save [color=#CC0000]$82[/color] and get a second domain for free


#5

I just ran some tests and it might also be that the physical distance between the MySQL server and the web server is to large or the network might be overloaded. I don’t know, but a “smart” host would have two networks, one for internal datacenter traffic (backup/MySQL) and one for outbound traffic (web). Maybe DH just has one network card in the servers which handles all the traffic. This would explain why the MySQL servers are so slow, without having a lot of queries.

In my tests a placed the webserver and mysql server around 300 miles apart with traffic running over one network interface and even though the servers are both fast themselves, suddenly the speed is dramaticly decreased if I have that large physical distance.

So DH might have the database servers in one datacenter and the webservers in another.

Jan

Promo-Code: [color=#CC0000]SAVEMONEY97[/color] - Save [color=#CC0000]$97[/color] on your first year of hosting.
Use [color=#CC0000]SAVE2DOMAIN[/color] to save [color=#CC0000]$82[/color] and get a second domain for free


#6

It took me around 15-20 seconds to load the page when I first went to gallery and other programs. But it took me around 2-3 seconds to load the page when I went to the same page again (go to home and back to the same page).

In Biblioteque, it is very fast to load other pages like “members”, “the tens”.

Do you have any special codes in the first page of Biblioteque?

Save $97 (MAX Discount) with code: [color=#CC0000]97YES[/color] Sign Up NOW or More Codes here


#7

no… there’s nothing “special” that i put. I think one reason why it loads quickly the second time around is because I have cookies enabled and most of the designs are in css, so once everything is loaded, it’s saved on one’s computer so it loads quicker on the next go. However, my friends and other visitors often give up when they first view my site because it takes too long, so i wind up using photobucket and other free image galleries (defeating the purpose of why i wanted a website–to keep everything together).

if the databases are slow, my site will keep getting slower as i use more database space to put in more stories and pictures. i’d gladly give up webspace and bandwidth for faster mysql databases, but seeing as that’s unlikely to be accepted, is there a way to get faster speeds, or should i shop around for hosts with fast mysql servers? I like dreamhost very much, but it won’t do me any good if i can’t have my site grow well.


#8

You might want to look at DreamHost Private Servers.

Have you actually done any testing to determine where the slowness is occurring (like Yslow for FireFox)?

–rlparker


#9

dreamhost ps costs extra, and it’s only invite only atm. i don’t have much to spend on webhosting, so if i need to spend more money just to have an increased load time, i think i’ll move to a different host. I don’t need the vast amounts of space and bandwidth anyway, but i do need my site to load quickly.

my plan expires in november, and some of my friends have mentioned hosting alternatives, so i’ll just move before then. thanks anyways.


#10

No problem … just thinking out loud. I have many sites using MySQL on DreamHost, and their performance has generally been fine.

Did you ever analyze the load time of the page using Yslow?

–rlparker


#11

What kind of data are you storing in MySQL?

Free unique IP and $67 off with code [color=#CC0000]LMIP67[/color] or use [color=#CC0000]LM97[/color] for $97 off. Click for more promo code discounts


#12

i use mysql for my blog, my image gallery, a small personal forum to save some works, and a story manager. i also planned to create a graphics database to store my webdesigns and graphics, but put that on hold due to the obvious strain on the mysql.


#13

The main question is whether you’re storing large graphics objects or other multimedia in MySQL.

Free unique IP and $67 off with code [color=#CC0000]LMIP67[/color] or use [color=#CC0000]LM97[/color] for $97 off. Click for more promo code discounts


#14

currently, the only thing that could stress the database is my image gallery. my blog is pretty small. however, they are all on separate databases and even my blog and story database are pretty slow for something rather small.


#15

You really should do some basic metrics on your page to determine what, precisely, is loading slowly and where the bottlenecks lie.

Since you are using MySQL in most of the stuff that is slow, it is easy to assume that is where the problems lie, but you will never really know, or be able to articulate it to support, without some solid page loading metrics.

So having flogged that horse well past it’s death, I’ll shut up about it now - but your troubleshooting should start there. :wink:

–rlparker


#16

I’m having poor mysql performance as well… I’m pretty sure it’s because I’m on a shared host. I’ve tried to find the bottleneck using the instructions found here…

http://wiki.dreamhost.com/index.php/User_resource_reporting

Anyway that didn’t reveal any abuse.

How do I go about performing “basics metrics”?

I have two databases one for a Joomla site and another for Mediawiki. The mediawiki is vanilla. I have installed some plugins for Joomla so perhaps those are causing issues but both sites are very low traffic yet most of the time the queries take more than a few seconds to load.


#17

[quote]I’ve tried to find the bottleneck using the instructions found here…

http://wiki.dreamhost.com/index.php/User_resource_reporting[/quote]
That process is useful for determining overall resource usage, but won’t help you much, if at all, for determining which of your PHP programs are consuming various amounts of resource. For that, you should review Getting PHP granularity in user resource reports.

The “basic metrics” I was talking about when referring to evaluating a page’s loading time involve determining what part of the page is taking how long to load, and otherwise reviewing how “efficiently” the page is constructed and what might be done to improve it’s loading time.

The FireFox Firebug extensions FireBug, Yslow, and Web Developer have numerous tools for checking on these things. There are many different things that can be learned about a given page/site’s performance using these tools, and often you will be surprised when you learn where the bottleneck really is when a page loads slowly. :wink:

–rlparker


#18

[quote]Getting PHP granularity in user resource reports.

[/quote]

Seems like some of the insructions on that page are out of date but I’ll give them a try.

I’m certain that the bottleneck is the mysql performance but I’ll look into some those programs.


#19

You may well be right about that! My point to the OP was that, until he has looked at some of those other potential issues, he won’t know for sure, and it will help him in discussing the slowness with DH tech support if he comes to them with all the information he can about why the pages load slowly. :wink:

Good luck!

–rlparker


#20

i just downloaded the yslow, and my site aces almost everything except these, which they all fail:

Use a CDN
Add an expires header
Gzip components
Configure Etags

i’ve never used these before but when i had free hosting, load times were much faster than my current situation here without ever implementing these things (all of which i’m unfamiliar about, anyway) so i’m in the dark in what they mean.