While waiting for the database server to load the data, I decided to do a little tech talk and give you an inside glimpse of some of our systems.
To dig a bit into the history, the idea of shortlinking mafia wars urls came up in the the old times of gifting and gift-links.
That is when we came up with the idea of our own shortlinking database. Sure, it would be great to have control over the links, to use our own systems and not be faced with limit. Those few links should not be hard to manage, we thought. We were so wrong.
Indexes: The main database tables did not contain any indexes. How could that happen? Well, it looks like the Spockon.me service was the first Yourls implementation with that high load happening. So we updated the code and added a few indexes here and there and changed a few SQL-statements.
Duplicates: When inserting a link, the database was searched if it contained a similar link. Not needed for us, we turned that off.
Ready to go live again.
And a month later, crashed again.
In the next weeks we also tweaked performance on the apache and the database-configuration and came up with a pretty decent and good working configuration. We stuffed the server with more CPUs and more memory and increased hard drive space.
Of course the database was used for other stuff too. We added the golden toolbar stuff,
our support forum,
Especially the "feature" of InnoDB to use one file for all databases was getting a problem for us. Meanwhile we had a file of around 170GB sitting on the harddrive which would not shrink, even if we deleted data from the tables.
Now this was in december, and it took us a while to get the time and effort to do this complex operation without losing any data.
We tested it two times on a copy of the production machine, so we knew it would work and be fast.
Additionally we are planning to make a maintenance script that cleans the database from unused requests, like property begging older than a week or other stuff that expires.
- Remove all unused tables and databases from the system (necessary cleanup)
- Shutdown the apache at exactly 6pm CEST
- export all databases to a dumpfile
- drop all databases
- shutdown mysql
- delete the ibdata1 file
- start mysql
- load dump.
The last point should be ready in the next half hour, while I spent the time waiting with writing this article. I hope someone read it to the end, if you're interested in the stuff we do, feel free to ask us question :)
I want to thank everybody who likes our scripts, everybody who used the service, and of course all those that helps with their donations to keep our servers up and running! You rock!!!
Spock On!!
UPDATE:
Something went wrong and we lost a few links. We're working on recreating them...
