So every night I run some cron jobs. It requires probably about 20 min to process all the records. I gather the script does something like 10,000 sql queries.
I figure this task was just that intense and needs time to complete, but I looked at CPU and memory usage, and it is super low. Cpu usage is between 1-3% and once in a while will bounce to 50ish for 2-3 seconds.
This VPS is running windows 2003 server with Apache and MySQL. Does this sound right?
How much RAM do you have on the machine? If it is too little, it could be choking and trashing the swap. That can explain the slow speeds on low-cpu usage.
Are you sure you know everything the scripts are doing? Maybe the process is disk bound. Some Linux machines run makewhatis every night and update the database used by locate. To do so, they scan the entire disk compiling a list of filenames. There's a lot of I/O but not much CPU usage. Maybe Windows does something similar? Compiling statistics by parsing the web logs?
If you really are doing lots of SQL queries, maybe they're running on non-indexed fields of large tables, and MySQL has to scan through a lot of records. I think you need to take a closer look at what the nightly process does, in order to track down why it takes so long.
