I have a table. Due to the nature of the website, in that table, if i have 400+ users or so, it will have in it inserted 100,000 new rows per hour.
If i have near 4000 then for 2 hours or so, i will have a minimum of 500,000 rows inserted. This means about 800-1000 inserts per second.
How do i prepare the table for such intense use? I am talking about quite the lot of inserts per second in the table and i don't want it to get locked or slow down.
How do i optimize it for such high usage?
Can I ask what it'll be used for?
A secret for now
switch it to innodb as that will do row locking instead of whole table locking. otherwise only one insert can happen at a time with MyISAM table locking.
(2011-05-22, 03:01 PM)pavemen Wrote: [ -> ]switch it to innodb as that will do row locking instead of whole table locking. otherwise only one insert can happen at a time with MyISAM table locking.
That's correct and will definitely help. However, taking into consideration that such an amount of rows will be created in two hours, I'm not sure how well it will resist without crashing. I'm not that experienced when it comes to servers which require a lot of resources but you might need to have more than one database and of course multiple servers to reduce resource usage.
(2011-05-22, 10:08 PM)Pirata Nervo Wrote: [ -> ] (2011-05-22, 03:01 PM)pavemen Wrote: [ -> ]switch it to innodb as that will do row locking instead of whole table locking. otherwise only one insert can happen at a time with MyISAM table locking.
That's correct and will definitely help. However, taking into consideration that such an amount of rows will be created in two hours, I'm not sure how well it will resist without crashing. I'm not that experienced when it comes to servers which require a lot of resources but you might need to have more than one database and of course multiple servers to reduce resource usage.
i agree, innodb was the simplest possible solution, but as you stated, the OP would need a much more robust solution. i can see massive lag on that site. 1000 write queries per second would require multiple servers, replication, etc. If its a one time deal then it may work. if its the regular usage, you really need to reconsider the script you are writing.
(2011-05-22, 12:13 PM)dikidera Wrote: [ -> ]A secret for now
*coughonlinegamecough*
I hope you have redundant servers.
(2011-05-23, 02:42 AM)Darth Stabro Wrote: [ -> ]*coughonlinegamecough*
>website
>db tables
>gaming
wat?
edit: nvm, disregard that (apparently can't delete own post now; wat)
Find a more intelligent way to do what you're trying to do? That's the best thing I can think of, 1k inserts per second is insane for only 4k users. If you're only updating info that's in the DB, perhaps you can combine several "writes" into one update?