MyISAM vs InnoDB on Windows with limited memory

MySQL & MariaDB Other MySQL® Questions
#1

Hi,

We have a server with the following specs:

Windows Server 2003 Standard
1 x Quad Core Xeon 1.86Ghz
3Gb RAM (Actually 5Gb, but windows can only use 3gb)
Raid 5 - SATA 250Gb

All the tables and database being used is about 40Gb with one busy table being 13Gb

We cannot set the InnoDB buffer larger than 640Mb, otherwise the DB does not start.

Most of the heavy traffic tables are bigger than 640Mb, and this just tells me that the tables will not one fit into the innodb buffer.

Would I be better off converting all tables to MyIsam, or should I leave it on Innodb, I am currently not happy with the performance at all, and we are running a failry busy website.

Any suggestions, and please don’t tell me to install Linux, its just not an option at this stage. We have to be on windows for a while longer since a lot of applications was developed in .NET (

I need to get the most out of the system I have and would love some tips in this area.

Thanks a million in advance

#2

Have you checked out the AWE buffer setting? : innodb_buffer_pool_awe_mem_mb
It is used on 32bit windows to be able to increase the memory allowed to the InnoDB buffer pool.

As for changing to MyISAM from InnoDB depends on factors like:
1.If you need foreign key support
2.or transactions
3.And one more important issue is if your application is heavy write/update/delete then you can get problems with MyISAM since MyISAM locks the entire table instead of only the affected rows.

So my suggestion is that you primarily try to get the AWE stuff to work, or you upgrade to a 64bit Windows version or add a separate server that runs only the DB and then you can choose whichever 64bit OS version that you want.

#3

I suppose AWE is an option, but I would need to re-compile mysql for that and that scares the living daylights out of me. (any guides out there)

But Will AWE help a lot, concidering that the tables will still be larger than all the available memory

We have a lot of relational tables, but no foreign keys, we do have indexes on all referenced fields, will creating a foreign key speed things up more? or will it just help with the integrity of the data?

If locking, transactions and foreign keys are not a problem, will myisam be better?

#4
[B]Pulse77 wrote on Thu, 31 January 2008 18:49[/B]
I suppose AWE is an option, but I would need to re-compile mysql for that and that scares the living daylights out of me. (any guides out there)
That is true and I can understand your point.
[B]Pulse77 wrote on Thu, 31 January 2008 18:49[/B]

But Will AWE help a lot, concidering that the tables will still be larger than all the available memory

I would say probably not as much as you hope for.
[B]Pulse77 wrote on Thu, 31 January 2008 18:49[/B]

We have a lot of relational tables, but no foreign keys, we do have indexes on all referenced fields, will creating a foreign key speed things up more? or will it just help with the integrity of the data?

No they just force integrity. The underlying indexes will be the same format.
[B]Pulse77 wrote on Thu, 31 January 2008 18:49[/B]
If locking, transactions and foreign keys are not a problem, will myisam be better?
Could be since you can set the key_buffer_size to about 1GB memory and then the OS cache can use all the rest of the memory. But at the same time starting to convert all those large tables to MyISAM will take quite a while and depending on how your application is accessing the data in the tables it might not affect that much since you still have a very large DB compared to the useful amount of RAM.

With very large DB’s like yours the way out for performance is:

  1. Make sure all queries are optimized with correct indexes etc. If you have a query and DB that is not optimized it is very unforgiving on large amounts of data since reading from disks is so very slow compared to reading data cached in RAM.
  2. More RAM
  3. More RAM :wink:
  4. Faster disks
  5. More disks to spread the load over

So in your case I suggest try optimize application and DB structure more if possible.
And if you have the time you could think about changing the tables to MyISAM but it will most definitely take a long time to convert them and I’m not so sure it would give such a big speed increase.

The best solution would of course be to buy a new 64bit box with a lot of RAM and change to a 64 bit OS. And then still run the application on your current box but run the DB backend on the other box, and then you can still run the .NET stuff just that they talk to the DB on the other server.

Yes it costs to buy a new server but your time is also valuable and by continuing with the machine that you have I fear that you are going to spend a lot of time that won’t really give you any increase in speed.

#5

Sterin,

Thanks for the post, it cleared my head a lot.

It seems as if the only way for us know is to optimize the queries and start looking at a new LINUX server with one trillion Gb of RAM. And then just keep a small windows server to run all the .NET applications from.

Would you say its best to have one server dedicated to the database, or is having apache and all your php code on the same box not so much overhead?

#6
[B]Pulse77 wrote on Thu, 31 January 2008 20:08[/B]
Sterin, Would you say its best to have one server dedicated to the database, or is having apache and all your php code on the same box not so much overhead?
As usual the answer is: it depends. On how much CPU your PHP code and apache is consuming, how much RAM it consumes etc.

The general advice is that you should keep them separated on two servers.
The reason is that you get a very good boundary between the application and the DB. And that makes it possible to much more freely and with certainty configure for example startup variables for MySQL memory usage limits etc. Because you know that there will not be any other application competing for memory and CPU on that server.

But many people with smaller DB’s run them on the same server so it is definitely doable.

#7

Our database is 40Gb and contains about 4 million unique records, but with all the foreign tables it could be as high as 40 million records. Is this considered a large database?

Our site is http://www.jump.co.za/, have a look, and please give some feedback on the response times.

#8

I would say that you have a pretty large DB, especially if it grows to 40 million records.
But more important is also how many queries you have per second.

Because even a small DB but with a lot of queries per second can bring a server to it’s knees if it is not optimized.

When clicking around on your site I didn’t experience any slow response due to the application. I felt it was a bit slow but when I checked the route to your server from where I sit in Sweden I got this:

[B]Quote:[/B]

Tracing route to www.jump.co.za [196.33.227.116]
over a maximum of 30 hops:

1 5 ms 1 ms 1 ms 192.168.1.254
2 * * * Request timed out.
3 <1 ms <1 ms <1 ms 212.247.6.254
4 1 ms 1 ms <1 ms kst-ncore-1.gigabiteth1-1.swip.net [130.244.206.97]
5 <1 ms <1 ms <1 ms kst-core-1.gigabiteth14-0-0.swip.net [130.244.52.105]
6 1 ms <1 ms <1 ms POS4-1.BR1.STK2.ALTER.NET [146.188.49.13]
7 1 ms 1 ms 1 ms so-3-1-0.TL2.STK2.ALTER.NET [146.188.6.85]
8 36 ms 36 ms 36 ms so-0-0-0.TL2.LND2.ALTER.NET [146.188.14.214]
9 37 ms 36 ms 36 ms so-6-0-0.XR2.LND2.ALTER.NET [146.188.7.233]
10 36 ms 36 ms 36 ms POS0-0-0.GW3.LND2.ALTER.NET [158.43.233.109]
11 37 ms 41 ms 38 ms U88745.CUSTOMER.ALTER.NET [158.43.10.66]
12 34 ms 36 ms 34 ms core1a-dock-gi1-0-5-13.ip.isnet.net [168.209.247.149]
13 290 ms 292 ms 293 ms 168.209.161.160
14 292 ms 292 ms 293 ms 168.209.160.208
15 375 ms 508 ms * core1b-rba-7600-gi1-0-19-4.ip.isnet.net [168.209.161.203]
16 294 ms 295 ms 290 ms core2a-rba-gi0-2.ip.isnet.net [168.209.1.142]
17 296 ms 294 ms 296 ms core2a-bry-gi0-3-216.ip.isnet.net [168.209.0.157]
18 297 ms * 297 ms 168.209.218.250
19 295 ms 295 ms 294 ms sw-osfw-shared-1-bry.hosting.co.za [196.38.64.6]
20 295 ms 297 ms 295 ms 196.14.75.242
21 296 ms 291 ms 293 ms www.jump.co.za [196.33.227.116]
Trace complete.

So my main experience of slow site is probably from just high latency.

But if you plan for future growth and are thinking about buying new hardware then remember two things:
A lot of RAM and fast disks because that is where the bottlenecks usually are when working with large DB’s.

#9

Thanks for all the help. Since the hardware will need to do for some time, I will spend most of my time on indexes and optimization of queries. On any given time there are about 60 people on site and Queries per second average on 20, so the load is not too bad, but I will now only focus on long running queries and query optimization and making sure that no queries run that is not needed.

Thanks again.

#10

Yes 20 qps is pretty ok.
And it is good that you look at optimizations, but remember that your time is also valuable, so you don’t spend weeks of work on optimizing something that could have been solved by a HW upgrade.
As always it’s a balance.

But optimizing is fun (at least I think so :wink: ) and it is a good thing to know. Since you will see things in a different light on the next project you start and you will think about it throughout the entire project.

You’re welcome! )

#11

Optimization is my life, if I can squeze another 0.1 of second of a query I am the happiest man alive.

I don’t think one can ever spend too much time on optimization ) just think if its fast a bad setup, how fast it will be when you upgrade )

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright © 2006 - 2021 Percona LLC. All rights reserved.