How do increase memory allocated to MySQL?
How do increase memory allocated to MySQL?
am 04.02.2011 02:43:08 von Yannis Haralambous
--Apple-Mail-48-649009067
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=iso-8859-1
Hi everybody,
I have loaded a very big amount of data in my MySQL database (coming =
from the YAGO project):=20
-rw-rw---- 1 yannis admin 65 3 f=E9v 16:07 db.opt
-rw-rw---- 1 yannis admin 6392030392 3 f=E9v 21:35 wasfoundin.MYD
-rw-rw---- 1 yannis admin 11085793280 4 f=E9v 04:54 wasfoundin.MYI
-rw-rw---- 1 yannis admin 8668 3 f=E9v 16:09 wasfoundin.frm
as you can see the MYI file is 11Gb and the MYD file 6.4Gb.
Whenever I try to send a query through phpmyadmin, it crashes and I get =
returned to the initial page of phpmyadmin.
When I send an SQL query directly, it works, but it takes many minutes =
(a simple SELECT will take about 10 minutes).
I'm using the latest version of MAMP under MacOS X (on a MacBook Pro 2.6 =
GHz Intel Core 2 with 4Gb of RAM).
What can I do to make phpmyadmin work? Is it a chance to gain more speed =
when interacting through regular SQL
queries, or is it hopeless?
Where can I find more information about using MySQL with such big =
tables?
Thanks in advance=20
--=20
-------------------------------------------------------
Yannis Haralambous
Directeur d'=E9tudes
ADRESSE TEMPORAIRE :
University of Aizu
Aizu-Wakamatsu, Fukushima-ken 965-8580, Japon
ADRESSE PERMANENTE :
Institut T=E9l=E9com, T=E9l=E9com Bretagne
D=E9partement Informatique
UMR CNRS 3192 Lab-STICC
Technop=F4le Brest Iroise
CS 83818, 29238 Brest Cedex 3, France
Tel: +33 2 29 00 14 27
Fax: +33 2 29 00 12 82
Email: yannis.haralambous@telecom-bretagne.eu
Internet: http://omega.enstb.org/yannis
ICBM address: 48°21'31.57"N 4°34'16.76"W
Twitter: y_haralambous
-------------------------------------------------------
....pour distinguer l'ext=E9rieur d'un aquarium,
mieux vaut n'=EAtre pas poisson
....the ball I threw while playing in the park
has not yet reached the ground
Es gab eine Zeit, wo ich nur ungern über Schubert sprechen,
nur Nächtens den Bäumen und Sternen von ihm vorerzählen mögen.
--Apple-Mail-48-649009067
Content-Disposition: attachment;
filename="Yannis Haralambous.vcf"
Content-Type: text/directory;
name="Yannis Haralambous.vcf"
Content-Transfer-Encoding: quoted-printable
BEGIN:VCARD
VERSION:3.0
N:Haralambous;Yannis;;;=0D =0AFN:Yannis=20=
Haralambous
ORG:Enseignant-chercheur\,=20TELECOM=20Bret agne;
=
EMAIL;type=3DINTERNET;type=3DWORK;type=3Dpref:yannis.haralam bous@telecom-b=
retagne.eu
TEL;type=3DWORK;type=3Dpref:+33=20229001427= 0D=0A=
TEL;type=3DCELL:+33=20607981626
TEL;type=3DWORK;type=3D FAX:+33=20=
229001282
item1.ADR;type=3DWORK;type=3Dpref:;;Dépa rtement=20=
Informatique\,=20TELECOM=20Bretagne\,=20CS=2083818;Brest=20C edex=20=
3;;29238;France
item1.X-ABADR:fr
=
X-ABUID:CD3E6B27-C13F-40A4-B2F7-8393D5CE6493\:ABPerson
END:VCARD
=
--Apple-Mail-48-649009067
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=us-ascii
--Apple-Mail-48-649009067
Content-Type: text/plain; charset=us-ascii
--
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
--Apple-Mail-48-649009067--
RE: How do increase memory allocated to MySQL?
am 04.02.2011 03:14:45 von David Brian Chait
Yannis,
How is the data structured? Can you give us an example of the queries that=
you are trying to run? Do you have indexes in place? A very inefficient qu=
ery, or poorly structured database can lead to this type of timeout issue o=
n the type of low end hardware that you are using.
Thanks,
David
-----Original Message-----
From: Yannis Haralambous [mailto:yannis.haralambous@telecom-bretagne.eu]=20
Sent: Thursday, February 03, 2011 5:43 PM
To: mysql@lists.mysql.com
Cc: YAGO@mpi-inf.mpg.de
Subject: How do increase memory allocated to MySQL?
Hi everybody,
I have loaded a very big amount of data in my MySQL database (coming from t=
he YAGO project):=20
-rw-rw---- 1 yannis admin 65 3 f=E9v 16:07 db.opt
-rw-rw---- 1 yannis admin 6392030392 3 f=E9v 21:35 wasfoundin.MYD
-rw-rw---- 1 yannis admin 11085793280 4 f=E9v 04:54 wasfoundin.MYI
-rw-rw---- 1 yannis admin 8668 3 f=E9v 16:09 wasfoundin.frm
as you can see the MYI file is 11Gb and the MYD file 6.4Gb.
Whenever I try to send a query through phpmyadmin, it crashes and I get ret=
urned to the initial page of phpmyadmin.
When I send an SQL query directly, it works, but it takes many minutes (a s=
imple SELECT will take about 10 minutes).
I'm using the latest version of MAMP under MacOS X (on a MacBook Pro 2.6 GH=
z Intel Core 2 with 4Gb of RAM).
What can I do to make phpmyadmin work? Is it a chance to gain more speed wh=
en interacting through regular SQL queries, or is it hopeless?
Where can I find more information about using MySQL with such big tables?
Thanks in advance=20
--
-------------------------------------------------------
Yannis Haralambous
Directeur d'=E9tudes
ADRESSE TEMPORAIRE :
University of Aizu
Aizu-Wakamatsu, Fukushima-ken 965-8580, Japon ADRESSE PERMANENTE :
Institut T=E9l=E9com, T=E9l=E9com Bretagne
D=E9partement Informatique
UMR CNRS 3192 Lab-STICC
Technop=F4le Brest Iroise
CS 83818, 29238 Brest Cedex 3, France
Tel: +33 2 29 00 14 27
Fax: +33 2 29 00 12 82
Email: yannis.haralambous@telecom-bretagne.eu
Internet: http://omega.enstb.org/yannis
ICBM address: 48°21'31.57"N 4°34'16.76"W
Twitter: y_haralambous
-------------------------------------------------------
....pour distinguer l'ext=E9rieur d'un aquarium, mieux vaut n'=EAtre pas poi=
sson
....the ball I threw while playing in the park has not yet reached the groun=
d
Es gab eine Zeit, wo ich nur ungern über Schubert sprechen, nur Nächten=
s den Bäumen und Sternen von ihm vorerzählen mögen.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
Re: How do increase memory allocated to MySQL?
am 04.02.2011 04:17:38 von Yannis Haralambous
--Apple-Mail-52-654678466
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=iso-8859-1
just a single table with an ID field (primary index) and three data =
fields (indexed):
id bigint(20) NO PRI NULL auto_increment
yagoid varchar(32) NO UNI NULL =20
yagoleft varchar(32) NO MUL NULL =20
yakoright varchar(255) NO NULL =20
what am I doing wrong?
the query was just=20
SELECT * FROM wasfoundin WHERE yakoright LIKE '%geography%'
Le 4 f=E9vr. 2011 =E0 11:14, David Brian Chait a =E9crit :
> Yannis,
> How is the data structured? Can you give us an example of the =
queries that you are trying to run? Do you have indexes in place? A very =
inefficient query, or poorly structured database can lead to this type =
of timeout issue on the type of low end hardware that you are using.
>=20
> Thanks,
> David
>=20
> -----Original Message-----
> From: Yannis Haralambous =
[mailto:yannis.haralambous@telecom-bretagne.eu]=20
> Sent: Thursday, February 03, 2011 5:43 PM
> To: mysql@lists.mysql.com
> Cc: YAGO@mpi-inf.mpg.de
> Subject: How do increase memory allocated to MySQL?
>=20
> Hi everybody,
>=20
> I have loaded a very big amount of data in my MySQL database (coming =
from the YAGO project):=20
>=20
> -rw-rw---- 1 yannis admin 65 3 f=E9v 16:07 db.opt
> -rw-rw---- 1 yannis admin 6392030392 3 f=E9v 21:35 wasfoundin.MYD
> -rw-rw---- 1 yannis admin 11085793280 4 f=E9v 04:54 wasfoundin.MYI
> -rw-rw---- 1 yannis admin 8668 3 f=E9v 16:09 wasfoundin.frm
>=20
> as you can see the MYI file is 11Gb and the MYD file 6.4Gb.
>=20
> Whenever I try to send a query through phpmyadmin, it crashes and I =
get returned to the initial page of phpmyadmin.
> When I send an SQL query directly, it works, but it takes many minutes =
(a simple SELECT will take about 10 minutes).
>=20
> I'm using the latest version of MAMP under MacOS X (on a MacBook Pro =
2.6 GHz Intel Core 2 with 4Gb of RAM).
>=20
> What can I do to make phpmyadmin work? Is it a chance to gain more =
speed when interacting through regular SQL queries, or is it hopeless?
> Where can I find more information about using MySQL with such big =
tables?
>=20
> Thanks in advance=20
>=20
> --
> -------------------------------------------------------
> Yannis Haralambous
> Directeur d'=E9tudes
> ADRESSE TEMPORAIRE :
> University of Aizu
> Aizu-Wakamatsu, Fukushima-ken 965-8580, Japon ADRESSE PERMANENTE :
> Institut T=E9l=E9com, T=E9l=E9com Bretagne
> D=E9partement Informatique
> UMR CNRS 3192 Lab-STICC
> Technop=F4le Brest Iroise
> CS 83818, 29238 Brest Cedex 3, France
> Tel: +33 2 29 00 14 27
> Fax: +33 2 29 00 12 82
> Email: yannis.haralambous@telecom-bretagne.eu
> Internet: http://omega.enstb.org/yannis
> ICBM address: 48°21'31.57"N 4°34'16.76"W
> Twitter: y_haralambous
> -------------------------------------------------------
> ...pour distinguer l'ext=E9rieur d'un aquarium, mieux vaut n'=EAtre =
pas poisson
>=20
> ...the ball I threw while playing in the park has not yet reached the =
ground
>=20
> Es gab eine Zeit, wo ich nur ungern über Schubert sprechen, nur =
Nächtens den Bäumen und Sternen von ihm vorerzählen mögen.
>=20
--=20
-------------------------------------------------------
Yannis Haralambous
Directeur d'=E9tudes
ADRESSE TEMPORAIRE :
University of Aizu
Aizu-Wakamatsu, Fukushima-ken 965-8580, Japon
ADRESSE PERMANENTE :
Institut T=E9l=E9com, T=E9l=E9com Bretagne
D=E9partement Informatique
UMR CNRS 3192 Lab-STICC
Technop=F4le Brest Iroise
CS 83818, 29238 Brest Cedex 3, France
Tel: +33 2 29 00 14 27
Fax: +33 2 29 00 12 82
Email: yannis.haralambous@telecom-bretagne.eu
Internet: http://omega.enstb.org/yannis
ICBM address: 48°21'31.57"N 4°34'16.76"W
Twitter: y_haralambous
-------------------------------------------------------
....pour distinguer l'ext=E9rieur d'un aquarium,
mieux vaut n'=EAtre pas poisson
....the ball I threw while playing in the park
has not yet reached the ground
Es gab eine Zeit, wo ich nur ungern über Schubert sprechen,
nur Nächtens den Bäumen und Sternen von ihm vorerzählen mögen.
--Apple-Mail-52-654678466
Content-Disposition: attachment;
filename="Yannis Haralambous.vcf"
Content-Type: text/directory;
name="Yannis Haralambous.vcf"
Content-Transfer-Encoding: quoted-printable
BEGIN:VCARD
VERSION:3.0
N:Haralambous;Yannis;;;=0D =0AFN:Yannis=20=
Haralambous
ORG:Enseignant-chercheur\,=20TELECOM=20Bret agne;
=
EMAIL;type=3DINTERNET;type=3DWORK;type=3Dpref:yannis.haralam bous@telecom-b=
retagne.eu
TEL;type=3DWORK;type=3Dpref:+33=20229001427= 0D=0A=
TEL;type=3DCELL:+33=20607981626
TEL;type=3DWORK;type=3D FAX:+33=20=
229001282
item1.ADR;type=3DWORK;type=3Dpref:;;Dépa rtement=20=
Informatique\,=20TELECOM=20Bretagne\,=20CS=2083818;Brest=20C edex=20=
3;;29238;France
item1.X-ABADR:fr
=
X-ABUID:CD3E6B27-C13F-40A4-B2F7-8393D5CE6493\:ABPerson
END:VCARD
=
--Apple-Mail-52-654678466
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=us-ascii
--Apple-Mail-52-654678466
Content-Type: text/plain; charset=us-ascii
--
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
--Apple-Mail-52-654678466--
RE: How do increase memory allocated to MySQL?
am 04.02.2011 04:24:35 von David Brian Chait
--_000_2EF89273EACD8D47B3A11F12E863AB1113F2E4B11CMAILWESTcor po_
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
VGhhdCBpcyBleGFjdGx5IHdoYXQgSSB3YXMgd29ycmllZCBhYm91dC4gSGUg Y291bGQgdHJ5IHJl
c3RydWN0dXJpbmcgdGhlIGRhdGEsIGluY3JlYXNpbmcgdGhlIHRpbWVvdXRz IHRvIGNvbXBlbnNh
dGUgZm9yIHRoZSBsb25nIHJ1biB0aW1lLCBvciBmaW5kIGJldHRlciBoYXJk d2FyZS4gQW55IG90
aGVyIGlkZWFzPw0KDQpGcm9tOiB3YW5nQHB5dGhpYW4uY29tIFttYWlsdG86 d2FuZ0BweXRoaWFu
LmNvbV0gT24gQmVoYWxmIE9mIFNpbmdlciBYLkouIFdhbmcNClNlbnQ6IFRo dXJzZGF5LCBGZWJy
dWFyeSAwMywgMjAxMSA3OjIwIFBNDQpUbzogWWFubmlzIEhhcmFsYW1ib3Vz DQpDYzogRGF2aWQg
QnJpYW4gQ2hhaXQ7IG15c3FsQGxpc3RzLm15c3FsLmNvbTsgWUFHT0BtcGkt aW5mLm1wZy5kZQ0K
U3ViamVjdDogUmU6IEhvdyBkbyBpbmNyZWFzZSBtZW1vcnkgYWxsb2NhdGVk IHRvIE15U1FMPw0K
DQpZb3UncmUgcXVlcnkgaXMgZG9pbmcgYSBmdWxsIHRhYmxlIHNjYW4uDQoy MDExLzIvMyBZYW5u
aXMgSGFyYWxhbWJvdXMgPHlhbm5pcy5oYXJhbGFtYm91c0B0ZWxlY29tLWJy ZXRhZ25lLmV1PG1h
aWx0bzp5YW5uaXMuaGFyYWxhbWJvdXNAdGVsZWNvbS1icmV0YWduZS5ldT4+ DQpqdXN0IGEgc2lu
Z2xlIHRhYmxlIHdpdGggYW4gSUQgZmllbGQgKHByaW1hcnkgaW5kZXgpIGFu ZCB0aHJlZSBkYXRh
IGZpZWxkcyAoaW5kZXhlZCk6DQoNCmlkICAgICAgYmlnaW50KDIwKSAgICAg IE5PICAgICAgUFJJ
ICAgICBOVUxMICAgIGF1dG9faW5jcmVtZW50DQp5YWdvaWQgIHZhcmNoYXIo MzIpICAgICBOTyAg
ICAgIFVOSSAgICAgTlVMTA0KeWFnb2xlZnQgICAgICAgIHZhcmNoYXIoMzIp ICAgICBOTyAgICAg
IE1VTCAgICAgTlVMTA0KeWFrb3JpZ2h0ICAgICAgIHZhcmNoYXIoMjU1KSAg ICBOTyAgICAgICAg
ICAgICAgTlVMTA0KDQp3aGF0IGFtIEkgZG9pbmcgd3Jvbmc/DQoNCnRoZSBx dWVyeSB3YXMganVz
dA0KDQpTRUxFQ1QgKiBGUk9NIHdhc2ZvdW5kaW4gV0hFUkUgeWFrb3JpZ2h0 IExJS0UgJyVnZW9n
cmFwaHklJw0KDQpMZSA0IGbDqXZyLiAyMDExIMOgIDExOjE0LCBEYXZpZCBC cmlhbiBDaGFpdCBh
IMOpY3JpdCA6DQoNCj4gWWFubmlzLA0KPiAgICAgICBIb3cgaXMgdGhlIGRh dGEgc3RydWN0dXJl
ZD8gQ2FuIHlvdSBnaXZlIHVzIGFuIGV4YW1wbGUgb2YgdGhlIHF1ZXJpZXMg dGhhdCB5b3UgYXJl
IHRyeWluZyB0byBydW4/IERvIHlvdSBoYXZlIGluZGV4ZXMgaW4gcGxhY2U/ IEEgdmVyeSBpbmVm
ZmljaWVudCBxdWVyeSwgb3IgcG9vcmx5IHN0cnVjdHVyZWQgZGF0YWJhc2Ug Y2FuIGxlYWQgdG8g
dGhpcyB0eXBlIG9mIHRpbWVvdXQgaXNzdWUgb24gdGhlIHR5cGUgb2YgbG93 IGVuZCBoYXJkd2Fy
ZSB0aGF0IHlvdSBhcmUgdXNpbmcuDQo+DQo+IFRoYW5rcywNCj4gRGF2aWQN Cj4NCj4gLS0tLS1P
cmlnaW5hbCBNZXNzYWdlLS0tLS0NCj4gRnJvbTogWWFubmlzIEhhcmFsYW1i b3VzIFttYWlsdG86
eWFubmlzLmhhcmFsYW1ib3VzQHRlbGVjb20tYnJldGFnbmUuZXU8bWFpbHRv Onlhbm5pcy5oYXJh
bGFtYm91c0B0ZWxlY29tLWJyZXRhZ25lLmV1Pl0NCj4gU2VudDogVGh1cnNk YXksIEZlYnJ1YXJ5
IDAzLCAyMDExIDU6NDMgUE0NCj4gVG86IG15c3FsQGxpc3RzLm15c3FsLmNv bTxtYWlsdG86bXlz
cWxAbGlzdHMubXlzcWwuY29tPg0KPiBDYzogWUFHT0BtcGktaW5mLm1wZy5k ZTxtYWlsdG86WUFH
T0BtcGktaW5mLm1wZy5kZT4NCj4gU3ViamVjdDogSG93IGRvIGluY3JlYXNl IG1lbW9yeSBhbGxv
Y2F0ZWQgdG8gTXlTUUw/DQo+DQo+IEhpIGV2ZXJ5Ym9keSwNCj4NCj4gSSBo YXZlIGxvYWRlZCBh
IHZlcnkgYmlnIGFtb3VudCBvZiBkYXRhIGluIG15IE15U1FMIGRhdGFiYXNl IChjb21pbmcgZnJv
bSB0aGUgWUFHTyBwcm9qZWN0KToNCj4NCj4gLXJ3LXJ3LS0tLSAgMSB5YW5u aXMgIGFkbWluICAg
ICAgICAgICA2NSAgMyBmw6l2IDE2OjA3IGRiLm9wdA0KPiAtcnctcnctLS0t ICAxIHlhbm5pcyAg
YWRtaW4gICA2MzkyMDMwMzkyICAzIGbDqXYgMjE6MzUgd2FzZm91bmRpbi5N WUQNCj4gLXJ3LXJ3
LS0tLSAgMSB5YW5uaXMgIGFkbWluICAxMTA4NTc5MzI4MCAgNCBmw6l2IDA0 OjU0IHdhc2ZvdW5k
aW4uTVlJDQo+IC1ydy1ydy0tLS0gIDEgeWFubmlzICBhZG1pbiAgICAgICAg IDg2NjggIDMgZsOp
diAxNjowOSB3YXNmb3VuZGluLmZybQ0KPg0KPiBhcyB5b3UgY2FuIHNlZSB0 aGUgTVlJIGZpbGUg
aXMgMTFHYiBhbmQgdGhlIE1ZRCBmaWxlIDYuNEdiLg0KPg0KPiBXaGVuZXZl ciBJIHRyeSB0byBz
ZW5kIGEgcXVlcnkgdGhyb3VnaCBwaHBteWFkbWluLCBpdCBjcmFzaGVzIGFu ZCBJIGdldCByZXR1
cm5lZCB0byB0aGUgaW5pdGlhbCBwYWdlIG9mIHBocG15YWRtaW4uDQo+IFdo ZW4gSSBzZW5kIGFu
IFNRTCBxdWVyeSBkaXJlY3RseSwgaXQgd29ya3MsIGJ1dCBpdCB0YWtlcyBt YW55IG1pbnV0ZXMg
KGEgc2ltcGxlIFNFTEVDVCB3aWxsIHRha2UgYWJvdXQgMTAgbWludXRlcyku DQo+DQo+IEknbSB1
c2luZyB0aGUgbGF0ZXN0IHZlcnNpb24gb2YgTUFNUCB1bmRlciBNYWNPUyBY IChvbiBhIE1hY0Jv
b2sgUHJvIDIuNiBHSHogSW50ZWwgQ29yZSAyIHdpdGggNEdiIG9mIFJBTSku DQo+DQo+IFdoYXQg
Y2FuIEkgZG8gdG8gbWFrZSBwaHBteWFkbWluIHdvcms/IElzIGl0IGEgY2hh bmNlIHRvIGdhaW4g
bW9yZSBzcGVlZCB3aGVuIGludGVyYWN0aW5nIHRocm91Z2ggcmVndWxhciBT UUwgcXVlcmllcywg
b3IgaXMgaXQgaG9wZWxlc3M/DQo+IFdoZXJlIGNhbiBJIGZpbmQgbW9yZSBp bmZvcm1hdGlvbiBh
Ym91dCB1c2luZyBNeVNRTCB3aXRoIHN1Y2ggYmlnIHRhYmxlcz8NCj4NCj4g VGhhbmtzIGluIGFk
dmFuY2UNCj4NCj4gLS0NCj4gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLQ0KPiBZYW5uaXMgSGFyYWxhbWJvdXMNCj4gRGly ZWN0ZXVyIGQnw6l0
dWRlcw0KPiBBRFJFU1NFIFRFTVBPUkFJUkUgOg0KPiBVbml2ZXJzaXR5IG9m IEFpenUNCj4gQWl6
dS1XYWthbWF0c3UsIEZ1a3VzaGltYS1rZW4gIDk2NS04NTgwLCBKYXBvbiBB RFJFU1NFIFBFUk1B
TkVOVEUgOg0KPiBJbnN0aXR1dCBUw6lsw6ljb20sIFTDqWzDqWNvbSBCcmV0 YWduZQ0KPiBEw6lw
YXJ0ZW1lbnQgSW5mb3JtYXRpcXVlDQo+IFVNUiBDTlJTIDMxOTIgTGFiLVNU SUNDDQo+IFRlY2hu
b3DDtGxlIEJyZXN0IElyb2lzZQ0KPiBDUyA4MzgxOCwgMjkyMzggQnJlc3Qg Q2VkZXggMywgRnJh
bmNlDQo+IFRlbDogKzMzIDIgMjkgMDAgMTQgMjcNCj4gRmF4OiArMzMgMiAy OSAwMCAxMiA4Mg0K
PiBFbWFpbDogeWFubmlzLmhhcmFsYW1ib3VzQHRlbGVjb20tYnJldGFnbmUu ZXU8bWFpbHRvOnlh
bm5pcy5oYXJhbGFtYm91c0B0ZWxlY29tLWJyZXRhZ25lLmV1Pg0KPiBJbnRl cm5ldDogaHR0cDov
L29tZWdhLmVuc3RiLm9yZy95YW5uaXMNCj4gSUNCTSBhZGRyZXNzOiA0OMKw MjEnMzEuNTciTiA0
wrAzNCcxNi43NiJXDQo+IFR3aXR0ZXI6IHlfaGFyYWxhbWJvdXMNCj4gLS0t LS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KPiAu Li5wb3VyIGRpc3Rp
bmd1ZXIgbCdleHTDqXJpZXVyIGQndW4gYXF1YXJpdW0sIG1pZXV4IHZhdXQg bifDqnRyZSBwYXMg
cG9pc3Nvbg0KPg0KPiAuLi50aGUgYmFsbCBJIHRocmV3IHdoaWxlIHBsYXlp bmcgaW4gdGhlIHBh
cmsgaGFzIG5vdCB5ZXQgcmVhY2hlZCB0aGUgZ3JvdW5kDQo+DQo+IEVzIGdh YiBlaW5lIFplaXQs
IHdvIGljaCBudXIgdW5nZXJuIMO8YmVyIFNjaHViZXJ0IHNwcmVjaGVuLCBu dXIgTsOkY2h0ZW5z
IGRlbiBCw6R1bWVuIHVuZCBTdGVybmVuIHZvbiBpaG0gdm9yZXJ6w6RobGVu IG3Dtmdlbi4NCj4N
Cg0KLS0NCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0t
LS0tLS0NCllhbm5pcyBIYXJhbGFtYm91cw0KRGlyZWN0ZXVyIGQnw6l0dWRl cw0KQURSRVNTRSBU
RU1QT1JBSVJFIDoNClVuaXZlcnNpdHkgb2YgQWl6dQ0KQWl6dS1XYWthbWF0 c3UsIEZ1a3VzaGlt
YS1rZW4gIDk2NS04NTgwLCBKYXBvbg0KQURSRVNTRSBQRVJNQU5FTlRFIDoN Ckluc3RpdHV0IFTD
qWzDqWNvbSwgVMOpbMOpY29tIEJyZXRhZ25lDQpEw6lwYXJ0ZW1lbnQgSW5m b3JtYXRpcXVlDQpV
TVIgQ05SUyAzMTkyIExhYi1TVElDQw0KVGVjaG5vcMO0bGUgQnJlc3QgSXJv aXNlDQpDUyA4Mzgx
OCwgMjkyMzggQnJlc3QgQ2VkZXggMywgRnJhbmNlDQpUZWw6ICszMyAyIDI5 IDAwIDE0IDI3DQpG
YXg6ICszMyAyIDI5IDAwIDEyIDgyDQpFbWFpbDogeWFubmlzLmhhcmFsYW1i b3VzQHRlbGVjb20t
YnJldGFnbmUuZXU8bWFpbHRvOnlhbm5pcy5oYXJhbGFtYm91c0B0ZWxlY29t LWJyZXRhZ25lLmV1
Pg0KSW50ZXJuZXQ6IGh0dHA6Ly9vbWVnYS5lbnN0Yi5vcmcveWFubmlzDQpJ Q0JNIGFkZHJlc3M6
IDQ4wrAyMSczMS41NyJOIDTCsDM0JzE2Ljc2IlcNClR3aXR0ZXI6IHlfaGFy YWxhbWJvdXMNCi0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0NCi4u
LnBvdXIgZGlzdGluZ3VlciBsJ2V4dMOpcmlldXIgZCd1biBhcXVhcml1bSwN Cm1pZXV4IHZhdXQg
bifDqnRyZSBwYXMgcG9pc3Nvbg0KDQouLi50aGUgYmFsbCBJIHRocmV3IHdo aWxlIHBsYXlpbmcg
aW4gdGhlIHBhcmsNCmhhcyBub3QgeWV0IHJlYWNoZWQgdGhlIGdyb3VuZA0K DQpFcyBnYWIgZWlu
ZSBaZWl0LCB3byBpY2ggbnVyIHVuZ2VybiDDvGJlciBTY2h1YmVydCBzcHJl Y2hlbiwNCm51ciBO
w6RjaHRlbnMgZGVuIELDpHVtZW4gdW5kIFN0ZXJuZW4gdm9uIGlobSB2b3Jl cnrDpGhsZW4gbcO2
Z2VuLg0KDQoNCg0KDQoNCi0tDQpNeVNRTCBHZW5lcmFsIE1haWxpbmcgTGlz dA0KRm9yIGxpc3Qg
YXJjaGl2ZXM6IGh0dHA6Ly9saXN0cy5teXNxbC5jb20vbXlzcWwNClRvIHVu c3Vic2NyaWJlOiAg
ICBodHRwOi8vbGlzdHMubXlzcWwuY29tL215c3FsP3Vuc3ViPXdhbmdAc2lu Z2Vyd2FuZy5jb20N
Cg0KDQotLQ0KDQpUaGUgYmVzdCBjb21wbGltZW50IHlvdSBjb3VsZCBnaXZl IFB5dGhpYW4gZm9y
IG91ciBzZXJ2aWNlIGlzIGEgcmVmZXJyYWwuDQoNCg0K
--_000_2EF89273EACD8D47B3A11F12E863AB1113F2E4B11CMAILWESTcor po_--
Re: How do increase memory allocated to MySQL?
am 04.02.2011 08:06:59 von Johan De Meersman
--000e0cdfff3c3c395d049b6f8732
Content-Type: text/plain; charset=ISO-8859-1
2011/2/4 Yannis Haralambous
> SELECT * FROM wasfoundin WHERE yakoright LIKE '%geography%'
>
That won't use a regular index. Have a look at fulltext indexing.
For the phpmyadmin, I personally feel it's an abomination, not to mention a
disaster waiting to happen; but if you really want to keep using it, you'll
probably have to set the PHP script runtime timeout to something
appropriately high to accomodate the long query runtimes. This can be done
from within the script if neccesary, look at the PHP documentation for that.
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--000e0cdfff3c3c395d049b6f8732--
RE: How do increase memory allocated to MySQL?
am 04.02.2011 16:27:50 von Jerry Schwartz
>-----Original Message-----
>From: Yannis Haralambous [mailto:yannis.haralambous@telecom-bretagne.eu]
>Sent: Thursday, February 03, 2011 10:18 PM
>To: David Brian Chait
>Cc: mysql@lists.mysql.com; YAGO@mpi-inf.mpg.de
>Subject: Re: How do increase memory allocated to MySQL?
[JS]
>
>the query was just
>
>SELECT * FROM wasfoundin WHERE yakoright LIKE '%geography%'
>
[JS] I think it is the leading "%" that is causing the problem.
I don't have a ready solution. Perhaps a full-text index would help, but I'm
not sure.
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
>Le 4 févr. 2011 à 11:14, David Brian Chait a écrit :
>
>> Yannis,
>> How is the data structured? Can you give us an example of the queries
>that you are trying to run? Do you have indexes in place? A very inefficient
>query, or poorly structured database can lead to this type of timeout issue
>on
>the type of low end hardware that you are using.
>>
>> Thanks,
>> David
>>
>> -----Original Message-----
>> From: Yannis Haralambous [mailto:yannis.haralambous@telecom-bretagne.eu]
>> Sent: Thursday, February 03, 2011 5:43 PM
>> To: mysql@lists.mysql.com
>> Cc: YAGO@mpi-inf.mpg.de
>> Subject: How do increase memory allocated to MySQL?
>>
>> Hi everybody,
>>
>> I have loaded a very big amount of data in my MySQL database (coming from
>> the
>YAGO project):
>>
>> -rw-rw---- 1 yannis admin 65 3 fév 16:07 db.opt
>> -rw-rw---- 1 yannis admin 6392030392 3 fév 21:35 wasfoundin.MYD
>> -rw-rw---- 1 yannis admin 11085793280 4 fév 04:54 wasfoundin.MYI
>> -rw-rw---- 1 yannis admin 8668 3 fév 16:09 wasfoundin.frm
>>
>> as you can see the MYI file is 11Gb and the MYD file 6.4Gb.
>>
>> Whenever I try to send a query through phpmyadmin, it crashes and I get
>returned to the initial page of phpmyadmin.
>> When I send an SQL query directly, it works, but it takes many minutes (a
>simple SELECT will take about 10 minutes).
>>
>> I'm using the latest version of MAMP under MacOS X (on a MacBook Pro 2.6
>> GHz
>Intel Core 2 with 4Gb of RAM).
>>
>> What can I do to make phpmyadmin work? Is it a chance to gain more speed
>> when
>interacting through regular SQL queries, or is it hopeless?
>> Where can I find more information about using MySQL with such big tables?
>>
>> Thanks in advance
>>
>> --
>> -------------------------------------------------------
>> Yannis Haralambous
>> Directeur d'études
>> ADRESSE TEMPORAIRE :
>> University of Aizu
>> Aizu-Wakamatsu, Fukushima-ken 965-8580, Japon ADRESSE PERMANENTE :
>> Institut Télécom, Télécom Bretagne
>> Département Informatique
>> UMR CNRS 3192 Lab-STICC
>> Technopôle Brest Iroise
>> CS 83818, 29238 Brest Cedex 3, France
>> Tel: +33 2 29 00 14 27
>> Fax: +33 2 29 00 12 82
>> Email: yannis.haralambous@telecom-bretagne.eu
>> Internet: http://omega.enstb.org/yannis
>> ICBM address: 48°21'31.57"N 4°34'16.76"W
>> Twitter: y_haralambous
>> -------------------------------------------------------
>> ...pour distinguer l'extérieur d'un aquarium, mieux vaut n'être pas poisson
>>
>> ...the ball I threw while playing in the park has not yet reached the
>> ground
>>
>> Es gab eine Zeit, wo ich nur ungern über Schubert sprechen, nur Nächtens
>> den
>Bäumen und Sternen von ihm vorerzählen mögen.
>>
>
>--
>-------------------------------------------------------
>Yannis Haralambous
>Directeur d'études
>ADRESSE TEMPORAIRE :
>University of Aizu
>Aizu-Wakamatsu, Fukushima-ken 965-8580, Japon
>ADRESSE PERMANENTE :
>Institut Télécom, Télécom Bretagne
>Département Informatique
>UMR CNRS 3192 Lab-STICC
>Technopôle Brest Iroise
>CS 83818, 29238 Brest Cedex 3, France
>Tel: +33 2 29 00 14 27
>Fax: +33 2 29 00 12 82
>Email: yannis.haralambous@telecom-bretagne.eu
>Internet: http://omega.enstb.org/yannis
>ICBM address: 48°21'31.57"N 4°34'16.76"W
>Twitter: y_haralambous
>-------------------------------------------------------
>...pour distinguer l'extérieur d'un aquarium,
>mieux vaut n'être pas poisson
>
>...the ball I threw while playing in the park
>has not yet reached the ground
>
>Es gab eine Zeit, wo ich nur ungern über Schubert sprechen,
>nur Nächtens den Bäumen und Sternen von ihm vorerzählen mögen.
--
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: How do increase memory allocated to MySQL?
am 05.02.2011 00:00:42 von Kevin Spencer
2011/2/3 Yannis Haralambous :
>
> what am I doing wrong?
>
> the query was just
>
> SELECT * FROM wasfoundin WHERE yakoright LIKE '%geography%'
When you use a leading wildcard symbol, MySQL will do a full table
scan regardless of any indexes you've created. If you've got a MyISAM
table, I recommend a FULLTEXT index.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
--
Kevin.
--
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: How do increase memory allocated to MySQL?
am 05.02.2011 03:35:16 von Feris Thia
--90e6ba6e82b2587c5e049b7fd9bf
Content-Type: text/plain; charset=ISO-8859-1
Hi Kevin,
On Sat, Feb 5, 2011 at 6:00 AM, Kevin Spencer wrote:
> When you use a leading wildcard symbol, MySQL will do a full table
> scan regardless of any indexes you've created.
>
Is it also apply to regex lookup ?
Regards,
Feris
--90e6ba6e82b2587c5e049b7fd9bf--