-1

I am running MySQL 5.0.51a on Windows Server 2003 Standard Edition on an HP DL580 G4 with 3GB installed. One of my database tables has grown to 5.3 GB with an index file of 2.5 GB, which I believe is causing MySQL to be slow due to having to constantly load and unload the index file when updates are made to the table. The server itself seems to be performing OK because MySQL is only using about 500MB of memory (there are other apps running on the system, but MySQL uses the most memory).

The table is fairly active with new records getting adding all during day but no deletes, ever. The MySQL server has up to 600 connections allowed, but only small number (10 or 20) would actually be writing to this table. I increased the memory limits in MySQL but since the max connections is so high I don't think I can give each connection 1GB without risking a problem. Is there some tuning that would let just certain connections get a lot of memory?

So I have started to look for alternatives to avert the crisis I know is coming soon. Some of the options I have:

  1. Upgrade to Server 2003 Enterprise to install 64GB of memory. Question: would 32 bit MySQL be able to access more than 2GB? Would that be 2GB per thread? That would still be smaller than the index table size so it might not solve the problem completely, but it would be better than now.

  2. Upgrade to Server 200x 64 bit and MySQL 64 bit.

  3. Switch to a *nix 64 bit server.

If anybody has suggestions for things to do in the meantime, opinions on which way to go, or other things that I have overlooked I would appreciate the help.

Thanks

Improve this question
5
  • 3
    Someone more experienced than me with MySQL will probably provide a better answer so I rather just comment. There is no reason, at all, to run a database server in 32bit mode, unless it's a OLD OLD OLD legacy setup that should be burniated.
    – pauska
    Sep 27, 2012 at 17:06
  • @pauska Well, actually, MS Access is 32-bit only, so if you're running an Access database, you... oh, nevermind, I see you cover that possibility in the last three words of your comment.
    – HopelessN00b
    Sep 27, 2012 at 17:08
  • The 2GB limit for 32-bit processes on 64-bit operating systems is a limit on per-process virtual memory. They can still take advantage of all of physical memory. (Exactly how much advantage depends on how the program is written, but worst case, as disk cache.)
    – David Schwartz
    Sep 27, 2012 at 17:27
  • @pauska - the server is pretty old but this is automotive manufacturing, always 1-2 generations behind!
    – Matt
    Sep 27, 2012 at 17:58
  • Yeah, but that's four generations behind!
    – Michael Hampton
    Oct 3, 2012 at 2:32

3 Answers 3

Reset to default
0

You probably have a maximum memory value set in my.conf. Find that file in the MySQL directory, edit the max memory value to something sane and restart the MySQL service.

Even on 32-bit Windows without PAE or the /3GB switch, you should be able to have individual processes address up to 2GB of RAM.

Improve this answer
3
  • thanks, but I read that MySQL is a single process (with multiple threads) so it can access only 2GB total. Plus I have 600 connections so even if they were all independent processes I would not want to let them each access 2GB, times 600 that would be a lot!
    – Matt
    Sep 27, 2012 at 18:02
  • That's correct. But you've said that it's currently only consuming 500MB of RAM. I'm suggesting that you triple or quadruple that (if resources allow).
    – MDMarra
    Sep 27, 2012 at 18:04
  • ok, I did tune MySQL to give it more memory per process but most of the time it isn't maxing out the 600 connections but if it did it would use a lot more than 500MB (the total usage reported in the TaskMgr). I could probably give it more without impacting the other apps yet (plus I am working to move some of them off the server anyway). Thanks
    – Matt
    Sep 27, 2012 at 18:13
0

Another comment.... Credit to MYSql for running so well in your current environment. Options 2 and 3 are your best bet. As a Windows guy, if it were me, I'd be going to an x64 platform. Overkill on the RAM and drive arrays, and you're golden.

But, MySql does very well in Linux. If your skillset is there you won't regret that route.

If you're staying on the same hardware you'd want to go Linux. Option 2 is only successful when going to a new platform.

Server Enterprise has a pretty high license cost to get into (I believe). It's feature-set is tailored at higher-end computing and still will limit MySQL to only 4 gig of space (I believe, - my enterprise knowledge is based on reading, not much hands on). http://msdn.microsoft.com/en-us/library/windows/desktop/aa366796(v=vs.85).aspx

HTH

Improve this answer
2
  • Thanks. Why does option 2 require a new platform? Or do you mean that the upgrade would take the server offline for too long? If I went that route I would probably migrate to another box, but I think it would be possible to upgrade in place with the current hardware + memory - just painful.
    – Matt
    Sep 27, 2012 at 17:57
  • Yes, you're right. Probably possible in place but Server 2012 on 3 gig of ram... while I've not tried it, I don't think I would want to. A RAM upgrade probably wouldn't be too expensive, might be a good choice. Then, one has to look at RAID arrays/drives on the old server vs what you can do budget wise on a new one... I'm not a fan of doing OS updates on hardware over three years old. I'm guessing your box is 4+? But, that's personal opinion.
    – KTech
    Oct 4, 2012 at 20:36
0

It sounds like you are running MySQL with MyISAM tables. In this configuration, it is rather common for a MySQL server not to consume a significant amount of memory. Contrary to InnoDB or MS SQL Server, which are reserving considerable amounts of memory in-process for data and index pages of the database, MyISAM is mostly relying on the OS filesystem cache for buffering disk data with the notable exception of the key buffer.

If you are seeing slow inserts or updates, it might be worth examining the SHOW PROCESSLIST output and looking for the state of your connections performing the DML statements. If you see index-related activity for prolonged periods of time, consider increasing key_buffer_size.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .