Re: Ssd vs mix of ssd and spinning disk

[shawn l.green]

Hi Shain,

On 5/8/2017 1:53 PM, Shain Miley wrote:

Hello,
We have traditionally setup our mysql database servers with a mix of ssd and 
spinning disk drives.

We use the ssd drives (Raid-1) for the mysql tablespace data, and we use the 
spinning disks (15 sas in Raid-1) for the index data, etc.

I am wondering if going forward we should simply put all of the data on the ssd 
drives. Does anyone have any information on whether or not we would see any 
significant performance increase if we made this switch?

I have been thinking about using 4 ssd drives (Raid-10) going forward…and 
wondering if we should expect to see any improvement in the database 
performance.

Any thoughts?

Thanks in advance,

Shain



If you would benefit from shifting storage technologies depends on how 
limiting your current devices are to your overall throughput.


In most cases, workloads are either CPU-bound (normally due to poor 
choices in table design or query patterns) or DISK-bound (too many reads 
and writes, i/o requests,  for the device to keep up).


Occasionally systems become MEMORY-bound (normally due to poor 
configuration choices which push the system to using swap) or 
NETWORK-bound (the number of concurrent network round trips to complete 
a task is higher than the components can handle).



Of those 4 situations, which is contributing most to your total response 
latency?


For example, are you spending more time waiting for data to be buffered 
in from disk than you are computing and returning the results? If so, 
faster disks could help temporarily.


What may help more (and for a longer time) is to improve your storage 
and retrieval patterns (table and query designs) to require less 
frequent trips to disk (aka, better buffering) or to need smaller slices 
of each table (more selective indexes, querying for fewer columns, 
sharding tables, sharding data to different instances, partitioning 
data, ... ).



--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Ssd vs mix of ssd and spinning disk

[Reindl Harald]

Am 08.05.2017 um 19:53 schrieb Shain Miley:

Hello,
We have traditionally setup our mysql database servers with a mix of ssd and 
spinning disk drives.

We use the ssd drives (Raid-1) for the mysql tablespace data, and we use the 
spinning disks (15 sas in Raid-1) for the index data, etc.

I am wondering if going forward we should simply put all of the data on the ssd 
drives. Does anyone have any information on whether or not we would see any 
significant performance increase if we made this switch?

I have been thinking about using 4 ssd drives (Raid-10) going forward…and 
wondering if we should expect to see any improvement in the database 
performance.

Any thoughts?


http://www.tansi.org/hybrid/


CAUTION: only works for RAID1 - not for linux RAID10

if i only woul dhave knwen that 10 years ago i would have my setups with 
4 disks on two RAID1 with a RAID0 on top...


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Ssd vs mix of ssd and spinning disk

[Shain Miley]

Hello,
We have traditionally setup our mysql database servers with a mix of ssd and 
spinning disk drives.

We use the ssd drives (Raid-1) for the mysql tablespace data, and we use the 
spinning disks (15 sas in Raid-1) for the index data, etc.

I am wondering if going forward we should simply put all of the data on the ssd 
drives. Does anyone have any information on whether or not we would see any 
significant performance increase if we made this switch?

I have been thinking about using 4 ssd drives (Raid-10) going forward…and 
wondering if we should expect to see any improvement in the database 
performance.

Any thoughts?

Thanks in advance,

Shain

Re: DATETIME vs CHAR for "timestamp"

[SSC_perl]

> On Apr 14, 2017, at 1:07 PM, shawn l.green  wrote:
> 
> That all depends. Do you...

Hi Shawn,

I thought I had replied to your response, but it looks like I didn’t.  
Thank you for your email.  It was a thorough response and the links were very 
helpful, as well.  I’ve settled on both DATE and DATETIME, depending on whether 
the time is needed or not, which means I’ll have to change some of my code, but 
that will only strengthen the script in the long run.

Thanks again,
Frank
https://www.surfshopcart.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: DATETIME vs CHAR for "timestamp"

[shawn l.green]

On 4/14/2017 3:11 PM, SSC_perl wrote:

I have creation date/time fields in my script that are formatted as 
|MM|DD|hh|mm|ss.  Short of changing the script, should I set the field type 
in MySQL to DATETIME, or would it be better in terms of speed and efficiency to 
set it as char(19)?  Or would it not make a difference?

Thanks,
Frank




That all depends. Do you...

a) want mysqld to treat that column as an actual temporal value

or

b) want mysqld to see it as an opaque string of random alphanumeric 
characters


As you appear to have referred to this as a "creation date/time" 
tracking field it appears you want this to be treated like a temporal 
value so that you can easily do things like


SELECT ... WHERE create_date > NOW() - interval 7 days ;

If it's a temporal column, you can use functions like those in the next 
URL against it. If it's a string-type column, you can't unless you first 
convert your string into a temporal data type.


https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html


My suggestion is to use a native temporal data type (I recommend 
DATETIME) and that you review this section on how to format temporal 
literals (so that you can pass them easily from your application into 
MySQL)

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html

Using the correct data type is important to performance. You want to 
avoid forcing the server to perform too many implicit type conversions. 
Those usually nullify any performance improvements an index on those 
columns might provide:

https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

And the native DATETIME data type only needs 8 bytes to store its data 
while your CHAR(16) may need up to 64 bytes of storage.

https://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

DATETIME vs CHAR for "timestamp"

[SSC_perl]

I have creation date/time fields in my script that are formatted as 
|MM|DD|hh|mm|ss.  Short of changing the script, should I set the field type 
in MySQL to DATETIME, or would it be better in terms of speed and efficiency to 
set it as char(19)?  Or would it not make a difference?

Thanks,
Frank
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ENUM() vs TINYINT

[shawn l.green]

On 9/21/2015 9:03 AM, Richard Reina wrote:

I have a column name quarter which I need to have 5 possible inputs; 1, 2,
3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
Hence, I am also thus considering ENUM('first', 'second', 'third',
'fourth', 'overtime') as the input will primarily be used in written
descriptions. Is this a wise or unwise way to design a table column?

Thanks



Depending on the specific sport (and level of competition), there may be 
more than one OT period. Do you really want to aggregate all of the OT 
stats into just one bucket?


It makes better sense to me to use a TINYINT for storage then for any 
values >=5 convert to "OT", "OT2", ...  unless it makes no difference 
for your purposes which period of extra play you might be in.


This would also allow you to easily query your stats for any rows where 
`quarter`>4 to see which games, if any, experienced any OT play at all.


You could do the same with ENUMS but then you would need a longer list 
of ENUMS (OT, OT2, OT3, ... ) to cover all the possibilities.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: ENUM() vs TINYINT

[Jan Steinman]

> From: Richard Reina 
> 
> I have a column name quarter which I need to have 5 possible inputs; 1, 2,
> 3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
> Hence, I am also thus considering ENUM('first', 'second', 'third',
> 'fourth', 'overtime') as the input will primarily be used in written
> descriptions. Is this a wise or unwise way to design a table column?

I think it's a wise way to do things.

I use ENUMs a lot, whenever I'm choosing from a fixed set of a relatively small 
number of items that will not change frequently (or at all).

One other thing to consider is if this particular set of choices will be used 
elsewhere. If so, then consider using a TINYINT index into a different table 
that associates those indices (PK) with strings. Otherwise, I see no good 
reason to use TINYINT.

 You don't have to take insults personally. You can sidestep negative 
energy; you can look for the good in others and utilize that good, as different 
as it may be, to improve your point of view and to enlarge your perspective. -- 
Stephen R. Covey
 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

ENUM() vs TINYINT

[Richard Reina]

I have a column name quarter which I need to have 5 possible inputs; 1, 2,
3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
Hence, I am also thus considering ENUM('first', 'second', 'third',
'fourth', 'overtime') as the input will primarily be used in written
descriptions. Is this a wise or unwise way to design a table column?

Thanks

Re: ENUM() vs TINYINT

[Basil Daoust]

I'm sure your enum is a fine option, but 0,1,2,3,4 would do the same thing.
And you could add a comment to the table to describe it if desired.

On Mon, Sep 21, 2015 at 8:03 AM, Richard Reina  wrote:

> I have a column name quarter which I need to have 5 possible inputs; 1, 2,
> 3, 4, or OT. Because of the OT possibility I am leaning towards ENUM.
> Hence, I am also thus considering ENUM('first', 'second', 'third',
> 'fourth', 'overtime') as the input will primarily be used in written
> descriptions. Is this a wise or unwise way to design a table column?
>
> Thanks
>

OPTIMIZE TABLE vs. myisamchk

[Larry Martell]

I have a very large table (~50GB) and periodically rows are purged
from it and I want to run OPTIMIZE TABLE to recover the space. But I
do not have enough space to run it. If I do run it the server hangs
and must be killed and restarted and the table is damaged and must be
repaired. I do this with myisamchk.

At https://dev.mysql.com/doc/refman/5.1/en/myisam-optimization.html I read this:

To coalesce fragmented rows and eliminate wasted space that results
from deleting or updating rows, run myisamchk in recovery mode:

shell myisamchk -r tbl_name

You can optimize a table in the same way by using the OPTIMIZE TABLE
SQL statement. OPTIMIZE TABLE does a table repair and a key analysis,
and also sorts the index tree so that key lookups are faster. There is
also no possibility of unwanted interaction between a utility and the
server, because the server does all the work when you use OPTIMIZE
TABLE.

Does this mean that myisamchk -r -a -S does the same thing as OPTIMIZE TABLE?

If they do do the same thing, why does myisamchk work but OPTIMIZE
TABLE run out of space?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[thufir]

On Fri, 12 Dec 2014 10:31:53 +0100, Lucio Chiappetti wrote:


 I use nntp newsgroups, in a very simple threaded mode, I subscribe
 interesting groups and look at them once a day or sometimes more
 frequently. I skim through thread titles, expand the interesting ones,
 read the messages, then occasionally save an interesting one to a mail
 folder or reply and take part to a thread.


Someday, in the far, far future, someone will re-invent usenet.


Until then, there's gmane!


although a surprising number of mailing lists have some sort of mistrust 
towards gmane.


-Thufir


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[Ruben Safir]

they are no indexing usenet any longer which is a real problem because
of volumns of archival information on them from everything from SQL
theory to networking commands.

It is depressing 

almost as depressing as the university library with stacks of books on
the floors pushed away to make room for computer terminals.

Ruben



On Thu, Dec 11, 2014 at 09:23:02AM +, Mark Goodge wrote:
 On 10/12/2014 23:40, Reindl Harald wrote:
 
 Am 10.12.2014 um 18:38 schrieb h...@tbbs.net:
 2014/12/10 09:00 +0100, Johan De Meersman 
  One of the (for me, at least) defining features of a forum, is
 that the subjects tend to be divided up into a tree structure, which
 has it's own benefits 
 
 Something more sophisticated than grouping messages by trimmed
 subject-lines?
 maybe involving such header lines as were used in the old netnews (if
 e-mail is part of it)?
 
 every sane MUA supports threading
 see attached screenshot
 
 Indeed. That, to me, is one of the key arguments in favour of a
 mailing list: people can choose how to view the list according to
 their own preference (some like it threaded, others prefer a flat
 view based simply on message date). Other arguments in favour of
 email include:
 
 * Email is a push medium. I don't have to continually re-check a
 website to see if there's any new messages, they simply arrive in my
 list mailbox and I view them at my convenience.
 
 * Individual emails can be forwarded and/or saved independently of
 the others.
 
 * Email gives me a local archive of messages in addition to any
 central archive.
 
 having said that, I think that web-based archives of mailing lists
 can be very useful, particularly for a public list where the archive
 is open to search engines. That makes them a valuable historical
 resource as well as merely a for-the-moment discussion forum. And,
 if you're going to have a web-based archive, it isn't a huge step
 from there to add the ability to post to the list via the web as
 well. That can be helpful for people on corporate email systems who
 don't easily have the ability to subscribe to a list (or filter mail
 from it into a separate folder), as well as people who only need to
 contribute very infrequently and don't want to have to subscribe in
 order to do so. But all this should, IMO, be in addition to the core
 features of an email mailing list, rather than a replacement for
 them.
 
 Mark
 
 -- 
 http://www.markgoodge.uk
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

-- 
So many immigrant groups have swept through our town
that Brooklyn, like Atlantis, reaches mythological
proportions in the mind of the world - RI Safir 1998
http://www.mrbrklyn.com 

DRM is THEFT - We are the STAKEHOLDERS - RI Safir 2002
http://www.nylxs.com - Leadership Development in Free Software
http://www2.mrbrklyn.com/resources - Unpublished Archive 
http://www.coinhangout.com - coins!
http://www.brooklyn-living.com 

Being so tracked is for FARM ANIMALS and and extermination camps, 
but incompatible with living as a free human being. -RI Safir 2013


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[Lucio Chiappetti]

True; and before that there was yahoo groups, and others. Those are not 
fora, however, merely web interfaces to mailing lists / newsgroups.


as a lurker on this list, I jump in. It is curious that newsgroups are 
mentioned only en passant, and NNTP is not mentioned at all.


Still vastly prefer email over fora, as interfaces go. Better 
read/unread views,


Personally I prefer the e-mail INTERFACE, and a good old(-fashioned) email 
client (I use alpine) allows to access with the same interface mail and 
NNTP newsgroups. I am in general not keen of forums because they are not 
interoperable with e-mail (one cannot save a message in a mail folder) 
and usually have each one its own interface ... I don't like to get lost 
in a maze of little forums all different (cit.)


of the (for me, at least) defining features of a forum, is that the 
subjects tend to be divided up into a tree structure, which has it's own 
benefits;


Threading is possible also with NNTP, and in the way and extent chosen by 
the user and the possibilities of the reading agent.


I do subscribe to mailing lists, but usually in MIME-digest mode whenever 
it is allowed. This way I receive a digest once per day, I skim through 
the subjects, and only if I see something interesting I expand the digest 
in a folder, and read the interesting messages. Rarely I could save a 
particularly interesting one in a folder, or reply and take part to a 
thread. I do not keep track of all (other) messages, usually there are the 
list archives.


I use nntp newsgroups, in a very simple threaded mode, I subscribe 
interesting groups and look at them once a day or sometimes more 
frequently. I skim through thread titles, expand the interesting ones, 
read the messages, then occasionally save an interesting one to a mail 
folder or reply and take part to a thread. When I exit I usually mark as 
deleted all posts except mine or a few I keep pending. Next time I enter 
I'll see only new posts and the few undeleted. Of course I can resurrect 
the deleted ones insofar they are still on the NNTP server.
I do not particularly care of the fact they expire from the server after 
some week/months. In the past there was dejanews, present google groups is 
not as good as a way to look for old stuff.


I do subscribe regularly only to two forums, and both use the PhpBB 
interface. I may have registered to other forums to ask a question, but 
may come back to them only if I have another one. On the forums I follows 
(and to which I take part), I keep the view of new (or recent) messages 
so I can see only the recent traffic and skim through interesting stuff. I 
regret there is no way to save interesting messages locally with one tick 
(but the topics of those forums do not deserve it ... eventually phpBB 
has an internal bookmarking mechanism)
Threads in forums tend to last forever or at least longer than they 
deserve and some forumers complain if one opens a new thread instead of 
posting in an existing one.


