Re: Facebook Trapped In MySQL a 'Fate Worse Than Death'
Daevid Vincent wrote: http://developers.slashdot.org/story/11/07/09/1256241/Facebook-Trapped-In-My SQL-a-Fate-Worse-Than-Death According to database pioneer Michael Stonebraker, Facebook is operating a huge, complex MySQL implementation equivalent to 'a fate worse than death,' and the only way out is 'bite the bullet and rewrite everything http://gigaom.com/cloud/facebook-trapped-in-mysql-fate-worse-than-death/ .' Not that it's necessarily Facebook's fault, though. Stonebraker says the social network's predicament is all too common among web startups that start small and grow to epic proportions. Fortunately those web startups that start small and grow to epic proportions are quite limited in numbers :-) /Per Jessen, Zürich -- 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 and set complements
Just now realized I answered to Mike only oops. So posting it again... forcing the use of the use_id index didn't really improve things, unfortunately. Cheers, Leonardo Borges www.leonardoborges.com On Sat, Jul 9, 2011 at 7:24 AM, mos mo...@fastmail.fm wrote: Leonardo, What happens when you use force index(user_id) ? See http://dev.mysql.com/doc/**refman/5.1/en/index-hints.htmlhttp://dev.mysql.com/doc/refman/5.1/en/index-hints.html Mike At 09:19 AM 7/8/2011, you wrote: Same as before, but with the new index listed in the possible keys: ++-+---+--**-+** --+---**---+-++---** -+-+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---+--**-+** --+---**---+-++---** -+-+ | 1 | SIMPLE | u | index | NULL | id_idx | 5 | NULL | 972064 | Using index | | 1 | SIMPLE | a | ref | user_idx,email_idx,activity_**idx,compound_idx | user_idx | 5 | getup.u.id | 20 | Using where | ++-+---+--**-+** --+---**---+-++---** -+-+ On Sat, Jul 9, 2011 at 12:00 AM, Johnny Withers joh...@pixelated.net wrote: What did the explain output look like after the new index? On Fri, Jul 8, 2011 at 8:53 AM, Leonardo Borges leonardoborges...@gmail.com wrote: Hi Johnny, I just gave that a try but it didn't help as I suspected. I still believe the problem is in mysql not being able to handle set subtractions. Therefore, it has to perform the work harder to return the rows that represent a no match with NULL values in place so they can then be filtered by the WHERE clause. This type of query seems to be a corner case in mysql one should be aware about when working with large datasets. Cheers, Leonardo Borges www.leonardoborges.com On Fri, Jul 8, 2011 at 11:18 PM, Johnny Withers joh...@pixelated.net wrote: Leonardo, I think a new compound key on email_id and activity in the activities table may help. I'm not sure if this will help or not, Its hard to test w/o having a large data set to test against. On Thu, Jul 7, 2011 at 9:07 PM, Leonardo Borges leonardoborges...@gmail.com wrote: Sure can: show create table activities; CREATE TABLE `activities` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `country_iso` varchar(2) DEFAULT NULL, `tags` varchar(255) DEFAULT NULL, `postcode` int(11) DEFAULT NULL, `activity` varchar(100) DEFAULT NULL, `page_id` int(11) DEFAULT NULL, `donation_frequency` varchar(100) DEFAULT NULL, `email_id` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_idx` (`user_id`), KEY `email_idx` (`email_id`), KEY `activity_idx` (`activity`) ) ENGINE=MyISAM AUTO_INCREMENT=11331976 DEFAULT CHARSET=latin1 And the explain: ++-+---+--**-+** -+--+-**+++---**--+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra | ++-+---+--**-+** -+--+-**+++---**--+ | 1 | SIMPLE | u | index | NULL | id_idx | 5 | NULL | 972064 | Using index | | 1 | SIMPLE | a | ref | user_idx,email_idx,activity_**idx | user_idx | 5 | getup.u.id | 20 | Using where | ++-+---+--**-+** -+--+-**+++---**--+ Cheers, Leonardo Borges www.leonardoborges.com On Fri, Jul 8, 2011 at 11:58 AM, Johnny Withers joh...@pixelated.netwrote: Can you post show create table for activity and explain output of the problem query? On Jul 7, 2011 8:51 PM, Leonardo Borges leonardoborges...@gmail.com wrote: Hello everyone, I have an increasingly popular web application running on top of mysql and due to its popularity, I'm running into performance issues. After carefully examining database indexes and tuning queries I was able to pin down the slowest part of the system. The app's got a user segmentation tool that allows you to filter users based on a range of criteria from which the slowest is: Select all users that did not receive the email of id 100 To answer this question we turn to the activities table, which is basically
Re: Schema for Website Comments
http://www.ferdychristant.com/blog//archive/DOMM-7QJPM7 On Sun, Jul 10, 2011 at 10:14 PM, Adarsh Sharma adarsh.sha...@orkash.comwrote: Dear all, Today I need to create a schema for my application website that allows user comments too. I think we have to maintain hierarchical data and it is very common as all sites are supporting this feature. Can somebody suggest me some guidelines to follow and some links too. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?** unsub=aim.prab...@gmail.comhttp://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
RE: Facebook Trapped In MySQL a 'Fate Worse Than Death'
Let this be a lesson to all of those designers who say That will never happen. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Low priority write
Client C - LOW PRIORITY inserts will always wait until absolutely no other processes are using a table. - Original Message - From: Jon Siebert jon.siebe...@gmail.com To: mysql@lists.mysql.com Sent: Saturday, 9 July, 2011 5:54:58 PM Subject: Low priority write Low Priority Lock Hello was wondering if anybody would have some input: This will be on the same table. Client (A) acquires a READ lock; immediately thereafter a large amount of clients wait for a read lock as well, and client(B) requests a LOW PRIORITY insert. Another surge of clients request read locks as client(C) requests a INSERT lock. The last of the read locks are finished selecting. Does client (B) or (C) acquire their lock first? Will it go by priority or in order which the requests were received. I could not find documentation in forums or MySQL reference regarding this. Thanks! -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [GENERAL] Schema for Website Comments
On Jul 12, 2011, at 7:54 PM, Prabhat Kumar wrote: Today I need to create a schema for my application website that allows user comments too. I think we have to maintain hierarchical data and it is very common as all sites are supporting this feature. Can somebody suggest me some guidelines to follow and some links too. I think you would like to go through following thread: http://archives.postgresql.org/pgsql-sql/2010-04/msg6.php Presentation: http://wiki.postgresql.org/images/9/91/Pguswest2008hnd.pdf Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blogs: http://vibhork.blogspot.com http://vibhorkumar.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
Re: Facebook Trapped In MySQL a 'Fate Worse Than Death'
On 7/12/2011 9:45 AM, Jerry Schwartz wrote: Let this be a lesson to all of those designers who say That will never happen. Let this be a lesson to all of those designers who say ''That will never happen' will never happen. :-) PB - Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Facebook Trapped In MySQL a 'Fate Worse Than Death'
Given the choice between doing right the first time, or having the second largest site on the internet, I'll take the latter, and deal with the problems of not doing it right the first time. -Hank On Tue, Jul 12, 2011 at 10:45 AM, Jerry Schwartz je...@gii.co.jp wrote: Let this be a lesson to all of those designers who say That will never happen.
MySQL creating empty index?
Hey guys, I'm working on some performance tuning tasks and realized that there was a missing index in one of my tables so sure enough I added it on my local database with the following sql statement: mysql create index id_idx on users(id); Query OK, 972064 rows affected (36.77 sec) Records: 972064 Duplicates: 0 Warnings: 0 This gave me the performance I wanted so I proceeded to create the same index in our staging database: mysql create index id_idx on users(id); Query OK, 0 rows affected (20.75 sec) Records: 0 Duplicates: 0 Warnings: 0 See the number of affected rows? It reports zero, even though it shows the index in the show create table result. It also doesn't speed up the query at all and it actually performs worse if I FORCE INDEX mysql to use this one. Do you guys have any idea as to why creating this index might not be working? Thanks, Leonardo Borges www.leonardoborges.com
Re: Facebook Trapped In MySQL a 'Fate Worse Than Death'
He is pushing his own products here, Looking at other implications of moving to a NoSQL system I would stay with an SQL system. NoSql has its place, but maybe not in such an environment. http://blogs.adobe.com/asset/2011/04/nosql-but-even-less-security.html Regards Michael Cole. On Wednesday 13 July 2011 7:33:43 am Hank wrote: Given the choice between doing right the first time, or having the second largest site on the internet, I'll take the latter, and deal with the problems of not doing it right the first time. -Hank On Tue, Jul 12, 2011 at 10:45 AM, Jerry Schwartz je...@gii.co.jp wrote: Let this be a lesson to all of those designers who say That will never happen. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
