0

I have a Windows VPS (Windows 2008 R2 x64) that runs a couple WordPress blogs and other few ASP.NET things. The VPS runs 2 CPUs (for example).

With ASP.NET, in such situation, I usually set the app pool to use one CPU / Core, and SQL Server to use the other one (just change it in the server properties).

In MySQL, can I tell it which CPU(s) / Core(s) to use?

quanta
  • 51,413
  • 19
  • 159
  • 217
Meligy
  • 123
  • 1
  • 1
  • 7

2 Answers2

2

Because this actually bugged me I did some extra digging. You can actually set affinity using powershell (!) that should be available for the web edition

create a simple script affinity.ps1

$proc = GET-PROCESS mysqld
$proc.ProcessorAffinity = 0x9

The number (0x9) is in hex and it depends on your cores. Each core has a corresponding decimal value of 2^(n-1) where n is the core number.

So if you want to assign mysql to cores 1 & 4 you add the values of the corresponding cores (1+8) and covert into hex. If you wanted cores 7 & 8 the value would be (64+128) ie 0xC0 or whatever the permutations in between.

Then just schedule the above script to execute on start-up.

thanosk
  • 940
  • 7
  • 16
1

You can set the process affinity.

From the Windows Task Manager choose the MySQL Process and then choose Set Affinity and assign to your preferred cores/CPUs.

If you want a more fine tuned management for allocating resources you could use the System Resource Management

thanosk
  • 940
  • 7
  • 16
  • The problem with this is I have to do it every time I run the MySQL service (i.e. reboot the server). This doesn't happen often of course, but it is a manual process that shouldn't be part of a server where everything should be automated. Is there a way to make it persistent across service (and machine) restarts? – Meligy Nov 10 '11 at 08:43
  • You can use the WSRM to set affinity. Create a policy that only applies to the MySQL process and then create a rule that sets CPU affinity. I don't readily have access to a 2008 Server atm so can't really guide you though it but follow the link above and you should be able to create the above policy. – thanosk Nov 10 '11 at 11:22
  • Oops. That seems to be only enterprise and data center edition. I'm currently running Web Edition and likely might only use Standard otherwise (sorry again for not mentioning it earlier, but I was really thinking the solution would be some MySQL configuration not Windows configuration). – Meligy Nov 10 '11 at 13:28