Re: how to figure out what options are supported, was Re: Odd crash with MySQL Embedded 5.1.28
On Fri, Sep 26, 2008 at 4:15 PM, Dan Nelson <[EMAIL PROTECTED]> wrote: > In the last episode (Sep 26), Ian Monroe said: >> On Fri, Sep 26, 2008 at 1:55 PM, Ian Monroe <[EMAIL PROTECTED]> wrote: >> > On Fri, Sep 26, 2008 at 9:37 AM, Ian Monroe <[EMAIL PROTECTED]> wrote: >> >> So we're using MySQL Embedded in Amarok, it works fine for most of us >> >> that use 5.1, including myself. However the following backtrace is >> >> from the second person to have this issue. Its repeatable, happens at >> >> startup. >> >> >> >> Is there something about how their MySQL is setup on their system that >> >> could cause such a crash? >> > [snip] >> >>mysql_library_init(num_elements, server_options, server_groups); >> >>m_db = mysql_init(NULL); // <- crash happens HERE >> > >> > So we did figure out that mysql_library_init is returning 1, which >> > explains why mysql_init is crashing. >> > >> > But how do we figure out what exactly the '1' means? Any clues? >> >> We figured it out: we had --skip-innodb, which crashed anyone who >> didn't have Innodb support built. >> >> Is there any way we could have it add --skip-innodb if and only if >> Innodb support is present? I can't find a mysql_* function that >> describes server functionality. A compile time option would work as >> well I suppose, since we're currently statically linking. > > You want --loose-skip-innodb. > > http://dev.mysql.com/doc/refman/5.0/en/command-line-options.html > > If an option is prefixed by --loose, a program does not exit with an > error if it does not recognize the option, but instead issues only a > warning: > > $ mysql --loose-no-such-option > mysql: WARNING: unknown option '--no-such-option' Hey thanks, I think thats exactly what we need. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to figure out what options are supported, was Re: Odd crash with MySQL Embedded 5.1.28
In the last episode (Sep 26), Ian Monroe said: > On Fri, Sep 26, 2008 at 1:55 PM, Ian Monroe <[EMAIL PROTECTED]> wrote: > > On Fri, Sep 26, 2008 at 9:37 AM, Ian Monroe <[EMAIL PROTECTED]> wrote: > >> So we're using MySQL Embedded in Amarok, it works fine for most of us > >> that use 5.1, including myself. However the following backtrace is > >> from the second person to have this issue. Its repeatable, happens at > >> startup. > >> > >> Is there something about how their MySQL is setup on their system that > >> could cause such a crash? > > [snip] > >>mysql_library_init(num_elements, server_options, server_groups); > >>m_db = mysql_init(NULL); // <- crash happens HERE > > > > So we did figure out that mysql_library_init is returning 1, which > > explains why mysql_init is crashing. > > > > But how do we figure out what exactly the '1' means? Any clues? > > We figured it out: we had --skip-innodb, which crashed anyone who > didn't have Innodb support built. > > Is there any way we could have it add --skip-innodb if and only if > Innodb support is present? I can't find a mysql_* function that > describes server functionality. A compile time option would work as > well I suppose, since we're currently statically linking. You want --loose-skip-innodb. http://dev.mysql.com/doc/refman/5.0/en/command-line-options.html If an option is prefixed by --loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning: $ mysql --loose-no-such-option mysql: WARNING: unknown option '--no-such-option' -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd crash with MySQL Embedded 5.1.28
On Fri, Sep 26, 2008 at 1:55 PM, Ian Monroe <[EMAIL PROTECTED]> wrote: > On Fri, Sep 26, 2008 at 9:37 AM, Ian Monroe <[EMAIL PROTECTED]> wrote: >> So we're using MySQL Embedded in Amarok, it works fine for most of us >> that use 5.1, including myself. However the following backtrace is >> from the second person to have this issue. Its repeatable, happens at >> startup. >> >> Is there something about how their MySQL is setup on their system that >> could cause such a crash? > [snip] >>mysql_library_init(num_elements, server_options, server_groups); >>m_db = mysql_init(NULL); // <- crash happens HERE > > So we did figure out that mysql_library_init is returning 1, which > explains why mysql_init is crashing. > > But how do we figure out what exactly the '1' means? Any clues? I created a bug for this "why is it returning 1?" issue: http://bugs.mysql.com/39693 Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to figure out what options are supported, was Re: Odd crash with MySQL Embedded 5.1.28
On Fri, Sep 26, 2008 at 1:55 PM, Ian Monroe <[EMAIL PROTECTED]> wrote: > On Fri, Sep 26, 2008 at 9:37 AM, Ian Monroe <[EMAIL PROTECTED]> wrote: >> So we're using MySQL Embedded in Amarok, it works fine for most of us >> that use 5.1, including myself. However the following backtrace is >> from the second person to have this issue. Its repeatable, happens at >> startup. >> >> Is there something about how their MySQL is setup on their system that >> could cause such a crash? > [snip] >>mysql_library_init(num_elements, server_options, server_groups); >>m_db = mysql_init(NULL); // <- crash happens HERE > > So we did figure out that mysql_library_init is returning 1, which > explains why mysql_init is crashing. > > But how do we figure out what exactly the '1' means? Any clues? We figured it out: we had --skip-innodb, which crashed anyone who didn't have Innodb support built. Is there any way we could have it add --skip-innodb if and only if Innodb support is present? I can't find a mysql_* function that describes server functionality. A compile time option would work as well I suppose, since we're currently statically linking. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd crash with MySQL Embedded 5.1.28
On Fri, Sep 26, 2008 at 2:27 PM, Martin Gainty <[EMAIL PROTECTED]> wrote: > 2 items- > > check that the my.cnf file cannot be found on your path Yea its right, plus we tried switching to --no-defaults instead of specifying an (empty) file. That didn't work either. > or your server groups are (usually just server, client) are incorrect How could they be incorrect? I only vaguely get what they are for, something about the config groups in my.cnf file. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd crash with MySQL Embedded 5.1.28
2 items- check that the my.cnf file cannot be found on your path or your server groups are (usually just server, client) are incorrect Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Fri, 26 Sep 2008 13:55:10 -0500 > From: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > Subject: Re: Odd crash with MySQL Embedded 5.1.28 > CC: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > On Fri, Sep 26, 2008 at 9:37 AM, Ian Monroe <[EMAIL PROTECTED]> wrote: > > So we're using MySQL Embedded in Amarok, it works fine for most of us > > that use 5.1, including myself. However the following backtrace is > > from the second person to have this issue. Its repeatable, happens at > > startup. > > > > Is there something about how their MySQL is setup on their system that > > could cause such a crash? > [snip] > >mysql_library_init(num_elements, server_options, server_groups); > >m_db = mysql_init(NULL); // <- crash happens HERE > > So we did figure out that mysql_library_init is returning 1, which > explains why mysql_init is crashing. > > But how do we figure out what exactly the '1' means? Any clues? > > Ian > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > _ Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie. http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008
Re: Odd crash with MySQL Embedded 5.1.28
On Fri, Sep 26, 2008 at 9:37 AM, Ian Monroe <[EMAIL PROTECTED]> wrote: > So we're using MySQL Embedded in Amarok, it works fine for most of us > that use 5.1, including myself. However the following backtrace is > from the second person to have this issue. Its repeatable, happens at > startup. > > Is there something about how their MySQL is setup on their system that > could cause such a crash? [snip] >mysql_library_init(num_elements, server_options, server_groups); >m_db = mysql_init(NULL); // <- crash happens HERE So we did figure out that mysql_library_init is returning 1, which explains why mysql_init is crashing. But how do we figure out what exactly the '1' means? Any clues? Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-text search and queries sensitive to accents
Hi all: I read in mysql documentation that searches are case-insensitive by default but this default behaviour can be changed using a latin1_bin collation. But, is there any way to make searches sensitive or unsensitive to accents, umlauts, etc.? I suppose that queries are accent sensitive by default. Is there any way to change this in mysql 5.1? Thanks in advance, Mario Barcala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed up slow SQL statement.
Hi Eric- the immediate challenge is to fic the join statement so make sure products.id is indexed make sure browse_nodes_to_products.product_id is indexed make sure browse_nodes_to_products.browse_node_id is indexed make sure browse_nodes.amazon_id is indexed there seems to be mapping/relationship challenge for your product to browse_node_id which finally maps to amazon_id would be simpler if is there any way you can carry the product_id thru from products table to browser_nodes_to_products table to browse_nodes table anyone? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > From: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > Subject: Speed up slow SQL statement. > Date: Fri, 26 Sep 2008 10:42:07 -0400 > > Good morning everyone, > > I've got a sql statement that is running quite slow. I've indexed > everything I can that could possibly be applicable but I can't seem to > speed it up. > > I've put up the table structures, row counts, the sql statement and > the explain dump of the sql statement all in paste online here > http://pastebin.com/m46cced58 > > I'm including the sql statement itself here as well: > > select distinct products.id as id, >products.created_at as created_at, >products.asin as asin, >products.sales_rank as sales_rank, >products.points as points > from products > inner join (browse_nodes, browse_nodes_to_products) on >(browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id > and products.id = browse_nodes_to_products.product_id) > where browse_nodes.lft >= 5 and browse_nodes.rgt <= 10 > order by products.sales_rank desc limit 10 offset 0; > > > What I'm trying to accomplish with this is to get an ordered list of > unique products found under a category. > > Any ideas on how I could speed this up? > > Thanks in advance, > > Eric Stewart > [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > _ See how Windows connects the people, information, and fun that are part of your life. http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
Re: Combining Multiple Tables
On Friday, 26. September 2008, Tim Carty wrote: | I would like to combine multiple tables into a single table. I can create a | UNION between tables with similar columns cols A, B, and C from table_1 | with cols A, B, and C from Table_2. What I can¹t figure out is how to add | additionals columns (D, E, F) from table_2 if the columns don¹t exist in | Table_1. select a, b, c, null as d, null as e, null as f from table_1 union select a, b, c, d, e, f from table_2 should do it. Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Combining Multiple Tables
Use null (or custom text) to replace the missing columns A quick ex: Select a,b,c,null,d from table1 Union all Select d,'test valid',e,f,g from table 2 This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Tim Carty [mailto:[EMAIL PROTECTED] Sent: Friday, September 26, 2008 10:41 AM To: mysql@lists.mysql.com Subject: Combining Multiple Tables I would like to combine multiple tables into a single table. I can create a UNION between tables with similar columns cols A, B, and C from table_1 with cols A, B, and C from Table_2. What I can¹t figure out is how to add additionals columns (D, E, F) from table_2 if the columns don¹t exist in Table_1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speed up slow SQL statement.
Good morning everyone, I've got a sql statement that is running quite slow. I've indexed everything I can that could possibly be applicable but I can't seem to speed it up. I've put up the table structures, row counts, the sql statement and the explain dump of the sql statement all in paste online here http://pastebin.com/m46cced58 I'm including the sql statement itself here as well: select distinct products.id as id, products.created_at as created_at, products.asin as asin, products.sales_rank as sales_rank, products.points as points from products inner join (browse_nodes, browse_nodes_to_products) on (browse_nodes.amazon_id = browse_nodes_to_products.browse_node_id and products.id = browse_nodes_to_products.product_id) where browse_nodes.lft >= 5 and browse_nodes.rgt <= 10 order by products.sales_rank desc limit 10 offset 0; What I'm trying to accomplish with this is to get an ordered list of unique products found under a category. Any ideas on how I could speed this up? Thanks in advance, Eric Stewart [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Combining Multiple Tables
I would like to combine multiple tables into a single table. I can create a UNION between tables with similar columns cols A, B, and C from table_1 with cols A, B, and C from Table_2. What I can¹t figure out is how to add additionals columns (D, E, F) from table_2 if the columns don¹t exist in Table_1.
Odd crash with MySQL Embedded 5.1.28
So we're using MySQL Embedded in Amarok, it works fine for most of us
that use 5.1, including myself. However the following backtrace is
from the second person to have this issue. Its repeatable, happens at
startup.
Is there something about how their MySQL is setup on their system that
could cause such a crash?
Thread 1 (Thread 0xb397d960 (LWP 23945)):
[KCrash Handler]
#6 0xb750550d in ?? () from /lib/tls/i686/cmov/libc.so.6
#7 0xb7506cad in malloc () from /lib/tls/i686/cmov/libc.so.6
#8 0xaf0525af in my_malloc (size=1228, my_flags=48) at my_malloc.c:34
#9 0xaf069d16 in create_key_cache (name=0xaf31f83a "default",
length=7) at set_var.cc:3898
#10 0xaf069f1d in get_or_create_key_cache (name=0xaf31f83a "default",
length=7) at set_var.cc:3932
#11 0xaf02bb16 in mysql_init_variables () at ../sql/mysqld.cc:7486
#12 0xaf02debc in init_common_variables (conf_file_name=0xaf3140c4
"my", argc=1, argv=0xaf312ba0, groups=0xbfdaae78) at
../sql/mysqld.cc:3139
#13 0xaf02ebb3 in init_embedded_server (argc=0, argv=0x0,
groups=0xbfdaae78) at lib_sql.cc:491
#14 0xaf01fb9c in mysql_server_init (argc=0, argv=0x0, groups=0x0) at
libmysql.c:173
#15 0xaf01c22f in mysql_init (mysql=0x0) at client.c:1509
#16 0xaf0119b8 in MySqlEmbeddedCollection (this=0x835cb20,
[EMAIL PROTECTED], [EMAIL PROTECTED]) at
/home/krf/kde-devel/src/amarok/src/collection/sqlcollection/MySqlEmbeddedCollection.cpp:128
#17 0xaefee54b in SqlCollectionFactory::init (this=0x8178240) at
/home/krf/kde-devel/src/amarok/src/collection/sqlcollection/SqlCollection.cpp:65
[snip]
The relevant code in question:
char* defaultsLine = qstrdup( QString( "--defaults-file=%1" ).arg(
defaultsFile ).toAscii().data() );
char* databaseLine = qstrdup( QString( "--datadir=%1" ).arg(
databaseDir ).toAscii().data() );
if( !QFile::exists( defaultsFile ) )
{
QFile df( defaultsFile );
df.open( QIODevice::WriteOnly );
}
if( !QFile::exists( databaseDir ) )
{
QDir dir( databaseDir );
dir.mkpath( "." );
}
static const int num_elements = 5;
char **server_options = new char* [ num_elements + 1 ];
server_options[0] = "amarokmysqld";
server_options[1] = defaultsLine;
server_options[2] = databaseLine;
server_options[3] = "--default-storage-engine=MYISAM";
server_options[4] = "--skip-innodb";
server_options[5] = 0;
char **server_groups = new char* [ 3 ];
server_groups[0] = "amarokserver";
server_groups[1] = "amarokclient";
server_groups[2] = 0;
mysql_library_init(num_elements, server_options, server_groups);
m_db = mysql_init(NULL); // <- crash happens HERE
delete [] server_options;
delete [] server_groups;
delete [] defaultsLine;
delete [] databaseLine;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and SAN storage - a request for comments
On Fri, Sep 26, 2008 at 1:54 AM, Aaron Blew <[EMAIL PROTECTED]> wrote: > Hi Michael, > Overall and most of the time, SANs are a good thing. They have several > advantaged over dedicated directly attached storage arrays: > 1.) They're generally a lot smarter about how and when they write and read > to the disks. Often they understand what's going on down at the head > level, > and can leverage that for better performance. > 2.) They've generally got a lot more cache than a directly attached array > (some systems can have up to 256GB of cache) > 3.) They're a lot more reliable than many direct attached arrays. There > have been many many hours put into algorithms to detect and predict disk > failures by these SAN vendors, and they're designed to keep that data > online > as much as possible as their reputation rides on their availabity. Hitachi > Data Systems (as one example) even offers configurations with a 100% data > availability guarantee (so long as the unit has power) > 4.) Having all those spindles under one management/virtualization framework > makes you a lot more agile with how you can make use of your storage. The > MySQL workloads your environment has may not all be striped across all the > spindles within the SANs, segregating the workloads. However, using all > the > spindles available can have advantages in some workloads as well, since not > all databases will be hammering down to the spindle all the time. > > A SAN environment isn't always a trivial thing to operate, but it will save > a lot of time over managing 100s of direct attached arrays and can offer > performance capabilities way beyond what can be practically achieved by > using direct attached storage. > > -Aaron > > > On Thu, Sep 25, 2008 at 6:38 PM, Michael Dykman <[EMAIL PROTECTED]> wrote: > > > Hello all, > > > > I recent started employment with a company which has a lot of mysql > > servers (100+ is my best estimate so far) and have all of their > > database servers, masters and slaves alike, using one of 2 SANs for > > data storage. They servers are connected to dedicated switches with > > fibre to to SANs and the SANs themselves seem to be well configured > > and tuned. > > > > However, it seems preposterous to me that all those very busy > > databases should, by design, have a common bottleneck and share a > > single point of failure. I am not deeply knowledgeable about SANs or > > their performance characteristics; my reaction thus far is pretty much > > intuition but I help can't but picture the simple analogue of single > > disk or a RAID 10 with synchronized spindles frantically thrashing > > back and forth to respond to tens of thousands of queries per second. > > > > Would anyone care to comment? Is my concern justified or am I merely > > confused? > I can't comment on the details, but I know our large medically based institution uses a SAN, and the transition to it was well thought out and implemented. To my knowledge, client applications have always been the cause of downtime, not bottlenecks on the SAN.
Re: long select query result as as query string on another select statment
Hi, ok i got it working. it was a typo(lol), i missed from in the initial select statment Wrong ### select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Correct ## select * from db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Thanks On Fri, Sep 26, 2008 at 5:18 PM, Madan Thapa <[EMAIL PROTECTED]>wrote: > -- >> >> --- >> >> (SELECT id FROM domains where name='abc.com'); >> >> gives a result of 124 >> >> >> i am also able to use and get proper results for the following query: >> >> select * from domains where id=(SELECT id FROM domains where name=' >> abc.com >> '); >> >> >> - >> Now, >> >> select * db_users where db_id=(SELECT id FROM data_bases where >> dom_id=(SELECT id FROM domains where name='abc.com')); >> >> Please correct me the syntax for the above command. I am trying to use the >> result of one select query as a query string on another. >> >> >> >> Thanks >> > > > On Fri, Sep 26, 2008 at 4:29 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > >> what is the issue ur facing. >> Any syntax error or something else. >> >> trying usin "IN" instead of "=" >> >> >>> >>> >> ==/> > > > The error is : > > The query could not be executed, I tried with IN instead of = too. > > > My guess is , double brackets is causing some syntax error in : > > select * db_users where db_id=(SELECT id FROM data_bases where > dom_id=(SELECT id FROM domains where name='abc.com')); > > Please note: > > SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name=' > abc.com') > > it works and give the id number, it seems I am not using the correct syntax > for a ))( double bracket ) in the above query. > > > Please advise. > > Thanks > > >
Re: long select query result as as query string on another select statment
-- > > --- > > (SELECT id FROM domains where name='abc.com'); > > gives a result of 124 > > > i am also able to use and get proper results for the following query: > > select * from domains where id=(SELECT id FROM domains where name='abc.com > '); > > > - > Now, > > select * db_users where db_id=(SELECT id FROM data_bases where > dom_id=(SELECT id FROM domains where name='abc.com')); > > Please correct me the syntax for the above command. I am trying to use the > result of one select query as a query string on another. > > > > Thanks > On Fri, Sep 26, 2008 at 4:29 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > what is the issue ur facing. > Any syntax error or something else. > > trying usin "IN" instead of "=" > > >> >> > ==/> The error is : The query could not be executed, I tried with IN instead of = too. My guess is , double brackets is causing some syntax error in : select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please note: SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name=' abc.com') it works and give the id number, it seems I am not using the correct syntax for a ))( double bracket ) in the above query. Please advise. Thanks
Re: MySQL and SAN storage - a request for comments
IO also depends on how many DISK controllers are used. The more controllers, better would be IO distribution among spindles On 9/26/08, Aaron Blew <[EMAIL PROTECTED]> wrote: > > Hi Michael, > Overall and most of the time, SANs are a good thing. They have several > advantaged over dedicated directly attached storage arrays: > 1.) They're generally a lot smarter about how and when they write and read > to the disks. Often they understand what's going on down at the head > level, > and can leverage that for better performance. > 2.) They've generally got a lot more cache than a directly attached array > (some systems can have up to 256GB of cache) > 3.) They're a lot more reliable than many direct attached arrays. There > have been many many hours put into algorithms to detect and predict disk > failures by these SAN vendors, and they're designed to keep that data > online > as much as possible as their reputation rides on their availabity. Hitachi > Data Systems (as one example) even offers configurations with a 100% data > availability guarantee (so long as the unit has power) > 4.) Having all those spindles under one management/virtualization framework > makes you a lot more agile with how you can make use of your storage. The > MySQL workloads your environment has may not all be striped across all the > spindles within the SANs, segregating the workloads. However, using all > the > spindles available can have advantages in some workloads as well, since not > all databases will be hammering down to the spindle all the time. > > A SAN environment isn't always a trivial thing to operate, but it will save > a lot of time over managing 100s of direct attached arrays and can offer > performance capabilities way beyond what can be practically achieved by > using direct attached storage. > > -Aaron > > > On Thu, Sep 25, 2008 at 6:38 PM, Michael Dykman <[EMAIL PROTECTED]> wrote: > > > Hello all, > > > > I recent started employment with a company which has a lot of mysql > > servers (100+ is my best estimate so far) and have all of their > > database servers, masters and slaves alike, using one of 2 SANs for > > data storage. They servers are connected to dedicated switches with > > fibre to to SANs and the SANs themselves seem to be well configured > > and tuned. > > > > However, it seems preposterous to me that all those very busy > > databases should, by design, have a common bottleneck and share a > > single point of failure. I am not deeply knowledgeable about SANs or > > their performance characteristics; my reaction thus far is pretty much > > intuition but I help can't but picture the simple analogue of single > > disk or a RAID 10 with synchronized spindles frantically thrashing > > back and forth to respond to tens of thousands of queries per second. > > > > Would anyone care to comment? Is my concern justified or am I merely > > confused? > > > > -- > > - michael dykman > > - [EMAIL PROTECTED] > > > > - All models are wrong. Some models are useful. > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > >
Cannot login to MySQL5.0 after compiling MySQL5.
Yes, mysql by default sets the socket file in the below directory /var/lib/mysql/mysql.sock. But why does it refer to /tmp/mysql.sock file then, as that was the error which was given when i tried to login to mysql. Is that hard-coded for MySQL5.1 source code, which is what i wanted to know ? Thanks
Re: MySQL and SAN storage - a request for comments
Hi Michael, Overall and most of the time, SANs are a good thing. They have several advantaged over dedicated directly attached storage arrays: 1.) They're generally a lot smarter about how and when they write and read to the disks. Often they understand what's going on down at the head level, and can leverage that for better performance. 2.) They've generally got a lot more cache than a directly attached array (some systems can have up to 256GB of cache) 3.) They're a lot more reliable than many direct attached arrays. There have been many many hours put into algorithms to detect and predict disk failures by these SAN vendors, and they're designed to keep that data online as much as possible as their reputation rides on their availabity. Hitachi Data Systems (as one example) even offers configurations with a 100% data availability guarantee (so long as the unit has power) 4.) Having all those spindles under one management/virtualization framework makes you a lot more agile with how you can make use of your storage. The MySQL workloads your environment has may not all be striped across all the spindles within the SANs, segregating the workloads. However, using all the spindles available can have advantages in some workloads as well, since not all databases will be hammering down to the spindle all the time. A SAN environment isn't always a trivial thing to operate, but it will save a lot of time over managing 100s of direct attached arrays and can offer performance capabilities way beyond what can be practically achieved by using direct attached storage. -Aaron On Thu, Sep 25, 2008 at 6:38 PM, Michael Dykman <[EMAIL PROTECTED]> wrote: > Hello all, > > I recent started employment with a company which has a lot of mysql > servers (100+ is my best estimate so far) and have all of their > database servers, masters and slaves alike, using one of 2 SANs for > data storage. They servers are connected to dedicated switches with > fibre to to SANs and the SANs themselves seem to be well configured > and tuned. > > However, it seems preposterous to me that all those very busy > databases should, by design, have a common bottleneck and share a > single point of failure. I am not deeply knowledgeable about SANs or > their performance characteristics; my reaction thus far is pretty much > intuition but I help can't but picture the simple analogue of single > disk or a RAID 10 with synchronized spindles frantically thrashing > back and forth to respond to tens of thousands of queries per second. > > Would anyone care to comment? Is my concern justified or am I merely > confused? > > -- > - michael dykman > - [EMAIL PROTECTED] > > - All models are wrong. Some models are useful. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: long select query result as as query string on another select statment
what is the issue ur facing. Any syntax error or something else. trying usin "IN" instead of "=" On 9/26/08, Madan Thapa <[EMAIL PROTECTED]> wrote: > > Hi, > > > - > > (SELECT id FROM domains where name='abc.com'); > > gives a result of 124 > > > i am also able to use and get proper results for the following query: > > select * from domains where id=(SELECT id FROM domains where name='abc.com > '); > > > - > Now, > > select * db_users where db_id=(SELECT id FROM data_bases where > dom_id=(SELECT id FROM domains where name='abc.com')); > > Please correct me the syntax for the above command. I am trying to use the > result of one select query as a query string on another. > > > > Thanks >
Re: Cannot login to MySQL5.0 after compiling MySQL5.1
please check your my.cnf for the location of mysql.sock file. If i am not wrong mysql by default creates the mysql.sock file /var/lib/mysql. On 9/26/08, Sudhir Menon <[EMAIL PROTECTED]> wrote: > > Hello, > > I have mysql-5.0.22-2.1.0.1.rpm version installed on RHEL5.1 system. I have > been trying certain benchmarking tools and > found that MySQL5.1 has inbuilt benchmarking tool known as "mysqlslap". > Since the tool is not available > for MySQL5.0, i have downloaded the source rpm > MySQL-community-5.1.28-0.rhel5.src.rpm and tried to get the binary > mysqlslap > so that i can execute it in the same > machine running MySQL5.0 and benchmark the same. > > But after compiling the tarball i was not able to login to mysql using : > mysql -u root -p . It gave me the below error. > Cannot connect to /tmp/mysql.sock, which is not the socket file as it is > /var/lib/mysql/mysql.sock. > > Although i could login to mysql by issuing this command :- ln -s > /var/lib/mysql/mysql.sock /tmp/mysql.sock > > The above scenario should not happen when i have just compiled the source > code. What could be wrong. Am i trying something wrong ??? > > > Best Regards > Sudhir.Menon >
long select query result as as query string on another select statment
Hi, - (SELECT id FROM domains where name='abc.com'); gives a result of 124 i am also able to use and get proper results for the following query: select * from domains where id=(SELECT id FROM domains where name='abc.com '); - Now, select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please correct me the syntax for the above command. I am trying to use the result of one select query as a query string on another. Thanks
Cannot login to MySQL5.0 after compiling MySQL5.1
Hello, I have mysql-5.0.22-2.1.0.1.rpm version installed on RHEL5.1 system. I have been trying certain benchmarking tools and found that MySQL5.1 has inbuilt benchmarking tool known as "mysqlslap". Since the tool is not available for MySQL5.0, i have downloaded the source rpm MySQL-community-5.1.28-0.rhel5.src.rpm and tried to get the binary mysqlslap so that i can execute it in the same machine running MySQL5.0 and benchmark the same. But after compiling the tarball i was not able to login to mysql using : mysql -u root -p . It gave me the below error. Cannot connect to /tmp/mysql.sock, which is not the socket file as it is /var/lib/mysql/mysql.sock. Although i could login to mysql by issuing this command :- ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock The above scenario should not happen when i have just compiled the source code. What could be wrong. Am i trying something wrong ??? Best Regards Sudhir.Menon
