RE: mysql cluster and auto shard

[Rick James]

Clustrix now has a software version of their auto-sharding system.  (It used to 
be that they only sold an 'appliance'.)

> -Original Message-
> From: Andrew Morgan [mailto:andrew.mor...@oracle.com]
> Sent: Monday, March 18, 2013 6:51 AM
> To: Mike Franon
> Cc: 
> Subject: RE: mysql cluster and auto shard
> 
> 
> 
> > -Original Message-
> > From: Mike Franon [mailto:kongfra...@gmail.com]
> > Sent: 18 March 2013 13:34
> > To: 
> > Subject: mysql cluster and auto shard
> >
> > I am looking at  the best way to scale writes.
> >
> > Either using sharding with our existing infrastructure, or moving to
> > mysql cluster.
> >
> > Does anyone have any pros/cons to using mysql cluster?  I am trying
> to
> > find a much better understanding on how the auto sharding works?  Is
> > it true we do not need to change code much on application level?
> 
> As a starting point, I think it's worth taking a look at this white
> paper... http://www.mysql.com/why-mysql/white-papers/mysql-cluster-
> evaluation-guide/
> 
> Most things will continue to work when migrating to MySQL Cluster but
> of course (as with any storage engine) to get the best performance
> you'll probably need to make some changes; this second paper explains
> how to optimize for MySQL Cluster - hopefully that will give a good
> feeling for the types of changes that you might need/want to make...
> http://www.mysql.com/why-mysql/white-papers/guide-to-optimizing-
> performance-of-the-mysql-cluster/
> 
> 
> >
> >
> > Thanks
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql


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

RE: a little doubt on text about MySQL

[Rick James]

Possibly related:
http://ronaldbradford.com/blog/why-sql_mode-is-important-2011-06-01/
http://rpbouman.blogspot.com/2009/01/mysqls-sqlmode-my-suggestions.html
http://gabrito.com/post/when-installing-mysql-always-set-the-sql-mode


> -Original Message-
> From: h...@tbbs.net [mailto:h...@tbbs.net]
> Sent: Saturday, March 16, 2013 6:23 AM
> To: Reindl Harald
> Cc: mysql@lists.mysql.com
> Subject: Re: a little doubt on text about MySQL
> 
>  2013/03/16 03:44 +0100, Reindl Harald 
> what are you speaking about?
> 
> you can define it in my.cnf and YOU are responsible for the
> configuration as you are also responsible the develop php code with
> error_reporting = E_ALL  These SQL-modes that pertain to type-
> safety are really part of the _type_: ALLOW_INVALID_DATES NO_ZERO_DATE
> NO_ZERO_IN_DATE Their value when one does "CREATE TABLE ..." really
> belongs to the newly created table, if not to particular fields in the
> table. It is type-declaration. This one, NO_AUTO_VALUE_ON_ZERO, is part
> of the table s type, and belongs with the newly created table, or with
> the fields on which it bears (MyISAM).
> 
> It really is not right that one who designs a table designs it with one
> date setting or another in mind, then another, who uses that table,
> changes any of these in local SQL mode, and thereby changes the type.
> 
> As for this one, NO_AUTO_CREATE_USER, there is no reason for letting it
> differ in local or global SQL-mode from that defined in my.cnf
> (my.ini). Inasmuch as MySQL lets one set that apart from the
> configuration file, there is a problem, especially from dropping it.
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql


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

Surge 2013 CFP Open

[Katherine Jeschke]

The Surge 2013 CFP is open. For details or to submit a paper, please visit
http://surge.omniti.com/2013

-- 
Katherine Jeschke
Director of Marketing and Creative Services
OmniTI Computer Consulting, Inc.
11830 West Market Place, Suite F
Fulton, MD 20759
O: 240-646-0770, 222
F: 301-497-2001
C: 443/643-6140
omniti.com
Surge 2013 

The information contained in this electronic message and any attached
documents is privileged, confidential, and protected from disclosure.  If
you are not the intended recipient, note that any review, disclosure,
copying, distribution, or use of the contents of this electronic message or
any attached documents is prohibited. If you have received this
communication in error, please destroy it and notify us immediately by
telephone (1-443-325-1360) or by electronic mail (i...@omniti.com). Thank
you.

RE: How to change max simultaneous connection parameter in mysql.

