# 40 million tweets from 200k users

mysql> select count(username) from users where no_tweets  > 50;
+-----------------+
+-----------------+
|          282366 |
+-----------------+
1 row in set (5.41 sec)

mysql> select sum(no_tweets) from users where no_tweets > 50;
+----------------+
| sum(no_tweets) |
+----------------+
|       38569853 |
+----------------+
1 row in set (1.75 sec)


I have that many users, who have collectively tweeted that many tweets. My aim is to store them in a file and then find out what a user generally tweets about (for starters, my aim is to run vanilla LDA and see if it works well on short documents), but the problem is that I ran the python code like an hour back and it has still not finished even 2% of users. I have posted the python code below.

'''
The plan is : Find out all users  from the users database who have more than 50 tweets
Create a file for them in the directory passed as an argument with the same name as the username and write all the tweets in them
'''
def fifty(cursor):
''' Find all users and return , all of them having more than 50 tweets'''
cursor.execute("select username from users where no_tweets>50")
return cursor.fetchall()

def connect():
''' Cursor for mySQLdb'''
return conn.cursor()

def tweets(cursor,name):
''' All tweets by a given user'''
cursor.execute("select tweets from tweets where username='"+name+"'")
return cursor.fetchall()
import sys,os,MySQLdb
directory = sys.argv[1] #Directory to write the user files
cursor = connect()
rows = fifty(cursor) #Find all users who have more than 50 tweets

for i in rows:#For all users
data = open(os.path.join(directory,i[0]),'w') #Open a file same as their name
allTweets = tweets(cursor,i[0]) #Find all tweets by them
for j in allTweets:
data.write(j[0]+"\n") #Write them
data.close()


The problem is that the code is running too slow and at this rate, it will take more than a day for it to finish writing all files. So some way that would make it faster would be great. So yeah, that is the question.

• And your question is...? – ceejayoz Jan 10 '12 at 19:32
• Did not realize that I had not put the question in there . Apologies. Made the necessary changes – crazyaboutliv Jan 10 '12 at 19:34
• This really isn't a "just go and fix my code for me" place. If you just want someone to go do the work for you you should hire a dev. – ceejayoz Jan 10 '12 at 19:36
• If I had written no code at all and asked for help from someone to literally write the code , that would have made sense. I thought SO is a place where mistakes by people who are learning , like me, are corrected by those who can correct them. – crazyaboutliv Jan 10 '12 at 19:38
• My gut feeling is that performance is slow due to I/O. That can't be avoided, but perhaps you could look into the subprocess module. – Makoto Jan 10 '12 at 19:38

Yikes, you're running almost 3,000,000 individual queries. If you could do 4 a second (and you probably cannot) that is still a day!

select username, tweets from tweets order by username


Then of course you have to do some fancy footwork to switch from one file to the next when the user changes.

It should only take a few hours to run.

• I guess I will have to make necessary change so that I select only those users who have more than 50 tweets in the other table . – crazyaboutliv Jan 10 '12 at 19:41
• select t.username, tweets from tweets t, (select username from tweets group by username having count(*) > 50) e where e.username = t.username order by t.username -- but if you think most people have more than 50 (seems likely, since the average is 500), you might be better off just creating the files for everybody and deleting the small ones later. – Malvolio Jan 10 '12 at 19:47
• I thought of the creating all files earlier. But , only 3% of users have more than 50 tweets ~ ( 282366 / 6161529 ). So had to chuck that. Thanks ! Thanks again ! – crazyaboutliv Jan 10 '12 at 19:55
• And I actually have two tables. One is (username,tweets) and other is (username,no_tweets) . I stored them separately as username would be a primary key in the 2nd table. Plus would help for future reference – crazyaboutliv Jan 10 '12 at 20:05

On the database side of things, make sure you have an index on the username field of the tweets table, if you don't MySQL will perform a full disk read on each query, taking minutes at a time (in a good scenario).

If you're using a transactional database, using transactions (that is, closing them) is a good idea. Assuming python2.6 (or do an from __future__ import with_statement for 2.5), this is almost trivially easy.

import os
import operator
import sys
import MySQLdb

def Connect():

def FiftyPlus(conn):
with conn as cursor:
cursor.execute('SELECT username FROM users WHERE no_tweets > 50')
return map(operator.itemgetter(0), cursor.fetchall())

def Tweets(conn, name):
with conn as cursor:
cursor.execute('SELECT tweets FROM tweets WHERE username = %s', (name,))
return map(operator.itemgetter(0), cursor.fetchall())

with file(os.path.join(base_dir, username), 'w') as user_tweet_log:
for tweet in tweets:
user_tweet_log.write(tweet + '\n')

def main():
root_dir = sys.argv[1]
conn = Connect()
for user in FiftyPlus(conn):
tweets = Tweets(conn, user)
WriteUserTweets(root_dir, user, tweets)