--

Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html

Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[Mark Goodge]

On 10/12/2014 23:40, Reindl Harald wrote:


Am 10.12.2014 um 18:38 schrieb h...@tbbs.net:

2014/12/10 09:00 +0100, Johan De Meersman 

 One of the (for me, at least) defining features of a forum, is
that the subjects tend to be divided up into a tree structure, which
has it's own benefits 

Something more sophisticated than grouping messages by trimmed
subject-lines?
maybe involving such header lines as were used in the old netnews (if
e-mail is part of it)?


every sane MUA supports threading
see attached screenshot


Indeed. That, to me, is one of the key arguments in favour of a mailing 
list: people can choose how to view the list according to their own 
preference (some like it threaded, others prefer a flat view based 
simply on message date). Other arguments in favour of email include:


* Email is a push medium. I don't have to continually re-check a website 
to see if there's any new messages, they simply arrive in my list 
mailbox and I view them at my convenience.


* Individual emails can be forwarded and/or saved independently of the 
others.


* Email gives me a local archive of messages in addition to any central 
archive.


having said that, I think that web-based archives of mailing lists can 
be very useful, particularly for a public list where the archive is open 
to search engines. That makes them a valuable historical resource as 
well as merely a for-the-moment discussion forum. And, if you're going 
to have a web-based archive, it isn't a huge step from there to add the 
ability to post to the list via the web as well. That can be helpful for 
people on corporate email systems who don't easily have the ability to 
subscribe to a list (or filter mail from it into a separate folder), as 
well as people who only need to contribute very infrequently and don't 
want to have to subscribe in order to do so. But all this should, IMO, 
be in addition to the core features of an email mailing list, rather 
than a replacement for them.


Mark

--
http://www.markgoodge.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[Johan De Meersman]