[Rick James]

> you never have hosted a large site
Check my email address before saying that.

20 may be low, but 100 is rather high.

> -Original Message-
> From: Reindl Harald [mailto:h.rei...@thelounge.net]
> Sent: Monday, March 18, 2013 1:36 PM
> To: mysql@lists.mysql.com
> Subject: Re: How to change max simultaneous connection parameter in
> mysql.
> 
> 
> 
> Am 18.03.2013 21:01, schrieb Rick James:
> > 20 is plenty if your pages run fast enough
> 
> it is not
> you never have hosted a large site
> 
> > Excess clients after MaxClients are queued in Apache
> 
> so what - it doe snot help you
> been there, done that
> 
> if you have some hundret USERS at the same time any every of them is
> requesting the same page with a lot of images you are simply DEAD with
> a limit of
> 20 in your configuration
> 
> > If the 20 are consuming resources (eg cpu/disk) it is better to queue
> > the excess than to have everybody stumbling over each other.
> 
> if your server can not serve more than 20 simultaionous requests you
> are not doing any serious things
> 
> sorry, 20 can be done with any crappy notebook these days
> 
> > In MySQL, the excess clients beyond max_connections are give an
> error.
> >
> >> -Original Message-
> >> From: Reindl Harald [mailto:h.rei...@thelounge.net]
> >> Sent: Monday, March 18, 2013 12:15 PM
> >> To: mysql@lists.mysql.com
> >> Subject: Re: How to change max simultaneous connection parameter in
> >> mysql.
> >>
> >>
> >>
> >> Am 18.03.2013 19:26, schrieb Rick James:
> >>> If you are running Apache with MaxClients set too high, that can
> >> cause the problem.
> >>
> >> "too high" is relative
> >>
> >>> That Apache setting should be something like 20.  (Other web
> servers
> >>> have similar settings.)
> >>
> >> 20 is a laughable value as long you are not hosting only sites with
> >> no users at all
> >>
> >> i have seen "MaxClients 500" be critical while the hardware was not
> >> overloaded and we had THOUSANDS of users which liked to get the
> >> website with all it's images, no way with stupid settings of 20
> which
> >> means only ONE USER at the same time can fetch a single page with
> >> images and stylesheets


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

RE: How to change max simultaneous connection parameter in mysql.

[Rick James]

If you are running Apache with MaxClients set too high, that can cause the 
problem.  That Apache setting should be something like 20.  (Other web servers 
have similar settings.)

> -Original Message-
> From: Igor Shevtsov [mailto:nixofort...@gmail.com]
> Sent: Saturday, March 16, 2013 1:45 AM
> To: mysql@lists.mysql.com
> Subject: Re: How to change max simultaneous connection parameter in
> mysql.
> 
> Hi Kevin,
> In your MySQL client pls execute:
> SET GLOBAL max_connections = 200;
> To Make the change permanent you can edit /etc/my.cnf or whatever MySQL
> config file you you've got in your system Look for this line
> max_connections under [mysqld] secction, add it if it's not in the
> config. make sure it looks like:
> max_connections = 200
> 
> No MySQL restart required
> Cheers,
> Igor
> 
> 
> 
> On 16/03/13 07:39, Manuel Arostegui wrote:
> > 2013/3/16 Kevin Peterson 
> >
> >> I am using PHP along with mysql. Mysql default configuration allows
> >> to have 100 simultaneous connection which I want to chane to 200.
> Please help.
> >>
> >
> > If you're reaching too many connections quite often, this change can
> > imply memory problems in your server. If you are close to get your
> > server to swap...be careful with this parameter as any swapping will
> > affect your performance.
> >
> > Manuel.
> >
> >
> >
> >
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql


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

Re: How to change max simultaneous connection parameter in mysql.

[Noel Butler]

On Mon, 2013-03-18 at 21:35 +0100, Reindl Harald wrote:

> 
> Am 18.03.2013 21:01, schrieb Rick James:
> > 20 is plenty if your pages run fast enough



> 
> if your server can not serve more than 20 simultaionous
> requests you are not doing any serious things
> 


or he's using a 286 




signature.asc
Description: This is a digitally signed message part

Re: How to change max simultaneous connection parameter in mysql.

[Reindl Harald]