• I have an index on username. Thanks ! I will need to dig up something on transactional databases :( Will do – crazyaboutliv Jan 10 '12 at 19:51
• Also, depending on your filesystem, you'll want to find a way to store the files in more than one directory. All flavors of ext (linux) and windows filesystems (and I've no reason to believe it'll be better on a Mac) start to give horrible performance once a directory accumulates over 50,000 files. – Elmer Jan 10 '12 at 19:59
• Sigh . Okay :) Will do that then. – crazyaboutliv Jan 10 '12 at 20:04
• I rate that question 2-1-1. Two wins: proper handling of the username (although not required at all in my, ahem, superior solution, it's still a good idea in the abstract), and breaking up the files into subdirectories; one miss: username was already indexed; and one mistake: using a transaction in an unchanging database only slows things down. – Malvolio Jan 11 '12 at 2:03

Am i missing something here or why don't you do it in one simple query and let MySQL write the file?

SELECT
t.tweets
INTO OUTFILE 'plus50tweets.txt'
FROM
tweets t
WHERE u.no_tweets > 50


That will still be rather slow without any indexing and keys; so use users.username as your primary key, and add an index on users.no_tweets. Add another (non-primary) key on tweets.username. If that doesn't help, set the foreign keys in tbl tweets to the users table.

Do not use a database for this. Use files.

A single file with one tweet per line showing User Name, Tweet Text and whatever other information you have.

You need to sort the file by user. Use the OS-level sort program, don't write your own.

Once the tweets are sorted by user, you simply read and count.

def group_by_user( iterable ):
tweet_iter= iter(iterable)
first= next(tweet_iter)
group= [ first ]
user= first.user
for tweet in tweet_iter:
if tweet.user != user:
yield user, group
user= tweet.user
group = []
group.append(tweet)
yield user, group

Tweet = namedtuple( 'Tweet', ['user','text',etc.] )

def make_named_tuples( raw_tweets ):
for tweet in raw_tweets:
yield Tweet( tweet.split('\t') ) # or whatever

with open( some_file ) as source:
for user, group in group_by_user( iterable )
if len(group) > 50:
# Open and write to a file.


This does not involve the huge overheads of a database.

As others have mentioned, this code is likely I/O bound. Let's bring down the time it takes to perform all the tweets queries:

## Improving read performance: parallel queries

Assuming the hardware running your MySQL database is capable, you can greatly improve the performance of the overall process by running multiple queries in parallel. I'm partial to gevent for this sort of thing, but the threading module from the Python standard lib should be more than sufficient. What we want to do is have multiple queries being performed at once, so that we are spending less time overall waiting for responses. To do this we take this code:

for i in rows:#For all users
data = open(os.path.join(directory,i[0]),'w') #Open a file same as their name
allTweets = tweets(cursor,i[0]) #Find all tweets by them
for j in allTweets:
data.write(j[0]+"\n") #Write them
data.close()


And instead do something like this:

def save_tweets(users):
''' For each username in the queue, retrieve all of their tweets and save them to disk '''
cursor = connect()
while True:
output.write(row[0] + '\n')

from queue import Queue
user_queue = Queue()
# Perform at most 10 queries at the same time.
max_connections = 10
for i in max_connections:
t.daemon = True
t.start()

for row in rows:#For all users
user_queue.put(row[0])
user_queue.join() # Wait for all tasks to be completed


Now, somebody is bound to point out that you should use the multiprocessing module instead of threading, and they may be right. If you find that the Python process for this script (not MySQL) is using 100% of the CPU time on one core, then you should consider using multiprocessing. Luckily it's a near drop-in replacement for the threading module, so the code will need very little change to adapt to it. Either way, this should get you into the position where you're pushing your database server to it's limits.

## Alternative, use the powers of SQL

Another approache that might be fun to try is using the GROUP_CONCAT function (found in this answer) to perform all of the work in a single query like so:

SELECT user, GROUP_CONCAT(tweet SEPARATOR '\n') as tweet_data, count(*) as tweet_count
FROM tweets WHERE tweet_count > 50
GROUP BY user;


However, given the amount of data you're dealing with, you'll probably just make your database server start swapping data in and out of memory and drive it's performance into the ground. (Unless your server happens to have +8GB of RAM, which according to Makotos estimates should be enough to hold the entire working set in memory)

You want better performance?

if you want really better performance you should try to use some of the compiled code languages (like C). Because interpreted language takes much more time.

For example, if you take a simple loop, you will notice the difference

int i = 0; int j = 0;

for(i = 0; i < 1500000; i++){
for(j = 0; j < 10000; j++){

}
}


It's like 50x faster with C comparing to Python.

• That won't help at all. The OP's post is I/O-bound, not CPU-bound. Changing the language won't change that. – Malvolio Jan 10 '12 at 19:36
• Yes it does help, compiled language are much better to perform these tasks that takes a lot of time and computer consuming. – Grego Jan 10 '12 at 19:38
• There's only so many bits you can slam onto hardware at a time. No programming language can change that. – Makoto Jan 10 '12 at 19:39
• @Makoto -- often, there are tricks you can do to reduce the amount of work to be done. The expensive part of a query is often making the query itself: creating it, parsing it, optimizing it. The OP had a quarter-million unnecessary queries he can get rid of. – Malvolio Jan 10 '12 at 19:41
• @Malvolio: Yes, the query is expensive, and that can be optimized. That still doesn't change the ~40 million tweets that are being written to disk on the basis of an individual's name. If we assume that each tweet is on average 80 characters long, then that's on the order of 6GB worth of data being written to disk. – Makoto Jan 10 '12 at 19:45