Win2K Performance Options question....

[GeoffS]

The stats I run are on a BioStar K7VKQ motherboard with an XP1900+ CPU and 640Mb ram running Win2K Pro. The software in place is php and mySQL (latest version of both I believe). The most cpu & drive intensive operations occur when I call the script to load the D2OL node data hourly. This is done from a scheduled batch file that calls php.exe with the name of the script to run. This process deletes around 50K rows and then inserts around 50K rows each time it is run (I keep 24 rolling hours of node data unless I wipe the file :roll.

I notice in the System Properties/Advanced/Performance Options I can choose between optimizing for Applications or for Background Services. It is currently set to Applications. Does anyone know what I would gain/lose by changing this?

Are there any other tweeks that would improve performance on this rig?

Thanks!
Geoff

 

[Unforgiven]

you should disable unneeded services. here is the best site for tweaking your installation for both windows 2000 and windows xp. that can gain a little bit of performance by freeing up ram that is used by programs that arent necessary.

 

[kloostec]

A couple suggestions for the database stuff if you haven't done them already:

If you're running MySQL 4.0, and use indexes in your tables, you can do the following:

ALTER TABLE tbl_name DISABLE KEYS
(inserts)
ALTER TABLE tbl_name ENABLE KEYS

This will make MySQL wait until the end of the inserts before it updates your indexes, which is way faster.

You can accomplish the same thing by locking the table:

LOCK TABLES tbl_name WRITE
(inserts)
UNLOCK TABLES

After you're done deleting and inserting, you may also want to run OPTIMIZE TABLE tbl_name.

I got these tips from the MySQL manual: http://dev.mysql.com/doc/mysql/en/Insert_speed.html . I've only ever done the ENABLE/DISABLE KEYS ond OPTIMIZE TABLE statements.

 

[BlackMountainCow]

Do you have all the latest Service Packs for Win2k installed? Especially SP3 boosted my PC a lot while SP4 seemed to slow it down a bit, but that might be just me.

 

[Unforgiven]

service pack 4 nuked my server at home, i reformatted and went back to sp3 after various blue screens in sp4! :|

 

[ProviaFan]

The apps vs. background services thing has always seemed to be a bit of fuzzy logic to me, as I've never understood exactly how it determines what is a foreground and what is a background application.

Anyway, I'll add my US$0.02... SP4 has worked fine for me on both Windows 2000 Pro systems that I've applied it to.

 

[GeoffS]

Originally posted by: kloostec
A couple suggestions for the database stuff if you haven't done them already:

If you're running MySQL 4.0, and use indexes in your tables, you can do the following:

ALTER TABLE tbl_name DISABLE KEYS
(inserts)
ALTER TABLE tbl_name ENABLE KEYS

This will make MySQL wait until the end of the inserts before it updates your indexes, which is way faster.

You can accomplish the same thing by locking the table:

LOCK TABLES tbl_name WRITE
(inserts)
UNLOCK TABLES

After you're done deleting and inserting, you may also want to run OPTIMIZE TABLE tbl_name.

I got these tips from the MySQL manual: http://dev.mysql.com/doc/mysql/en/Insert_speed.html . I've only ever done the ENABLE/DISABLE KEYS ond OPTIMIZE TABLE statements.

Does the DISABLE KEYS only disable the updating of the keys, or does it disable table access via the keys? If it's the latter, then anyone trying to access that table will be in for a bit of a wait. That table currently runs about 1 million records...

 

[GeoffS]

Originally posted by: BlackMountainCow
Do you have all the latest Service Packs for Win2k installed? Especially SP3 boosted my PC a lot while SP4 seemed to slow it down a bit, but that might be just me.

Running SP4 it looks like... I keep it patched whenever an update comes out simply because I don't know enough about security to know what I can safely ignore and what I can't...

Geoff

 

[Rattledagger]

Well, looking on the info that shows then clicking on ?
Application: "Specifies that more processor resources are given to the foreground program than the background program."
Background services: "Specifies that all programs receive equal amounts of processor resources."

So, depending on what application currently is in front, your batch-file should run a little slower if selected "application".
Personally I'm not detecting any marked difference either way.

 

[GeoffS]

I used the DISABLE/ENABLE keys tip that kloostec mentioned above and that made a significant difference not only to the load, but also to accessing the nodes page during the load which used to cause a timeout! Great tip... thanks! :beer:

Another question... I've got 640Mb ram in there... Windows Task Manager says that only 120Mb is being used and any given time... are there tweeks that can be done to MySQL to make better use of the available memory?

Geoff

 

[BlackMountainCow]

There used to be a command line like "ConservativeSwapFileUsage=1" in win98, but I don't know if this still applies to win2k! Used to be in "system.ini".

 

[ProviaFan]

I seem to recall something along the lines of "DisablePagingExecutive" or something like that, which prevents Windows from swapping out parts of the kernel. No idea where to find it, though.

 

[Zbox]

nm

 

[GeoffS]

Originally posted by: Zbox
are you deleting everything in the table where you are removing 50k rows?

No.... otherwise I'd just truncate the table The table holds 24 records for each D2OL node... one for each hour... and I roll them each hour... in with the new hour, out with the old hour.

 

[Zbox]

do you have to use php to do what you do with the table?

 

[GeoffS]

hmmm... dunno... that's the only way I know of, but I'm sure there are other interfaces into mySQL. I just wrote the script in php and call it hourly from a batch file. I'm not sure that there would be a difference if I called the mySQL command from an interpreted module (like php) instead of a compiled module (like a C program) since it's the database that's doing the work...

Geoff

 

[TAandy]

Originally posted by: jliechty
I seem to recall something along the lines of "DisablePagingExecutive" or something like that, which prevents Windows from swapping out parts of the kernel. No idea where to find it, though.

In XP it's at

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management

Not sure if it's the same in 2K though

 

[Zbox]

how do you think it would affect performance if you created 24 hourly tables then just drop the oldest table during your update then rename each of the hourly tables to be an hour older, throw your new hourly data into a new hour1 table.

(obviously you would have to make some other significant changes if you did it this way)

just a curious/silly idea

you may laugh at me if you will

 

[kloostec]

Glad the enable/disable keys thing worked so well!

If you want to start using up a lot of memory, and your data isn't important (you can use this for temporary stuff), you can use HEAP tables:

http://dev.mysql.com/doc/mysql/en/HEAP.html

which are 100% in memory tables. You can't get much faster than that

 

[GeoffS]

Yeah... I saw that... there's not much in the D2OL stats that would work well for... I'll have to give that some thought. I'm just surprised that mySQL isn't more memory intensive than it is!