Am 18.03.2013 21:01, schrieb Rick James:
> 20 is plenty if your pages run fast enough

it is not
you never have hosted a large site

> Excess clients after MaxClients are queued in Apache

so what - it doe snot help you
been there, done that

if you have some hundret USERS at the same time
any every of them is requesting the same page with
a lot of images you are simply DEAD with a limit of
20 in your configuration

> If the 20 are consuming resources (eg cpu/disk) it is better to queue the 
> excess 
> than to have everybody stumbling over each other.

if your server can not serve more than 20 simultaionous
requests you are not doing any serious things

sorry, 20 can be done with any crappy notebook these days

> In MySQL, the excess clients beyond max_connections are give an error.
> 
>> -Original Message-
>> From: Reindl Harald [mailto:h.rei...@thelounge.net]
>> Sent: Monday, March 18, 2013 12:15 PM
>> To: mysql@lists.mysql.com
>> Subject: Re: How to change max simultaneous connection parameter in
>> mysql.
>>
>>
>>
>> Am 18.03.2013 19:26, schrieb Rick James:
>>> If you are running Apache with MaxClients set too high, that can
>> cause the problem.
>>
>> "too high" is relative
>>
>>> That Apache setting should be something like 20.  (Other web servers
>>> have similar settings.)
>>
>> 20 is a laughable value as long you are not hosting only sites with no
>> users at all
>>
>> i have seen "MaxClients 500" be critical while the hardware was not
>> overloaded and we had THOUSANDS of users which liked to get the website
>> with all it's images, no way with stupid settings of 20 which means
>> only ONE USER at the same time can fetch a single page with images and
>> stylesheets



signature.asc
Description: OpenPGP digital signature

RE: How to change max simultaneous connection parameter in mysql.

[Rick James]

20 is plenty if your pages run fast enough.
Excess clients after MaxClients are queued in Apache.  If the 20 are consuming 
resources (eg cpu/disk) it is better to queue the excess than to have everybody 
stumbling over each other.
In MySQL, the excess clients beyond max_connections are give an error.

> -Original Message-
> From: Reindl Harald [mailto:h.rei...@thelounge.net]
> Sent: Monday, March 18, 2013 12:15 PM
> To: mysql@lists.mysql.com
> Subject: Re: How to change max simultaneous connection parameter in
> mysql.
> 
> 
> 
> Am 18.03.2013 19:26, schrieb Rick James:
> > If you are running Apache with MaxClients set too high, that can
> cause the problem.
> 
> "too high" is relative
> 
> > That Apache setting should be something like 20.  (Other web servers
> > have similar settings.)
> 
> 20 is a laughable value as long you are not hosting only sites with no
> users at all
> 
> i have seen "MaxClients 500" be critical while the hardware was not
> overloaded and we had THOUSANDS of users which liked to get the website
> with all it's images, no way with stupid settings of 20 which means
> only ONE USER at the same time can fetch a single page with images and
> stylesheets
> 


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

Re: How to change max simultaneous connection parameter in mysql.

[Reindl Harald]

Am 18.03.2013 19:26, schrieb Rick James:
> If you are running Apache with MaxClients set too high, that can cause the 
> problem.  

"too high" is relative

> That Apache setting should be something like 20.  (Other web servers have 
> similar settings.)

20 is a laughable value as long you are not hosting only
sites with no users at all

i have seen "MaxClients 500" be critical while the hardware
was not overloaded and we had THOUSANDS of users which liked
to get the website with all it's images, no way with stupid
settings of 20 which means only ONE USER at the same time
can fetch a single page with images and stylesheets




signature.asc
Description: OpenPGP digital signature

MySQL Error#: 2002

[Patrice Olivier-Wilson]

I have about 60 websites based on mysql and php. Suddenly they have all gone 
blank, just white pages. The files are still on the server and I can see the 
tables in all the databases via myphpadmin interfact. I'm not getting any 
response from hosting gods yet. When I try to connect to server via 
Dreamweaver, the error message is:  MySQL Error#: 2002
Can't connect to local MySQL server through socket 
'/var/lib/myswl/mysql.sock'(111).

I have one site that uses a different IP number that that site is ok. My static 
sites, ie, no database inclusion, are ok.

Any ideas what to look for, most appreciated.

Regards,