- Original Message -
 From: Sándor Halász h...@tbbs.net
 Subject: Re: forum vs email

 Something more sophisticated than grouping messages by trimmed subject-lines?
 maybe involving such header lines as were used in the old netnews (if e-mail 
 is

That's only a single level. Fora are more often than not split into entire 
trees, sometimes four or more levels deep.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

回复: Re: forum vs email

[xiangdongzou]

DEAR guys:

 I think Email is good for search and I can download to my moible 
device.

2014-12-11



I AM AN ORACLE FANS!
Skype:Frank.oracle
Email:xiangdong...@gmail.com
Name:东东堂



发件人：Mark Goodge m...@good-stuff.co.uk
发送时间：2014-12-11 17:23
主题：Re: forum vs email
收件人：mysqlmysql@lists.mysql.com
抄送：

On 10/12/2014 23:40, Reindl Harald wrote: 
 
 Am 10.12.2014 um 18:38 schrieb h...@tbbs.net: 
 2014/12/10 09:00 +0100, Johan De Meersman  
  One of the (for me, at least) defining features of a forum, is 
 that the subjects tend to be divided up into a tree structure, which 
 has it's own benefits  
  
 Something more sophisticated than grouping messages by trimmed 
 subject-lines? 
 maybe involving such header lines as were used in the old netnews (if 
 e-mail is part of it)? 
 
 every sane MUA supports threading 
 see attached screenshot 

Indeed. That, to me, is one of the key arguments in favour of a mailing  
list: people can choose how to view the list according to their own  
preference (some like it threaded, others prefer a flat view based  
simply on message date). Other arguments in favour of email include: 

* Email is a push medium. I don't have to continually re-check a website  
to see if there's any new messages, they simply arrive in my list  
mailbox and I view them at my convenience. 

* Individual emails can be forwarded and/or saved independently of the  
others. 

* Email gives me a local archive of messages in addition to any central  
archive. 

having said that, I think that web-based archives of mailing lists can  
be very useful, particularly for a public list where the archive is open  
to search engines. That makes them a valuable historical resource as  
well as merely a for-the-moment discussion forum. And, if you're going  
to have a web-based archive, it isn't a huge step from there to add the  
ability to post to the list via the web as well. That can be helpful for  
people on corporate email systems who don't easily have the ability to  
subscribe to a list (or filter mail from it into a separate folder), as  
well as people who only need to contribute very infrequently and don't  
want to have to subscribe in order to do so. But all this should, IMO,  
be in addition to the core features of an email mailing list, rather  
than a replacement for them. 

Mark 

--  
http://www.markgoodge.uk 

--  
MySQL General Mailing List 
For list archives: http://lists.mysql.com/mysql 
To unsubscribe:http://lists.mysql.com/mysql 

Re: forum vs email

[Johan De Meersman]

- Original Message -
 From: Sándor Halász h...@tbbs.net
 Subject: Re: forum vs email

 I believ that one could both by e-mail and through a webbrowser comment on a
 Google group.

True; and before that there was yahoo groups, and others. Those are not fora, 
however, merely web interfaces to mailing lists / newsgroups. One of the (for 
me, at least) defining features of a forum, is that the subjects tend to be 
divided up into a tree structure, which has it's own benefits; although as 
Shawn says, there is also benefit to the broad exposure you get on a mailing 
list.


 And one who reads  sends e-mail through a webbrowser surely considers
 discussion through e-mail simplie more overhead than using his webbrowser for

I use webmail - to the point where I host my own domains. Still vastly prefer 
email over fora, as interfaces go. Better read/unread views, proper filtering, 
sorting stuff into folder structures that are convenient for me instead of for 
the administrator, etc.


 discussion, too. I further suspect e-mail clients on own computers are not in
 fashion.

That does seem to be the case; although I think in a corporate setting the 
situation is different.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email [was: Re: table-for-column]

[Johan De Meersman]

- Original Message -
 From: Jigal van Hemert ji...@xs4all.nl
 Subject: Re: forum vs email [was: Re: table-for-column]
 
 On typo3.org there used to be mailing lists only in a distant past.
 Later on newsgroups were set up which communicate with the mailing lists
 (newsgroups are the central source of messages).
 Rather recently a forum was built on top of the newsgroup data (FUD
 forum was used). Users on all three message sources can easily
 communicate with eachother.

Hmm. That sounds interesting, I'll have a look. I don't suppose the software is 
available under a foss license? :-p


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email [was: Re: table-for-column]

[Johan De Meersman]

- Original Message -
 From: Johan De Meersman vegiv...@tuxera.be
 Sent: Wednesday, 10 December, 2014 09:02:45
 Subject: Re: forum vs email [was: Re: table-for-column]

 Hmm. That sounds interesting, I'll have a look. I don't suppose the software 
 is
 available under a foss license? :-p

Hm. Typo3 is a CMS; I take it the integration you're speaking of is specific to 
their support environment, and not part of the CMS?


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email [was: Re: table-for-column]

[Jigal van Hemert]

Hi,

On 10/12/2014 09:02, Johan De Meersman wrote:

- Original Message -

From: Jigal van Hemert ji...@xs4all.nl
Subject: Re: forum vs email [was: Re: table-for-column]

On typo3.org there used to be mailing lists only in a distant past.
Later on newsgroups were set up which communicate with the mailing lists
(newsgroups are the central source of messages).
Rather recently a forum was built on top of the newsgroup data (FUD
forum was used). Users on all three message sources can easily
communicate with eachother.

Hmm. That sounds interesting, I'll have a look. I don't suppose the software is 
available under a foss license? :-p


Mailing lists [1] themselves use Mailman [2] (GPL), forum [3] is done 
with FUD forum [4] (FOSS GPL2), for integration between mailing lists 
and newsgroups, please contact the TYPO3 server admin team [5]. I'm sure 
they will tell you all you want to know about this setup.


[1] http://lists.typo3.org/cgi-bin/mailman/listinfo
[2] http://www.gnu.org/software/mailman/
[3] http://forum.typo3.org/
[4] http://cvs.prohost.org/index.php
[5] http://typo3.org/teams/server-team/

-- Met vriendelijke groet, Jigal van Hemert.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email [was: Re: table-for-column]

[Jigal van Hemert]

Hi,

On 10/12/2014 10:09, Johan De Meersman wrote:
Hm. Typo3 is a CMS; I take it the integration you're speaking of is 
specific to their support environment, and not part of the CMS? 


Correct, TYPO3 is a CMS (also FOSS GPL2+) and the integration is indeed 
not part of the CMS. See my other reply for details on the software that 
was used.


--
Met vriendelijke groet,

Jigal van Hemert.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[hsv]

 2014/12/10 09:00 +0100, Johan De Meersman 
 One of the (for me, at least) defining features of a forum, is that the 
subjects tend to be divided up into a tree structure, which has it's own 
benefits  

Something more sophisticated than grouping messages by trimmed subject-lines?
maybe involving such header lines as were used in the old netnews (if e-mail is 
part of it)?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[Reindl Harald]

Am 10.12.2014 um 18:38 schrieb h...@tbbs.net:

2014/12/10 09:00 +0100, Johan De Meersman 

 One of the (for me, at least) defining features of a forum, is that the 
subjects tend to be divided up into a tree structure, which has it's own 
benefits 

Something more sophisticated than grouping messages by trimmed subject-lines?
maybe involving such header lines as were used in the old netnews (if e-mail is 
part of it)?


every sane MUA supports threading
see attached screenshot

maybe if you switch to such one it also could quote properly



signature.asc
Description: OpenPGP digital signature

Re: forum vs email

[Johan De Meersman]

- Original Message -
 From: Sándor Halász h...@tbbs.net
 Subject: Re: forum vs email

 That is, this list, right? What does it lack (besides readers)?

This list interacts with the forums on mysql.com? Every thread here matches one 
on there, and vice versa? (Honest question; I hardly ever visit the fora - but 
Shawn's earlier mention that he only time for one, not both, makes me think not 
so.)


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[Peter Brawley]

On 2014-12-09 9:55 AM, Johan De Meersman wrote:

- Original Message -

From: Sándor Halász h...@tbbs.net
Subject: Re: forum vs email
That is, this list, right? What does it lack (besides readers)?

This list interacts with the forums on mysql.com?


Nope.

PB

-


Every thread here matches one on there, and vice versa? (Honest question; I 
hardly ever visit the fora - but Shawn's earlier mention that he only time for 
one, not both, makes me think not so.)





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[hsv]

 2014/12/09 15:20 -0600, Peter Brawley 
Nope.

And why not? Because no one bothered to implement it? Now I (for the first 
time?) looked at forums.mysql.com and see more topics than on 
lists.mysql.com. The former is just more with-it, I guess.

I believ that one could both by e-mail and through a webbrowser comment on a 
Google group.

And one who reads  sends e-mail through a webbrowser surely considers 
discussion through e-mail simplie more overhead than using his webbrowser for 
discussion, too. I further suspect e-mail clients on own computers are not in 
fashion.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[shawn l.green]

On 12/9/2014 9:10 PM, h...@tbbs.net wrote:

2014/12/09 15:20 -0600, Peter Brawley 

Nope.

And why not? Because no one bothered to implement it? Now I (for the first time?) looked at 
forums.mysql.com and see more topics than on lists.mysql.com. The former is 
just more with-it, I guess.

I believ that one could both by e-mail and through a webbrowser comment on a 
Google group.

And one who reads  sends e-mail through a webbrowser surely considers 
discussion through e-mail simplie more overhead than using his webbrowser for 
discussion, too. I further suspect e-mail clients on own computers are not in 
fashion.



Well, the Forum does provide a bit less permanence than subscribing to a 
list. You can login, post your questions, then disappear without 
worrying about future emails about topics you may never be interested in.


I guess we (the list members) are more dedicated than the forum users 
because we all recognize the usefulness of seeing a broad range of 
topics presented in an easily filterable and save-able format (email) 
over the web-based content of the forums.


I also find it easier to monitor the emails than the forum simply 
because the most recent response to a list topic does not automatically 
reposition the topic to the top of the list.  It's harder to lose a 
question in the noise when I can see what I have tagged as read/unread. 
I can't do that in the forums.


--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email [was: Re: table-for-column]

[Johan De Meersman]

- Original Message -
 From: Jan Steinman j...@ecoreality.org
 Subject: Re: forum vs email [was: Re: table-for-column]

 There actually seem to be a lot of these around. I'm on several that send me
 email when there are new forum postings.

Yes, that bit is pretty standard functionality; but usually they're little more 
than a notification that something was posted, maybe the first few lines of a 
post.

I want:
 * The entire post, and as little notification-type content as possible,
 * headers and subjects so that mail clients that support threading will thread 
everything from a single forum topic in a mail thread and vice versa,
 * and, most importantly, the ability to also *reply* through mail and have it 
appear in the forum thread at the appropriate place in the conversation

Those things are what would make it a proper mailing list integration, instead 
of just another notification tool.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[hsv]

 2014/12/06 12:51 +0100, Johan De Meersman 
I want:
 * The entire post, and as little notification-type content as possible,
 * headers and subjects so that mail clients that support threading will thread 
everything from a single forum topic in a mail thread and vice versa,
 * and, most importantly, the ability to also *reply* through mail and have it 
appear in the forum thread at the appropriate place in the conversation

Those things are what would make it a proper mailing list integration, instead 
of just another notification tool. 

That is, this list, right? What does it lack (besides readers)?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[Reindl Harald]

Am 06.12.2014 um 16:53 schrieb h...@tbbs.net:

2014/12/06 12:51 +0100, Johan De Meersman 

I want:
  * The entire post, and as little notification-type content as possible,
  * headers and subjects so that mail clients that support threading will 
thread everything from a single forum topic in a mail thread and vice versa,
  * and, most importantly, the ability to also *reply* through mail and have it 
appear in the forum thread at the appropriate place in the conversation

Those things are what would make it a proper mailing list integration, instead 
of just another notification tool.

That is, this list, right? What does it lack (besides readers)?


mail-clients using a readable quoting, your's do not :-)



signature.asc
Description: OpenPGP digital signature

Re: forum vs email [was: Re: table-for-column]

[Jigal van Hemert]

Hi,
On 05/12/2014 20:54, Jan Steinman wrote:

From: Johan De Meersman vegiv...@tuxera.be

I've long wanted to - but never quite got around to - write a forum that 
integrated a mailing list. Bar mail clients that don't handle list threads 
well, it really doesn't seem such a difficult task.

There actually seem to be a lot of these around. I'm on several that send me 
email when there are new forum postings.


On typo3.org there used to be mailing lists only in a distant past. 
Later on newsgroups were set up which communicate with the mailing lists 
(newsgroups are the central source of messages).
Rather recently a forum was built on top of the newsgroup data (FUD 
forum was used). Users on all three message sources can easily 
communicate with eachother.
Only some mail clients have difficulty keeping the threading headers in 
tact, but other than that there are no real issues.


--
Met vriendelijke groet,

Jigal van Hemert.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email [was: Re: table-for-column]

[Michael Dykman]

I have been a resident of this list for a very long time. In the early
days, this was the only place to get reliable information about what was
then a relatively obscure database system. Now, local and online bookstores
have shelves full of books, many of them authored by list regulars. We have
expert forums which have become more more mature and tens of thousands of
example projects readily accessible on github and Google code. And,  lest
we forget, as the product and the documentation matured,  fewer desperate
situations arose. The list of not such a critical last resort as it once
was.

It's true that the list lost a lot of steam after the Oracle acquisition
and Monty's rants had a polarizing effect. Since then, it has been low
traffic with few threads of much interest.

In spite of the rapid rise of NoSql, managed instances of MySQL on a cloud
have become a major commodity. The relational model is not dead and
reliable implementations will always be in demand.

On Sat, 6 Dec 2014 15:53 Jigal van Hemert ji...@xs4all.nl wrote:

 Hi,
 On 05/12/2014 20:54, Jan Steinman wrote:
  From: Johan De Meersman vegiv...@tuxera.be
 
  I've long wanted to - but never quite got around to - write a forum
 that integrated a mailing list. Bar mail clients that don't handle list
 threads well, it really doesn't seem such a difficult task.
  There actually seem to be a lot of these around. I'm on several that
 send me email when there are new forum postings.

 On typo3.org there used to be mailing lists only in a distant past.
 Later on newsgroups were set up which communicate with the mailing lists
 (newsgroups are the central source of messages).
 Rather recently a forum was built on top of the newsgroup data (FUD
 forum was used). Users on all three message sources can easily
 communicate with eachother.
 Only some mail clients have difficulty keeping the threading headers in
 tact, but other than that there are no real issues.

 --
 Met vriendelijke groet,

 Jigal van Hemert.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email

[Mogens Melander]

Just to pitch in, on this rather weird discussion.

I've been on the MySQL pretty much from day one. I started
on mSQL and transferred to MySQL when Monty took that corner.

I'm probably not the only one, lurking in the shadows.

On Sat, December 6, 2014 17:33, Reindl Harald wrote:

 Am 06.12.2014 um 16:53 schrieb h...@tbbs.net:
 2014/12/06 12:51 +0100, Johan De Meersman 
 I want:
   * The entire post, and as little notification-type content as
 possible,
   * headers and subjects so that mail clients that support threading
 will thread everything from a single forum topic in a mail thread and
 vice versa,
   * and, most importantly, the ability to also *reply* through mail and
 have it appear in the forum thread at the appropriate place in the
 conversation

 Those things are what would make it a proper mailing list integration,
 instead of just another notification tool.
 
 That is, this list, right? What does it lack (besides readers)?

 mail-clients using a readable quoting, your's do not :-)




-- 
Mogens Melander
+66 8701 33224


-- 
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

Re: forum vs email [was: Re: table-for-column]

[Jan Steinman]

 From: Johan De Meersman vegiv...@tuxera.be
 
 I've long wanted to - but never quite got around to - write a forum that 
 integrated a mailing list. Bar mail clients that don't handle list threads 
 well, it really doesn't seem such a difficult task.

There actually seem to be a lot of these around. I'm on several that send me 
email when there are new forum postings.

Here is one:
http://www.mobileread.com

Based on hints in the html comments, they appear to be using VBulletin 
(http://www.vbulletin.com/) a fairly common forum package.

 Jan Steinman, EcoReality Co-op 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: forum vs email [was: Re: table-for-column]

[hsv]

 2014/12/04 22:56 -0500, shawn l.green 
I guess this email-based peer-to-peer exchange is slowly disappearing into the 
background like the old usenet newsgroups, eh? 

And _I_ like using an off-line e-mail client, and not being bothered by going 
through a webbrowser--but I suspect that others prefer not to have an e-mail 
client, and prefer to have the freedom to use small, sophisticated gadgets 
instead of bigger gadgets that sit on the table, or take most of a lap.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

[5.1 Vs 5.5 ] ERROR 1045 (28000): Access denied for user 'testuser'@'Serv1.corp.domain.in' (using password: YES)

[Vinay Gupta]

Hi,

I am trying to connect two mysql servers with different versions ( 5.1 
5.5 ) . But in Mysql 5.1 i am facing strange issues.

Below testuser exists in both Mysql Versions :


mysql select host,user,password from mysql.user where user='testuser';
+---++---+
| host  | user   |
password  |
+---++---+
| localhost | testuser   | *FJHHEU5746DDHDUDYDH66488 |
| %.corp.domain.in| testuser   | *FJHHEU5746DDHDUDYDH66488 |
+---++---+

and skip_networking is OFF


*Mysql Version : 5.1.58-log*

root@Serv1:~# mysql -utestuser -p@8AsnM0! -h $(hostname)
ERROR 1045 (28000): Access denied for user 'testuser'@'Serv1.corp.domain.in'
(using password: YES)

It connect successfully if i remove -h option because it connects by
localhost then

*Mysql version : 5.5.36-log *

root@Serv2:~# mysql -utestuser -p@8AsnM0! -h $(hostname)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 5.5.36-log MySQL Community Server (GPL)
mysql

mysql select user(),current_user();
+--+---+
| user()   |
current_user()|
+--+---+
| testu...@serv2.corp.domain.in| testuser@%.corp.domain.in
   |
+--+---+

Is dere some bug in Mysql5.1 or i need to set bind_address parameter in it.

Thanks

MySQL 5.0.0 [2003] vs. MySQL 5.6 [2013] from a SQL and SQL/PSM developer viewpoint

[Lukas Lehner]

Hi

Are there big changes between MySQL 5.0.0 vs. MySQL 5.6? I am only
interested in developer changes (not admin)
Can I use development books for MySQL 5.0.0 [2003] and use the code an
recent MariaDB and MySQL releases?

Re: MySQL 5.0.0 [2003] vs. MySQL 5.6 [2013] from a SQL and SQL/PSM developer viewpoint

[Reindl Harald]

Am 07.01.2014 13:48, schrieb Lukas Lehner:
 Are there big changes between MySQL 5.0.0 vs. MySQL 5.6? I am only
 interested in developer changes (not admin)
 Can I use development books for MySQL 5.0.0 [2003] and use the code an
 recent MariaDB and MySQL releases?

clearly yes

the *other direction* may be problematic in case of unsupported
features in old version - hence you can even use 3.0 books



signature.asc
Description: OpenPGP digital signature

Re: MyISAM table size vs actual data, and performance

[Johan De Meersman]

- Original Message -
 From: Rick James rja...@yahoo-inc.com

Hey Rick,

Thanks for your thoughts.

 * Smells like some huge LONGTEXTs were INSERTed, then DELETEd.
  Perhaps just a single one of nearly 500M.

I considered that, too; but I can see the on-disk size grow over a period of a 
few months - it's not a sudden bump.


 * Yes, there is an impact on full table scans -- it has to step over
 the empty spots.  Or maybe not -- one big cow chip of 500MB would be
 easy to leap over.

Hmm, that is a point. I keep expecting a full tablescan to still use the PK, 
but this isn't InnoDB. Still, it's peculiar then that it suddenly becomes slow 
- I would expect the odds to tip in favour of an index scan as table_free 
grows, not the other way around.


 * OPTIMIZE TABLE is the primary way to recover the space.  It _may_
 be that space on the _end_ is automatically recovered.  If so, you
 might see the .MYD shrink even when OPTIMIZE is not run.

Yes, that's what I do, of course; but the free space should really be 
reallocated to updates - escpecially because of the longtext, which means it's 
allowed to fragment. Are there any tools available to analyze MyISAM datafiles? 
It'd be interesting to see how the free space is really distributed.


 * LONGTEXT is almost never useful.  Do you really think there are
 thingies that big?  Consider changing it to MEDIUMTEXT -- that would
 truncate any biggies to 16MB.
 
 * Smells like a key-value (EAV) schema design.  Such is destined to
 fail when trying to scale.  Yeah, you are probably stuck with
 Drupal.  Here are my comments and recommendations on EAV:
  http://mysql.rjweb.org/doc.php/eav

Yeps, Drupal. I could probably truncate that field, yes; but that's just 
another workaround, not a fix.

It's indeed a key/value scheme - every single page load that gets through the 
caches will select-star that entire table. It's crap, but I have precious 
little influence on the CMS decisionmaking.


 * Please try to find a way in your Email client to display STATUS
 without losing the spacing.

Heh, sorry. I've always preferred the wide layout, and tend to forget \G for 
mails.


 * When you switched to InnoDB, I hope you had innodb_file_per_table
 turned on.  That way, you can actually recoup the space when doing
 ALTER.  Otherwise, you will be stuck with a bloated ibdata1 file
 that you cannot easily shrink.

Default on all instances, of course.


 * In InnoDB, the LONGTEXT will usually be stored separately, thereby
 making a full table scan relatively efficient.

For now, they seem to be behaving. We'll see.


Thanks,
Johan


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: MyISAM table size vs actual data, and performance

[Rick James]

* Smells like some huge LONGTEXTs were INSERTed, then DELETEd.  Perhaps just a 
single one of nearly 500M. 

* Yes, there is an impact on full table scans -- it has to step over the empty 
spots.  Or maybe not -- one big cow chip of 500MB would be easy to leap over.

* OPTIMIZE TABLE is the primary way to recover the space.  It _may_ be that 
space on the _end_ is automatically recovered.  If so, you might see the .MYD 
shrink even when OPTIMIZE is not run.

* LONGTEXT is almost never useful.  Do you really think there are thingies that 
big?  Consider changing it to MEDIUMTEXT -- that would truncate any biggies to 
16MB.

* Smells like a key-value (EAV) schema design.  Such is destined to fail when 
trying to scale.  Yeah, you are probably stuck with Drupal.  Here are my 
comments and recommendations on EAV:  http://mysql.rjweb.org/doc.php/eav

* Please try to find a way in your Email client to display STATUS without 
losing the spacing.

* When you switched to InnoDB, I hope you had innodb_file_per_table turned on.  
That way, you can actually recoup the space when doing ALTER.  Otherwise, you 
will be stuck with a bloated ibdata1 file that you cannot easily shrink.

* In InnoDB, the LONGTEXT will usually be stored separately, thereby making a 
full table scan relatively efficient.

 -Original Message-
 From: Johan De Meersman [mailto:vegiv...@tuxera.be]
 Sent: Friday, February 15, 2013 4:21 AM
 To: mysql.
 Subject: MyISAM table size vs actual data, and performance
 
 
 
 Hey list,
 
 I've got another peculiar thing going on :-) Let me give you a quick
 summary of the situation first: we host a number of Drupal sites, each
 site and it's db on separate VMs for reasons that are not important to
 this scenario. MySQL is 5.0.51a-24+lenny4-log (Debian); I don't have
 the exact Drupal version here but it's likely to be a 5.x branch.
 
 The easy thing to say would of course be upgrade your versions, but
 that's not an option right now. I don't really care if that means I
 have no actual *fix* for the problem - I know how to work around it.
 I'm just looking for a cause, ideally maybe even a specific known bug.
 Strangely enough, I'm seeing this on three distinct installs; but
 others with the same versions and setup (but different sites) seem to
 not exhibit the issue.
 
 So, what I'm seeing is this: Drupal's variable table keeps growing,
 but there does not seem to be more data. I understand how record
 allocation and free space in datafiles works, but this is well beyond
 the normal behaviour.
 
 
 http://www.tuxera.be/filestore/heciexohhohj/df-year.png
 
 As you can see here (the lime green line of /data), growth occurs
 gradually (and the issue happened in september, as well), until it
 seems to reach a certain point. At some point, however, performance on
 that table (notably select * - it's a drupal thing) pretty much
 instantly plummets, and the query takes around half a minute to run -
 whereas now, after reclaiming the free space, it takes 0.03 seconds.
 
 I don't have the exact numbers as I wasn't on-site yesterday evening,
 but since the disk is 5GB, the reclaimed space yesterday must have been
 around 850MB - for a table that is now 30MB. No records were deleted
 from the table, the workaround is as simple as OPTIMIZE TABLE
 variable - simply rebuild the table. The logs make no mention of a
 crashed table, so it's very unlikely that this is a borked index. Even
 if it were, I wouldn't expect a scan of 30MB in 1202 rows to take half
 a minute, on a table that is accessed so often that it's relevant
 blocks are bound to be in the filesystem cache.
 
 The table's structure is fairly simple, too:
 
 
 
 CREATE TABLE `variable` (
 `name` varchar(128) NOT NULL DEFAULT '', `value` longtext NOT NULL,
 PRIMARY KEY (`name`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8
 
 
 
 
 I currently have another system that's also growing that table, here's
 a bit of session:
 
 
 blockquote
 mysql show table status like 'variable';
 +--++-++--++---
 --+-+--+---+---
 -+-+-+-+---
 --+--++-+
 | Name | Engine | Version | Row_format | Rows | Avg_row_length |
 | Data_length | Max_data_length | Index_length | Data_free |
 | Auto_increment | Create_time | Update_time | Check_time | Collation |
 | Checksum | Create_options | Comment |
 +--++-++--++---
 --+-+--+---+---
 -+-+-+-+---
 --+--++-+
 | variable | MyISAM | 10 | Dynamic | 1188 | 795 | 493277732 |
 | 281474976710655 | 41984 | 492332716 | NULL | 2011-12-13 16:18:53 |
 | 2013-02-15 12:35:18 | 2012-10-17 15:45:11 | utf8_general_ci | NULL

MyISAM table size vs actual data, and performance

[Johan De Meersman]

Hey list, 

I've got another peculiar thing going on :-) Let me give you a quick summary of 
the situation first: we host a number of Drupal sites, each site and it's db on 
separate VMs for reasons that are not important to this scenario. MySQL is 
5.0.51a-24+lenny4-log (Debian); I don't have the exact Drupal version here but 
it's likely to be a 5.x branch. 

The easy thing to say would of course be upgrade your versions, but that's 
not an option right now. I don't really care if that means I have no actual 
*fix* for the problem - I know how to work around it. I'm just looking for a 
cause, ideally maybe even a specific known bug. Strangely enough, I'm seeing 
this on three distinct installs; but others with the same versions and setup 
(but different sites) seem to not exhibit the issue. 

So, what I'm seeing is this: Drupal's variable table keeps growing, but there 
does not seem to be more data. I understand how record allocation and free 
space in datafiles works, but this is well beyond the normal behaviour. 


http://www.tuxera.be/filestore/heciexohhohj/df-year.png

As you can see here (the lime green line of /data), growth occurs gradually 
(and the issue happened in september, as well), until it seems to reach a 
certain point. At some point, however, performance on that table (notably 
select * - it's a drupal thing) pretty much instantly plummets, and the query 
takes around half a minute to run - whereas now, after reclaiming the free 
space, it takes 0.03 seconds. 

I don't have the exact numbers as I wasn't on-site yesterday evening, but since 
the disk is 5GB, the reclaimed space yesterday must have been around 850MB - 
for a table that is now 30MB. No records were deleted from the table, the 
workaround is as simple as OPTIMIZE TABLE variable - simply rebuild the 
table. The logs make no mention of a crashed table, so it's very unlikely that 
this is a borked index. Even if it were, I wouldn't expect a scan of 30MB in 
1202 rows to take half a minute, on a table that is accessed so often that it's 
relevant blocks are bound to be in the filesystem cache. 

The table's structure is fairly simple, too: 



CREATE TABLE `variable` ( 
`name` varchar(128) NOT NULL DEFAULT '', 
`value` longtext NOT NULL, 
PRIMARY KEY (`name`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 




I currently have another system that's also growing that table, here's a bit of 
session: 


blockquote
mysql show table status like 'variable'; 
+--++-++--++-+-+--+---++-+-+-+-+--++-+
 
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | 
Max_data_length | Index_length | Data_free | Auto_increment | Create_time | 
Update_time | Check_time | Collation | Checksum | Create_options | Comment | 
+--++-++--++-+-+--+---++-+-+-+-+--++-+
 
| variable | MyISAM | 10 | Dynamic | 1188 | 795 | 493277732 | 281474976710655 | 
41984 | 492332716 | NULL | 2011-12-13 16:18:53 | 2013-02-15 12:35:18 | 
2012-10-17 15:45:11 | utf8_general_ci | NULL | | | 
+--++-++--++-+-+--+---++-+-+-+-+--++-+
 

12:36:55|root@xendbprod1-1:/data/mysql 0 # mysqlcheck --verbose DBNAME variable 
# Connecting to localhost... 
DBBAME.variable OK 
# Disconnecting from localhost... 

12:37:07|root@xendbprod1-1:/data/mysql 0 # mysqlcheck --verbose -g DBNAME 
variable 
# Connecting to localhost... 
DBNAME.variable OK 
# Disconnecting from localhost... 

mysql show table status where name like variable; 
+--++-++--++-+-+--+---++-+-+-+-+--++-+
 
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | 
Max_data_length | Index_length | Data_free | Auto_increment | Create_time | 
Update_time | Check_time | Collation | Checksum | Create_options | Comment | 
+--++-++--++-+-+--+---++-+-+-+-+--++-+
 
| variable | MyISAM | 10 | Dynamic | 1188 | 497 | 493277732 | 281474976710655 | 
41984 | 492686616 | NULL | 2011-12-13 

Re: InnoDB vs. other storage engines

[Michael Widenius]

Hi!

 Manuel == Manuel Arostegui man...@tuenti.com writes:

Manuel 2012/9/19 Mark Haney ma...@abemblem.com
 I hope this doesn't end in some kind of flame war.  I'm looking to
 optimize my tables (and performance in general) of the DB my web app is
 using.  I'm tweaking things a little at a time, but I'm curious as to what
 the rest of the MySQL list thinks about changing my storage engine from
 InnoDB to something else so I can optimize the tables on a regular basis.
 
 Is it worth the effort?  Any caveats?


Manuel Hi Mark,

Manuel I would depend on what your workload would be. Mostly writes, mostly 
reads,
Manuel how many writes/reads do you expect etc.
Manuel The best approach, from my point of view, would be, firstly, tune your
Manuel MySQL server (if you've not done it yet) before getting into 
engine/tables
Manuel optimizations which can be more complicated.

InnoDB is a great engine, but not suitable for everything.
Depending on your usage, moving some tables to another engine may
help.

Here is some suggestions (in no particular order):

- If you want to have small footprint but don't need commit, foreign
  keys or explicite rollback then ARIA is an option.
  http://kb.askmonty.org/en/aria-formerly-known-as-maria/
- Duplicating some data in the MEMORY engine may also be beneficially.
- If your problem is a lot of write, then you should take a look at
  Tokutek. It's an engine that is optimized for a lot of inserts.
  http://www.tokutek.com/products/tokudb-for-mysql/
- If you want to utilize a lot of computers to analyze BIG data then
  ScaleDB (http://www.scaledb.com) or InfiniDB (http://infinidb.org/)
  may be an option.
  
Good luck and please post/blog about your experiences!

Regards,
Monty
Creator of MySQL and MariaDB

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: InnoDB vs. other storage engines

[hsv]

 2012/09/19 13:44 -0700, Rick James 
http://mysql.rjweb.org/doc.php/myisam2innodb

Also, InnoDB enforces foreign-key constraints, MyISAM not.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

InnoDB vs. other storage engines

[Mark Haney]

I hope this doesn't end in some kind of flame war.  I'm looking to 
optimize my tables (and performance in general) of the DB my web app is 
using.  I'm tweaking things a little at a time, but I'm curious as to 
what the rest of the MySQL list thinks about changing my storage engine 
from InnoDB to something else so I can optimize the tables on a regular 
basis.


Is it worth the effort?  Any caveats?

I've never really encountered this situation before and I'm curious to 
see what others have to say on it.


Thanks in advance.

--

Mark Haney
Software Developer/Consultant
AB Emblem
ma...@abemblem.com
Linux marius.homelinux.org 3.5.1-1.fc17.x86_64 GNU/Linux

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: InnoDB vs. other storage engines

[Manuel Arostegui]

2012/9/19 Mark Haney ma...@abemblem.com

 I hope this doesn't end in some kind of flame war.  I'm looking to
 optimize my tables (and performance in general) of the DB my web app is
 using.  I'm tweaking things a little at a time, but I'm curious as to what
 the rest of the MySQL list thinks about changing my storage engine from
 InnoDB to something else so I can optimize the tables on a regular basis.

 Is it worth the effort?  Any caveats?


Hi Mark,

I would depend on what your workload would be. Mostly writes, mostly reads,
how many writes/reads do you expect etc.
The best approach, from my point of view, would be, firstly, tune your
MySQL server (if you've not done it yet) before getting into engine/tables
optimizations which can be more complicated.

 Manuel.

RE: InnoDB vs. other storage engines

[Rick James]

No flames from me; I stay out of that religious war.  However, the general 
consensus is to move to InnoDB.  So, here are the gotchas.  Most are 
non-issues; a few might bite you, but can probably be dealt with:

http://mysql.rjweb.org/doc.php/myisam2innodb



 -Original Message-
 From: Manuel Arostegui [mailto:man...@tuenti.com]
 Sent: Wednesday, September 19, 2012 12:51 PM
 To: Mark Haney
 Cc: mysql mailing list
 Subject: Re: InnoDB vs. other storage engines
 
 2012/9/19 Mark Haney ma...@abemblem.com
 
  I hope this doesn't end in some kind of flame war.  I'm looking to
  optimize my tables (and performance in general) of the DB my web app
  is using.  I'm tweaking things a little at a time, but I'm curious as
  to what the rest of the MySQL list thinks about changing my storage
  engine from InnoDB to something else so I can optimize the tables on
 a regular basis.
 
  Is it worth the effort?  Any caveats?
 
 
 Hi Mark,
 
 I would depend on what your workload would be. Mostly writes, mostly
 reads, how many writes/reads do you expect etc.
 The best approach, from my point of view, would be, firstly, tune your
 MySQL server (if you've not done it yet) before getting into
 engine/tables optimizations which can be more complicated.
 
  Manuel.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: C api mysql_store_result vs mysql_use_result

[Johan De Meersman]

- Original Message -
 From: Alex Schaft al...@quicksoftware.co.za
 
 If I were to do a select count(*) from x where y prior to doing
 select * from x where y to get a number of records, how would this impact
 performance on the server itself? Would the first query be the one to
 do the most processing, with the second one being faster, or would both
 have to do the same amount of work?

Heh. The amount of work put into parsing and executing would be the same, 
except if you can compose your count query to use only indexed fields.

Easily checked with an explain of both queries, I'd say.

Also, do consider if you really need a %complete progress indicator, or if a 
simple record counter with no indicated endpoint will do. That is, do your 
users need to know how long it's going to take, or do they just want assurance 
that the process didn't hang?


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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: C api mysql_store_result vs mysql_use_result

[Alex Schaft]

On 2012/02/09 01:40 PM, Johan De Meersman wrote:

- Original Message -

From: Alex Schaftal...@quicksoftware.co.za

If I were to do a select count(*) from x where y prior to doing
select * from x where y to get a number of records, how would this impact
performance on the server itself? Would the first query be the one to
do the most processing, with the second one being faster, or would both
have to do the same amount of work?

Heh. The amount of work put into parsing and executing would be the same, 
except if you can compose your count query to use only indexed fields.

Easily checked with an explain of both queries, I'd say.

Also, do consider if you really need a %complete progress indicator, or if a 
simple record counter with no indicated endpoint will do. That is, do your 
users need to know how long it's going to take, or do they just want assurance 
that the process didn't hang?


From the user's perspective, they just need to know the process didn't 
hang. The count() query is more for getting memory requirements upfront. 
Can I handle it all, or do I need to break it down into pages?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: C api mysql_store_result vs mysql_use_result

[Johan De Meersman]

- Original Message -
 From: Alex Schaft al...@quicksoftware.co.za
 
 From the user's perspective, they just need to know the process didn't
 hang. The count() query is more for getting memory requirements upfront.
 Can I handle it all, or do I need to break it down into pages?

Then just use the cursor-based api (I guess that's mysql_use_result) all the 
time, and you won't have any memory problems at all. If you need to retrieve 
pages (as in, the third block of 10 results, for instance) LIMIT is your 
friend. Do read the documentation on limit, though - there's performance 
caveats when you use order by and similar.


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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

C api mysql_store_result vs mysql_use_result

[Alex Schaft]

Hi,

I'm currently using mysql_store_result to retrieve all records of a 
query. This poses a problem however if say a couple of thousand records 
get returned, and the user gets no feedback during the progress. I now 
want to change this to mysql_use_result. The only catch is that you 
don't know how many records you're going to get and allocating memory 
for them.


If I were to do a select count(*) from x where y prior to doing select * 
from x where y to get a number of records, how would this impact 
performance on the server itself? Would the first query be the one to do 
the most processing, with the second one being faster, or would both 
have to do the same amount of work?


Thanks,
Alex



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

MERGE Engine vs. UNION ALL

[James W. McKelvey]

We've been experimenting with the merge engine.

But suppose that instead of using the MERGE engine I instead modified my 
code to UNION ALL the shards.


Would I get worse performance? In other words, besides the convenience, 
does the MERGE engine have specific performance optimizations that make 
it perform better?


This is of interest for several reasons. First, my code could determine 
in some cases that only a few (or a single) shards were needed. Second, 
I could apply the shard technique to InnoDB tables. Third, I could apply 
the shard technique to databases that were not identical.


I didn't get any response in the MERGE forum.

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

` vs '

[Brent Clark]

Hiya

Im wondering if someone could help me understand this. If you look at my 
two queries below. By the ORDER BY one is using ` and the other ', as a 
result, if you do an explain you will see that the top query does a 
filesort, while the other does not.


Would anyone know why.

mysql explain SELECT `Contact`.`id`, `Contact`.`name`, 
`Contact`.`surname`, `Contact`.`mobile`, `Contact`.`user_id`, 
`Contact`.`active`, `Contact`.`created` FROM `contacts` AS `Contact`   
WHERE `Contact`.`user_id` = 10203 AND `Contact`.`active` = '1'   ORDER 
BY `name` asc LIMIT 10;

++-+-+--+---+--+-+--++-+
| id | select_type | table   | type | possible_keys | key  | key_len | 
ref  | rows   | Extra   |

++-+-+--+---+--+-+--++-+
|  1 | SIMPLE  | Contact | ALL  | NULL  | NULL | NULL| 
NULL | 344709 | Using where; Using filesort |

++-+-+--+---+--+-+--++-+
1 row in set (0.00 sec)

mysql explain SELECT `Contact`.`id`, `Contact`.`name`, 
`Contact`.`surname`, `Contact`.`mobile`, `Contact`.`user_id`, 
`Contact`.`active`, `Contact`.`created` FROM `contacts` AS `Contact`   
WHERE `Contact`.`user_id` = 10203 AND `Contact`.`active` = '1'   ORDER 
BY 'name' asc LIMIT 10;

++-+-+--+---+--+-+--++-+
| id | select_type | table   | type | possible_keys | key  | key_len | 
ref  | rows   | Extra   |

++-+-+--+---+--+-+--++-+
|  1 | SIMPLE  | Contact | ALL  | NULL  | NULL | NULL| 
NULL | 344710 | Using where |

++-+-+--+---+--+-+--++-+
1 row in set (0.00 sec)

Thanks
Brent

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

Re: ` vs '

[Simcha Younger]

On Wed, 30 Mar 2011 10:05:50 +0200
Brent Clark brentgclarkl...@gmail.com wrote:

 Hiya
 
 Im wondering if someone could help me understand this. If you look at my 
 two queries below. By the ORDER BY one is using ` and the other ', as a 
 result, if you do an explain you will see that the top query does a 
 filesort, while the other does not.
 
 Would anyone know why.
 
 mysql explain SELECT `Contact`.`id`, `Contact`.`name`,  ORDER 
 BY `name` asc LIMIT 10;
this orders by the column `name`, as you expect.
 
 mysql explain SELECT `Contact`.`id`, `Contact`.`name`,   ORDER 
 BY 'name' asc LIMIT 10;

When you use quotes you are no longer referring to the column, instead the 
order orders by the string 'name'. 
This is a meaningless sort, and your results wil not be ordered at all.

 
 Thanks
 Brent

-- 
Simcha Younger sim...@syounger.com

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

Re: ` vs '

[Mark Goodge]

On 30/03/2011 09:05, Brent Clark wrote:

Hiya

Im wondering if someone could help me understand this. If you look at my
two queries below. By the ORDER BY one is using ` and the other ', as a
result, if you do an explain you will see that the top query does a
filesort, while the other does not.


Because column names either need to be unquoted or enclosed in 
backticks. If you put a string inside ordinary quotes (either single or 
double) then it's treated as a string variable. And you can't sort by a 
string variable.


As a demonstration, try these:

SELECT * FROM contacts LIMIT 10
SELECT * FROM `contacts` LIMIT 10
SELECT * FROM 'contacts' LIMIT 10

The first two will work. The third will fail, as you can't select from a 
variable.


Alternatively, try this:

SELECT id FROM contacts LIMIT 10
SELECT `id` FROM contacts LIMIT 10
SELECT 'id' FROM contacts LIMIT 10

and all will be even more clear :-)

Mark
--
 http://mark.goodge.co.uk
 http://www.ratemysupermarket.com

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

Mysql Clustering Vs Scalr

[Adarsh Sharma]

Dear all,

I researched on a link that describes that Mysql to use with scalr for 
fault-tolerance and high availability.


http://scottmartin.net/2009/07/11/creating-a-virtual-datacenter-with-scalr-and-amazon-web-services/

Is it mandatory to use Scalr in our Mysql Production Servers.

What are the limitations that we faced if we don't use Scalr for 
auto-scaling . I think mysql -Clustering Provides HA Cluster and is 
sufficient to handle PB's of storage.


Steps that I folow:

1. We use Mysql Replication for having day to day backups of our 
Production Database Servers despite it doesn't provide HA and fault 
tolerance behaviour i.e if Master fails , it takes time to switch to 
Replicated Node and act as Master.


2. Mysql Clustering handled this limitation well.

But I need points to understand for Scalr usage  and if it is not 
worthful, points to reject it.



Thanks  best regards,
Adarsh Sharma



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

Re: Mysql Clustering Vs Scalr

[Walter Heck]

Take a look at mmm for mysql. Easy and robust.

sent from my mobile phone
On Mar 22, 2011 12:07 PM, Adarsh Sharma adarsh.sha...@orkash.com wrote:
 Dear all,

 I researched on a link that describes that Mysql to use with scalr for
 fault-tolerance and high availability.


http://scottmartin.net/2009/07/11/creating-a-virtual-datacenter-with-scalr-and-amazon-web-services/

 Is it mandatory to use Scalr in our Mysql Production Servers.

 What are the limitations that we faced if we don't use Scalr for
 auto-scaling . I think mysql -Clustering Provides HA Cluster and is
 sufficient to handle PB's of storage.

 Steps that I folow:

 1. We use Mysql Replication for having day to day backups of our
 Production Database Servers despite it doesn't provide HA and fault
 tolerance behaviour i.e if Master fails , it takes time to switch to
 Replicated Node and act as Master.

 2. Mysql Clustering handled this limitation well.

 But I need points to understand for Scalr usage and if it is not
 worthful, points to reject it.


 Thanks  best regards,
 Adarsh Sharma



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

mysql vs source

[Brent Clark]

Hiya

I just found that I can run
 mysql db -e 'source exporteddbdata.sql'

The question I would like to ask is. Is there a speed difference between

mysql db  exporteddbdata.sql
and
mysql db -e 'source exporteddbdata.sql'
(using source)

Reason im asking is, I got a exported 5.4GB database file, and I need to 
import it as quickly as possible.

When I initially testing it, is ran for 170m40.935s

Generally I just scp the files (learnt this from Mylvmbackup), but the 
problem is, is that the source DB is Mysql 5.1 and the target is Mysql 5.0.


Thanks
Brent

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

Re: mysql vs source

[Dan Nelson]

In the last episode (Mar 10), Brent Clark said:
 Hiya
 
 I just found that I can run
   mysql db -e 'source exporteddbdata.sql'
 
 The question I would like to ask is. Is there a speed difference between
 
 mysql db  exporteddbdata.sql
 and
 mysql db -e 'source exporteddbdata.sql'
 (using source)
 
 Reason im asking is, I got a exported 5.4GB database file, and I need to 
 import it as quickly as possible.
 When I initially testing it, is ran for 170m40.935s

I would guess that 95% of that time will be spent within the mysqld server
rather than the mysql client, and if there is a difference between your two
mysql commandlines it won't be noticed.
 
-- 
Dan Nelson
dnel...@allantgroup.com

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

Re: Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!

[Henrik Ingo]

On Sun, Feb 13, 2011 at 11:40 PM, Andrés Tello mr.crip...@gmail.com wrote:
 I have a test process, which runs in the old server in 35 seconds, the new
 server runs the same process in 110.

 There is a change of version from mysql 4.1.22 to  5.1.22.
 We were stuck at 5.1.22 because higher version give us another issules like
 encoding, case sensitivity...

Hi. For 5.0 or 5.1 you should really use the most recent versions.
5.1.22 is beta software, the first stable (GA) release was 5.1.30.

For best performance, you might be better off going directly to 5.5 series.

henrik

-- 
henrik.i...@avoinelama.fi
+358-40-5697354 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

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

Re: Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!

[Andrés Tello]

Yup, I'm doing clean tests,lshutdown, and reload mysql each test.
The raid setup is similar, Faster is raid1 with 10k harddisk, slower is raid
10 with 15k.

Metrics show
Old raid
Secuecial writting 1G: 533 mb/s  (using dd if=/dev/zero of=1G bs=1024
count=102400)
Secuencial reading 1G: 500 mb/s

New raid
Writting: 500 mb/s
Reading 800 mb/s

Average...


On Wed, Feb 16, 2011 at 4:33 PM, Singer X.J. Wang w...@singerwang.comwrote:

 Dumb questions

 1) Are you doing clean tests? Shutdown and restart MySQL after each test?
 2) Is the RAID setup similar on the servers? Are you doing something like
 RAID10 on the old and RAID6 on the new?

 Singer



 On Sun, Feb 13, 2011 at 16:40, Andrés Tello mr.crip...@gmail.com wrote:

 I have a test process, which runs in the old server in 35 seconds, the
 new
 server runs the same process in 110.

 There is a change of version from mysql 4.1.22 to  5.1.22.
 We were stuck at 5.1.22 because higher version give us another issules
 like
 encoding, case sensitivity...

 I really belive that the issue is regarding the mysql server
 there is extensive information about my setup...

 I have more processors, more memory, more disk speed, but lower results...
 T_T, because the wtf is long forgotten.



 hdparm  -tT /dev/sda
 /dev/sda:
  Timing cached reads:   13392 MB in  2.00 seconds = 6699.90 MB/sec
  Timing buffered disk reads:  174 MB in  3.02 seconds =  57.64 MB/sec

  free
 total   used   free sharedbuffers cached
 Mem:  16631296   16065356 565940  0  83148   13415520
 -/+ buffers/cache:2566688   14064608
 Swap: 16779852128   16779724

 uname -r
 2.6.16.21-0.8-bigsmp


 Your MySQL connection id is 21 to server version: 4.1.22-standard-log

 more /proc/cpuinfo  | grep -e processor\|name
 processor   : 0
 model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
 processor   : 1
 model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
 processor   : 2
 model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
 processor   : 3
 model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz

 grep -v ^# /etc/my.cnf | sed '/^$/d'
 [client]
 port= 3306
 socket  = /tmp/mysql.sock
 [mysqld]
 innodb_file_per_table
 port= 3306
 socket  = /tmp/mysql.sock
 back_log = 50
 max_connections = 100
 max_connect_errors = 10
 table_cache = 2048
 max_allowed_packet = 256M
 binlog_cache_size = 16M
 max_heap_table_size = 64M
 sort_buffer_size = 16M
 join_buffer_size = 32M
 thread_cache = 8
 thread_concurrency = 8
 query_cache_size = 256M
 query_cache_limit = 32M
 ft_min_word_len = 4
 memlock
 default_table_type = INNODB
 thread_stack = 192K
 transaction_isolation = REPEATABLE-READ
 tmp_table_size = 256M
 log_slow_queries
 long_query_time = 2
 log_long_format
 tmpdir = /tmp
 key_buffer_size = 128M
 read_buffer_size = 64M
 read_rnd_buffer_size = 128M
 bulk_insert_buffer_size = 64M
 myisam_sort_buffer_size = 128M
 myisam_max_sort_file_size = 10G
 myisam_max_extra_sort_file_size = 10G
 myisam_repair_threads = 1
 myisam_recover
 skip-bdb
 innodb_additional_mem_pool_size = 16M
 innodb_buffer_pool_size = 2G
 innodb_data_file_path = ibdata1:10M:autoextend
 innodb_file_io_threads = 4
 innodb_thread_concurrency = 32
 innodb_flush_log_at_trx_commit = 1
 innodb_log_buffer_size = 8M
 innodb_log_file_size = 256M
 innodb_log_files_in_group = 3
 innodb_max_dirty_pages_pct = 90
 innodb_lock_wait_timeout = 120
 [mysqldump]
 quick
 max_allowed_packet = 16M
 [mysql]
 no-auto-rehash
 [isamchk]
 key_buffer = 512M
 sort_buffer_size = 512M
 read_buffer = 8M
 write_buffer = 8M
 [myisamchk]
 key_buffer = 512M
 sort_buffer_size = 512M
 read_buffer = 8M
 write_buffer = 8M
 [mysqlhotcopy]
 interactive-timeout
 [mysqld_safe]
 open-files-limit = 10240

 New Server, which happnes to be 2x SLOWER!

 hdparm -tT /dev/sda ; free ; uname -r ; cat /proc/cpuinfo | grep -e
 processor\|name

 /dev/sda:
  Timing cached reads:   5858 MB in  2.00 seconds = 2932.17 MB/sec
  Timing buffered disk reads:  1304 MB in  3.00 seconds = 434.06 MB/sec

 total   used   free sharedbuffers cached
 Mem:  330086242097924   30910700  0  21308  76024
 -/+ buffers/cache:2000592   31008032
 Swap:  8388604  08388604

 uname -r
 2.6.34.7-0.7-desktop

 processor   : 0
 model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
 processor   : 1
 model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
 processor   : 2
 model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
 processor   : 3
 model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
 processor   : 4
 model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
 processor   : 5
 model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
 processor   : 6
 model name  : Intel(R) Xeon(R) CPU

Performance issue old server witn mysql 4 vs new server with mysql 5 and old server WINS!

[Andrés Tello]

I have a test process, which runs in the old server in 35 seconds, the new
server runs the same process in 110.

There is a change of version from mysql 4.1.22 to  5.1.22.
We were stuck at 5.1.22 because higher version give us another issules like
encoding, case sensitivity...

I really belive that the issue is regarding the mysql server
there is extensive information about my setup...

I have more processors, more memory, more disk speed, but lower results...
T_T, because the wtf is long forgotten.



hdparm  -tT /dev/sda
/dev/sda:
 Timing cached reads:   13392 MB in  2.00 seconds = 6699.90 MB/sec
 Timing buffered disk reads:  174 MB in  3.02 seconds =  57.64 MB/sec

 free
 total   used   free sharedbuffers cached
Mem:  16631296   16065356 565940  0  83148   13415520
-/+ buffers/cache:2566688   14064608
Swap: 16779852128   16779724

uname -r
2.6.16.21-0.8-bigsmp


Your MySQL connection id is 21 to server version: 4.1.22-standard-log

more /proc/cpuinfo  | grep -e processor\|name
processor   : 0
model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
processor   : 1
model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
processor   : 2
model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz
processor   : 3
model name  : Intel(R) Xeon(R) CPU5160  @ 3.00GHz

grep -v ^# /etc/my.cnf | sed '/^$/d'
[client]
port= 3306
socket  = /tmp/mysql.sock
[mysqld]
innodb_file_per_table
port= 3306
socket  = /tmp/mysql.sock
back_log = 50
max_connections = 100
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 256M
binlog_cache_size = 16M
max_heap_table_size = 64M
sort_buffer_size = 16M
join_buffer_size = 32M
thread_cache = 8
thread_concurrency = 8
query_cache_size = 256M
query_cache_limit = 32M
ft_min_word_len = 4
memlock
default_table_type = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
log_slow_queries
long_query_time = 2
log_long_format
tmpdir = /tmp
key_buffer_size = 128M
read_buffer_size = 64M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-bdb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 10240

New Server, which happnes to be 2x SLOWER!

hdparm -tT /dev/sda ; free ; uname -r ; cat /proc/cpuinfo | grep -e
processor\|name

/dev/sda:
 Timing cached reads:   5858 MB in  2.00 seconds = 2932.17 MB/sec
 Timing buffered disk reads:  1304 MB in  3.00 seconds = 434.06 MB/sec

 total   used   free sharedbuffers cached
Mem:  330086242097924   30910700  0  21308  76024
-/+ buffers/cache:2000592   31008032
Swap:  8388604  08388604

uname -r
2.6.34.7-0.7-desktop

processor   : 0
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 1
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 2
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 3
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 4
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 5
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 6
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 7
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 8
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 9
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 10
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz
processor   : 11
model name  : Intel(R) Xeon(R) CPU   E7450  @ 2.40GHz

Server version: 5.1.52-log Source distribution



grep -v ^# /etc/my.cnf | sed '/^$/d'
[client]
port  = 3306
socket= /tmp/mysqld.sock
[safe_mysqld]
err_log   = /mysql/logs/mysql.err

[mysqld]
skip-external-locking
server_id = 9000
user  = mysql
port   

RE: localhost vs domain for connection string

[Jerry Schwartz]

-Original Message-
From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De
Meersman
Sent: Thursday, November 25, 2010 3:29 AM
To: Jerry Schwartz
Cc: Brent Clark; mysql mailing list
Subject: Re: localhost vs domain for connection string

On Wed, Nov 24, 2010 at 4:44 PM, Jerry Schwartz je...@gii.co.jp wrote:

 [JS] This might or might not be enabled by default. I'm running on Windows,
 and I seem to remember having to change it.

 # Enable named pipe, bypassing the network stack
 enable-named-pipe


Windows' named pipes are not the same as unix sockets, although the general
idea is similar. I'm not sure, but I think the Unix socket file is always
created.

[JS] I don't remember either. I also don't remember if the original question 
was about *nix or Windows.


Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com



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




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

Re: localhost vs domain for connection string

[Johan De Meersman]

On Wed, Nov 24, 2010 at 4:44 PM, Jerry Schwartz je...@gii.co.jp wrote:

 [JS] This might or might not be enabled by default. I'm running on Windows,
 and I seem to remember having to change it.

 # Enable named pipe, bypassing the network stack
 enable-named-pipe


Windows' named pipes are not the same as unix sockets, although the general
idea is similar. I'm not sure, but I think the Unix socket file is always
created.


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

RE: localhost vs domain for connection string

[Jerry Schwartz]

-Original Message-
From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De
Meersman
Sent: Wednesday, November 24, 2010 2:39 AM
To: Jerry Schwartz
Cc: Brent Clark; mysql mailing list
Subject: Re: localhost vs domain for connection string

On Tue, Nov 23, 2010 at 7:55 PM, Jerry Schwartz je...@gii.co.jp wrote:

 IIRC, localhost is seen by the client as a magic word to mean use the
 UNIX socket, not 127.0.0.1.
 
 [JS] IF it is enabled in my.cnf.

Hmm, didn't know that bit. What's the option called ?

[JS] This might or might not be enabled by default. I'm running on Windows, 
and I seem to remember having to change it.

# SERVER SECTION
# --
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Enable named pipe, bypassing the network stack
enable-named-pipe

=
Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com




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




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

localhost vs domain for connection string

[Brent Clark]

Hiya

Is there a difference if someone had to make the connection string the a 
domain (hosts file entry makes the machine look at its ip) as opposed to 
just using localhost.

If so would a performance hit be incurred?

I have this client that has used the domain and in netstat im  seeing 
all this


tcp0  0 own.ex.ip:50340  own.ex.ip:3306   
ESTABLISHED 30324/apache2
tcp0  0 own.ex.ip:50287  own.ex.ip:3306   
ESTABLISHED 30309/apache2
tcp0  0 own.ex.ip:3306   own.ex.ip:50287  
ESTABLISHED 29234/mysqld
tcp0  0 own.ex.ip:50357  own.ex.ip:3306   
ESTABLISHED 31714/apache2
tcp0  0 own.ex.ip:3306   own.ex.ip:50335  
ESTABLISHED 29234/mysqld


But I have another client that is using localhost and netstat is quiet 
as a mouse.


This actually never dawned on me. Hence the reason im asking
But the real reason is that the first clients machine is under heavy 
load and we are trying to see what can be improved.


Kind Regards
Brent Clark

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

Re: localhost vs domain for connection string

[Johan De Meersman]

IIRC, localhost is seen by the client as a magic word to mean use the
UNIX socket, not 127.0.0.1.

So, yes, that would make the connection not show up in netstat :-)

On Tue, Nov 23, 2010 at 11:11 AM, Brent Clark brentgclarkl...@gmail.comwrote:

 Hiya

 Is there a difference if someone had to make the connection string the a
 domain (hosts file entry makes the machine look at its ip) as opposed to
 just using localhost.
 If so would a performance hit be incurred?

 I have this client that has used the domain and in netstat im  seeing all
 this

 tcp0  0 own.ex.ip:50340  own.ex.ip:3306   ESTABLISHED
 30324/apache2
 tcp0  0 own.ex.ip:50287  own.ex.ip:3306   ESTABLISHED
 30309/apache2
 tcp0  0 own.ex.ip:3306   own.ex.ip:50287  ESTABLISHED
 29234/mysqld
 tcp0  0 own.ex.ip:50357  own.ex.ip:3306   ESTABLISHED
 31714/apache2
 tcp0  0 own.ex.ip:3306   own.ex.ip:50335  ESTABLISHED
 29234/mysqld

 But I have another client that is using localhost and netstat is quiet as a
 mouse.

 This actually never dawned on me. Hence the reason im asking
 But the real reason is that the first clients machine is under heavy load
 and we are trying to see what can be improved.

 Kind Regards
 Brent Clark

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@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

Re: localhost vs domain for connection string

[Jan Steinman]

 From: Brent Clark brentgclarkl...@gmail.com
 
 Is there a difference if someone had to make the connection string the a 
 domain (hosts file entry makes the machine look at its ip) as opposed to just 
 using localhost.
 If so would a performance hit be incurred?

Using 'localhost' will always be faster, although perhaps imperceptibly so.


I look into the future because that’s where I am going to spend the rest of my 
life. -- George Burns
 Jan Steinman, EcoReality Co-op 


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

RE: localhost vs domain for connection string

[Jerry Schwartz]

-Original Message-
From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De
Meersman
Sent: Tuesday, November 23, 2010 6:19 AM
To: Brent Clark
Cc: mysql mailing list
Subject: Re: localhost vs domain for connection string

IIRC, localhost is seen by the client as a magic word to mean use the
UNIX socket, not 127.0.0.1.

[JS] IF it is enabled in my.cnf.

So, yes, that would make the connection not show up in netstat :-)

On Tue, Nov 23, 2010 at 11:11 AM, Brent Clark 
brentgclarkl...@gmail.comwrote:

 Hiya

 Is there a difference if someone had to make the connection string the a
 domain (hosts file entry makes the machine look at its ip) as opposed to
 just using localhost.
 If so would a performance hit be incurred?

 I have this client that has used the domain and in netstat im  seeing all
 this

 tcp0  0 own.ex.ip:50340  own.ex.ip:3306   ESTABLISHED
 30324/apache2
 tcp0  0 own.ex.ip:50287  own.ex.ip:3306   ESTABLISHED
 30309/apache2
 tcp0  0 own.ex.ip:3306   own.ex.ip:50287  ESTABLISHED
 29234/mysqld
 tcp0  0 own.ex.ip:50357  own.ex.ip:3306   ESTABLISHED
 31714/apache2
 tcp0  0 own.ex.ip:3306   own.ex.ip:50335  ESTABLISHED
 29234/mysqld

 But I have another client that is using localhost and netstat is quiet as a
 mouse.

 This actually never dawned on me. Hence the reason im asking
 But the real reason is that the first clients machine is under heavy load
 and we are trying to see what can be improved.

 Kind Regards
 Brent Clark

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@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




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

Re: localhost vs domain for connection string

[Johan De Meersman]

On Tue, Nov 23, 2010 at 7:55 PM, Jerry Schwartz je...@gii.co.jp wrote:

 IIRC, localhost is seen by the client as a magic word to mean use the
 UNIX socket, not 127.0.0.1.
 
 [JS] IF it is enabled in my.cnf.


Hmm, didn't know that bit. What's the option called ?

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

mySQL vs. NoSQL

[Daevid Vincent]

You guys hear talk about NoSQL and here's a good article on the topic 
especially as to how it pertains to mySQL...
 
http://www.linuxjournal.com/article/10770


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

Re: Persistent Connection VS One Time Connection Was Update Table

[Willy Mularto]

I have checked Apache's log. There is no refused connection. And also with 
MySQL I have set it to 999 connections and view the processes. Maximum 
connection ever reached was only around 200. What I'm thinking now is. Is it 
because of I use one time connection method? I mean every time the script's 
called I create new connection and disconnect it after execute the query. Thanks



sangprabv
sangpr...@gmail.com
http://www.petitiononline.com/froyo/


On Sep 27, 2010, at 7:28 PM, Nigel Wood wrote:

 On Mon, 2010-09-27 at 11:25 +0100, Willy Mularto wrote:
 Hi,
 I work on MySQL 5 with PHP 5 and use Apache 2 as the webserver. I have a 
 simple query that searches matched row id and update the field via  HTTP GET 
 query. On a low load it succeed update the row. But on high traffic 
 sometimes it failed to update some  rows. No errors return by the script. 
 What usually cause this and how to solve this problem? Thanks
 
 
 1.) Are you sure the script is executed under those conditions? Is
 Apache refusing the request because to many children have been forked?
 
 2.) Are you sure the script will report if MySQL fails with too many
 connections?
 
 
 sangprabv
 sangpr...@gmail.com
 http://www.petitiononline.com/froyo/
 
 
 
 
 


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

mysql vs postgresql -- is this list accurate?

[Robert P. J. Day]

  no, i don't want to start a flame war, i just want some feedback on
a current list of mysql drawbacks WRT postgresql.

  in the context of a fully open-source, java based ECM product, there
is a FAQ entry that summarizes why the developers would prefer their
users to use postgresql as opposed to mysql:

http://www.nuxeo.org/xwiki/bin/view/FAQ/WhyAvoidMySQL

  i'm not advocating one way or the other, i just want to make sure
that list is accurate and i'm not enough of an expert to be able to
judge the entire list.

  if anyone wants to tell me whether any of those entries are no
longer relevant, or are overblown, or whatever, i'd appreciate it.
again, i'm not taking sides, i just want to make sure the information
is as accurate as possible.  thanks.

rday

-- 


Robert P. J. Day   Waterloo, Ontario, CANADA

Top-notch, inexpensive online Linux/OSS/kernel courses
http://crashcourse.ca

Twitter:   http://twitter.com/rpjday
LinkedIn:   http://ca.linkedin.com/in/rpjday


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

Re: mysql vs postgresql -- is this list accurate?

[Carsten Pedersen]

On Mon, 6 Sep 2010 06:36:02 -0400 (EDT), Robert P. J. Day
rpj...@crashcourse.ca wrote:
 no, i don't want to start a flame war, i just want some feedback on
 a current list of mysql drawbacks WRT postgresql.
 
   in the context of a fully open-source, java based ECM product, there
 is a FAQ entry that summarizes why the developers would prefer their
 users to use postgresql as opposed to mysql:
 
 http://www.nuxeo.org/xwiki/bin/view/FAQ/WhyAvoidMySQL

There are a few odd criticisms, such as the limitation on VARCHAR fields
(haven't they discovered TEXT/BLOB types?). Also, some of their
expectations seem very specific to their own implementation (I've
personally never had to do 15 levels of cascade delete). 

But within the particular context they describe, I'd say that overall they
are being fair.

Many, many other CMSs are very succesful with MySQL implementations, so if
you already have a marked preference for working with MySQL, you might want
to look at other products before making a decision. Having read that page,
I know I would. 

/ Carsten

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

Replication VS Cluster

[Jangita]

Hi Guys,
We have a system that has been running along nicely for the past three 
months on a pc (4gb 1,8ghz,debian lenny pc). It is a telecom-financal 
system; slightly 2 hits per minute but growing exponentally as customers 
increase.


We have now bought two servers 12Gb RAM RAID blah blah; and we want to 
set the servers up such that one is an exact duplicate of the other; to 
guard against hardware failiure (in case for example one motherboard is 
fried for some reason). We want to be able to switch from one server to 
the next and continue with minimum downtime. Switching will be manual 
until I figure out how to do an automatic switch (probably continuously 
ping the main server from the hot backup and if the ping fails the hot 
backup can change its ip automatically or something!)


Anyway, what method of keeping the two servers in sync would the experts 
recommend between replication and setting up a cluster (or something 
else)? which will also give me a painless (and later maybe automatic) 
changeover? Both servers are connected to the same switch.

--
Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
Skype: jangita | GTalk: jangita.nyag...@gmail.com

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

Re: Replication VS Cluster

[Johan De Meersman]

On Thu, Sep 2, 2010 at 1:10 PM, Jangita jang...@jangita.com wrote:

 Hi Guys,
 We have a system that has been running along nicely for the past three
 months on a pc (4gb 1,8ghz,debian lenny pc). It is a telecom-financal
 system; slightly 2 hits per minute but growing exponentally as customers
 increase.


Growth should be linear to the growth of customers, no ? :-)


 We have now bought two servers 12Gb RAM RAID blah blah;


RAID setup is important :-) Datafiles preferably on raid 10.



 and we want to set the servers up such that one is an exact duplicate of
 the other; to guard against hardware failiure (in case for example one
 motherboard is fried for some reason). We want to be able to switch from one
 server to the next and continue with minimum downtime. Switching will be
 manual until I figure out how to do an automatic switch (probably
 continuously ping the main server from the hot backup and if the ping fails
 the hot backup can change its ip automatically or something!)


Have a look at Ultramonkey for that.


 Anyway, what method of keeping the two servers in sync would the experts
 recommend between replication and setting up a cluster (or something else)?
 which will also give me a painless (and later maybe automatic) changeover?
 Both servers are connected to the same switch.


Standard setup would be replication, yes. If you setup automatic failover,
make sure you prevent automatic failback - that's the best way to mess up
your dataset.

I also hear MMM is pretty good, although I have no personal experience with
it.

Another route you might want to investigate, is Xen (or VMWare, if so
inclined). Build a single virtual host on your hardware, allocate everything
and the kitchen sink to it, and run your MySQL in it. You'll have a slight
performance loss, obviously, but here's the benefit: you can set up the
second server so that it keeps a bit-perfect copy of your primary machine.
The moment your primary machine dies, the second takes over; and since it
has the EXACT same state down to the last bit of ram, you don't even lose a
ping.

Under Xen this feature is called Remus I believe, VMWare calls it Live
Migration or something similar.




 --
 Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
 Skype: jangita | GTalk: jangita.nyag...@gmail.com

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@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

Re: Replication VS Cluster

[a . smith]

Clustering is a general term, do you know which one you are comparing  
with replication? Clustering most typically refers to high  
availability clustering or high performance clustering, which wouldnt  
necessarily/normally imply any copy of the actual data.


If you want a copy of your data on another server, replication is the  
obvious choice. Assuming the realities of replication arent a problem  
with respect to your requirements, ie replication is asynchronous...


Andy.




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

Re: Replication VS Cluster

[Jangita]

On 02/09/2010 4:32 p, Johan De Meersman wrote:

On Thu, Sep 2, 2010 at 1:10 PM, Jangita jang...@jangita.com
mailto:jang...@jangita.com wrote:



...


Growth should be linear to the growth of customers, no ? :-)



I thought so too; but one customer = 1 customer record, plus all his 
transactions, and also weirdly enough (common for us Africans) customers 
tend to use the service more as more customers enroll (did that make any 
sence?) :):)

...

RAID setup is important :-) Datafiles preferably on raid 10.


Thanks a bunch on that generous tip!
...


Have a look at Ultramonkey for that.


Thanks again!


Standard setup would be replication, yes. If you setup automatic
failover, make sure you prevent automatic failback - that's the best way
to mess up your dataset.

I also hear MMM is pretty good, although I have no personal experience
with it.

Another route you might want to investigate, is Xen (or VMWare, if so
inclined). Build a single virtual host on your hardware, allocate
everything and the kitchen sink to it, and run your MySQL in it. You'll
have a slight performance loss, obviously, but here's the benefit: you
can set up the second server so that it keeps a bit-perfect copy of your
primary machine. The moment your primary machine dies, the second takes
over; and since it has the EXACT same state down to the last bit of ram,
you don't even lose a ping.

Under Xen this feature is called Remus I believe, VMWare calls it Live
Migration or something similar.


Thanks!
--
Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
Skype: jangita | GTalk: jangita.nyag...@gmail.com

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

Re: Replication VS Cluster

[Jangita]

On 02/09/2010 4:35 p, a.sm...@ukgrid.net wrote:

Clustering is a general term, do you know which one you are comparing
with replication? Clustering most typically refers to high availability
clustering or high performance clustering, which wouldnt
necessarily/normally imply any copy of the actual data.

If you want a copy of your data on another server, replication is the
obvious choice. Assuming the realities of replication arent a problem
with respect to your requirements, ie replication is asynchronous...

Andy.


True.

Simply put: I want a solution that ensures that server 2 has all the 
data at server 1 at any point in time; say server 1 suddenly fell into a 
pond :) . I wouldnt want to open server 2 and find the last 
insert/update/delete missing...


