Re: category with parentid
Hello ts. Tanaka, I've tried your way and succeed. Thank you. But I want to ask your help again what if the result will be like this: A. Cat A - Subcat A - Subcat A 2. CatB - Subcat B possible by using br or div which the previous result is Cat A - SubcatA, SubcatA Cat B - SubcatB thank you __ Information from ESET NOD32 Antivirus, version of virus signature database 7179 (20120529) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
and also id (either cat or subcat) can be called - Original Message - From: HaidarPesebe haidarpes...@gmail.com To: MySQL Lists mysql@lists.mysql.com Sent: Wednesday, May 30, 2012 2:31 PM Subject: Re: category with parentid Hello ts. Tanaka, I've tried your way and succeed. Thank you. But I want to ask your help again what if the result will be like this: A. Cat A - Subcat A - Subcat A 2. CatB - Subcat B possible by using br or div which the previous result is Cat A - SubcatA, SubcatA Cat B - SubcatB thank you __ Information from ESET NOD32 Antivirus, version of virus signature database 7179 (20120529) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 7179 (20120529) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
ANN: Only 2 days left, 70% discount on all products!
Hello everyone, There's only a few days left to get a massive discount of 70% on all database development products at Upscene Productions. www.upscene.com Database tools for: - Firebird - MySQL - Microsoft SQL Server - Oracle - InterBase - NexusDB - Advantage Database - ADO ODBC -- With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ANN: Hopper for MySQL, first public beta available!
Welcome to Hopper! Claudio Thank you Claudio, we hope it will serve it's purpose and we get lots of feedback on it. 2012/5/25 Martijn Tonies m.ton...@upscene.com Dear ladies and gentlemen, Upscene Productions is happy to announce the launch of a new database-developer tool: Hopper (public beta 1) Hopper is a Stored Routine Debugger, the first beta for MySQL is now available. For more information, see http://www.upscene.com/** displaynews.php?item=20120525http://www.upscene.com/displaynews.php?item=20120525 With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: 回复： 回复： Why is creating indexes faster after inserting massive data rows?
On 05/07/2012 12:30 PM, Zhangzhigang wrote: Thanks, i thought about this answer in the past, and i appreciate your reply. How about the omelet? What's your method? -- RMA. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: category with parentid
Pesebe-san,
I'm sorry to i'm not clear what do you want to do.
do you mean like this? (exampled by Perl)
#---
use DBI;
$cn = DBI-connect(DBI:mysql:test,root,);
$st = $cn-prepare(SELECT t1.name AS cat,GROUP_CONCAT(t2.name) AS
subcat FROM `table` AS t1 LEFT JOIN `table` AS t2 ON t1.id =
t2.parentid WHERE t1.parentid = 0 GROUP BY cat;);
$st-execute;
while ($rs = $st-fetchrow_hashref) {
print $rs-{cat},br\n;
@buff = split(/,/,$rs-{subcat});
foreach $i (@buff) {
print -,$i,br\n;
}
}
#---
regards,
ts. tanaka//
2012/5/30 HaidarPesebe haidarpes...@gmail.com:
and also id (either cat or subcat) can be called
- Original Message - From: HaidarPesebe haidarpes...@gmail.com
To: MySQL Lists mysql@lists.mysql.com
Sent: Wednesday, May 30, 2012 2:31 PM
Subject: Re: category with parentid
Hello ts. Tanaka,
I've tried your way and succeed. Thank you.
But I want to ask your help again what if the result will be like this:
A. Cat A
- Subcat A
- Subcat A
2. CatB
- Subcat B
possible by using br or div
which the previous result is
Cat A - SubcatA, SubcatA
Cat B - SubcatB
thank you
__ Information from ESET NOD32 Antivirus, version of virus
signature database 7179 (20120529) __
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__ Information from ESET NOD32 Antivirus, version of virus signature
database 7179 (20120529) __
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
--
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 Community Server 5.5.25 has been released
Dear MySQL users, MySQL 5.5.25 is a new version of the 5.5 production release of the world's most popular open source database. MySQL 5.5.25 is recommended for use on production systems. MySQL 5.5 includes several high-impact enhancements to improve the performance and scalability of the MySQL Database, taking advantage of the latest multi-CPU and multi-core hardware and operating systems. In addition, with release 5.5, InnoDB is now the default storage engine for the MySQL Database, delivering ACID transactions, referential integrity and crash recovery by default. MySQL 5.5 also provides a number of additional enhancements including: - Significantly improved performance on Windows, with various Windows specific features and improvements - Higher availability, with new semi-synchronous replication and Replication Heart Beat - Improved usability, with Improved index and table partitioning, SIGNAL/RESIGNAL support and enhanced diagnostics, including a new Performance Schema monitoring capability. For a more complete look at what's new in MySQL 5.5, please see the following resources: MySQL 5.5 is GA, Interview with Tomas Ulin: http://dev.mysql.com/tech-resources/interviews/thomas-ulin-mysql-55.html Documentation: http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html Whitepaper: What's New in MySQL 5.5: http://dev.mysql.com/why-mysql/white-papers/mysql-wp-whatsnew-mysql-55.php If you are running a MySQL production level system, we would like to direct your attention to MySQL Enterprise Edition, which includes the most comprehensive set of MySQL production, backup, monitoring, modeling, development, and administration tools so businesses can achieve the highest levels of MySQL performance, security and uptime. http://mysql.com/products/enterprise/ For information on installing MySQL 5.5.25 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.5/en/installing.html For upgrading from previous MySQL releases, please see the important upgrade considerations at: http://dev.mysql.com/doc/refman/5.5/en/upgrading.html MySQL Database 5.5.25 is available in source and binary form for a number of platforms from our download pages at: http://dev.mysql.com/downloads/mysql/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.5. It may also be viewed online at: http://dev.mysql.com/doc/refman/5.5/en/news-5-5-25.html Enjoy! On behalf of the MySQL Build Team, Joerg Bruehe D.1.2. Changes in MySQL 5.5.25 (2012-May-30) Functionality Added or Changed * The --safe-mode server option now is deprecated and will be removed in MySQL 5.6. Bugs Fixed * Performance: InnoDB: Improved the algorithm related to adaptive flushing. This fix increases the rate of flushing in cases where compression is used and the data set is larger than the buffer pool, leading to eviction. (Bug #13990648, Bug #65061) * InnoDB: In a transaction using the REPEATABLE READ isolation level, an UPDATE or DELETE statement for an InnoDB table could sometimes overlook rows recently committed by other transactions. As explained in Section 14.3.9.2, Consistent Nonlocking Reads, DML statements within a REPEATABLE READ transaction apply to rows committed by other transactions, even if a query could not see those rows. (Bug #14007649, Bug #65111) * InnoDB: The Innodb_buffer_pool_pages_flushed status variable was incorrectly set to twice the value it should be. Its value should never exceed the value of Innodb_pages_written. (Bug #14000361, Bug #65030) * InnoDB: The error handling and message was improved for attempting to create a foreign key with a column referencing itself. The message suggested a potential problem with the data dictionary, when no such problem existed. (Bug #12902967) * InnoDB: The CHECK TABLE statement could fail for a large InnoDB table due to a timeout value of 2 hours. For typical storage devices, the issue could occur for tables that exceeded approximately 200 or 350 GB, depending on I/O speed. The fix relaxes the locking performed on the table being checked, which makes the timeout less likely. It also makes InnoDB recognize the syntax CHECK TABLE QUICK, which avoids the possibility of the timeout entirely. (Bug #11758510, Bug #50723) * Replication: It was theoretically possible for concurrent execution of more than one instance of SHOW BINLOG EVENTS to crash the MySQL Server. (Bug #13979418) * Replication: Statements using
Re: Need help for performance tuning with Mysql
Check performance with script :
http://mysqltuner.pl/mysqltuner.pl - Variable tunning
http://hackmysql.com/mysqlidxchk - Unused Index
Thanks,
Prabhat
On Thu, May 24, 2012 at 4:32 PM, Rick James rja...@yahoo-inc.com wrote:
Thanks. I got tired of answering the same questions about buffer_pool and
key_buffer over and over on forums.mysql.com; now I just point people at
that page.
INT(1) and INT(50) are identical -- and take 4 bytes. See TINYINT,
BIGINT, etc. Also, UNSIGNED is probably wanted in more places than you
have it.
555KB is not very big. But a table scan (as indicated by the EXPLAIN)
costs something.
select * -- Is this what you are really fetching? If not, we
can discuss a covering index.
from thold_data
where thold_enabled='on' -- 2-valued flag? Not likely to be useful in
an index, esp. not by itself
AND data_id = 91633; -- Probably the best bet.
Recommend:
INDEX(data_id) -- or UNIQUE, if it is unique
INDEX(data_id, thold_enabled) -- or the opposite order; this probably
would not be noticeable better.
`notify_default` enum('on','off') default NULL
Did you really mean to have 3 values (on, off, NULL)?
-Original Message-
From: Yu Watanabe [mailto:yu.watan...@jp.fujitsu.com]
Sent: Wednesday, May 23, 2012 7:10 PM
To: mysql@lists.mysql.com
Subject: Re: Need help for performance tuning with Mysql
Rick
Thank you for the reply.
1. There are only a few things worth tuning -- see
http://mysql.rjweb.org/doc.php/memory (they don't include the ones you
tried)
The page is really cool. Its very simple and easy to understand.
2. Instead INDEXes and schema design must be studied. Please provide:
SHOW CREATE TABLE
| thold_data | CREATE TABLE `thold_data` (
`id` int(11) NOT NULL auto_increment,
`rra_id` int(11) NOT NULL default '0',
`data_id` int(11) NOT NULL default '0',
`thold_hi` varchar(100) default NULL,
`thold_low` varchar(100) default NULL,
`thold_fail_trigger` int(10) unsigned default NULL,
`thold_fail_count` int(11) NOT NULL default '0',
`thold_alert` int(1) NOT NULL default '0',
`thold_enabled` enum('on','off') NOT NULL default 'on',
`bl_enabled` enum('on','off') NOT NULL default 'off',
`bl_ref_time` int(50) unsigned default NULL,
`bl_ref_time_range` int(10) unsigned default NULL,
`bl_pct_down` int(10) unsigned default NULL,
`bl_pct_up` int(10) unsigned default NULL,
`bl_fail_trigger` int(10) unsigned default NULL,
`bl_fail_count` int(11) unsigned default NULL,
`bl_alert` int(2) NOT NULL default '0',
`lastread` varchar(100) default NULL,
`oldvalue` varchar(100) NOT NULL default '',
`repeat_alert` int(10) unsigned default NULL,
`notify_default` enum('on','off') default NULL,
`notify_extra` varchar(255) default NULL,
`host_id` int(10) default NULL,
`syslog_priority` int(2) default '3',
`cdef` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `rra_id` (`rra_id`)
) ENGINE=MyISAM AUTO_INCREMENT=69641 DEFAULT CHARSET=latin1 |
SHOW TABLE SIZE
You must be mentioning about the show table status
mysql show table status where name = thold_data;
+++-++--++-
+-+--+---+-
---+-+-
+-+---+--+
+-+
| 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 |
+++-++--++-
+-+--+---+-
---+-+-
+-+---+--+
+-+
| thold_data | MyISAM | 10 | Dynamic| 6161 | 90 |
555128 | 281474976710655 | 140288 | 0 | 70258 |
2012-05-24 10:41:47 | 2012-05-24 10:47:19 | 2012-05-24
10:41:47 | latin1_swedish_ci | NULL || |
+++-++--++-
+-+--+---+-
---+-+-
+-+---+--+
+-+
1 row in set (0.00 sec)
EXPLAIN SELECT
I have seen the following select query in the slow query log.
I also saw update queries as well.
mysql explain select * from thold_data where thold_enabled='on' AND
mysql data_id = 91633;
Re: Architectural Help
Hi Anupam, You can achive this by circular replication, Circular replication between 4 nodes works fine. but not sure with 50 node server how practical it was. Each database node will be a data center, *Node 1 - data center 1* *-* *Node 2 - Data center 2* *-* *Node 3 - Data Center 3* *-* *Node 4 - Data Center 4* *- .. Node 50 - Data Center - 50*. so basicaly it will Multimaster Replication System; Fail over :- eg. data center 20 have network issue , in that case Node 19 replicate from Node 21, bypass node 20 -- need to do some scripting. http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html Thank You. On Thu, May 24, 2012 at 4:41 PM, Rick James rja...@yahoo-inc.com wrote: I deal with dozens of systems, all doing cross-country or cross-ocean replication. The only viable approach (that we have deployed in production) is * Dual-master, single writer -- That is, all 49 clients write to one machine * The two masters are geographically separate. * Failover involves switching to the 'other' master. It is _not_ fully automated. * Slaves, as needed, scattered around the world -- This provides read scaling. 1M writes per day -- that is an average of 12/sec. Yawn. Replication delay -- you probably will almost never see any. Network outages -- one of many things that can cause trouble. The sooner you write the data _and_ copy it to a _remote_ site, the sooner you are immune to floods, tornados, cyclones, power outages, motherboard death, etc. -Original Message- From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 3:39 AM To: Nigel Wood Cc: mysql@lists.mysql.com Subject: Re: Architectural Help Dear Nigel, Thank for reply.. See my comments below --Anupam From: Nigel Wood nw...@plus.net To: Anupam Karmarkar sb_akarmar...@yahoo.com; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, 24 May 2012 3:35 PM Subject: RE: Architectural Help A few questions: which is more or a problem: network outages, network capacity or query latency? Network Outages is problem, between data centres When you say near real-time do you need transactional consistent view on all 49 servers or can some lag be tolerated? Some lag can be tolerated considering network outage Can any one of the 49 local servers potentially update/delete the same rows or data? Yes central server can update same row or local server but not at same time, Few data will be updated in central location and others majorly in local.One Application Writes in Centeral and Others in local, Local app and central app shares some data. Is there any natural segmentation point within the data? No Do the data centers have diverse networks so that connections to some data centers may remain when others? Yes, we have diverse nework , so connectivity will be avaiable in other data centers. In the event that a local data centre is totally isolated from the others what data should it be allowed to update? Loca application should always write in local database, Central Application Updated will not be available to local. Do your applications produce/examine large data set querying by secondary keys or using tull text search? We dont have text search or we dont query large data Are you in a position to modify the applications? No, Micro changes ok __ From: Anupam Karmarkar [sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam -- 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 -- Best Regards, Prabhat
sort order
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
Re: sort order
Hi Wes, have you double checked you character set and collation ??
http://dev.mysql.com/doc/refman//5.5/en/charset-charsets.html
The collation is which determines the sorting order.
Carlos Proal
On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote:
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
Re: sort order
I'll double check and get back.
Thanks,
-wes
On Wed, May 30, 2012 at 3:43 PM, Carlos Proal carlos.pr...@gmail.comwrote:
Hi Wes, have you double checked you character set and collation ??
http://dev.mysql.com/doc/refman//5.5/en/charset-charsets.html
The collation is which determines the sorting order.
Carlos Proal
On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote:
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
Re: sort order
In phpMyAdmin it says the database collation is utf8_unicode_ci and each of
the columns is utf8_unicode_ci except the ID primary key column.
If you create a simple table with ID and TITLE and put the data below (each
row in a record) and run 'select * from table order by title' what do you
get?
CREATE TABLE IF NOT EXISTS `testtab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
The above is a sample export of what I have for the table definition.
-wes
On Wed, May 30, 2012 at 3:43 PM, Carlos Proal carlos.pr...@gmail.comwrote:
Hi Wes, have you double checked you character set and collation ??
http://dev.mysql.com/doc/refman//5.5/en/charset-charsets.html
The collation is which determines the sorting order.
Carlos Proal
On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote:
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like this
(order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
RE: sort order
a and A are identical, according to utf8_unicode_ci and utf8_general_ci,
but not utf8_bin.
That is, I would not expect [ to slip in between them.
About 80% of the way down this page, there is a list of some of the
'equivalences' in selected collations:
http://mysql.rjweb.org/doc.php/charcoll
In particular:
utf8 : utf8_unicode_ciA=a=ª=À=Á=Â=Ã=Ä=Å=à=á=â=ã=ä=å ae az Æ=æ
says that lots of things collate equal to 'A'. All of them sort before ae az
the ligature Æ (upper or lower case)
-Original Message-
From: Wes James [mailto:compte...@gmail.com]
Sent: Wednesday, May 30, 2012 2:03 PM
To: mysql general discussion
Subject: sort order
I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for
utf8-unicode doesn't seem to be right. It is sorting some text like
this (order by title):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
![
!@
!a
!A
!t
!test
'Coal age'
[
\--\
100 years
when it should be (shouldn't it?):
!
! *`-=[];',./~@#$%^()_+{}|:?\
!
!@
!A
![
!a
!t
!test
'Coal age'
100 years
[
\--\
--
Why is it doing this?
Thanks,
-wes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
RE: Architectural Help
Caution!! If any one of them dies and cannot be recovered, you will have a mess to fix. From: Prabhat Kumar [mailto:aim.prab...@gmail.com] Sent: Wednesday, May 30, 2012 1:43 PM To: Rick James Cc: Anupam Karmarkar; Nigel Wood; mysql@lists.mysql.com Subject: Re: Architectural Help Hi Anupam, You can achive this by circular replication, Circular replication between 4 nodes works fine. but not sure with 50 node server how practical it was. Each database node will be a data center, Node 1 - data center 1 - Node 2 - Data center 2 - Node 3 - Data Center 3 - Node 4 - Data Center 4 - .. Node 50 - Data Center - 50. so basicaly it will Multimaster Replication System; Fail over :- eg. data center 20 have network issue , in that case Node 19 replicate from Node 21, bypass node 20 -- need to do some scripting. http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html Thank You. On Thu, May 24, 2012 at 4:41 PM, Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com wrote: I deal with dozens of systems, all doing cross-country or cross-ocean replication. The only viable approach (that we have deployed in production) is * Dual-master, single writer -- That is, all 49 clients write to one machine * The two masters are geographically separate. * Failover involves switching to the 'other' master. It is _not_ fully automated. * Slaves, as needed, scattered around the world -- This provides read scaling. 1M writes per day -- that is an average of 12/sec. Yawn. Replication delay -- you probably will almost never see any. Network outages -- one of many things that can cause trouble. The sooner you write the data _and_ copy it to a _remote_ site, the sooner you are immune to floods, tornados, cyclones, power outages, motherboard death, etc. -Original Message- From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.commailto:sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 3:39 AM To: Nigel Wood Cc: mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: Re: Architectural Help Dear Nigel, Thank for reply.. See my comments below --Anupam From: Nigel Wood nw...@plus.netmailto:nw...@plus.net To: Anupam Karmarkar sb_akarmar...@yahoo.commailto:sb_akarmar...@yahoo.com; mysql@lists.mysql.commailto:mysql@lists.mysql.com mysql@lists.mysql.commailto:mysql@lists.mysql.com Sent: Thursday, 24 May 2012 3:35 PM Subject: RE: Architectural Help A few questions: which is more or a problem: network outages, network capacity or query latency? Network Outages is problem, between data centres When you say near real-time do you need transactional consistent view on all 49 servers or can some lag be tolerated? Some lag can be tolerated considering network outage Can any one of the 49 local servers potentially update/delete the same rows or data? Yes central server can update same row or local server but not at same time, Few data will be updated in central location and others majorly in local.One Application Writes in Centeral and Others in local, Local app and central app shares some data. Is there any natural segmentation point within the data? No Do the data centers have diverse networks so that connections to some data centers may remain when others? Yes, we have diverse nework , so connectivity will be avaiable in other data centers. In the event that a local data centre is totally isolated from the others what data should it be allowed to update? Loca application should always write in local database, Central Application Updated will not be available to local. Do your applications produce/examine large data set querying by secondary keys or using tull text search? We dont have text search or we dont query large data Are you in a position to modify the applications? No, Micro changes ok __ From: Anupam Karmarkar [sb_akarmar...@yahoo.commailto:sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we