Patrice Olivier-Wilson
828-628-0500
http://Biz-comm.com
b...@biz-comm.com


"Everything will be alright in the end, so if it is not alright, it is not yet 
the end." - Quote from movie: The Best Exotic Marigold Hotel


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

RE: MySQL Error#: 2002

[Rick James]

Check directory permissions, and check out the 'answers' in here:
http://forums.mysql.com/read.php?10,284776,284936

> -Original Message-
> From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com]
> Sent: Monday, March 18, 2013 12:05 PM
> To: mysql@lists.mysql.com
> Subject: MySQL Error#: 2002
> 
> I have about 60 websites based on mysql and php. Suddenly they have all
> gone blank, just white pages. The files are still on the server and I
> can see the tables in all the databases via myphpadmin interfact. I'm
> not getting any response from hosting gods yet. When I try to connect
> to server via Dreamweaver, the error message is:  MySQL Error#: 2002
> Can't connect to local MySQL server through socket
> '/var/lib/myswl/mysql.sock'(111).
> 
> I have one site that uses a different IP number that that site is ok.
> My static sites, ie, no database inclusion, are ok.
> 
> Any ideas what to look for, most appreciated.
> 
> Regards,
> 
> Patrice Olivier-Wilson
> 828-628-0500
> http://Biz-comm.com
> b...@biz-comm.com
> 
> 
> "Everything will be alright in the end, so if it is not alright, it is
> not yet the end." - Quote from movie: The Best Exotic Marigold Hotel
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql


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

RE: Retrieve most recent of multiple rows

[Rick James]

select * from tab where anwer_timestamp in (select max(anwer_timestamp) from 
tab where q_id in (select distinct q_id from tab) group by q_id);

That query will be extremely slow if you have lots of data.  This is because 
the construct "in (select...)" is not optimized (until version 5.6).

select t.*
from tab t
join ( select max(answer_timestamp) as ts from tab group by q_id ) x 
on t.answer_timestamp = x.ts
That should almost work.  It fails to do what you want if there are duplicate 
timestamps.

So, you make a second pass, this time taking the max(q_id) from each.  (I'll 
leave that as an exercise for the student.)

> -Original Message-
> From: Johan De Meersman [mailto:vegiv...@tuxera.be]
> Sent: Thursday, March 14, 2013 8:29 AM
> To: Ananda Kumar
> Cc: MySQL; Stefan Kuhn
> Subject: Re: Retrieve most recent of multiple rows
> 
> 
> - Original Message -
> > From: "Ananda Kumar" 
> > Subject: Re: Re: Retrieve most recent of multiple rows
> >
> > select * from tab where anwer_timestamp in (select
> max(anwer_timestamp) from tab where q_id in (select distinct q_id from
> tab) group by q_id);
> 
> This is entirely equivalent to
> select * from tab where anwer_timestamp in (select
> max(anwer_timestamp) from tab group by q_id);
> 
> Additionally, there are no double timestamps in the dataset you used,
> whereas there are in OP's given dataset.
> 
> It's also fairly hard to provide an accurate solution as long as OP
> hasn't clarified what exactly they want, really.
> 
> 
> 
> 
> --
> 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


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

RE: mysql cluster and auto shard

[Andrew Morgan]

> -Original Message-
> From: Mike Franon [mailto:kongfra...@gmail.com]
> Sent: 18 March 2013 13:34
> To: 
> Subject: mysql cluster and auto shard
> 
> I am looking at  the best way to scale writes.
> 
> Either using sharding with our existing infrastructure, or moving to
> mysql cluster.
> 
> Does anyone have any pros/cons to using mysql cluster?  I am trying to
> find a much better understanding on how the auto sharding works?  Is it
> true we do not need to change code much on application level?

As a starting point, I think it's worth taking a look at this white paper... 
http://www.mysql.com/why-mysql/white-papers/mysql-cluster-evaluation-guide/ 

Most things will continue to work when migrating to MySQL Cluster but of course 
(as with any storage engine) to get the best performance you'll probably need 
to make some changes; this second paper explains how to optimize for MySQL 
Cluster - hopefully that will give a good feeling for the types of changes that 
you might need/want to make... 
http://www.mysql.com/why-mysql/white-papers/guide-to-optimizing-performance-of-the-mysql-cluster/


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

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