Help optimizing settings?

I've got a fairly large -- 100+ GB -- MySQL database. It isn't accessed
often -- it's acting more as an archive right now than anything else.
That being said, when it does get accessed, the indeces seem to take
forever to load. Being as I just bumped the RAM from 2 GB to 6 GB, what,
generically, would be the best way to go forward to take advantage of the
extra RAM?

Thanks!

-Ken


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help optimizing settings?

--0050450181022f1510047edb6ed0
Content-Type: text/plain; charset=ISO-8859-1

Using "load index" might help, but you'll have to make sure you allocate
plenty of space to your keycache. If the queries are identical, the query
cache might also be a good candidate, but that seems unlikely.

The memory will also be used by your OS to cache often-used parts of the
filesystem, including the data- and index files. This will also help.

Consider using compressed (archive) tables, as they require less disk I/O
for a given amount of data to be retrieved. Consider additional indexes,
maybe even clustered indexes if the dataset you need is very close to what
you use in the where-clause.



On Fri, Feb 5, 2010 at 3:26 PM, Ken D'Ambrosio wrote:

> I've got a fairly large -- 100+ GB -- MySQL database. It isn't accessed
> often -- it's acting more as an archive right now than anything else.
> That being said, when it does get accessed, the indeces seem to take
> forever to load. Being as I just bumped the RAM from 2 GB to 6 GB, what,
> generically, would be the best way to go forward to take advantage of the
> extra RAM?
>
> Thanks!
>
> -Ken
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0050450181022f1510047edb6ed0--

RE: Help optimizing settings?

Which MySQL server do you use?
What is your storage engine?

-----Original Message-----
From: Ken D'Ambrosio [mailto:ken@jots.org]
Sent: Friday, February 05, 2010 5:26 PM
To: mysql@lists.mysql.com
Subject: Help optimizing settings?

I've got a fairly large -- 100+ GB -- MySQL database. It isn't accessed
often -- it's acting more as an archive right now than anything else.
That being said, when it does get accessed, the indeces seem to take forever
to load. Being as I just bumped the RAM from 2 GB to 6 GB, what,
generically, would be the best way to go forward to take advantage of the
extra RAM?

Thanks!

-Ken


--
This message has been scanned for viruses and dangerous content by
MailScanner, and is believed to be clean.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=kazakevich@devexperts.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Help optimizing settings?

--_000_C791AD6013FCAolafsteinnationwidechildrensorg_
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

In general you need to adjust your server settings to actually use the new =
RAM. =0AThis depends on various things, storage engines used, what else=
runs on the box, etc. =0AIn addition you should look at your sch=
ema, see if you have the right indices for what you want to do. =0AE=
g All columns that you use in joins as well as all columns you use with =
where, order and group should be indexed Olaf O=
n 2/5/10 9:26 AM, "Ken D'Ambrosio" wrote: I've =
got a fairly large -- 100+ GB -- MySQL database It isn't accessed =
often -- it's acting more as an archive right now than anything else. =
=0AThat being said, when it does get accessed, the indeces seem to take=0D=
=0Aforever to load Being as I just bumped the RAM from 2 GB to 6 GB, wh=
at, generically, would be the best way to go forward to take advantage=
of the extra RAM? Thanks! -Ken =0D=
=0A-- This message has been scanned for viruses and dangerous con=
tent by MailScanner, and is believed to be clean. =0A-=
- MySQL General Mailing List For list archives: http://listsmy=
sqlcom/mysql To unsubscribe: http://listsmysqlcom/mysql?un=
sub=3Dolafstein@nationwidechildrensorg =0D= 0A =
------------------------- Olaf Stein DBA B=
attelle Center for Mathematical Medicine Nationwide Children's Hospita=
l, The Research Institute 700 Children's Drive 43205 Columbus, OH=
phone: 1-614-355-5685 cell: 1-614-843-0432 email: olafst=
ein@nationwidechildrensorg "I consider that the golden =
rule requires that if I like a program I must share it with other people wh=
o like it" Richard M Stallman
--_000_C791AD6013FCAolafsteinnationwidechildrensorg_--