MySQL on Windows 2000 server

[alura]

I'm running smf (www.ilpistone.com) on a Windows 2000 server (is a virtual hosting, done with vmware on a dual processor Xeon 2.8 MHz shared in 4 virtual machine).

I've installed Apache 1.3.3, PHP 4.3.10, Zend Optimizer 2.5.7 and MySQL 4.1.10, ad running SMF 1.0.2

All works smooth on surfing the board, but when someone make a new post there are big slow down and the couse seem to be the mysql server that rise cpu activity to 100% for some second. Simultaneos post make long delay. I can see page generated in up 40 seconds after someone make a post (normally page generation is about 0.6 - 0.9 second).

I've tried different version of mysql, changed buffers, cache, also disable concurrent inserts (this seem help a bit) but I can't have good performance after posts.

Any help ?

Thank's in advance

Marco (alura)
www.ilpistone.com

[Ben_S]

Tried optimizing your tables?

[[Unknown]]

You're actually running a server within VMware?

Forgive me if I can't imagine that being efficient, but doing so isn't much different from using less ram and a slower processor/processors.

Regardless, you can try using InnoDB for some tables, but even then it probably won't affect insertion times.  If you look at the processlist, what query or queries is it being held up by?

-[Unknown]

[alura]

Tried optimizing your tables?

Thank's Ben, but tables are optimized.

You're actually running a server within VMware?

Forgive me if I can't imagine that being efficient, but doing so isn't much different from using less ram and a slower processor/processors.

Regardless, you can try using InnoDB for some tables, but even then it probably won't affect insertion times. If you look at the processlist, what query or queries is it being held up by?

-[Unknown]

Yes, unknown, I'm using a double xeon 2.8 server split in four virtual machine (i have 512 mbyte of ram on my virtual machine). As I can see it is little slower then my laptop (a athlon xp 2.8 + with 512mbyte ram).

I have made some test on my laptop. If I run a search in smf on one IE window (it will take up 20 second, taking about 100% processor that slow down all OS) and then open a borad index in another window, the second is stuck until search completes.

It seems that I was not able to do simultaneos operation on mysql (also with "concurrent inserts" enabled).

Related InnoDB, what table you suggest to convert ? All ? Only message ?

Note: my board has 295130 posts and 14043 threads.

Thank's.

Marco (alura)
www.ilpistone.com

[[Unknown]]

I have made some test on my laptop. If I run a search in smf on one IE window (it will take up 20 second, taking about 100% processor that slow down all OS) and then open a borad index in another window, the second is stuck until search completes.

Likely because tables are being locked.  It's also possible you may not have indexes set up properly..

I suggest you try making topics, log_topics, attachments, members, log_boards, and log_mark_read InnoDB for a start.  Some tables, such as messages, are usually better of MyISAM.

-[Unknown]

[alura]

Likely because tables are being locked. It's also possible you may not have indexes set up properly..

Is smf that lock tables ? How I can control table locking ?

How I can check/verify/repair that ?

My database was imported by yabb->yabbSE->smf rc1->smf 1.0.2

Take in consideration that I check&optimize (and backup) tables every week. Also I defrag the partition with the database (yes, i'm using a dedicated partition for mysql).

I suggest you try making topics, log_topics, attachments, members, log_boards, and log_mark_read InnoDB for a start. Some tables, such as messages, are usually better of MyISAM.

Ok... I will try...

Thank's for your time 

[alura]

Ok... I will try...

Thank's for your time 

Tried... it seems a bit faster.

I you have any idea (ie why table locking during search) I'm here 

[[Unknown]]

Is smf that lock tables ? How I can control table locking ?

Table locking is done automatically when a table is needed to be looked through.  It's hard to avoid, but full text can help - a feature 1.1 has.  Even so, I know of larger forums running 1.0.2 with 512 megs of ram and a slower processor that do fine....

My database was imported by yabb->yabbSE->smf rc1->smf 1.0.2

That shouldn't cause any problems...

If those helped, you might also try.... log_actions, log_activity, log_search, log_floodcontrol, sessions, and settings.

-[Unknown]

[alura]

Thanks'... seem that also using --low-priority-updates in mysql help a bit on win32.

Marco

[alura]

If those helped, you might also try.... log_actions, log_activity, log_search, log_floodcontrol, sessions, and settings.

-[Unknown]

I've modified also that tables... and performance increase is visible

So, let me understand... InnoDB tables are memory based ? You think that all small tables can be stored as InnoDB (pratically all but messages) ?

Reading mysql documentation, is clear that mysql on Linux (or any unix OS) perform much better then Win32 version but I've no possibility for now to migrate my server on linux.

Thank's again ! 

Marco (alura)
www.ilpistone.com

[alura]

Another hint for who using mysql on a win32 machine is to set the mysqld-nt.exe process priority "BelowNormal" instead of "normal". Single querys are slower but more user can works better (for example if a user ask a search, this doesn't kill other user request).

You can use this FREE command line utility to do that:

http://www.beyondlogic.org/solutions/processutil/processutil.htm [nofollow]

Also you can run a batch file (mysql service has no flags to force priority) without logging, but you need windows Resource Kit CD-ROM:

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B243486&Product=win2000 [nofollow]

[[Unknown]]

So, let me understand... InnoDB tables are memory based ? You think that all small tables can be stored as InnoDB (pratically all but messages) ?

No, they're transactionally based.  It's slower to scan them, and they have a few other flaws.  But it's faster to write to them.

HEAP/MEMORY tables are memory based, and are emptied once the server restarts.  The log_floodcontrol table could be made HEAP if you know what you're doing.

Still, a single search query shouldn't bring down even a Windows server.... even on a large forum, really.

-[Unknown]

[alura]

Still, a single search query shouldn't bring down even a Windows server.... even on a large forum, really.

-[Unknown]

Ther is a thing that I have not said     

My forum has a "fast nav" that shows (only for registered users) a modified and lighter index.php?action=unread;all, limited to the last 30 threads so user can follow discussion much better, as you can see in the picture:

[nofollow]    [nofollow]

Also user online is shown on every page but now is cached on a file (it will rebuilded every minutes). When I have time I will try to cache also "last 30 threads" function.

This modifications about double page creation times, on my workstation...

Anyway, with your suggestions the forum is much more responsive, also with 40-50 users online. Before problem started with less then 30 users.

Bye

Marco (alura)
www.ilpistone.com

[[Unknown]]

My forum has a "fast nav" that shows (only for registered users) a modified and lighter index.php?action=unread;all, limited to the last 30 threads so user can follow discussion much better, as you can see in the picture:

* [Unknown] faints.

Do you have any idea how database intensive that is?  I'm surprised it *only* doubles page generation times!

-[Unknown]

[alura]

* [Unknown] faints.

ROTFL 

Do you have any idea how database intensive that is?  I'm surprised it *only* doubles page generation times!

index.php?action=unread;all will kill the server... but my implementation add about 0.2 - 0.3 second on my workstation...  maybe it become critical on simultaneos query (for this I'm planning to cache it). 

[[Unknown]]

For that, I would recommend pulling the most recent topics with IDs of more than $modSettings['maxMsgID'] - 500.  That would be much much faster.

-[Unknown]

[alura]

For that, I would recommend pulling the most recent topics with IDs of more than $modSettings['maxMsgID'] - 500.  That would be much much faster.

-[Unknown]

Thank's. Anyway I've completed the caching of recent topics. I've choose a total different approach, as I added a new table "recents" that is update with topic ID and last post time every time a user make a post (in post2 function). No more then n post are added as the old one is automatically removed from the table. Then I use this table to feed your crazy (  ) query:

Code Select Expand



$select_clause = '
ms.subject AS firstSubject, ms.posterTime AS firstPosterTime, ms.ID_TOPIC, t.ID_BOARD, b.name as bname,
t.numReplies, t.numViews, ms.ID_MEMBER AS ID_FIRST_MEMBER, ml.ID_MEMBER AS ID_LAST_MEMBER,
ml.posterTime AS lastPosterTime, IFNULL(mems.realName, ms.posterName) AS firstPosterName,
IFNULL(meml.realName, ml.posterName) AS lastPosterName, ml.subject AS lastSubject,
ml.icon AS lastIcon, ms.icon AS firstIcon, t.ID_POLL, t.isSticky, t.locked, ml.modifiedTime AS lastModifiedTime,
IFNULL(lt.logTime, IFNULL(lmr.logTime, 0)) AS isRead, LEFT(ml.body, 384) AS lastBody, LEFT(ms.body, 384) AS firstBody,
ml.smileysEnabled AS lastSmileys, ms.smileysEnabled AS firstSmileys, t.ID_FIRST_MSG, t.ID_LAST_MSG';


$request = db_query("
SELECT $select_clause
FROM {$db_prefix}messages AS ms, {$db_prefix}messages AS ml, {$db_prefix}topics AS t, {$db_prefix}boards AS b
LEFT JOIN {$db_prefix}members AS mems ON (mems.ID_MEMBER = ms.ID_MEMBER)
LEFT JOIN {$db_prefix}members AS meml ON (meml.ID_MEMBER = ml.ID_MEMBER)
LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC = t.ID_TOPIC AND lt.ID_MEMBER = $ID_MEMBER)
LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD = t.ID_BOARD AND lmr.ID_MEMBER = $ID_MEMBER)
WHERE t.ID_TOPIC IN (" . implode(', ', $topics) . ")
AND t.ID_TOPIC = ms.ID_TOPIC
AND b.ID_BOARD = t.ID_BOARD
AND ms.ID_MSG = t.ID_FIRST_MSG
AND ml.ID_MSG = t.ID_LAST_MSG
AND ml.posterTime > $firstTime
ORDER BY ml.ID_MSG DESC
LIMIT $maxtopics",  "LastTopics2", __LINE__);



with my addition of "AND ml.posterTime > $firstTime" where $firstTime is calculated from the new "recent" table.

On my laptop this query is now executed very very fast, pratically no time added to page creation. Today or tomorrow I will try this on the web server and see how works under heavy load.

Thank's again

Marco (alura)
www.ilpistone.com

[alura]

Ok, just to let you known that all my problems where gone... Tryed online and lowered loading time by an huge 0.4 second and no more slow down. Pratically half of the time for page generation.

Bye

Marco (alura)
www.ilpistone.com

[NoRad]

How can I increase the memory usage for mySQL on w2k? It hovers at around 23mb right now. I have 1gb of ram on the server and it never goes over 500mb... Would increasing the mySQL memory help cache some queries to increase performance?

[[Unknown]]

Probably.  You'll need to set its options to do this....

http://dev.mysql.com/doc/mysql/en/set-option.html

-[Unknown]