Hello,

We are rebuilding a database that has disk performance issues. As soon as we access the largest tables (see below) for reporting the disk slows down and other reports take a LONG time to run. Inserts and updates are fine.


The current server
- MySQL 5.0
- Windows 2003 Server (32-bit)
- running on VMWare ESX server
- VMWare monitoring tools indicate that memory is not fully utilised, CPU is not fully utilised, disks are not fully utilised until the reports are run against the large tables (see below)
- RAM = 4 GB assigned
- 6 disks in RAID 5
- innodb database
- innodb_buffer_pool_size = 2 GB
- our database has about 100 tables
- database size = 180GB (data length = 140 GB, index length = 40 GB)
- all in one ibdata file
- the tables range from 100 kB to 40 GB in size, with the 40 GB table being accessed for reports frequently
- Index length from 100 kB up to 15 GB


The new server is:
- MySQL 5.1
- Windows 2008 Server, 64-bit
- on VMware ESX
- 6 disks in RAID 5 (i would have liked RAID 10 but not possible at the moment)
- same database as above BUT... one file per table
- we will be transferring the data between the old and new databases in about a week

- we have improved the speed by modifying some queries, batch processing information overnight and querying that data rather than doing things on-the-fly, and other changes. I thinkw e have hti the limit there, however.

- ultimately, i would like the server to respond quickly even when getting data from the large tables.


The questions I have are:
- how much RAM to give the server, we were thing 18 GB for the server
- what size to make the innodb_buffer_pool_size, we thought 13 GB (18GB on server)
- what settings for any other memory options in my.ini
- how does the RAM get used in MySQL, and the impact it would have on disk performance, especially given the size of some of the tables and indexes.


any assistance or comments on the server are welcome.

kind regards,
DJmysql

before slinging hardware at the problem have you checked the indexing strategy
works?
have you SHOWn or EXPLAINed the queries? to make sure that its not soemhtign in your query design which is causing problems

hello,

thanks for the reply, healdem. but, "slinging hardware" (great term) is much more fun...

Our developer did look at the indexing and did find some problems with indexes. An automated archiving process was removing indexes from the tables in question. These were fixed and we saw some improvement. He has spent some time trying to optimise queries, creating summary tables for some queries (for want of a better word) but we still see performance issues.

I will probably go back to him tomorrow/monday and see what other strategies he comes up with. We have all but the RAM to buy now (repurposing server hardware) so the cost is not an issue but i do not want to waste money, either, especially if this is not the solution...

thanks, again.

DJmysql

The fact you haven't given any details of the tables or their indexes or the query you're running - this indicates you haven't much experience dealing with databases so I'd guess the problem is there. Throwing hardware at the problem might double the speed if you spend enough money but fixing any poor indexes or poor SQL will probably improve the performance by a 100 fold.

Thanks for the reply, mike_bike_kite.

well, true, my own experience is rather limited but our developer has more experience than I do. He has worked through his SQL and we believe has improved the queries as best as can be done, as well as looking at the indexes. Of course, it could be argued that he has doen the best he can but it could be done better, however, that argument coudl be used for most things. It was just such an investigation that led to oru discovery that some indexes were missing in the tables in question.

I would be more than happy to provide output of SHOW and EXPLAIN queries but was not sure if:
- anyone would want to see them
- they may be too specific to our system

other strategies may be to reduce the size of the table by splitting it up so that queries are run on smaller files.

In some ways the original question was to get soem feedback on the effect of RAM on systems where we assume (rightly or wrongly) that the queries and indexes, etc. are efficent.

However, both your response and healdem's response, does prompt me to revisit the actual database/query design.

thanks, again,
DJmysql

I'm sure he's done a fine job but sometimes you find that having a few more eyes looking at the code might just improve it further. We'd need a typical example (preferably short) of a query that seems to be hogging resources, a quick explanation of the sql and and the table definitions including indexes.