BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  MySQL 5.0 Beta Challenge Goto page 1, 2  Next          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message paladin Guest Posted: Thu Jun 16, 2005 6:09 pm    Post subject: MySQL 5.0 Beta Challenge The following news is now available at MySQL.com: -------------------------------------------------------------------------------- The MySQL 5.0 Beta Challenge To assist the MySQL 5.0 Quality Assurance process, we are looking for as many users as possible to test 5.0 in general, and specifically key features such as stored procedures, views, triggers. See this article for details: Evaluating MySQL 5.0 Key Features - Helping the QA Process. There will be cool MySQL goodies (T-shirts, mugs, books) for users who are the most helpful either directly with 5.0 testing, or indirectly by informing other users about 5.0. For instance: a.. The best or most (5.0 feature related) bug reports at bugs.mysql.com b.. The most helpful people on related MySQL forums at forums.mysql.com c.. The best MySQL 5.0 related articles (magazine or online) or dedicated web site. d.. The most informational/inspiring blog entries on MySQL 5.0 topics. To tell us about published articles and web sites or your blog feed URL, use the community feedback form. Rules: a.. All bugs reported, articles published, etc by July 30th (note: this date has no relation to the 5.0 release schedule.) b.. All winners announced at the sole discretion of MySQL AB. Arjen Lentz (Community Relations Manager) and Brian Aker (Director of Architecture) will be among the judges. Your contribution is highly appreciated! Go for it & good luck. Back to top Dmitry Arefiev [gs-soft.r Guest Posted: Sun Jun 19, 2005 1:44 pm    Post subject: Re: MySQL 5.0 Beta Challenge Me and AnyDAC participating there <g> Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access framework gs-soft AG - www.gs-soft.com SAPx - Delphi to SAP R/3 direct access Saphir - SAP R/3 metadata at your fingertips MetaBase - ERWIN model in Delphi applications Back to top paladin Guest Posted: Mon Jun 20, 2005 1:26 am    Post subject: Re: MySQL 5.0 Beta Challenge I would be interested in your experiences with mysql version 5.0 tia! paladin "Dmitry Arefiev [gs-soft.ru]" <darefiev (AT) gs-soft (DOT) ru> wrote Quote: Me and AnyDAC participating there Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access framework gs-soft AG - www.gs-soft.com SAPx - Delphi to SAP R/3 direct access Saphir - SAP R/3 metadata at your fingertips MetaBase - ERWIN model in Delphi applications Back to top Dmitry Arefiev [gs-soft.r Guest Posted: Mon Jun 20, 2005 9:17 pm    Post subject: Re: MySQL 5.0 Beta Challenge Hi Paladin ! Quote: I would be interested in your experiences with mysql version 5.0 I will say about MySQL client API. Few thoughts are: 1) Today the best technologie to fetch data from server to client as fast as it is possible is the usage of few records per network packet. V 5.0.6 allows to set the number of rows per packet (STMT_ATTR_PREFETCH_ROWS) but only for new "binary" protocol applications. Nice to have ... For other DBMS's it speedups fetching few times. 2) Another best technologie to send data to server from client is the ability to pack few sets of parameter values into one packet and ask the server to execute the specified SQL required number of times. I have no idea why, but the API remains the same here - does not allow that ... 3) The high precision numbers still are in the string format. Not a big issue, but good to have well documented BCD format. The application may get few percents of performance from that, when queries will have most columns as numbers. 4) The latest version is able to send BLOB data to the server in pieces. But the fetching of BLOB data is possible just in full. BTW, the server itself misses handling of BLOB data by reference ... 5) The most biggest MySQL client API issue (from my POV), is the avoiding of handles usage. So, the client must "know" the layout of API internal structures. Not a problem, when the API is not changing. But MySQL changes it too often ! And that is the main reason of why Borland dbExp driver is able to work just with one major MySQL client version. 6) The MySQL VIO (protocol transport) code is still not thread safe. Actually, connection establishment code. Also still connection clean-up code is releasing socket not correct. 7) The error info routine returns this info as unparsed string. It will be good to return some structure ... That is just few things on the top, i can say without deep diging. But MySQL constantly improves they server ! :) Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access framework gs-soft AG - www.gs-soft.com SAPx - Delphi to SAP R/3 direct access Saphir - SAP R/3 metadata at your fingertips MetaBase - ERWIN model in Delphi applications Back to top paladin Guest Posted: Mon Jun 20, 2005 9:40 pm    Post subject: Re: MySQL 5.0 Beta Challenge thanks dmitry. I hope you send your recommendations to mysql team as well. -pal "Dmitry Arefiev [gs-soft.ru]" <darefiev (AT) gs-soft (DOT) ru> wrote Quote: Hi Paladin ! I would be interested in your experiences with mysql version 5.0 I will say about MySQL client API. Few thoughts are: 1) Today the best technologie to fetch data from server to client as fast as it is possible is the usage of few records per network packet. V 5.0.6 allows to set the number of rows per packet (STMT_ATTR_PREFETCH_ROWS) but only for new "binary" protocol applications. Nice to have ... For other DBMS's it speedups fetching few times. 2) Another best technologie to send data to server from client is the ability to pack few sets of parameter values into one packet and ask the server to execute the specified SQL required number of times. I have no idea why, but the API remains the same here - does not allow that ... 3) The high precision numbers still are in the string format. Not a big issue, but good to have well documented BCD format. The application may get few percents of performance from that, when queries will have most columns as numbers. 4) The latest version is able to send BLOB data to the server in pieces. But the fetching of BLOB data is possible just in full. BTW, the server itself misses handling of BLOB data by reference ... 5) The most biggest MySQL client API issue (from my POV), is the avoiding of handles usage. So, the client must "know" the layout of API internal structures. Not a problem, when the API is not changing. But MySQL changes it too often ! And that is the main reason of why Borland dbExp driver is able to work just with one major MySQL client version. 6) The MySQL VIO (protocol transport) code is still not thread safe. Actually, connection establishment code. Also still connection clean-up code is releasing socket not correct. 7) The error info routine returns this info as unparsed string. It will be good to return some structure ... That is just few things on the top, i can say without deep diging. But MySQL constantly improves they server ! :) Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access framework gs-soft AG - www.gs-soft.com SAPx - Delphi to SAP R/3 direct access Saphir - SAP R/3 metadata at your fingertips MetaBase - ERWIN model in Delphi applications Back to top Cristian Nicola Guest Posted: Tue Jun 21, 2005 9:00 am    Post subject: Re: MySQL 5.0 Beta Challenge "Dmitry Arefiev [gs-soft.ru]" <darefiev (AT) gs-soft (DOT) ru> wrote Quote: Hi Paladin ! I would be interested in your experiences with mysql version 5.0 I will say about MySQL client API. Few thoughts are: 1) Today the best technologie to fetch data from server to client as fast as it is possible is the usage of few records per network packet. V 5.0.6 allows to set the number of rows per packet (STMT_ATTR_PREFETCH_ROWS) but only for new "binary" protocol applications. Nice to have ... For other DBMS's it speedups fetching few times. Actually i think you mix "network packets" and logical packets. A mysql logical packet is the equivalent of a single row, however it may translate in many network packets. Subject to the network, rows sizes and so on many logical packets could form a single network packet and many network packets could form a single logical packet (especially when a single row size is large). Also i think STMT_ATTR_PREFETCH_ROWS doesnt have anything to do with either logical packets nor network packets but rather to how many rows are send back to the client when a cursor is opened... Kind regards, Cristian Nicola Back to top Cristian Nicola Guest Posted: Tue Jun 21, 2005 9:04 am    Post subject: Re: MySQL 5.0 Beta Challenge A nice improvement would make in my opinion the capability to resume the cursor transmission to an arbitrary position eg: - client opens a cursor which has 10000 rows - client receives a network error after 4789 row - later on reconnection if the cursor has not changed and no time out occured, the client could ask the rows starting with 4790. All of these are currently "possible" with the query cache and the "limit" sql statement, however they are not implemented in the client level ... Kind regards, Cristian Nicola Back to top Dmitry Arefiev [gs-soft.r Guest Posted: Tue Jun 21, 2005 10:28 am    Post subject: Re: MySQL 5.0 Beta Challenge Hi Cristian ! Quote: Actually i think you mix "network packets" and logical packets. No, i am not. If to talk long & detailed, then logical packet is translated into network packet, etc and so on. But at end there are network roundtrips and network packets. Quote: could form a single logical packet (especially when a single row size is large). That is not a case to think about optimization. Also in case of "store result" - the things are well, because server is returning result set in big chanks and does not wait for additional commands / confirmations from client. Actually, it is a "client cursor" - fastest way to deliver data to client. Quote: Also i think STMT_ATTR_PREFETCH_ROWS doesnt have anything to do with either logical packets nor network packets but rather to how many rows are send back to the client when a cursor is opened... You are wrong. In case of "server cursor" each mysql_stmt_fetch call will send command to server to return next rows chunk. And server will return STMT_ATTR_PREFETCH_ROWS amount of rows to the client. If STMT_ATTR_PREFETCH_ROWS = 1 then to fetch one row, there will be **at least 2 network packets**: - client -> server. Command COM_FETCH. - server -> client. One row in a chank . Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access framework gs-soft AG - www.gs-soft.com SAPx - Delphi to SAP R/3 direct access Saphir - SAP R/3 metadata at your fingertips MetaBase - ERWIN model in Delphi applications Back to top Dmitry Arefiev [gs-soft.r Guest Posted: Tue Jun 21, 2005 10:37 am    Post subject: Re: MySQL 5.0 Beta Challenge Hi Cristian ! Quote: All of these are currently "possible" with the query cache and the "limit" sql statement, however they are not implemented in the client level ... I dont see valuable reason to implement that at MySQL API level. You can create your own "topside" handler of this situation, which will transparently for application resume fetching. And, i think, this handler will be able to handle more resume-fetch scenarious, then MySQL API. Also, dont you think, that overloading API will make it potentially less stable, fast and intuitive clear ... Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access framework gs-soft AG - www.gs-soft.com SAPx - Delphi to SAP R/3 direct access Saphir - SAP R/3 metadata at your fingertips MetaBase - ERWIN model in Delphi applications Back to top Cristian Nicola Guest Posted: Tue Jun 21, 2005 11:50 am    Post subject: Re: MySQL 5.0 Beta Challenge "Dmitry Arefiev [gs-soft.ru]" <darefiev (AT) da-soft (DOT) com> wrote Quote: If to talk long & detailed, then logical packet is translated into network packet, etc and so on. But at end there are network roundtrips and network packets. The socket that mysql is using are stream oriented. You write 100 bytes you read 100 bytes - you do not need to know/care how many packets on the network does this involve. The only things that could be improved there are - enabling naggle -which would slightly delay packets, however if my memory is right then this only would work on some of the operating systems mysql supports, but not all of them - when the rows sizes is very small to send more than a row in a go - this indeed could save some packets on the network, but it would mean extra buffering and extra logic on the server side. Quote: Also in case of "store result" - the things are well, because server is returning result set in big chanks and does not wait for additional commands / confirmations from client. Actually, it is a "client cursor" - fastest way to deliver data to client. There is NO difference on how server sends data if you store or use the result. The only difference is on the client, and the "used" result should NOT be used unless the processing of each row is very very short (otherwise you risk the tcp buffers to get filled up and in result to have extra packets over network). I think we are looking at the client server from different levels - i;m looking from low-level up and you seem to consider it based on API... Kind regards, Cristian Nicola Back to top Cristian Nicola Guest Posted: Tue Jun 21, 2005 12:00 pm    Post subject: Re: MySQL 5.0 Beta Challenge "Dmitry Arefiev [gs-soft.ru]" <darefiev (AT) da-soft (DOT) com> wrote Quote: If STMT_ATTR_PREFETCH_ROWS = 1 then to fetch one row, there will be **at least 2 network packets**: - client -> server. Command COM_FETCH. - server -> client. One row in a chank . I think (if my memory is right - and i will check later on tonight) the way mysql writes/generates packets is NOT affected by the value of STMT_ATTR_PREFETCH_ROWS. At a logical level i think mysql does so (pseudocode): count:= STMT_ATTR_PREFETCH_ROWS repeat write a row to socket decrement count until cound = 0 If my assumption is right then STMT_ATTR_PREFETCH_ROWS is not going to affect the number of logical packets (write a row to socket = 1 logical packet), but rather the speed at which they arrive ... Regards, Cristian Nicola Back to top Cristian Nicola Guest Posted: Tue Jun 21, 2005 12:06 pm    Post subject: Re: MySQL 5.0 Beta Challenge What i meant was a client side cursor which would have a corresponding server side cursor. The client could be able to use the client cursor *independent* of the connection ... at the moment once a connection is dropped even for a second then ALL the cursors are lost. What would be nice is the capability to keep both cursors in sync without actually having to re-send all the data, but as you've said i think this is a higher level thing.. Hope this makes it a bit clearer... Cristian Nicola Back to top Dmitry Arefiev [gs-soft.r Guest Posted: Tue Jun 21, 2005 12:25 pm    Post subject: Re: MySQL 5.0 Beta Challenge Hi ! Quote: The socket that mysql is using are stream oriented. You write 100 bytes you read 100 bytes - you do not need to know/care how many packets on the network does this involve. No, i need Than i will transfer less amount of data (less packets of less size) through network, than it will be faster. Also sending 100 bytes as request and then reading 100 bytes as answer, means, that before i will read data, data i was sending must be delivered to the server. So, at least 2 synchronized packets each 100 byte size. Quote: The only things that could be improved there are I am not going to improove something <g> I just sayd, that 5.0.6 allows to tune fetching of "server side" cursors. And "store result" is already very well optimized. At least, on my bechmarks MySQL FetchAll operation is fastest across Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA on the same data (structure, size). Quote: There is NO difference on how server sends data if you store or use the result. I was talking about "server side" cursors & new binary protocol. In 5.0.3 was implemented new command COM_FETCH, which allows to fetch data in new way. Which is sure **very** different from old way to send data to client. Quote: I think we are looking at the client server from different levels - i;m looking from low-level up and you seem to consider it based on API... I think, i am looking as low as top level. Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access framework gs-soft AG - www.gs-soft.com SAPx - Delphi to SAP R/3 direct access Saphir - SAP R/3 metadata at your fingertips MetaBase - ERWIN model in Delphi applications Back to top Dmitry Arefiev [gs-soft.r Guest Posted: Tue Jun 21, 2005 12:29 pm    Post subject: Re: MySQL 5.0 Beta Challenge Quote: count:= STMT_ATTR_PREFETCH_ROWS ................. until cound = 0 May be. I have not checked server side code. Quote: If my assumption is right then STMT_ATTR_PREFETCH_ROWS is not going to affect the number of logical packets (write a row to socket = 1 logical packet), but rather the speed at which they arrive ... So, amount of network packets depends on STMT_ATTR_PREFETCH_ROWS ? Regards, Dmitry -- Dmitry Arefiev - www.da-soft.com AnyDAC - Oracle, MySQL, MSSQL, MSAccess, IBM DB2, Sybase ASA, DbExpress, ODBC freeware data access framework gs-soft AG - www.gs-soft.com SAPx - Delphi to SAP R/3 direct access Saphir - SAP R/3 metadata at your fingertips MetaBase - ERWIN model in Delphi applications Back to top Cristian Nicola Guest Posted: Tue Jun 21, 2005 1:00 pm    Post subject: Re: MySQL 5.0 Beta Challenge "Dmitry Arefiev [gs-soft.ru]" <darefiev (AT) da-soft (DOT) com> wrote Quote: So, amount of network packets depends on STMT_ATTR_PREFETCH_ROWS ? I think it does and it doesnt As i;ve said it depends on too many things but the short version is so: - the client server protocol (independent of it is binary or the "old" protocol" sends a single row as a logical packet) - last row is identified by an data length of 254 and a length less than 8 (and this is how the client knows it has received the last row - the server does NOT send the number of rows upfront (which i think could be nice)). - when writing a logical packet there are at least 2 writes on the socket (write header, write data). Assuming no naggle (and full moon) this means a row chould result in at least 4 network packet. (2 data from server, 2 ack from client) - the at least is due that if a row is larger than the network frame it would result in more packets for each frame. Assuming naggle subject to the size of the rows an arbitrary number of rows can result in 2 network packet as 1 data packet and on ack. I would assume that under NO circumstances there would be more than few 100s rows per network packet (a row must have at minimum 2 bytes + header min 4 bytes, total 6 bytes - which would mean about ~200 rows/ network packet - if we only consider the ~ 1400 bytes frame). You have to bear in mind that naggle is not going to delay the data for a very long time so probably in the best of the best scenario you could squeeze some performance there by actually building the packets in server memory. Now this would add extra checks/logic in the server (hence loading the cpu) and it would increase the memory usage (to the size of the "cache" thing) and this is going to happend for each connected client ... Kind regards, Cristian Nicola Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Goto page 1, 2  Next Page 1 of 2   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.