MySQL Performance: MySQL => Maximum number of insertions

Members

Help

Home

Home » Performance » MySQL » Maximum number of insertions

Show: Today's Messages :: Show Polls :: Message Navigator

Maximum number of insertions [message #2675]

Wed, 27 February 2008 20:16

pieroch
Messages: 2
Registered: February 2008

Junior Member

I need that mysql support an average of 200 insertions per second and that this value can be scalable, it is possible? ... that would be necessary hardware and configurations? ... thanks

Report message to a moderator

Re: Maximum number of insertions [message #2677 is a reply to message #2675 ]

Wed, 27 February 2008 23:45

scoundrel
Messages: 58
Registered: August 2006
Location: Toronto, ON, Canada

Member

Actually maximum throughput depends on hardware, configuration, database size and schema. But yes - it is really possible - 200 qps is really low threshold. We had some clients with thousands of inserts per second and they have pretty standard mysql. Everything depends on approach and configs Smile

Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting

Report message to a moderator

Re: Maximum number of insertions [message #2713 is a reply to message #2675 ]

Mon, 10 March 2008 05:57

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

As a small example I can say that I get up to about 8000 inserts/second with InnoDB table on my 3 year old laptop.
As long as it doesn't have to write to disk to much. Since a laptop disk is so very slow in situations like this.
Example figures:

50000 records in: 6.27902913093567 seconds
50000 records in: 6.30907201766968 seconds
50000 records in: 9.94429898262024 seconds
50000 records in: 7.40064191818237 seconds
50000 records in: 6.3791720867157 seconds
50000 records in: 7.81123185157776 seconds

(The dip i performance in the middle is due to other applications also running on my laptop.)

So buy a real server with a lot of RAM and fast CPU then there is no doubt that it can handle a lot more inserts than 200 per second even if your DB is big in size.

The question is now how heavy selects you will run against this machine at the same time?

Report message to a moderator

Re: Maximum number of insertions [message #2715 is a reply to message #2713 ]

Tue, 11 March 2008 10:37

pierochp
Messages: 1
Registered: March 2008

Junior Member

Accurate, the application is actually a mail server in a duplication enbebido existing aplicaion this nesecita first make 8 inserts a parsing of data that comes in the form of mail, then 6 consultations to validate the operation, and just send the data paraseada another apliacacion this event and register again in the bd with 8 more inserts, this completes the routine has to do as 200 times per second minimum, I tried it on a server with 4 cpu redhat 4, and does not give optimal results , so if I could reach configurations that would have to be done in the bd to make this the most optimal possible by the side of this code in C and optimized. Thanks

Report message to a moderator

Re: Maximum number of insertions [message #2716 is a reply to message #2715 ]

Tue, 11 March 2008 12:50

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

OK, but you will have to give us some information about your hardware, like RAM, disks etc.

And you have to tell us if you are you running MyISAM or InnoDB tables? The settings depend a lot on this.

You also has to tell us if this is a standalone SQL server or if the server is performing a lot of other stuff as well, depends on how much RAM we can allow the MySQL instance to allocate.

Report message to a moderator

Re: Maximum number of insertions [message #2717 is a reply to message #2716 ]

Tue, 11 March 2008 13:19

pieroch
Messages: 2
Registered: February 2008

Junior Member

The tables are MyISAM, my hardware info is:
Uname-a
Linux 2.6.9-42.ELsmp # 1 SMP Mon Jul 12 23:27:17 EDT 2006 i686 i686 i386 GNU / Linux
[Root @ src noparadigma02] # free
Total used free cached shared buffers
Mem: 4933844 3089312 1844532 0 76856 2372344
-- / + Buffers / cache: 640112 4293732
Swap: 4192956 0 4192956

This state in normal use. Thanks

Report message to a moderator

Re: Maximum number of insertions [message #2718 is a reply to message #2675 ]

Tue, 11 March 2008 13:57

sterin
Messages: 323
Registered: March 2007
Location: Sweden

Senior Member

OK, looks fine and dandy.

How big are the tables in rows and MB?

Do you have a lot of selects running against these tables also?
And more important is the application deleting records during normal working routines?

Output from:

SHOW STATUS LIKE 'Table_locks%';

[Updated on: Tue, 11 March 2008 13:58]

Report message to a moderator

Previous Topic:	Innodb crash recovery - how to tell when it has completed?
Next Topic:	Slow query - Using where; Using temporary; Using filesort

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 20 23:35:26 EST 2008

Total time taken to generate the page: 0.05876 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.