--
Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
Skype: jangita | GTalk: jangita.nyag...@gmail.com

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

RE: Replication VS Cluster

[Neil Aggarwal]

 Simply put: I want a solution that ensures that server 2 has all the 
 data at server 1 at any point in time

If server 1 and 2 are on the same local network, I would use
a cluster.  If they are located on physically separate networks,
I would use master-master replication.

Neil

--
Neil Aggarwal, (281)846-8957
FREE trial: Wordpress VPS with unmetered bandwidth
http://UnmeteredVPS.net/wordpress 


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

Re: Replication VS Cluster

[a . smith]

Quoting Jangita jang...@jangita.com:



Simply put: I want a solution that ensures that server 2 has all the  
data at server 1 at any point in time; say server 1 suddenly fell  
into a pond :) . I wouldnt want to open server 2 and find the last  
insert/update/delete missing...




Ok so that rules out any asynchronous replication (MySQL replication  
for example).
So options available would include, HA clustering with a shared fibre  
channel RAID array. Or synchronous replication over the network using  
something like DRBD or HAST (on FreeBSD), plus any other suggestions  
from others... :P





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

Re: Replication VS Cluster

[Johan De Meersman]

On Thu, Sep 2, 2010 at 5:12 PM, Neil Aggarwal n...@jammconsulting.comwrote:

 If server 1 and 2 are on the same local network, I would use
 a cluster.


