RE: Performance MySQL with C-API
Hello, I don't know whether this is a solution for you or not, but I had to insert a lot of registers ( about 14.000.000 ) and in a first instance I tried to do it via INSERT; the response was not as fast as I needed (I don't currently have de data here). I changed the approach and, instead of INSERT I made an external file and then a LOAD DATA. The difference between both methods is great. Hope this will be useful. Best regards. -- De: Raf Geusens[SMTP:[EMAIL PROTECTED]] Enviado el: martes 8 de mayo de 2001 14:27 Para: [EMAIL PROTECTED] Asunto: Performance MySQL with C-API Hi, I've got a couple a questions concerning the speed of insert queries when using the C API from MySQL. I've written an application that receives data through a CORBA event channel and stores it in a MySQL database. The problem is that a lot of events are dropped. I think (I'm quite sure actually) that the MySQL database is the bottleneck because it's a slow consumer. Now if I use a benchmark to check how many inserts the database can handle each second is will that result be about the same as the number of inserts the same database can handle using the C API? I guess not. Using a MySQL server on a machine with a 1000 mhz AMD processor I can insert 200 strings + timestamps a second through the API. Does that sound too less or am I expecting too much? Do benchmarks or test results for the C API exist? If someone has more experience on this or knows a place where I can find out more, plz let me know. Greetings, Raf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance MySQL with C-API
200 connections table locks a second is good performance, isn't it? what you also can do is insert bunches of data, so instead of inserting each record seperately, you just collect them and insert them at once like: insert into mytable (field1, field2) values (1,2), (3,4), (5,6), (7,8), .. (n,m); your c program could dynamically build this string and just once a second or so you insert all values at once. gl rene On Wed, 9 May 2001 09:38:40 +0200 Manuel Barrientos [EMAIL PROTECTED] wrote: Hello, I don't know whether this is a solution for you or not, but I had to insert a lot of registers ( about 14.000.000 ) and in a first instance I tried to do it via INSERT; the response was not as fast as I needed (I don't currently have de data here). I changed the approach and, instead of INSERT I made an external file and then a LOAD DATA. The difference between both methods is great. Hope this will be useful. Best regards. -- De: Raf Geusens[SMTP:[EMAIL PROTECTED]] Enviado el: martes 8 de mayo de 2001 14:27 Para: [EMAIL PROTECTED] Asunto: Performance MySQL with C-API Hi, I've got a couple a questions concerning the speed of insert queries when using the C API from MySQL. I've written an application that receives data through a CORBA event channel and stores it in a MySQL database. The problem is that a lot of events are dropped. I think (I'm quite sure actually) that the MySQL database is the bottleneck because it's a slow consumer. Now if I use a benchmark to check how many inserts the database can handle each second is will that result be about the same as the number of inserts the same database can handle using the C API? I guess not. Using a MySQL server on a machine with a 1000 mhz AMD processor I can insert 200 strings + timestamps a second through the API. Does that sound too less or am I expecting too much? Do benchmarks or test results for the C API exist? If someone has more experience on this or knows a place where I can find out more, plz let me know. Greetings, Raf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance MySQL with C-API
insert 200 strings + timestamps a second through the API. Does that sound too less or am I expecting too much? Do benchmarks or test results for the C API exist? If someone has more experience on this or knows a place where I can find out more, plz let me know. I just ran this test on a win32-machine with 500MHz, 196 MB RAM and IDE-drive: - created table test with 3 fields: id (unsigned int, primary key, autoincrement, not null), string (varchar 128), ts (timestamp 14) - generated 10.000 random string-values (32 chars each) and made 1 insert-query out of them: insert into test (string) values (randomstring1), values (randomstring3), ... values (randomstring1) - executed this query with mysql-front (same as C-API) Result: less than one second execution time! On our sun solaris (450MHz, 256RAM) the same query needed 1,6 seconds (through php!). I think execution time depends very much on database-design. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Performance MySQL with C-API
Not only database design but system as well. Drive speed, drive fragmentation and I/O resources can play a large factor in database speed. Could you have a better disk environment on the Solaris machine versus the win32-machine? -Original Message- From: Ansgar Becker [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 08, 2001 9:29 AM To: [EMAIL PROTECTED] Subject: Re: Performance MySQL with C-API insert 200 strings + timestamps a second through the API. Does that sound too less or am I expecting too much? Do benchmarks or test results for the C API exist? If someone has more experience on this or knows a place where I can find out more, plz let me know. I just ran this test on a win32-machine with 500MHz, 196 MB RAM and IDE-drive: - created table test with 3 fields: id (unsigned int, primary key, autoincrement, not null), string (varchar 128), ts (timestamp 14) - generated 10.000 random string-values (32 chars each) and made 1 insert-query out of them: insert into test (string) values (randomstring1), values (randomstring3), ... values (randomstring1) - executed this query with mysql-front (same as C-API) Result: less than one second execution time! On our sun solaris (450MHz, 256RAM) the same query needed 1,6 seconds (through php!). I think execution time depends very much on database-design. Greetings, Ansgar Becker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance MySQL with C-API
Could you have a better disk environment on the Solaris machine versus the win32-machine? The Solaris machine was a little bit slower executing the query, but it runs as a production-webserver and there is much more load on it than on my win32-test-machine. query that inserted 1 string values, but wouldn't it make a great difference if you made 1 queries that each inserted only one value?? that's more my situation and I think that's a lot slower, but I do agree I tested it with 10.000 queries executing one after the other with MySQL-Front, too. Result: about 20 seconds. You must notice one thing: executing one query with a GUI like MySQL-Front means not only executing, but *many* other things too: desplaying the query in the history-window, finding out, if the query returns a result and so on. These side-mechanisms take a lot of time. If you make an application with the C-API, which executes exactly only the query and *nothing* else, you would get much better results. Greetings, Ansgar Becker [EMAIL PROTECTED] | http://www.anse.de AB7670-RIPE - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php