mysql cluster with 3 db/data and 2 mgm nodes
Hi, i am about to configure mysql-cluster setup with 3 data+sql nodes and 2 mgm nodes, i would like to know if it's ok to go ahead with this setup, because somewhere i read it's preferred to setup _even_ number of data nodes instead e.g. 2, 4, or 6. please advice me. thanks and best regards, -m -- Ghulam Mustafa cell: +92 333.611.7681 sip: cyren...@ekiga.net mail: mustafa...@gmail.com web: cyrenity.wordpress.com
Re: mysql cluster with 3 db/data and 2 mgm nodes
Unless you have a very good reason, you probably shouldn't go with cluster in the first place. If it is HA you want to have, check out other options like MMM for MySQL (http://mysql-mmm.org), DRBD +Heartbeat and others. Can you tell us a bit more about your goals/desires? Walter Heck Engineer @ Open Query (http://openquery.com) On Mon, Aug 9, 2010 at 14:46, Ghulam Mustafa mustafa...@gmail.com wrote: Hi, i am about to configure mysql-cluster setup with 3 data+sql nodes and 2 mgm nodes, i would like to know if it's ok to go ahead with this setup, because somewhere i read it's preferred to setup _even_ number of data nodes instead e.g. 2, 4, or 6. please advice me. thanks and best regards, -m -- Ghulam Mustafa cell: +92 333.611.7681 sip: cyren...@ekiga.net mail: mustafa...@gmail.com web: cyrenity.wordpress.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 data get and set problem
Dear All, Table values are getting rollback without calling rollback. I have a table Test with some columns in it. Here's the Table Structure:- Create Table TestID ( TestID int not null, MyID int ); I am calling two procedures one is get the value of MyID column and one is to set the value after that. FirstProc :- Create procedure SP_GetMyID(TestID int) Begin Select MyID From Test Where TestID = TestID; End; Call SP_GetMyID(1); [OUTPUT = 1] If suppose the above proc returns me 1 as MyID then below proc will set MyID as 2 (incrementing by one) Call SP_SetMyID(1,2); Create Procedure SP_SetMyID(TestID int, NewMyID int) Begin Update Test Set MyID = NewMyID Where TestID = TestID; End; After setting the value I am again calling get procedure Call SP_GetMyID(1); [OUTPUT = 2] and if after some time say after 10 seconds if I m calling SP_GetMyID again. It gives me OUTPUT as 1. --Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392
Re: mysql cluster with 3 db/data and 2 mgm nodes
On Sun, Aug 8, 2010 at 11:49 PM, Walter Heck - OlinData.com li...@olindata.com wrote: Unless you have a very good reason, you probably shouldn't go with cluster in the first place. If it is HA you want to have, check out other options like MMM for MySQL (http://mysql-mmm.org), DRBD +Heartbeat and others. Can you tell us a bit more about your goals/desires? Walter Heck Engineer @ Open Query (http://openquery.com) Walter is spot on and yes, 3 is not a good number for data nodes. The only recommended (and somewhat well tested) number of replicas is 2, so 3 would not be useful. You may want to buy another box so that do 2 replicas with 2 shards, OR just use the third node as a warm standby. -- 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
info on mysql
Hi All, How can i get following information from database or is there any query for the same. (1) mysql server uptime (2) Total number of users connected to server (3) Data file information / where it is located through mysql prompt / size of data file (4) each Database size (5) Database I/O information (6) Invalid object in database (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. -- Thanks Pratiksha
searching serialized data stored in mysql
hello, i have a table called checkout, this has a row called products
which has contains a python dictionary data, like
http://pastie.org/1082137
{products: [{productId: 123, productName: APPLE,
productPrice: 2.34, productUrl: http://appple-fruits.net,
productDescription: nice juicy apples},
{productId: 333, productName: ORANGE,
productPrice: 4.21, productUrl: http://appple-fruits.net,
productDescription: nice juicy oranges},
...]}
what will be the correct way to make a search on this data, for
example if i want to search for a range of products with a price
between €2 - €4
is this the correct way to store this type of data?
thanks
norman
would i have to create a temporary table for each serialized value and
then make my query on this?
--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )
--
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 data get and set problem
You might have better results if you use unique identifiers: Select MyID From Test Where TestID = TestID; I'm not sure how mysql is supposed to tell the param 'TestID' from the column name. - michael dykman On Mon, Aug 9, 2010 at 3:10 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear All, Table values are getting rollback without calling rollback. I have a table Test with some columns in it. Here's the Table Structure:- Create Table TestID ( TestID int not null, MyID int ); I am calling two procedures one is get the value of MyID column and one is to set the value after that. FirstProc :- Create procedure SP_GetMyID(TestID int) Begin Select MyID From Test Where TestID = TestID; End; Call SP_GetMyID(1); [OUTPUT = 1] If suppose the above proc returns me 1 as MyID then below proc will set MyID as 2 (incrementing by one) Call SP_SetMyID(1,2); Create Procedure SP_SetMyID(TestID int, NewMyID int) Begin Update Test Set MyID = NewMyID Where TestID = TestID; End; After setting the value I am again calling get procedure Call SP_GetMyID(1); [OUTPUT = 2] and if after some time say after 10 seconds if I m calling SP_GetMyID again. It gives me OUTPUT as 1. -- Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392 -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: info on mysql
For most of your questions, use: SHOW GLOBAL STATUS; SHOW GLOBAL VARIABLES; (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. For these, you need to enable the slow query log, gather queries over a given interval of time, then use either mysql_dump_slow or maatkit's mk-query-digest to parse the log. -Original Message- From: PRATIKSHA JAISWAL [mailto:pratikshadjayswa...@gmail.com] Sent: Monday, August 09, 2010 9:01 AM To: mysql@lists.mysql.com Subject: info on mysql Hi All, How can i get following information from database or is there any query for the same. (1) mysql server uptime (2) Total number of users connected to server (3) Data file information / where it is located through mysql prompt / size of data file (4) each Database size (5) Database I/O information (6) Invalid object in database (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. -- Thanks Pratiksha This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Dropping ALL indexes from a database / not just a table?
Hello Gurus, is there a way / script that will let me DROP ALL the indexes in a single database? for example, lets say my database is call db_Animals, and inside db_Animals there are 97 tables, is there a SINGLE command or a perl script of some kind that can read all the MYI files, remove the .MYI from the file name then proceed to deleting whatever indexes it finds? I am doing this to debug a server that seems to be slow and sluggish. After I am done deleting I will review the slow query logs and then re-index to get the best performance? TIA... Nunzio
Re: Moving from one MySQL server to three MySQL servers?
On 8/5/2010 9:35 AM, Nunzio Daveri wrote: So when I do top-c before I run the reports, it says mysql is using 2GB, then I run the stress test (several reports) and it hits 12GB then I stop the stress and even 30 mins later the server says there is only 800mb of ram free??? That's normal Linux behavior: http://www.linuxatemyram.com/ I've known this to be true for many years, but just for grins, I tested it again on a box here. It's a development box, so it hadn't been used at all over the weekend, and hadn't been used yet today, yet it shows only 5% of its 6 GB total as free. Also it doesn't help when your innodb index is larger than physical memory ;-) Yes, you should indeed fix that. But after all the chatter, I think I will use one of our test/dev servers, install fresh OS, install 5.1.49 then import the db without indexing, run a good 100mb of sql statements against it from our prod servers logs, then look for what fields need to be indexed under slow query logs and then go from there. Is this a good idea vs. going straight to splitting the load into 3 servers? Yes. Keep in mind that replication is a sidecar bolted onto DB systems like MySQL. It's not a core behavior of the relational model, so it has a lot of penalties. The current hoopla about NoSQL systems is one answer to this, and for a lot of applications, it is a much better way to get a distributed DB. mgmt says throw hardware as it's cheaper then re-writting code and re-architecting the db ;-) They may well be right. Just one observation: your 16 GB RAM number means you're not using DDR3 yet, either because the machine doesn't support it, or you're not putting memory sticks in it in threes like you should. Either way, it means RAM accesses could be 50% faster simply by moving to DDR3, changing nothing else about the system configuration. Couple that with the fact that the next common step up in RAM size for DDR3 systems from where you are now is 24 GB, just over your current index size. Those two simple changes may be enough to fix your problem. If you find a way to optimize the indexes to get it all under 16 GB, well, so much the better. Upgrade to 24 GB (or 36...?) anyway and be happy knowing you've bought yourself more time before you need to do the next upgrade. Meantime, let Linux continue to eat your RAM. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to use SSL? (SSL is enabled but not used)
Does anybody know how to use SSL-secured connections to a MySQL server? Has anybody done that at all? In the manual I have now found the following statement: http://dev.mysql.com/doc/refman/5.1/en/ssl-options.html Note that use of --ssl does not require an SSL connection. For example, if the server or client is compiled without SSL support, a normal unencrypted connection is used. What's that supposed to mean? If there's no way to force the connection into SSL, it is entirely useless. Anyone on the wire could simply pretend that the server doesn't support SSL and so deny the encryption and the client wouldn't even care... I don't want to use REQUIRE SSL for an account that is regularly used locally and doesn't need SSL. SSL should really be selected by the client per connection when connecting from some other untrusted network. The whole SSL thing looks pretty unfinished like that. -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: info on mysql
Hello Pratiksha, To get the uptime of the MYSQL instance :- mysql\s as given above just give the above command \s For Total number of users connected to server :- mysqlshow global status like '%user%'; ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Data size, index size no. of tables, engine type* SELECT s.schema_name,t.engine, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),Mb) as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),Mb) as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in(mysql,information_schema,test) GROUP BY s.schema_name,t.engine order by Data_size DESC; For top slow queries :- mysqldumpslow -s c -a -t 5 mysqlslow.log top10_slow-count_envr.log For Engine Info of a table and other details :- Show table status like 'tablename'; Cheers, Anirudh Sundar On Mon, Aug 9, 2010 at 9:31 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi All, How can i get following information from database or is there any query for the same. (1) mysql server uptime (2) Total number of users connected to server (3) Data file information / where it is located through mysql prompt / size of data file (4) each Database size (5) Database I/O information (6) Invalid object in database (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. -- Thanks Pratiksha
Re: MySQL data get and set problem
Hi Michale, I have made that parameter column name change. but I am unable to understand how even after execution of update statement and commit after that, data is getting reverted. Any inputs on this will be a great help. -- Regards, Manasi Save On Mon, 9 Aug 2010 14:11:19 -0400, Michael Dykman wrote: You might have better results if you use unique identifiers: Select MyID From Test Where TestID = TestID; I'm not sure how mysql is supposed to tell the param 'TestID' from the column name. - michael dykman On Mon, Aug 9, 2010 at 3:10 AM, Manasi Save manasi.s...@artificialmachines.com wrote: Dear All, Table values are getting rollback without calling rollback. I have a table Test with some columns in it. Here's the Table Structure:- Create Table TestID ( TestID int not null, MyID int ); I am calling two procedures one is get the value of MyID column and one is to set the value after that. FirstProc :- Create procedure SP_GetMyID(TestID int) Begin Select MyID From Test Where TestID = TestID; End; Call SP_GetMyID(1); [OUTPUT = 1] If suppose the above proc returns me 1 as MyID then below proc will set MyID as 2 (incrementing by one) Call SP_SetMyID(1,2); Create Procedure SP_SetMyID(TestID int, NewMyID int) Begin Update Test Set MyID = NewMyID Where TestID = TestID; End; After setting the value I am again calling get procedure Call SP_GetMyID(1); [OUTPUT = 2] and if after some time say after 10 seconds if I m calling SP_GetMyID again. It gives me OUTPUT as 1. -- Regards, Manasi Save Artificial Machines Private Limited manasi.s...@artificialmachines.com Ph:-9833537392 -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