As in NDB ? I've no personal experience with it - save for a sales talk by
MySQL guys some years back where we decided it was useless to us - but I
understand it has quite a few specific limitations that make it suited for a
rather specific range of applications.



 If they are located on physically separate networks,
 I would use master-master replication.


If you're on separate networks, you're gonna have trouble maintaining both
performance and perfect replication, regardless of what you do; not to
mention you'll be in performance hell as soon as you want to switch to the
remote master.

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

Re: Replication VS Cluster

[Johan De Meersman]

On Thu, Sep 2, 2010 at 5:51 PM, a.sm...@ukgrid.net wrote:

 Quoting Jangita jang...@jangita.com:


 Simply put: I want a solution that ensures that server 2 has all the data
 at server 1 at any point in time; say server 1 suddenly fell into a pond :)
 . I wouldnt want to open server 2 and find the last insert/update/delete
 missing...


 Ok so that rules out any asynchronous replication (MySQL replication for
 example).



Actually, recent 5.1 servers do have 'semi-synchronous' replication, where
replication is synchronous until the slave happens to timeout, where it
reverts to asynchronous until you fix it. Incidentally, this is default
Oracle behaviour, too, if you're not talking RAC.

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

C api query vs. real_query vs. send_query

[Surendra Singhi]

Hi,

I am doing some work on a nodejs/v8 bindings for libmysqlclient and
ran into some questions while working on the asynchronous part of the
interface.
http://github.com/kreetitech/node-mysql-libmysqlclient

The documentation says that:

mysql_query() cannot be used for statements that contain binary data;
you must use mysql_real_query() instead.
In addition, mysql_real_query() is faster than mysql_query() because
it does not call strlen() on the statement string.

Besides these, I found another important difference, it is possible to
call mysql_query followed by mysql_store_result multiple times and
have separate MYSQL_RES objects which work.

Meaning:

mysql_query(conn. 'some select query');
res1 = mysql_store_result(conn)

mysql_query(conn. 'some other select query');
res2 = mysql_store_result(conn)

   mysql_fetch_row(res1)
   mysql_fetch_row(res2)


But the above doesn't work with mysql_real_query, if returns error
code CR_COMMANDS_OUT_OF_SYNC.
mysql_real_query expects that all the results have been fetched,
before it can be called again.

Is the behavior of mysql_query, as expected, and something which can
be relied upon? Can res1 and res2 be assumed to be independent?
Why does mysql_real_query behaves differently?

mysql_send_query is not  a documented function, is there any reason for that?

Your help will be appreciated.

Thanks!
-- 
Surendra Singhi

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

STRAIGHT JOIN vs. field names

[Mike Spreitzer]

Why is it that a field name that works fine for a JOIN is invalid in a 
STRAIGHT JOIN?

mysql show create table fldsndm;
+-++
| Table   | Create Table|
+-++
| fldsndm | CREATE TABLE `fldsndm` (
  `p` varchar(200) NOT NULL,
  `cd` datetime NOT NULL,
  `cms` smallint(6) NOT NULL,
  `pip` char(15) NOT NULL,
  `pport` smallint(6) NOT NULL,
  `pboot` bigint(20) NOT NULL,
  `msgid` bigint(20) NOT NULL,
  `startgtime` bigint(20) NOT NULL,
  `datalen` int(11) NOT NULL,
  `toself` tinyint(1) DEFAULT NULL,
  `sepoch` bigint(20) NOT NULL DEFAULT '0',
  `c` decimal(11,3) NOT NULL DEFAULT '0.000',
  UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 | 
+-++
1 row in set (0.00 sec)

mysql show create table fldrcv;
++--+
| Table  | Create Table |
++--+
| fldrcv | CREATE TABLE `fldrcv` (
  `p` varchar(200) NOT NULL,
  `cd` datetime NOT NULL,
  `cms` smallint(6) NOT NULL,
  `pip` char(15) NOT NULL,
  `pport` smallint(6) NOT NULL,
  `pboot` bigint(20) DEFAULT NULL,
  `qip` char(15) NOT NULL,
  `qport` smallint(6) NOT NULL,
  `qboot` bigint(20) DEFAULT NULL,
  `msgid` bigint(20) NOT NULL,
  `startgtime` bigint(20) NOT NULL,
  `datalen` int(11) NOT NULL,
  `q` varchar(200) DEFAULT NULL,
  `repoch` bigint(20) NOT NULL DEFAULT '0',
  `c` decimal(11,3) NOT NULL DEFAULT '0.000',
  KEY `c` (`c`),
  KEY `pec` (`p`,`repoch`,`c`),
  KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`),
  KEY `qbm` (`q`,`qboot`,`msgid`),
  KEY `pbm` (`p`,`pboot`,`msgid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 

Re: STRAIGHT JOIN vs. field names

[Michael Dykman]

The relationship looks righteous enough but I note that you use
'straight join' in your expression, rather than 'straight_join' as
indicated in the manual
(http://dev.mysql.com/doc/refman/5.1/en/join.html).

Perhaps the message is a red herring and your trouble is elsewhere?

 - michael dykman

On Wed, Aug 11, 2010 at 4:25 PM, Mike Spreitzer mspre...@us.ibm.com wrote:
 Why is it that a field name that works fine for a JOIN is invalid in a
 STRAIGHT JOIN?

 mysql show create table fldsndm;
 +-++
 | Table   | Create Table                                    |
 +-++
 | fldsndm | CREATE TABLE `fldsndm` (
  `p` varchar(200) NOT NULL,
  `cd` datetime NOT NULL,
  `cms` smallint(6) NOT NULL,
  `pip` char(15) NOT NULL,
  `pport` smallint(6) NOT NULL,
  `pboot` bigint(20) NOT NULL,
  `msgid` bigint(20) NOT NULL,
  `startgtime` bigint(20) NOT NULL,
  `datalen` int(11) NOT NULL,
  `toself` tinyint(1) DEFAULT NULL,
  `sepoch` bigint(20) NOT NULL DEFAULT '0',
  `c` decimal(11,3) NOT NULL DEFAULT '0.000',
  UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`)
 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
 +-++
 1 row in set (0.00 sec)

 mysql show create table fldrcv;
 ++--+
 | Table  | Create Table                     |
 ++--+
 | fldrcv | CREATE TABLE `fldrcv` (
  `p` varchar(200) NOT NULL,
  `cd` datetime NOT NULL,
  `cms` smallint(6) NOT NULL,
  `pip` char(15) NOT NULL,
  `pport` smallint(6) NOT NULL,
  `pboot` bigint(20) DEFAULT NULL,
  `qip` char(15) NOT NULL,
  `qport` smallint(6) NOT NULL,
  `qboot` bigint(20) DEFAULT NULL,
  `msgid` bigint(20) NOT NULL,
  `startgtime` bigint(20) NOT NULL,
  `datalen` int(11) NOT NULL,
  `q` varchar(200) DEFAULT NULL,
  `repoch` bigint(20) NOT NULL DEFAULT '0',
  `c` decimal(11,3) NOT NULL DEFAULT '0.000',
  KEY `c` (`c`),
  KEY `pec` (`p`,`repoch`,`c`),
  KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`),
  KEY `qbm` (`q`,`qboot`,`msgid`),
  KEY `pbm` (`p`,`pboot`,`msgid`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
 

Re: STRAIGHT JOIN vs. field names

[Mike Spreitzer]

Yes, that's it.  I should be typing STRAIGHT_JOIN instead of STRAIGHT 
JOIN.

Thanks!
Mike Spreitzer

First impression of mysql 5.5.3 vs mysql 4.1.22

[Andrés Tello]

WW...

While uploading the database to a clean mysql, mysql 4.1.22 didn't even get
over 26 mb/s of writting speed, but I'm monitoring the speed mysql 5.5.3 is
reaching and can squeeze 100mb/s, averga I'm seeing like 35mb/s

22GB at mysql 4.1 lasted like 3 hours to fully load, this one I belive less
than an hour...

I like the performance I'm seeing...

Going to sleep XD can't wait to test it! but have to sleep... battery mode
and I'm watching the special about LOTR from History Channel...

*Three hardware family  for the sysadmin under the sky,
Seven version for the bazar  in their halls of code,
Nine engines for DBAs doomed to die,
One Enterprise for the Dark Lord on his dark throne
In the Land of Redwood where the Shadows lie.
One Enterprise to rule them all, One License to find them,
One Contract to bring them all and in the darkness bind them*

Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[Rob Wultsch]

   On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote:
  
  
   Can any one help me with understanding the mysql spatial
   functions?
   I
   can
   only seem to find bits and pieces of how-to's etc.
  
   I have an existing table of lat / long data representing unique
   boundaries
   i.e. rectangles and I want to search the table to find the
 rectangle
   that
   bounds a specific point.
  
   Dan
  

troll
Please at least considered PostGIS.

In my limited experience all the good GIS people I know use PG. Also
spatial indexes are limited to MyISAM in MySQL, which is a significant
limitation for many users.

http://postgis.refractions.net/

/troll


-- 
Rob Wultsch
wult...@gmail.com

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

Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[dan]

It works great for me.  After working out the bugs and adding the spatial

index I am now searching in the 0.05 second timeframe vs. minutes

otherwise.



Dan



On Sun, 2 May 2010 23:39:41 -0700, Rob Wultsch wult...@gmail.com wrote:

   On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote:

  

  

   Can any one help me with understanding the mysql spatial

   functions?

   I

   can

   only seem to find bits and pieces of how-to's etc.

  

   I have an existing table of lat / long data representing

unique

   boundaries

   i.e. rectangles and I want to search the table to find the

 rectangle

   that

   bounds a specific point.

  

   Dan

  

 

 troll

 Please at least considered PostGIS.

 

 In my limited experience all the good GIS people I know use PG. Also

 spatial indexes are limited to MyISAM in MySQL, which is a significant

 limitation for many users.

 

 http://postgis.refractions.net/

 

 /troll

 

 

 -- 

 Rob Wultsch

 wult...@gmail.com

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

mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[dan]

Can any one help me with understanding the mysql spatial functions?  I can

only seem to find bits and pieces of how-to's etc.



I have an existing table of lat / long data representing unique boundaries

i.e. rectangles and I want to search the table to find the rectangle that

bounds a specific point.



Dan

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

Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[dan]

I have seen that but I am stuck at just populating my POLYGON column

(poly).  I have tried this:



UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s` `e`, `s`

`w`, `n` `w`, `n` `e`)');



but my poly column just reports back NULL.



the n, e, s  w columns are decimal lat / long data.



Dan



On Sun, 2 May 2010 06:43:13 -0700, Ted Yu yuzhih...@gmail.com wrote:

 I think you may have seen this:

 http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html

 

 On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote:

 



 Can any one help me with understanding the mysql spatial functions?  I

 can

 only seem to find bits and pieces of how-to's etc.



 I have an existing table of lat / long data representing unique

 boundaries

 i.e. rectangles and I want to search the table to find the rectangle

that

 bounds a specific point.



 Dan



 --

 MySQL General Mailing List

 For list archives: http://lists.mysql.com/mysql

 To unsubscribe:   

http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com





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

Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[Baron Schwartz]

Dan,

I think you are trying to create a polygon based on the values in
other columns in the same row.  I think these other columns are named
`n` and so on.

Your mistake is that you are creating a text string, POLYGON(..)
and embedding column names inside it.  That won't work.  Those column
names are just part of a string.  They are not literal values that the
POLYGON() function can interpret.  You will need to use CONCAT() or
similar to build a string that POLYGON() can interpret.

On Sun, May 2, 2010 at 11:15 AM, dan d...@tappin.ca wrote:

 I have seen that but I am stuck at just populating my POLYGON column
 (poly).  I have tried this:

 UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s` `e`, `s`
 `w`, `n` `w`, `n` `e`)');

 but my poly column just reports back NULL.

 the n, e, s  w columns are decimal lat / long data.

 Dan

 On Sun, 2 May 2010 06:43:13 -0700, Ted Yu yuzhih...@gmail.com wrote:
 I think you may have seen this:
 http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html

 On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote:


 Can any one help me with understanding the mysql spatial functions?  I
 can
 only seem to find bits and pieces of how-to's etc.

 I have an existing table of lat / long data representing unique
 boundaries
 i.e. rectangles and I want to search the table to find the rectangle
 that
 bounds a specific point.

 Dan

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



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





-- 
Baron Schwartz
Percona Inc http://www.percona.com/
Consulting, Training, Support  Services for MySQL

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

Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[dan]

I am still lost... I tried this:



UPDATE `grid` SET poly = PolygonFromText(CONCAT('POLYGON(',n,' ',e,', ',s,'

',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));



I had my delimiters mixed up and I know my CONCAT worked:



mysql select CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,'

',w,', ',n,' ',e,')') from grid limit 3;

+---+

| CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,'

',e,')')|

+---+

| POLYGON(49.07756615 101.36211395, 49.07390213 101.36211395, 49.07390213

101.36764145, 49.07756615 101.36764145, 49.07756615 101.36211395) | 

| POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395, 49.07756615

101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) | 

| POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395, 49.08123016

101.36764145, 49.08489418 101.36764145, 49.08489418 101.36211395) | 

+---+



But after my UPDATE my poly column is still full of NULL values.



Dan



On Sun, 2 May 2010 13:34:14 -0400, Baron Schwartz ba...@xaprb.com wrote:

 Dan,

 

 I think you are trying to create a polygon based on the values in

 other columns in the same row.  I think these other columns are named

 `n` and so on.

 

 Your mistake is that you are creating a text string, POLYGON(..)

 and embedding column names inside it.  That won't work.  Those column

 names are just part of a string.  They are not literal values that the

 POLYGON() function can interpret.  You will need to use CONCAT() or

 similar to build a string that POLYGON() can interpret.

 

 On Sun, May 2, 2010 at 11:15 AM, dan d...@tappin.ca wrote:



 I have seen that but I am stuck at just populating my POLYGON column

 (poly).  I have tried this:



 UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s` `e`, `s`

 `w`, `n` `w`, `n` `e`)');



 but my poly column just reports back NULL.



 the n, e, s  w columns are decimal lat / long data.



 Dan



 On Sun, 2 May 2010 06:43:13 -0700, Ted Yu yuzhih...@gmail.com wrote:

 I think you may have seen this:

 http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html



 On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote:





 Can any one help me with understanding the mysql spatial functions?

 I

 can

 only seem to find bits and pieces of how-to's etc.



 I have an existing table of lat / long data representing unique

 boundaries

 i.e. rectangles and I want to search the table to find the rectangle

 that

 bounds a specific point.



 Dan



 --

 MySQL General Mailing List

 For list archives: http://lists.mysql.com/mysql

 To unsubscribe:

 http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com







 --

 MySQL General Mailing List

 For list archives: http://lists.mysql.com/mysql

 To unsubscribe:    http://lists.mysql.com/mysql?unsub=ba...@xaprb.com





 

 

 

 -- 

 Baron Schwartz

 Percona Inc http://www.percona.com/

 Consulting, Training, Support  Services for MySQL

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

Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[dan]

Tried it but no luck:



mysql UPDATE `grid` SET poly = GeomFromText(CONCAT('POLYGON(',n,' ',e,',

',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));

Query OK, 0 rows affected (2 min 3.86 sec)

Rows matched: 7876282  Changed: 0  Warnings: 0



mysql select poly from grid limit 10;

+--+

| poly |

+--+

| NULL | 

| NULL | 

| NULL | 

| NULL | 

| NULL | 

| NULL | 

| NULL | 

| NULL | 

| NULL | 

| NULL | 

+--+

10 rows in set (0.01 sec)







On Sun, 2 May 2010 12:54:07 -0700, Ted Yu yuzhih...@gmail.com wrote:

 Have you tried replacing GeomFromText in place of PolygonFromText ?

 

 On Sun, May 2, 2010 at 10:59 AM, dan d...@tappin.ca wrote:

 



 I am still lost... I tried this:



 UPDATE `grid` SET poly = PolygonFromText(CONCAT('POLYGON(',n,' ',e,',

 ',s,'

 ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));



 I had my delimiters mixed up and I know my CONCAT worked:



 mysql select CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,',

 ',n,'

 ',w,', ',n,' ',e,')') from grid limit 3;





+---+

 | CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,' ',w,',

 ',n,'

 ',e,')')|





+---+

 | POLYGON(49.07756615 101.36211395, 49.07390213 101.36211395,

49.07390213

 101.36764145, 49.07756615 101.36764145, 49.07756615 101.36211395) |

 | POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395,

49.07756615

 101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) |

 | POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395,

49.08123016

 101.36764145, 49.08489418 101.36764145, 49.08489418 101.36211395) |





+---+



 But after my UPDATE my poly column is still full of NULL values.



 Dan



 On Sun, 2 May 2010 13:34:14 -0400, Baron Schwartz ba...@xaprb.com

 wrote:

  Dan,

 

  I think you are trying to create a polygon based on the values in

  other columns in the same row.  I think these other columns are named

  `n` and so on.

 

  Your mistake is that you are creating a text string, POLYGON(..)

  and embedding column names inside it.  That won't work.  Those column

  names are just part of a string.  They are not literal values that the

  POLYGON() function can interpret.  You will need to use CONCAT() or

  similar to build a string that POLYGON() can interpret.

 

  On Sun, May 2, 2010 at 11:15 AM, dan d...@tappin.ca wrote:

 

  I have seen that but I am stuck at just populating my POLYGON column

  (poly).  I have tried this:

 

  UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s` `e`,

  `s`

  `w`, `n` `w`, `n` `e`)');

 

  but my poly column just reports back NULL.

 

  the n, e, s  w columns are decimal lat / long data.

 

  Dan

 

  On Sun, 2 May 2010 06:43:13 -0700, Ted Yu yuzhih...@gmail.com

wrote:

  I think you may have seen this:

 

http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html

 

  On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote:

 

 

  Can any one help me with understanding the mysql spatial functions?

  I

  can

  only seem to find bits and pieces of how-to's etc.

 

  I have an existing table of lat / long data representing unique

  boundaries

  i.e. rectangles and I want to search the table to find the

rectangle

  that

  bounds a specific point.

 

  Dan

 

  --

  MySQL General Mailing List

  For list archives: http://lists.mysql.com/mysql

  To unsubscribe:

  http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com

 

 

 

  --

  MySQL General Mailing List

  For list archives: http://lists.mysql.com/mysql

  To unsubscribe:http://lists.mysql.com/mysql?unsub=ba...@xaprb.com

 

 

 

 

 

  --

  Baron Schwartz

  Percona Inc http://www.percona.com/

  Consulting, Training, Support  Services for MySQL



 --

 MySQL General Mailing List

 For list archives: http://lists.mysql.com/mysql

 To unsubscribe:   

http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com





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

Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[dan]

poly is a polygon but I have not added a spatial index yet.



Here's where it gets weird.  I tried adding dummy data:



mysql SET @bbox = 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))';

Query OK, 0 rows affected (0.01 sec)



mysql update grid set poly = GeomFromText(@bbox);

Query OK, 7876282 rows affected (7 min 7.04 sec)

Rows matched: 7876282  Changed: 7876282  Warnings: 0



mysql select AsText(poly) from grid limit 10;

++

| AsText(poly)   |

++

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

| POLYGON((0 0,10 0,10 10,0 10,0 0)) | 

++

10 rows in set (0.20 sec)



So it works but not my index:



mysql ALTER TABLE grid ADD SPATIAL INDEX(poly);

ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL





On Sun, 2 May 2010 14:00:16 -0700, Ted Yu yuzhih...@gmail.com wrote:

 Have you declared poly to be of spatial type ?

 Cheers

 

 On Sun, May 2, 2010 at 1:03 PM, dan d...@tappin.ca wrote:

 



 Tried it but no luck:



 mysql UPDATE `grid` SET poly = GeomFromText(CONCAT('POLYGON(',n,'

',e,',

 ',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));

 Query OK, 0 rows affected (2 min 3.86 sec)

 Rows matched: 7876282  Changed: 0  Warnings: 0



 mysql select poly from grid limit 10;

 +--+

 | poly |

 +--+

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 +--+

 10 rows in set (0.01 sec)







 On Sun, 2 May 2010 12:54:07 -0700, Ted Yu yuzhih...@gmail.com wrote:

  Have you tried replacing GeomFromText in place of PolygonFromText ?

 

  On Sun, May 2, 2010 at 10:59 AM, dan d...@tappin.ca wrote:

 

 

  I am still lost... I tried this:

 

  UPDATE `grid` SET poly = PolygonFromText(CONCAT('POLYGON(',n,' ',e,',

  ',s,'

  ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));

 

  I had my delimiters mixed up and I know my CONCAT worked:

 

  mysql select CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,',

  ',n,'

  ',w,', ',n,' ',e,')') from grid limit 3;

 

 





+---+

  | CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,' ',w,',

  ',n,'

  ',e,')')|

 

 





+---+

  | POLYGON(49.07756615 101.36211395, 49.07390213 101.36211395,

 49.07390213

  101.36764145, 49.07756615 101.36764145, 49.07756615 101.36211395) |

  | POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395,

 49.07756615

  101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) |

  | POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395,

 49.08123016

  101.36764145, 49.08489418 101.36764145, 49.08489418 101.36211395) |

 

 





+---+

 

  But after my UPDATE my poly column is still full of NULL values.

 

  Dan

 

  On Sun, 2 May 2010 13:34:14 -0400, Baron Schwartz ba...@xaprb.com

  wrote:

   Dan,

  

   I think you are trying to create a polygon based on the values in

   other columns in the same row.  I think these other columns are

   named

   `n` and so on.

  

   Your mistake is that you are creating a text string,

   POLYGON(..)

   and embedding column names inside it.  That won't work.  Those

   column

   names are just part of a string.  They are not literal values that

   the

   POLYGON() function can interpret.  You will need to use CONCAT() or

   similar to build a string that POLYGON() can interpret.

  

   On Sun, May 2, 2010 at 11:15 AM, dan d...@tappin.ca wrote:

  

   I have seen that but I am stuck at just populating my POLYGON

   column

   (poly).  I have tried this:

  

   UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s`

`e`,

   `s`

   `w`, `n` `w`, `n` `e`)');

  

   but my poly column just reports back NULL.

  

   the n, e, s  w columns are decimal lat / long data.

  

   Dan

  

   On Sun, 2 May 2010 06:43:13 -0700, Ted Yu yuzhih...@gmail.com

 wrote:

   I think you may have seen this:

  

 http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html

  

   On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote:

  

  

   Can any one help me with understanding the mysql spatial

   functions?

   I

   can

   only seem 

Re: mysql spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)

[dan]

Ok... I am close I forgot an extra () in my POLYGON statement:



UPDATE `grid` SET lsd_poly = GeomFromText(CONCAT('POLYGON((',n,' ',e,',

',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,'))'));



(I also created a new GEOMETRY lsd_poly column rather than the poly POLYGON

one).



Now I need to figure out the rest of it...



Dan



On Sun, 2 May 2010 14:00:16 -0700, Ted Yu yuzhih...@gmail.com wrote:

 Have you declared poly to be of spatial type ?

 Cheers

 

 On Sun, May 2, 2010 at 1:03 PM, dan d...@tappin.ca wrote:

 



 Tried it but no luck:



 mysql UPDATE `grid` SET poly = GeomFromText(CONCAT('POLYGON(',n,'

',e,',

 ',s,' ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));

 Query OK, 0 rows affected (2 min 3.86 sec)

 Rows matched: 7876282  Changed: 0  Warnings: 0



 mysql select poly from grid limit 10;

 +--+

 | poly |

 +--+

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 | NULL |

 +--+

 10 rows in set (0.01 sec)







 On Sun, 2 May 2010 12:54:07 -0700, Ted Yu yuzhih...@gmail.com wrote:

  Have you tried replacing GeomFromText in place of PolygonFromText ?

 

  On Sun, May 2, 2010 at 10:59 AM, dan d...@tappin.ca wrote:

 

 

  I am still lost... I tried this:

 

  UPDATE `grid` SET poly = PolygonFromText(CONCAT('POLYGON(',n,' ',e,',

  ',s,'

  ',e,', ',s,' ',w,', ',n,' ',w,', ',n,' ',e,')'));

 

  I had my delimiters mixed up and I know my CONCAT worked:

 

  mysql select CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,',

  ',n,'

  ',w,', ',n,' ',e,')') from grid limit 3;

 

 





+---+

  | CONCAT('POLYGON(',n,' ',e,', ',s,' ',e,', ',s,' ',w,', ',n,' ',w,',

  ',n,'

  ',e,')')|

 

 





+---+

  | POLYGON(49.07756615 101.36211395, 49.07390213 101.36211395,

 49.07390213

  101.36764145, 49.07756615 101.36764145, 49.07756615 101.36211395) |

  | POLYGON(49.08123016 101.36211395, 49.07756615 101.36211395,

 49.07756615

  101.36764145, 49.08123016 101.36764145, 49.08123016 101.36211395) |

  | POLYGON(49.08489418 101.36211395, 49.08123016 101.36211395,

 49.08123016

  101.36764145, 49.08489418 101.36764145, 49.08489418 101.36211395) |

 

 





+---+

 

  But after my UPDATE my poly column is still full of NULL values.

 

  Dan

 

  On Sun, 2 May 2010 13:34:14 -0400, Baron Schwartz ba...@xaprb.com

  wrote:

   Dan,

  

   I think you are trying to create a polygon based on the values in

   other columns in the same row.  I think these other columns are

   named

   `n` and so on.

  

   Your mistake is that you are creating a text string,

   POLYGON(..)

   and embedding column names inside it.  That won't work.  Those

   column

   names are just part of a string.  They are not literal values that

   the

   POLYGON() function can interpret.  You will need to use CONCAT() or

   similar to build a string that POLYGON() can interpret.

  

   On Sun, May 2, 2010 at 11:15 AM, dan d...@tappin.ca wrote:

  

   I have seen that but I am stuck at just populating my POLYGON

   column

   (poly).  I have tried this:

  

   UPDATE `grid` SET poly = PolygonFromText('POLYGON(`n` `e`, `s`

`e`,

   `s`

   `w`, `n` `w`, `n` `e`)');

  

   but my poly column just reports back NULL.

  

   the n, e, s  w columns are decimal lat / long data.

  

   Dan

  

   On Sun, 2 May 2010 06:43:13 -0700, Ted Yu yuzhih...@gmail.com

 wrote:

   I think you may have seen this:

  

 http://dev.mysql.com/doc/refman/5.0/en/relations-on-geometry-mbr.html

  

   On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote:

  

  

   Can any one help me with understanding the mysql spatial

   functions?

   I

   can

   only seem to find bits and pieces of how-to's etc.

  

   I have an existing table of lat / long data representing unique

   boundaries

   i.e. rectangles and I want to search the table to find the

 rectangle

   that

   bounds a specific point.

  

   Dan

  

   --

   MySQL General Mailing List

   For list archives: http://lists.mysql.com/mysql

   To unsubscribe:

   http://lists.mysql.com/mysql?unsub=yuzhih...@gmail.com

  

  

  

   --

   MySQL General Mailing List

   For list archives: http://lists.mysql.com/mysql

   To unsubscribe:

 http://lists.mysql.com/mysql?unsub=ba...@xaprb.com

  

  

  

  

  

   --

   Baron Schwartz

   Percona Inc http://www.percona.com/

   Consulting, Training, Support  Services for MySQL

 

  --

  MySQL General Mailing List

  For list archives: http://lists.mysql.com/mysql

  To 

Re: Best index for searching on lat / long data i.e. decimal vs. float

[dan]

On Sat, 01 May 2010 15:28:46 -0500, mos mo...@fastmail.fm wrote:



  SELECT * FROM `grid`� force index(section) WHERE n  49.012 AND s 

49.012

 AND e 

  110.0244 AND w  110.0244;

 

  It should give you the answer around 0.1 seconds. Give it a try. :-)

 

  Mike



It actually makes it worse by about 12 times!!



mysql SELECT * FROM `grid` FORCE INDEX(section) WHERE n  49.012 AND s 

49.012 AND e  110.0244 AND w  110.0244;

++-+--+-+--+-+--+-+--+-+--+--+

| lsd| n   | e| s   | w|

lat | lng  | sec_n   | sec_w| sec_s   |

sec_e| province |

++-+--+-+--+-+--+-+--+-+--+--+

| 1301001014 | 49.01424023 | 110.02723089 | 49.01062631 | 110.02169300 |

49.01243327 | 110.02446195 | 49.01424026 | 110.02722931 | 48.99978638 |

110.00508118 | AB   | 

++-+--+-+--+-+--+-+--+-+--+--+

1 row in set (6 min 4.83 sec)





mysql SELECT * FROM `grid` WHERE n  49.012 AND s  49.012 AND e 

110.0244 AND w  110.0244;

++-+--+-+--+-+--+-+--+-+--+--+

| lsd| n   | e| s   | w|

lat | lng  | sec_n   | sec_w| sec_s   |

sec_e| province |

++-+--+-+--+-+--+-+--+-+--+--+

| 1301001014 | 49.01424023 | 110.02723089 | 49.01062631 | 110.02169300 |

49.01243327 | 110.02446195 | 49.01424026 | 110.02722931 | 48.99978638 |

110.00508118 | AB   | 

++-+--+-+--+-+--+-+--+-+--+--+

1 row in set (33.26 sec)

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

Best index for searching on lat / long data i.e. decimal vs. float

[dan]

I have a table with over 8 million rows of lat / long data all currently in

decimal(12,8) format (actually some in float(12,8)).



First question - should I have these all in decimal or float?  I can't

think of any reason why I would actually do any math via mysql with this

data.  I am just running queries and dumping the reults into a webapp.



Second question - my data is actual distinct rows with a north, east, south

and west lat / long for the boundary of a specific area.  I want to search

based on a point lat / long (i.e. WHERE n  lat, s  lat, e  long, w 

long) to find the specific area that the point is located in.



I tried a simple index with my n,e,s  w  but it still takes along time to

run such a query i.e. phpmyadmin times out.  Any idea on the best structure

for such a query?



Thanks,



Dan T

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

Re: Best index for searching on lat / long data i.e. decimal vs. float

[mos]

At 04:54 PM 4/30/2010, you wrote:


I have a table with over 8 million rows of lat / long data all currently in
decimal(12,8) format (actually some in float(12,8)).

First question - should I have these all in decimal or float?  I can't
think of any reason why I would actually do any math via mysql with this
data.  I am just running queries and dumping the reults into a webapp.


I would use decimal because float may round the values slightly so it may 
not show up properly in a search.

Example: Is it 55.05 or 55.04?



Second question - my data is actual distinct rows with a north, east, south
and west lat / long for the boundary of a specific area.  I want to search
based on a point lat / long (i.e. WHERE n  lat, s  lat, e  long, w 
long) to find the specific area that the point is located in.


Use Explain in front of your Select statement to see how many indexes it is 
using.

You could shorten the sql to something like:

select  from table where lat between s and n and long between w and e;

For me, this is simpler to read, although MySQL may optimize it to what you 
had before.
(Assuming of course you have normalized the lat and long in the table and 
don't need to specify east longitude or south latitude etc..)





I tried a simple index with my n,e,s  w  but it still takes along time to
run such a query i.e. phpmyadmin times out.  Any idea on the best structure
for such a query?


What version of MySQL are you using? MySQL may be using only one index so 
I'd recommend making a compound index of all 4 columns:
n,s,e,w columns. Now MySQL only has to traverse the single index and won't 
have to access the data records to satisfy the query.


Mike




Thanks,

Dan T

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



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