Thanks to the circumstances, where I was left with no option other than to complete the node script, I was able to learn a bit of node.
Initially for the node script we opted for SQLite, considering it is lite and it’ll serve the basic purpose to handle the read and writes we required, a good db performance. But as the no. of concurrent users increased, it totally blew up. My node script stopped responding as it was waiting for database all the time.
What I concluded: Sqlite uses file for storing database, and each time I was making a read or write it locks the whole db slowing down the whole db performance. Thanks to one of my teammate Aaron, he suggested to switch to MySql.
And it seemed to work much better then sqlite, script was running without being choked for multiple simultaneous users.
But the happiness wasn’t long term, because I’m a newbie š when it comes to database administration. As the database size increased it started slowing down again as I haven’t implemented table indexes, and I was relying only on my Primary Key for all SELECT queries.
As a result a single SELECT query would take upto 9s, 20s and so on. So I added indexes for all columns which were used in WHERE clause, but beware, more the number of indexes you use, more it’ll slow down inserts, as mysql updates the whole index after each query.
This kind of sorted out a whole lot of performance issues.
This is where I made a big mistake: Performance issue was resolved upto a big extent, but then I started facing trouble with some particular file names having special characters Ć, Ć as I was using a character set other then `utf8_unicode_ci`. I added the ALTER table query to the script and boom another problem solved. Happy Faces all around š
Later on I increased the buffer pool size after reading about it on http://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size
And the last issue, after my db size grew over 1Gb, whenever I restarted my node request handler script, it’d take atleast 20 minutes, yeah minutes not seconds before mysql would start responding and I had to think before pushing changes, as it’d result in server downtime for that long. And it was all just because I was too careless or a noob :), to leave the Alter table query in the script, yeah.
So everytime I restart the script the whole 2Gb table would be modified, taking half an hour or more, and it’ll keep the tables lock for all that period, resulting into non responding server. And removing those queries solved the whole slowing down on node server restart.
Right now, I’m bit relaxed, unless I encounter another mistake of my own š
About Mysqlite, I think if I have used indexes in sqlite, it would have been scalable enough.
If you are encountering any such issues, you can always check MySql slow query log or if you are using PHPMyAdmin, you can always check the current query being executed and if it is holding the lock over the table, blocking other queries or you can simply login to your mysql console and run the query SHOW FULL PROCESSLIST\G
For updated PMA:
If you have any other method to tune Mysql database for performance, drop it in comments, I’d love to hear it.
I’m already planning to implement Table compression and partitioning, after I read them thoroughly.