Speed issue/mySQL

Post by **Don Chambers** » Tue Jan 30, 2007 10:28 pm

My site has virtually no content, yet I encounter numerous instances of extremely slow response times. Here are some screenshots from phpMyAdmin. Can anyone tell me if something is improper, especially the second link from serendipity_entry_properties?? I have several static pages so I thought I might include that screenshot as well:

http://chicagoloopcruise.com/library/s9y_entries.jpg

http://chicagoloopcruise.com/library/s9 ... erties.jpg

http://chicagoloopcruise.com/library/s9 ... cpages.jpg

The following are my event plugins in case that matters:

Markup: Serendipity, version: 1.1
Markup: Emoticate, version: 1.1
Markup: NL2BR, version: 1.2
Browser Compatibility, version: 1.0
Show Emoticon/Smilie toolbar, version: 1.3
Spam Protector, version: 1.26
Spartacus, version: 2.5
User Profiles, version: 0.14
Contact Form, version: 1.10
Static Pages, version: 3.41
Frequently Asked Questions, version: 1.7
Sample! (Head Nugget), version: 1.3
Contact Form, version: 1.10
Extended properties for entries, version: 1.6

carl_galloway · Post by **carl_galloway** » Wed Jan 31, 2007 6:11 am

I can't help with your screenshots, but your list of event plugins seems pretty tame, I can't imagine any of them being a problem although I've recently had some problems with the browser compatibility plugin not installing with a fresh/updated 1.1 install, but I doubt this would be connected to your problem.

I would suggest you check your host's forum for any complaints about their mysql server being affected by other users. Its possible your site is being affected by another user whose script might be hogging the mysql server. Recently had that problem with my own host.

Post by **Don Chambers** » Wed Jan 31, 2007 6:56 am

Thanks Carl.... I have other pages on the same server.. they are leftovers from the early generation of my site, and about to be removed as I attempt to transition to a purely s9y site. Those other pages do not have any lag time whatsoever, and they are very inefficient, code laden MS Frontpage pages. The delay is the s9y pages......

Post by **Don Chambers** » Wed Jan 31, 2007 3:22 pm

Garvin - any input here?

Post by **Don Chambers** » Thu Feb 01, 2007 5:16 am

Anyone? I have some serious speed issues, and all fingers point to s9y!

Post by **garvinhicking** » Thu Feb 01, 2007 12:19 pm

Hi!

The fingers point to your SQL server, please ask your server support. Also this forum contains a lot of tuning suggestions, please search for "mysql" and "speed" or something like that?

Your old frontpage pages, did they have PHP/Database connectivity?

The SQL indexes on your screenshots are proper!

Best regards,
Garvin

Post by **Don Chambers** » Thu Feb 01, 2007 2:58 pm

garvinhicking wrote:Hi!

The fingers point to your SQL server, please ask your server support. Also this forum contains a lot of tuning suggestions, please search for "mysql" and "speed" or something like that?

Your old frontpage pages, did they have PHP/Database connectivity?

The SQL indexes on your screenshots are proper!

Best regards,
Garvin

Thanks Garvin. No - the other pages did not have php/database connectivity. I have searched these formums for speed/mysql etc, which is what led me to take those snapshots to begin with.

If I am to "ask my server support", any suggestions as to what I should ask since "all fingers point to my SQL server"?

Post by **garvinhicking** » Thu Feb 01, 2007 3:17 pm

Hi!

If I currently call your page:

Warning: mysql_connect(): Unknown MySQL Server Host 'mysql65.secureserver.net' (0) in /home/content/c/h/a/chambersdjc/html/include/db/mysql.inc.php on line 156

Warning: mysql_select_db(): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /home/content/c/h/a/chambersdjc/html/include/db/mysql.inc.php on line 157

Warning: mysql_select_db(): A link to the server could not be established in /home/content/c/h/a/chambersdjc/html/include/db/mysql.inc.php on line 157
DATABASE_ERROR

This is what I get, so your first contact could be to ask what doesn't work here, and if the server is down?

Best regards,
Garvin

Post by **Don Chambers** » Thu Feb 01, 2007 7:47 pm

I received the same error message you posted above Garvin. It happened while I was in the admin panel when I tried to either edit or add an entry. A few seconds later, the error message was no longer displayed.

I have contacted my provider (godaddy). They did not initially see any problems but did experience slow page loads so they agreed to look further. This is their response:

The server that your hosting account and database is on is running under a low load right now. This indicates that the issue that you are experiencing is likely coding related. You will need to optimize your database structure and website functionality to increase the performance of your website.

1) You buying that explanation, especially with the sql errors we both saw?

2) Just for clarification, my original message posted a screen shot for serendipity_entryproperties indexes. There is clearly a warning there that says "UNIQUE and INDEX keys should not both be set for column "entryid". This is not a problem???

