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.
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.
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?
[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:
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.
More RAM
More RAM
Faster disks
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.
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?
[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.
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?
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:
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.
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.
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 ) 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.
