Everyone, sometime, has their early morning post, well this is mine.
What I decided to talk about is MySQL and Java as a background service (While listening to “The Deep Dark Woods – Charlie’s (Is Coming Down)”). Hell of a combination.
MySQL (version any, mine is 5.0xx for production, 5.1 local)
Whenever you build a database from scratch, please, please (I’m begging on my knees), if you have no idea and no clue, contact someone and ask for advice. Why?
1. Whenever you leave that database to someone else, that someone will be fucked.
2. You will be fucked
3. You again will be fucked
You might ask, what is this double-fucking of your self? Well when deciding your column data types also including database engine (Innodb or MyISAM?), when having a small database up to 10 000 records on some table with varchar type for a column that holds a unix timestamp or something of the same kind, you might not see the difference in the speed. But what if those records multiply to a range from 10 000 to the 100 000 000 or more? Well this where the fucking begins. You might remember the golden times when every query would last at most 0.00016, but now those are over and a simple
SELECT COUNT(1) FROM some_table |
lasts at least 2sec – 3sec, and
SELECT id, some_column, some_other_column FROM some_table WHERE some_very_other_column > 2456789 |
will last 15 sec. You really don’t want to see this. Well I had to fix it.
What are the golden steps to the enlightening of our poor hearts and minds with proper knowledge to apply changes to the database for a decent speed up?
1. Convert all the column data types to what they should be
If a column holds only int(11) types, please make sure the column is of that type, If it holds a decimal(8,8) value, make it a such value. (This will give you a performance boost, so big that your ego will explode)
2. When querying the database with
SELECT id, some_column, some_other_column FROM some_table WHERE some_very_other_column > 2456789 |
you might think, what the hell should I speed up here? Well let me give you the golden and damn simple solution
SELECT some_table.id, some_table.some_column, some_table.some_other_column FROM some_table WHERE some_table.some_very_other_column > 2456789 |
This will get you your results from your grandma speed database for about 10%-20% faster. You ask why? Read the MySQL documentation it has the right answer for you
With only these 2 steps, you got yourself a 510%-520% database boost. If you read more from the MySQL documentation you might even get more speed and reliability from your database.
Java as a background service
You have now evolved, from the scratchy monkey, to this high skilled ninja, and are writing java applications that take hundreds of information per second. You are in your golden age. But hell the server is going down every 2-3 days, even if it has installed the almighty GNU/Linux some flashy speedy distro. You see the services crashing with the “too many open files in system”, you have no clue what that is, until you read something which happens rarely, and find out that you might as well increase the system limits to 10000 with ‘ulimit -n 10000′. And now you are the king of the hill or your chair at least.
4 days have passed and the server hasn’t gone down, nor it’s services are crashing. Damn that feels good, just as you are about to drink your glorious coffee, made from best coffee makers of the world (Nescaffe Classic), your services start to go down as if I was shooting them down with a AK47 from 1 meter distance.
There is one solution to this, usually there is this application not to name it (of course it is your java app) that opens some connections but doesn’t close them all properly after receiving/sending information. You think that all the connections are closed after a while, but what if I told you that only 80% of those connections are closed while 20% stay in CLOSE_WAIT (ask google what it is) state. They stay in that state, since the network (internet of course) where we chat, play games and watch porn, isn’t reliable at all. It looses packets, it even mutilates them to the unknown.
The simplest solution to this is: close your beautiful sockets, that’s all, really, I mean it. Of course some times you might still encounter problems, but this will save your ass from getting fired today, tomorrow you get the chance to screw something else.
After a bunch of dirty words and a shoot down with AK47, I end it here. Bye, see you in the next post (hopefully with a job :p).