--0015174beb627597ed04a1bef529
Content-Type: text/plain; charset=ISO-8859-1
How can I know how memory is being used by Mysql?
I have 32GB Ram, but I can't make mysql to use more than 12GB Ram , and even
that I have tables over 40GB...
Thanks! xD
--0015174beb627597ed04a1bef529--
--------------enig63787842790B003DB64585DB
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Am 25.04.2011 16:24, schrieb Andr=E9s Tello:
> How can I know how memory is being used by Mysql?
>=20
> I have 32GB Ram, but I can't make mysql to use more than 12GB Ram , and=
even
> that I have tables over 40GB...
>=20
> Thanks! xD
depends on storage-engine (myisam or innodb), buffer-sizes, size
of the query-cache, size of keys - the target is not to use the
full memory, the target is use available momory wise
search for "mysqltuner.pl"
very interesting is the memory per connection (depends on many buffer-par=
ams)
becuase with a little mistake the possible max usage can easily be some
hundret GB!
[--] Up for: 17d 21h 53m 13s (39M q [25.329 qps], 132K conn, TX: 60B, RX:=
19B)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 6.1G global + 1.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 6.7G (66% of installed RAM)
[OK] Slow queries: 0% (13/39M)
[OK] Highest usage of available connections: 72% (362/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/74.1M
[OK] Key buffer hit rate: 97.6% (109M cached / 2M reads)
[OK] Query cache efficiency: 73.8% (23M cached / 32M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (247 temp sorts / 731K sorts)
[OK] Temporary tables created on disk: 0% (1K on disk / 1M total)
[OK] Thread cache hit rate: 99% (362 created / 132K connections)
[!!] Table cache hit rate: 8% (131 open / 1K opened)
[OK] Open file limit used: 0% (67/30K)
[OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
[!!] InnoDB data size / buffer pool: 16.6G/5.0G
--=20
Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/
http://www.thelounge.net/signature.asc.what.htm
--------------enig63787842790B003DB64585DB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/
iEYEARECAAYFAk21hWcACgkQhmBjz394AnmOpQCeL6F2pwujBSRU5Ml2Y/cD /Gk8
KhAAn27/hyCAX95t1n/kR+QVUzBfWF0K
=H6KD
-----END PGP SIGNATURE-----
--------------enig63787842790B003DB64585DB--
>-----Original Message-----
>From: Andrés Tello [mailto:mr.criptos@gmail.com]
>Sent: Monday, April 25, 2011 10:24 AM
>To: Mailing-List mysql
>Subject: Memory Usage.
>
>How can I know how memory is being used by Mysql?
>
>I have 32GB Ram, but I can't make mysql to use more than 12GB Ram , and even
>that I have tables over 40GB...
>
>Thanks! xD
[JS] The amount of memory used will be the smallest of
1. Available physical memory
2. Usable memory (this is a limitation built into the code design, both at OS
level and at the MySQL/storage engine level)
3. Allowed memory (a configuration setting)
4. Needed memory
1 and 2 are usually easy to find out.
When it comes to 4, things get very murky. As someone else said, the goal is
to use memory wisely. That means finding a trade-off between efficient use of
memory and speed. Once you understand and make some choices with 4, you can go
back and tinker with 3.
As any performance consultant worth his salt will tell you, "It depends."
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.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
--20cf303f6aae2f8cf404a1d18288
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
I would go with join rather than where condition.
2011/4/26 Hal=E1sz S=E1ndor
> >>>> 2011/04/25 17:42 +0300, Andre Polykanine >>>>
> Here is the first one.
> We have two queries:
> SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON
> `Blogs`.`UserId`=3D`Users`.`Id`;
> and the following one:
> SELECT `Blogs`.* FROM `Blogs`, `Users` WHERE
> `Blogs`.`UserId`=3D`Users`.`Id`;
>
> 1. Are they identical?
> 2. Which is better (faster, more optimal, more kosher, I mean, better
> style...)?
> <<<<<<<<
>
> >>>> 2011/04/25 10:16 -0500, Johnny Withers >>>>
> The only difference once MySQL parses these two queries is the first one =
is
> a LEFT JOIN, which will produce all records from the blogs table even if
> there is no matching record in the users table. The second query produces
> an
> INNER JOIN which means only rows with matching records in both tables wil=
l
> be returned.
>
> ...
>
> I prefer to write the INNER JOIN out though because it leaves my WHERE
> clause to do filtering.
> <<<<<<<<
> and it is usual to write all about the joining in the FROM-clause --the
> tables and the criterion for joining them-- and reserve the WHERE-clause =
for
> filtering the result:
>
> SELECT `blogs`.* FROM `Blogs` LEFT JOIN `Users` ON `Blogs`.`UserId` =3D
> `Users`.`Id`
>
> SELECT `Blogs`.* FROM `Blogs` JOIN `Users` ON `Blogs`.`UserId` =3D
> `Users`.`Id`
>
> That is, if you already had a table with the joined outcome, you would us=
e
> the WHERE-clause to determine what of it enters into further processing.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dsureshkumarilu@gmail.co m
>
>
--=20
Thanks
Suresh Kuna
MySQL DBA
--20cf303f6aae2f8cf404a1d18288--