(Solved?) Database slow response

I have repaired all tables, optimize them and restart the server, but the problem persist.

After a slow response,  I get _FATALERROR _NOTCONNECTED

The hosting said is the application.

Please tell me what else I can do.

I really don't think it's eFiction because this doesn't happen to anyone else. Despite what your host says, it's got to be your server or a setting on your php or mySQL. I'd guess the latter since that's where the problems show up.

Well, I think also is the hosting. I have 3 efiction sites and all of them run without problems, and this hosting have many problems.

Please tell me what parameter can be causing this at php.ini

Thanks

Unfortunately, I don't know. You might try googling your error and see if anything comes up. I've done that with mixed results. If you find anything, let us know!

OK, I am testing some parameters, and the problem is a too long time to excecute queries.

I have 8000 stories and 30,000 chapters, 8000 authors, so the database is not "too big" and worked fine with eFiction 2.07, but now I can see queries like:

SELECT count()...
SELECT DISTINCT COUNT()

This queries take more than 1000 seconds in peak hours.

I optimize all tables, check all indexes, but the problem persist.

One thing you can do is make sure all blocks that are only displayed on the index page are set to "index only". Otherwise those queries run on every page regardless of if they are used or not. Another thing you could do is, if you have site stats displayed, turn that off and remove the block from your index page. (Make sure you set it to inactive on the blocks page.) The script is obviously going to do a lot of counting, but this should cut back on it a little.

As a last resort, if you are storing your stories in the database, you might consider moving them to files. I can almost guarantee that it's the 30,000 chapters that are bloating your database. Of course, you'd have to figure out some query to do this, because doing it by hand is not an option, unless your schedule is clear for the next year or two.  😆

I've been having the same problems.  Click a few times and then suddenly, runs really, really slow.  And unfortunately, I'm pretty much mySQL illiterate.  (probably not so good for me, heh).  I'm a new eFiction user too, so I wouldn't even know where to begin on solving this.  I tried the blocks thing and while I don't have anything archived yet, I'll be storing stories in Files and not on the database.  any other suggestions?  =/

Well, after check the php files, I find something interesting:

1. I have 4  queries consuming excesive resources, two are in includes/queries.php and the others in stories.php

They are something like this:

define ("_MEMBERLIST", "SELECT count( stories.sid ) as stories, "._PENNAMEFIELD." as penname, "._UIDFIELD." as uid FROM "._AUTHORTABLE." LEFT JOIN ".TABLEPREFIX."fanfiction_authorprefs AS ap ON "._UIDFIELD." = ap.uid LEFT JOIN ".TABLEPREFIX."fanfiction_stories AS stories ON stories.validated > 0 AND (FIND_IN_SET("._UIDFIELD.", stories.coauthors) > 0 OR stories.uid = "._UIDFIELD.") ");
define ("_MEMBERCOUNT", "SELECT COUNT(DISTINCT "._UIDFIELD.") FROM "._AUTHORTABLE." LEFT JOIN ".TABLEPREFIX."fanfiction_stories as stories ON stories.validated > 0 AND (FIND_IN_SET("._UIDFIELD.", stories.coauthors) > 0 OR stories.uid = "._UIDFIELD.")  LEFT JOIN ".TABLEPREFIX."fanfiction_authorprefs as ap ON "._UIDFIELD." = ap.uid");
?>

2. At sometimes I can see in my process list in mySQL:

SELECT count(stories.sid) as stories, author.penname as penname, author.uid as uid FROM fanfiction_authors....

3. When this querie is running at the queue, its starus is "Copying to tmp table" and all the other queries are locked until it finishes

4. The time vary... it can be 400-500 seconds or even 1000, and its duration is 5-6 minutes or 10 depending of traffic.

So, the problem is not the way I save stories in a database instead of files. The problem is such queries.

I have deactivated ALL this queries, replacing them for other operations, depending of the case, and it seems to be resolved.

It would seem my database problem has fixed itself.  The site runs smoothly.  Still would like to know what the heck was going on though. 

Oh, I remember Lyndsie's host making her member list page inaccessible because it was contributing to a lot of server load?

Yes, my shared hosting blocked me in August, but now I have a VPS and it runs smoothly until I upgrade to eFiction 33.

I solved my issue making changes in the script, but the problem is basically when in stories.php makes an update to the statistics table for the number of authors. Because I have no multiple authors, I can discard the query and use a simple database operation, but it's only for emergency.

It would be great that the next releases can consider this issue and solve it properly.

You should make a post in the new features forum then. That way, you know it will be seen.

OK, I will.

Thanks

The fundamental issue here is one I raise a while ago: efiction stores co-authors as comma seperated lists in a string field the stories table, rather than as mutliple rows of integer fields in a seperate table. This means once you have a large number of both stories and authors, the queries to generate the authors page that join authors to stories on the co-authors field start to take a very long time to run as they can't be indexed in their present form. The problematic queries are the ones where FIND_IN_SET is used on large tables.

I have had another site owner contact me privately about the authors page taking 30 seconds to display on a 4 processor Xeon server with 4Gb of RAM that was dedicated to the efiction site. Until this is resolved, efiction 3 isn't really suitable for large sites.

I know Tammy is aware of this, but possibly not of the severity of the problem for larger sites on shared hosting.