3) Would "build cached entries" have any affect on page loads if I selected that from the admin panel?

*EDIT* - found this post which is identical to my problems: slow page loads (intermittent), loading without CSS and occasional failure: http://board.s9y.org/viewtopic.php?t=74 ... ysql+speed

Post by **Don Chambers** » Thu Feb 01, 2007 11:54 pm

Latest response from godaddy{

We are not now, nor were we, encountering hosting or MySQL server difficulties. It is likely that this issue is the result of the number of concurrent connections to your database. You are limited to 50 connections per MySQL user. Exceeding that number of connections will result it the errors that you are reporting. Unfortunately, there is nothing that we can do to reduce the number of connections being made by your scripts. I recommend optimizing your scripts and/or database to lower the number of connections.

Does this explanation make sense? How do I go about implementing these suggestions? What exactly constitutes a "connection" and does it seem likely that my site would be exceeding 50 of them?

judebert · Post by **judebert** » Fri Feb 02, 2007 7:12 am

Bullpuckey.

They want to pull the fleece over your eyes? Tell 'em we follow Jay Pipes' recommendations for MySQL performance tuning (point them to his Google Video presentation on MySQL performance). Ask them to provide the results of a mysqldumpslow command -- or the raw log, since they obviously must be running --log-slow-queries.

That'll keep 'em busy.

Sorry; I must be cranky. In polite words, I don't think Serendipity creates many connections to the database. Probably only one, actually. It's not like we're multithreaded or anything, and Garvin uses a nice wrapper method to get to the database.

Meanwhile, we need a script to test the database connection speed. Nothing fancy, like phpMyTop, just a script to connect, make a tiny little query, and display how long connection, query, and processing took. I can't find one online, or I would have linked it. Maybe one of our database gurus can hack something up.

Post by **garvinhicking** » Fri Feb 02, 2007 10:12 am

Hi!

Are you using the "Use persistent connections" s9y option? If so, turn it off.

Other than that, 50 concurrent connections is a lot, you would see that in the traffic your page generates. Do you have 10.000 page hits a day?

About the "Index warnings": This is an oddity of phpMyAdmin. In some cases, like for s9y, it makes sense to set double indices. So no worries about that one.

On our end we've done all we could to optimize Database connectivity. Dynamic blogs are limited, so there's no way we can circumvent creating one DB connection for each page request.

Using the 'build cached entries' plugin will improve page creation times, but it should not at all be related to the error message you are getting from time to time!

Best regards,
Garvin

Post by **Don Chambers** » Fri Feb 02, 2007 4:48 pm

Thanks Judebert & Garvin. I asked tech support for recommendations on lowering database connections, or how to determine how many concurrent connections existed. This was their response:

We would recommend checking your scripts to verify that that the connections to your database are being closed properly. This should allow your site to run without the slowness issues.

They did not tell me how to determine the number of connections. At what point is a connection "opened" and at what point should it be "closed"? Is there any way I can verify that "connections are being closed properly?"

Garvin - I do not have persistent connections set. There is no way I am getting 10k page hits/day... this site is only visited by a small handful of people. Also, to view my site stats, I need to hit my domain.com/stats/.... but all I get is s9y. How do I correct that?

Judebert - is there some way I can execute this mysqldumpslow command? Let me know if you have something that can test the connection speed.

judebert · Post by **judebert** » Fri Feb 02, 2007 5:27 pm

If Serendipity is installed in the root of domain.com, then its .htaccess redirects all subdirectories to Serendipity. To make Serendipity ignore a particular subdirectory, add this .htaccess the subdir:

Code: Select all

RewriteEngine off

In your case, just save that one line as domain.com/stats/.htaccess and you should be able to access your stats.

The mysqldumpslow command must be run from a shell. I don't think GoDaddy accounts come with shell access. However, phpMyAdmin does have a "server status" tab that includes server stats. Have a quick peruse through those and bring anything in red to the admins' attentions, especially if it recommends changing system variables.

I'm still looking for a database connection benchmarking script.

Post by **garvinhicking** » Fri Feb 02, 2007 5:39 pm

Hi!

If no persistent connections are enforced, then a database connection is closed after a PHP script is finished.

A database connection CAN NOT be upheld once a PHP script is closed. A php script always has a complete teardown phase where all resource handles (file pointers, database connections) will be shut down.

Thus, the only way to get that high concurrent connections if:

1. You have as many concurrent users as concurrent database connections

2. The time a single script runs multiplies the number of DB connections for users coming in at that time range.

To clarify 2.: Say you have 10 visitors at time X. Your s9y page call takes 5 seconds. That means for 5 seconds, 10 DB connections will be upload. Let's say at time X + 4 seconds another 20 visitors come in. Added to the already existing 10 connections you will have to have room for 30 concurrent connections at time X + 4 to X + 5.

Best regards,
Garvin