MySQL Speed Getting Slower
Hi, My MySQL server's data is increasing for about 1 million new records per day. And it's now become slower when processing data. Is there any way to speed up? TIA. Regards, Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Speed Getting Slower
If you are using innodb. Then increase innodb_buffer_pool_size. If myisam then increse key_buffer_size. Please send status of mysql server and OS details On Wed, Sep 24, 2008 at 12:39 PM, sangprabv [EMAIL PROTECTED] wrote: Hi, My MySQL server's data is increasing for about 1 million new records per day. And it's now become slower when processing data. Is there any way to speed up? TIA. Regards, Willy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
MySQL speed
Hello, For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. More information: table messages is an MyISAM table of 48MB which has 67000 rows, a primary key on id, a key on touser and a key on isread with the following fields: id - bigint(20) autoincrement, fromuser - int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - tinyint(1). I experience this problem also with other tables and queries but as this is the most simple one, I show this one as an example. So: is it normal that the difference is so big, and that tables which are not so very big (I think 67000 rows, or 48MB is not that huge for a good database) are so hard to find rows into, keeping in mind that the key makes the query only walk trough 2500 rows? Or is this normal? That I would find very strange because I wrote several Oracle based server applications with huge queries much more complicated as the one I use here, which take up tot at most 0.1 seconds. Those Oracle tables have millions of records and the full database dump (not sql dump) is 18GB! And I really don't think that Oracle is that much smarter than MySQL. Can anyone tell me what's going on? Are there MySQL parameters that can improve things? Or is MySQL really that slow? I won't believe the last one... I tested these queries on a MySQL 3.3 and a MySQL 4.0 database, all with the same speed-result. Regards, Dirk. -- Schippers Dirk Zaakvoerder Frixx-iT http://www.frixx-it.com Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
Dirk, Hello, For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. More information: table messages is an MyISAM table of 48MB which has 67000 rows, a primary key on id, a key on touser and a key on isread with the following fields: id - bigint(20) autoincrement, fromuser - int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - tinyint(1). I experience this problem also with other tables and queries but as this is the most simple one, I show this one as an example. So: is it normal that the difference is so big, and that tables which are not so very big (I think 67000 rows, or 48MB is not that huge for a good database) are so hard to find rows into, keeping in mind that the key makes the query only walk trough 2500 rows? Or is this normal? Just some ideas for you. 1: I think you should use CHAR instead of VARCHAR. Might take more place, but using fixed length fields in general mean faster access time. 2: To walk through 2500 rows might take some time, as the hidden fields for each rows must be fetched (IMHO). 3: You should use query caching. That might speed up your query tremendously. 4: You can try a hack: instead of using a field do denote that a user is not hidden, you might want to consider using some other method, e.g. using a signed integer for userid, and storing inactive users with a negative userid. Then you could count on users where userid 0. Regards, - Csongor Fagyal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
In the last episode (Sep 14), Dirk Schippers said: For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. That's up to 2500 random disk seeks, and even the fastest SCSI disks do only 300 seeks/sec (ATA disks max at ~150). Best case is where all the records are in memory and it doesn't have to hit the disk at all, but depending on how many tables you have and your RAM, that may not always be true. Try creating a multicolumn index on (touser,hidden), which will let mysql process the query without doing any record fetches at all. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Bulk] Re: MySQL speed
You have not mentioned the use of a multiple column index, for that matter, no indexes at all. I would suggest a multiple column index in the form of hidden, touser. That index form should speed up your queries and solve the problem. Mike - Original Message - From: Fagyal Csongor [EMAIL PROTECTED] To: Dirk Schippers [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: September 14, 2004 12:24 PM Subject: [Bulk] Re: MySQL speed Dirk, Hello, For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. More information: table messages is an MyISAM table of 48MB which has 67000 rows, a primary key on id, a key on touser and a key on isread with the following fields: id - bigint(20) autoincrement, fromuser - int(10) unsigned, touser - int(10) unsigned, ts - datetime, message - text, isread - tinyint(1) unsigned, ipnumber - varchar(20), hidden - tinyint(1). I experience this problem also with other tables and queries but as this is the most simple one, I show this one as an example. So: is it normal that the difference is so big, and that tables which are not so very big (I think 67000 rows, or 48MB is not that huge for a good database) are so hard to find rows into, keeping in mind that the key makes the query only walk trough 2500 rows? Or is this normal? Just some ideas for you. 1: I think you should use CHAR instead of VARCHAR. Might take more place, but using fixed length fields in general mean faster access time. 2: To walk through 2500 rows might take some time, as the hidden fields for each rows must be fetched (IMHO). 3: You should use query caching. That might speed up your query tremendously. 4: You can try a hack: instead of using a field do denote that a user is not hidden, you might want to consider using some other method, e.g. using a signed integer for userid, and storing inactive users with a negative userid. Then you could count on users where userid 0. Regards, - Csongor Fagyal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL speed
Why not just add an index on touser+hidden. Problem solved. Donny -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 14, 2004 11:48 AM To: Dirk Schippers Cc: [EMAIL PROTECTED] Subject: Re: MySQL speed In the last episode (Sep 14), Dirk Schippers said: For several years I am hosting a popular website using PHP and MySQL. As the site is growing and evolving, speed is becoming more and more important. With my latest inventions on the website, I notice that the website is becoming slow and I want to find out what's causing this. And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) even if EXPLAIN says mysql only has to walk trough 2500 rows thanks to the key on touser. (Oh yeah, I did an ANALYSE on the table) If I think of making my own program walking trough a datafile with 2500 items, checking every item for the flag hidden I would think that should not take up to even 0.01 second! Of course MySQL is more complicated than this, but I think it still is a very big difference. That's up to 2500 random disk seeks, and even the fastest SCSI disks do only 300 seeks/sec (ATA disks max at ~150). Best case is where all the records are in memory and it doesn't have to hit the disk at all, but depending on how many tables you have and your RAM, that may not always be true. Try creating a multicolumn index on (touser,hidden), which will let mysql process the query without doing any record fetches at all. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
Can anyone tell me what's going on? Are there MySQL parameters that can improve things? To know if you need to tune something, you can send us the copy of SHOW STATUS; and SHOW VARIABLES; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
(Hi all, I'm a friend of Dirk and helping him trying to speed up everything) Fagyal Csongor wrote: Dirk Schippers wrote: And this is the question that makes me totally confused: How is it possible that the following query: SELECT COUNT(*) FROM messages WHERE touser = 20 AND hidden = 0 with a key on touser can take up to 1 second (I even had moments where it would take up to 18 seconds!!!) 1: I think you should use CHAR instead of VARCHAR. Might take more place, but using fixed length fields in general mean faster access time. 2: To walk through 2500 rows might take some time, as the hidden fields for each rows must be fetched (IMHO). 3: You should use query caching. That might speed up your query tremendously. We're using Debian stable, which means MySQL 3.23. I like to use the packages and versions that come with Debian, but if necessary we'll look into upgrading. 4: You can try a hack: instead of using a field do denote that a user is not hidden, you might want to consider using some other method, e.g. using a signed integer for userid, and storing inactive users with a negative userid. Then you could count on users where userid 0. That's not how it works: it's not the users that are hidden; only some of their messages are hidden. Anyway, I'm confused too... I have a copy of a week-old version of Dirk's database on my home machine. When I run the same query, it takes between 0.00 and 0.02 seconds, on a machine that is in more or less the same performance category as Dirk's server. There is a world of difference between that and the 1 seconds Dirk is gettig. So it seems there is something wrong somewhere. I checked CPU and memory usage on the server, but everything looks pretty normal. Now I'm guessing there's something wrong with the MySQL configuration, but I don't know enough about MySQL to know what it could be. Any pointers in the right direction would be greatly appreciated. -- Codito ergo sum Roel Schroeven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed
Tobias Asplund wrote: Can anyone tell me what's going on? Are there MySQL parameters that can improve things? To know if you need to tune something, you can send us the copy of SHOW STATUS; and SHOW VARIABLES; What is preferred in this mailing list: the whole texts in the mail, or as attachments? -- Codito ergo sum Roel Schroeven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL speed problem
Hi, I faced a speed access problem today with the MySQL. In the morning was working well and fast, but after lunch when I tried to access the MySQl using Apache+PHP it was terrible. For just a select, it took 2 a 3 minutes to process. I checked the log files from Apache and PHP and I could not find any problem. The unique thing that I found was the hard-drive space. It was 1.7 GB. I am using Mac OS 10.3. So I cleaned and I got 3.1 GB and after 1 hour the fast speed came back. Does MySQL use a lot the swap area? I am using Innodb. Where I can find a tutorial or a good documentation that can help to set up a good MySQL environment, because I will hold a lot of data in near future. Thanks. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL speed problem
Andre MATOS wrote: Hi, I faced a speed access problem today with the MySQL. In the morning was working well and fast, but after lunch when I tried to access the MySQl using Apache+PHP it was terrible. For just a select, it took 2 a 3 minutes to process. I checked the log files from Apache and PHP and I could not find any problem. The unique thing that I found was the hard-drive space. It was 1.7 GB. I am using Mac OS 10.3. So I cleaned and I got 3.1 GB and after 1 hour the fast speed came back. Does MySQL use a lot the swap area? I am using Innodb. Under certain circumstances it might. However, the problem you are describing seems to me more like a file system fragmentation issue - when the disk is crowded, newly created large files are likely to be fragmented. There could be a number of situations when MySQL will need to create a fairly large temporary file. Ideally you want to avoid them, and in most cases you can with careful programming, but very few applications actually do. When it happens, a crowded file system is likely to cause performance degradation. Where I can find a tutorial or a good documentation that can help to set up a good MySQL environment, because I will hold a lot of data in near future. I do not think one can write a comprehensive howto on the subject. In order to succeed at the task you will need to understand how MySQL works. With that in mind, to gain that understanding: * read the list archives * read MySQL manual * try to optimize your own queries * participate in the list discussions * if you are a book person, read books -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql speed test
hi! i have 2 machines. both of them running mysql daemon. i want to check the speed difference, and i am looking for a method how can i do this ? is there an official tool for this thing ? Vaso -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql speed concerns
On Wed, May 01, 2002 at 10:49:36AM -0400, Jason Yates wrote: Currently our MySQL server runs around 20-30 queries per second. The upper management decided they wanted to add about 4 times the customers in the next two or three weeks. I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? You'll be fine if your app is well designed and your tables are properly indexed. You should be able to easily pump out 500-1000 queries per second on that hardware. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 83 days, processed 2,169,815,560 queries (300/sec. avg) - 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: mysql speed concerns
.sorry ... RFC 793 - Original Message - From: Gelu [EMAIL PROTECTED] To: Michael Chang [EMAIL PROTECTED] Cc: Svensson, B.A.T. (HKG) [EMAIL PROTECTED]; Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 10:52 AM Subject: Re: mysql speed concerns TCP/IP is a protocol for data interchange, host to host, on the network (RFC731). Sockets ,indeed , are also used for IPC. _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Michael Chang [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Svensson, B.A.T. (HKG) [EMAIL PROTECTED]; Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:29 PM Subject: Re: mysql speed concerns Technically speaking, TCP/IP communication *is* a form of IPC. IPC isn't restricted to semaphores or message queues, etc. I think that's what he meant (i.e.: mysql client on one box communicating via TCP/IP to a MySQL server on another box). Michael On Wed, 1 May 2002, Gelu wrote: Hi, About at the same IPC(semaphore,share memory,message) i make references too.Are more ways to made a inter process communication mechanism. For example, in my applications i don't use semaphores and messages.This are system functions available from the Kernel. If you type ipcs you can see that mySQL don't use the system functions for inter process communication. My opinion is referencing at, if this CGI (about Shaun said early) retrieve huge data from RDBMS ,sure can create unbalanced processes. For this reason i said it's strongly recommended to setup MySQL on the other host. I have a bad experience with INFORMIX mounted on SCO running in a dual XEON machine.And i think SCO it's more stable than Linux. Of course who use mainframe don't must have any concern. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 7:32 PM Subject: RE: mysql speed concerns With IPC I mean Inter Process Communication - any process that wants to talk with something else than with it self _needs_ necessarily IPC. Particularly an RDBMS needs IPC - otherwise it is useless. Anyhow. one way one might get faster performance could be to (if possible with the OS) move the swap space from disk into RAM instead. Or even better for speed and response time don't use swap at all! - but then you must know how much RAM memory your system will consume - otherwise you might get into big trouble. It is not impossible to estimate this, but tricky. One way could be to limit number of connections/jobs on the machine, and then assume the worst case scenario from this. -Original Message- From: Gelu Sent: Wednesday, May 01, 2002 6:12 PM To: Svensson, B.A.T. (HKG); Shaun Bramley Cc: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED
Re: mysql speed concerns
TCP/IP is a protocol for data interchange, host to host, on the network (RFC731). Sockets ,indeed , are also used for IPC. _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Michael Chang [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Svensson, B.A.T. (HKG) [EMAIL PROTECTED]; Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:29 PM Subject: Re: mysql speed concerns Technically speaking, TCP/IP communication *is* a form of IPC. IPC isn't restricted to semaphores or message queues, etc. I think that's what he meant (i.e.: mysql client on one box communicating via TCP/IP to a MySQL server on another box). Michael On Wed, 1 May 2002, Gelu wrote: Hi, About at the same IPC(semaphore,share memory,message) i make references too.Are more ways to made a inter process communication mechanism. For example, in my applications i don't use semaphores and messages.This are system functions available from the Kernel. If you type ipcs you can see that mySQL don't use the system functions for inter process communication. My opinion is referencing at, if this CGI (about Shaun said early) retrieve huge data from RDBMS ,sure can create unbalanced processes. For this reason i said it's strongly recommended to setup MySQL on the other host. I have a bad experience with INFORMIX mounted on SCO running in a dual XEON machine.And i think SCO it's more stable than Linux. Of course who use mainframe don't must have any concern. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 7:32 PM Subject: RE: mysql speed concerns With IPC I mean Inter Process Communication - any process that wants to talk with something else than with it self _needs_ necessarily IPC. Particularly an RDBMS needs IPC - otherwise it is useless. Anyhow. one way one might get faster performance could be to (if possible with the OS) move the swap space from disk into RAM instead. Or even better for speed and response time don't use swap at all! - but then you must know how much RAM memory your system will consume - otherwise you might get into big trouble. It is not impossible to estimate this, but tricky. One way could be to limit number of connections/jobs on the machine, and then assume the worst case scenario from this. -Original Message- From: Gelu Sent: Wednesday, May 01, 2002 6:12 PM To: Svensson, B.A.T. (HKG); Shaun Bramley Cc: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:13 PM To: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information
Re: mysql speed concerns
Hi Anders, How ever, this is probaly not an interesstign discussion for the rest of the people who subscribes to the mysql list. I AGREE. But... i consider this place, offer a good opportunity to exchange ideas,knowledge and experiences about how we using or create MySQL application . In this way we can learn reciprocal: some from the others.Of course, maybe some answers can be considered wrong because it's possible to be applicable in other circumstances(unfortunately for the user/programmer who have problems) and not in what it's described.Are several reason for what is happened.But one of this is CONFUSION. To avoid any confusion or doubt , I feel is my duty to be more specific , for sake of the people who are subscribed in the mysql list: For this reason : IPC - InterProcess Communication mechanism in the Unix environment - described by the W. Richard Stevens(and not only) at pg.482 in book Advanced programming in the Unix environment.PIPE,MESSAGE QUEUES,SEMAPHORES AND SHARED MEMORY are CLASSICAL FORMS of IPC. STREAM PIPE,NAMED STREAM PIPE are ADVANCED FORMS of IPC. SOCKET is a type of file for network(or nonnetwork) communication between processes. ipcs - a little Unix program and provide information about IPC facilities. TCP/IP - it was,it is and always remain :Transmission Control Protocol (TCP) and is intended for use as a highly reliable host-to-host protocol between hosts in packet-switched computer communication networks.RFC 793 prepared for DARPA in 1981. So, it's a big difference between DATA COMMUNICATION and PROCESS COMMUNICATION. All this are not invented by me. I apologize for any inconvenience Sincerely, Gelu Gogancea _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Sent: Thursday, May 02, 2002 2:04 PM Subject: RE: mysql speed concerns As well as semaphores, shared memory and messages queues not necessarily involves IPC - on the other hand the protocoll family TCP/IP is always IPC. We are talking about two different issues, and maybe you missunderstodd what I did talk about in the first place. How ever, this is probaly not an interesstign discussion for the rest of the people who subscribes to the mysql list. Regards, file://Anders -Original Message- From: Gelu [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 9:52 AM To: Michael Chang Cc: Svensson, B.A.T. (HKG); Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns TCP/IP is a protocol for data interchange, host to host, on the network (RFC731). Sockets ,indeed , are also used for IPC. _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Michael Chang [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Svensson, B.A.T. (HKG) [EMAIL PROTECTED]; Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:29 PM Subject: Re: mysql speed concerns Technically speaking, TCP/IP communication *is* a form of IPC. IPC isn't restricted to semaphores or message queues, etc. I think that's what he meant (i.e.: mysql client on one box communicating via TCP/IP to a MySQL server on another box). Michael On Wed, 1 May 2002, Gelu wrote: Hi, About at the same IPC(semaphore,share memory,message) i make references too.Are more ways to made a inter process communication mechanism. For example, in my applications i don't use semaphores and messages.This are system functions available from the Kernel. If you type ipcs you can see that mySQL don't use the system functions for inter process communication. My opinion is referencing at, if this CGI (about Shaun said early) retrieve huge data from RDBMS ,sure can create unbalanced processes. For this reason i said it's strongly recommended to setup MySQL on the other host. I have a bad experience with INFORMIX mounted on SCO running in a dual XEON machine.And i think SCO it's more stable than Linux. Of course who use mainframe don't must have any concern. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 7:32 PM Subject: RE: mysql speed concerns With IPC I mean Inter Process Communication - any
mysql speed concerns
Currently our MySQL server runs around 20-30 queries per second. The upper management decided they wanted to add about 4 times the customers in the next two or three weeks. I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? Replication? I not sure about that since the box is about 60-70% select and the rest inserts and deletes. Won't I lose real-time data. I not familiar with any type of load balancing with MySQL? SQL Relay? I also thought of separating the Apache server and MySQL server. I'll take any ideas. I need them pretty bad. -Jason - 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: mysql speed concerns
Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information (and vice versa). Shaun - Original Message - From: Jason Yates [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:49 AM Subject: mysql speed concerns Currently our MySQL server runs around 20-30 queries per second. The upper management decided they wanted to add about 4 times the customers in the next two or three weeks. I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? Replication? I not sure about that since the box is about 60-70% select and the rest inserts and deletes. Won't I lose real-time data. I not familiar with any type of load balancing with MySQL? SQL Relay? I also thought of separating the Apache server and MySQL server. I'll take any ideas. I need them pretty bad. -Jason - 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: mysql speed concerns
[snip] I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? [/snip] Before I'd panic I would run some of the bench-marking available on MySQL; http://www.mysql.com/doc/M/y/MySQL_Benchmarks.html And look at the results. I have a customer service tool that runs over 100 queries per second from support personnel on a P3 with 512Mb RAM, Slackware Linux, Apache, PHP. There are millions of records per table (good indexing helps) with the top tables containing 40 million records. We are running a RAID (because of disk space concerns) and regularly run archiving scripts. Thus far we have not noticed a degredation in performance. (BUT, that is why I moved another large database set to another server, the one I had been having problems with the past couple of days. All is well now.) Once our data data requests starts to get really out of hand (which, according to Murphy's Laws, it will) we will be upgrading to much larger servers. But until that day Jay Blanchard - 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: mysql speed concerns
An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. //Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:13 PM To: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information (and vice versa). Shaun - Original Message - From: Jason Yates [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:49 AM Subject: mysql speed concerns Currently our MySQL server runs around 20-30 queries per second. The upper management decided they wanted to add about 4 times the customers in the next two or three weeks. I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? Replication? I not sure about that since the box is about 60-70% select and the rest inserts and deletes. Won't I lose real-time data. I not familiar with any type of load balancing with MySQL? SQL Relay? I also thought of separating the Apache server and MySQL server. I'll take any ideas. I need them pretty bad. -Jason - 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: mysql speed concerns
Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:13 PM To: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information (and vice versa). Shaun - Original Message - From: Jason Yates [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:49 AM Subject: mysql speed concerns Currently our MySQL server runs around 20-30 queries per second. The upper management decided they wanted to add about 4 times the customers in the next two or three weeks. I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? Replication? I not sure about that since the box is about 60-70% select and the rest inserts and deletes. Won't I lose real-time data. I not familiar with any type of load balancing with MySQL? SQL Relay? I also thought of separating the Apache server and MySQL server. I'll take any ideas. I need them pretty bad. -Jason - 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: mysql speed concerns
Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:13 PM To: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information (and vice versa). Shaun - Original Message - From: Jason Yates [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 10:49 AM Subject: mysql speed concerns Currently our MySQL server runs around 20-30 queries per second. The upper management decided they wanted to add about 4 times the customers in the next two or three weeks. I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? Replication? I not sure about that since the box is about 60-70% select and the rest inserts and deletes. Won't I lose real-time data. I not familiar with any type of load balancing with MySQL? SQL Relay? I also thought of separating the Apache server and MySQL server. I'll take any ideas. I need them pretty bad. -Jason - 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: mysql speed concerns
With IPC I mean Inter Process Communication - any process that wants to talk with something else than with it self _needs_ necessarily IPC. Particularly an RDBMS needs IPC - otherwise it is useless. Anyhow. one way one might get faster performance could be to (if possible with the OS) move the swap space from disk into RAM instead. Or even better for speed and response time don't use swap at all! - but then you must know how much RAM memory your system will consume - otherwise you might get into big trouble. It is not impossible to estimate this, but tricky. One way could be to limit number of connections/jobs on the machine, and then assume the worst case scenario from this. -Original Message- From: Gelu Sent: Wednesday, May 01, 2002 6:12 PM To: Svensson, B.A.T. (HKG); Shaun Bramley Cc: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:13 PM To: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information (and vice versa). Shaun - 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: mysql speed concerns
Hi, We have faced this kind of problem for our main web site. When a single server was not enough any more (2 years ago), we have separated the database from Apache server. Last year, we added 3 more Apache servers with load-balancing and upgraded the database server with a faster machine (2x1GHz Pentium III, 2Gb RAM, HW RAID1). With this configuration, we serve around 80 million pages/month (all pages are dynamic and make requests to the database) and MySQL is serving an average of 400 queries/s and up to 800 on peak hours. As we plan to add at least 3 more Apache frontends this year, we have started using database replication: each Apache frontend is also a mysql slave server and some almost static tables are replicated and SELECTs on these tables are run on the slaves. Of these 3 solutions (DB on dedicated server, Apache load-balancing, MySQL replication), setting up a dedicated DB server was the easiest to setup: you move the database to a new machine, change DB hostname in your applications and that's all! Replication is the hardest: we had to find out which tables where suitable for replication (some of our tables have more insert/update than select and we need to be sure that selects return latest data; we don't replicate them). Since you don't have too much time, I would suggest that you move your database to a dedicated server. Also, you don't say what language you use for your CGI; we use Perl and we got a big performance improvement (3 to 10x execution speed!) when we migrated from pure CGI to Apache mod_perl. Hope this helps -- Joseph Bueno NetClub/Trader.com Jason Yates wrote: Currently our MySQL server runs around 20-30 queries per second. The upper management decided they wanted to add about 4 times the customers in the next two or three weeks. I'm worried that MySQL on this particular box won't be able to handle the load of around 100-120 queries per second. Not to mention the CGI scripts are also getting run on the same box with apache. The system has 1gb of RAM, 1 Pentium III 700Mhz, and some ultrascsi HDs (no raid), running Red Hat 7.1 and Linux 2.4.6. What are my options here? Replication? I not sure about that since the box is about 60-70% select and the rest inserts and deletes. Won't I lose real-time data. I not familiar with any type of load balancing with MySQL? SQL Relay? I also thought of separating the Apache server and MySQL server. I'll take any ideas. I need them pretty bad. -Jason - 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: mysql speed concerns
Hi, About at the same IPC(semaphore,share memory,message) i make references too.Are more ways to made a inter process communication mechanism. For example, in my applications i don't use semaphores and messages.This are system functions available from the Kernel. If you type ipcs you can see that mySQL don't use the system functions for inter process communication. My opinion is referencing at, if this CGI (about Shaun said early) retrieve huge data from RDBMS ,sure can create unbalanced processes. For this reason i said it's strongly recommended to setup MySQL on the other host. I have a bad experience with INFORMIX mounted on SCO running in a dual XEON machine.And i think SCO it's more stable than Linux. Of course who use mainframe don't must have any concern. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 7:32 PM Subject: RE: mysql speed concerns With IPC I mean Inter Process Communication - any process that wants to talk with something else than with it self _needs_ necessarily IPC. Particularly an RDBMS needs IPC - otherwise it is useless. Anyhow. one way one might get faster performance could be to (if possible with the OS) move the swap space from disk into RAM instead. Or even better for speed and response time don't use swap at all! - but then you must know how much RAM memory your system will consume - otherwise you might get into big trouble. It is not impossible to estimate this, but tricky. One way could be to limit number of connections/jobs on the machine, and then assume the worst case scenario from this. -Original Message- From: Gelu Sent: Wednesday, May 01, 2002 6:12 PM To: Svensson, B.A.T. (HKG); Shaun Bramley Cc: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:13 PM To: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information (and vice versa). Shaun - 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: mysql speed concerns
Technically speaking, TCP/IP communication *is* a form of IPC. IPC isn't restricted to semaphores or message queues, etc. I think that's what he meant (i.e.: mysql client on one box communicating via TCP/IP to a MySQL server on another box). Michael On Wed, 1 May 2002, Gelu wrote: Hi, About at the same IPC(semaphore,share memory,message) i make references too.Are more ways to made a inter process communication mechanism. For example, in my applications i don't use semaphores and messages.This are system functions available from the Kernel. If you type ipcs you can see that mySQL don't use the system functions for inter process communication. My opinion is referencing at, if this CGI (about Shaun said early) retrieve huge data from RDBMS ,sure can create unbalanced processes. For this reason i said it's strongly recommended to setup MySQL on the other host. I have a bad experience with INFORMIX mounted on SCO running in a dual XEON machine.And i think SCO it's more stable than Linux. Of course who use mainframe don't must have any concern. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 7:32 PM Subject: RE: mysql speed concerns With IPC I mean Inter Process Communication - any process that wants to talk with something else than with it self _needs_ necessarily IPC. Particularly an RDBMS needs IPC - otherwise it is useless. Anyhow. one way one might get faster performance could be to (if possible with the OS) move the swap space from disk into RAM instead. Or even better for speed and response time don't use swap at all! - but then you must know how much RAM memory your system will consume - otherwise you might get into big trouble. It is not impossible to estimate this, but tricky. One way could be to limit number of connections/jobs on the machine, and then assume the worst case scenario from this. -Original Message- From: Gelu Sent: Wednesday, May 01, 2002 6:12 PM To: Svensson, B.A.T. (HKG); Shaun Bramley Cc: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hi, MySQL don't seems to use IPC.!?...Strange ..!?...And if Shaun will need real-time response from RDBMS,in my opinion, is strongly recomended to set up MySQL on the other host. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Svensson, B.A.T. (HKG) [EMAIL PROTECTED] To: Shaun Bramley [EMAIL PROTECTED] Cc: Jason Yates [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, May 01, 2002 6:20 PM Subject: RE: mysql speed concerns An webserver uses mostly CPU resources while a DB uses I/O, or? Separation between the webserver and the RDBMS also suggest increased delays with IPC. I think one needs to find a balance somewhere in between, and I guess the only way to tell is to actually measure the performance of the system to see what's need to be adjusted in order to get better performance. I would start out by collecting some stats from the RDBMS and the OS, lets say during a weeks time or so, and then just create dummy instances that perform the very same things and slowly increases the load in steps in order to see what happens with the system. file://Anders -Original Message- From: Shaun Bramley [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 01, 2002 5:13 PM To: Jason Yates; [EMAIL PROTECTED] Subject: Re: mysql speed concerns Hello, The first thing that I would do would be to separate the DB and apache. Set the DB up so that it is on a box of it's own. The system cannot serve pages if it is crunching through the db tables looking for information (and vice versa). Shaun - 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 -- /*-- * It might look like I'm doing nothing, but at the cellular level * I'm really quite busy. * ---anonymous * * * -BEGIN GEEK CODE BLOCK- * Version: 3.1 * GCS/P/H/L/O d- s
Re: Mysql speed :)
At 13:35 Uhr -0400 6.4.2001, Vivek Khera wrote: ... LIMIT '3',2 and resulting in a parse error. The fix was to call $sth-execute($start+0,$howmany+0) and then DBI did the right thing. A similar problem may occur if you use a text variable in a == comparison before passing it to execute. If you run perl with tainting check, DBD::mysql will interpret the text variable as number and not quote it, leading to either a parsing error or a security hole in your application. I have written about this on 2000/08/23 to the dbi-users and msql-mysql-modules lists. Tim Bunce suspected it to be a driver bug. I don't know if it has been solved. Christian. - 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: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
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: Mysql speed :)
Heikki Tuuri writes:
Hi!
I tried a test similar to Peter. There is a table with 3 integer
columns. There is a primary key on the first column and a non-unique
key on the second column. I wrote a Perl program which inserts rows
one at a time, and also selects rows one at a time.
I ran the tests on a Linux 2-CPU Xeon 450 MHz. The times below are wall
clock times.
The results were:
MyISAMInnobase
100 000 inserts40 s. 25 s.
100 000 selects on
primary key58 s. 57 s.
100 000 selects on
secondary key 95 s. 68 s.
It is unusual for a database that a select is slower than an insert. There is
probably slowness in the communication between the client and the server
in the select case. Another possible explanation is that query optimization
takes time in a select. Innobase can do some 100 000 searches per second
when it calculates a count(*) on a large join. Thus the slowness cannot
come directly from the access method. It must have something to
do with query parsing and initialization, or more probably, communication.
Also the Perl code seems to take almost as much CPU as the database server.
Someone could perhaps measure using the C++ API?
Regards,
Heikki
Hi!
The above should be faster with MySQL++, particularly if you use
store() method, like in custom1.cc anad custom3.cc examples from
MySQL++.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Mysql speed :)
Richard Ellerbrock writes:
Are the Mysql supplied statically linked binaries built using these optimised glibc
2.2 libraries, or are they just the stock standard 2.1.3 libraries?
--
Richard Ellerbrock
[EMAIL PROTECTED]
Hi!
Our binaries are based on our patched glibc and not on the standard
ones.
So far, 2.1.3 was used , but we are switching to 2.2.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Mysql speed :)
But does that really change anything on the database server side ?
In other words is MySQL doing anything with respect to using prepared
statements ?
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 3:01 AM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
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: Mysql speed :)
On Fri, Apr 06, 2001 at 06:51:39AM -0700, Martin Hubert wrote:
But does that really change anything on the database server side ?
In other words is MySQL doing anything with respect to using prepared
statements ?
No. (Sadly MySQL doesn't support prepared statements. Pity.)
But if you're measuring the elapsed time of the perl script to do
benchmarks, as here, then it'll give a more accurate picture by
removing irrelevant overheads.
Tim.
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 3:01 AM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
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: Mysql speed :)
How do you get it not to try an quote numbers? I realize this should be
obvious, but I can't find an exampl in the msql/mysql book.
On Fri, 6 Apr 2001, Tim Bunce wrote:
Date: Fri, 6 Apr 2001 11:00:57 +0100
From: Tim Bunce [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
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
Sincerely,
William Mussatto, Senior Systems Engineer
CyberStrategies, Inc
ph. 909-920-9154 ext. 27
-
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: Mysql speed :)
On Fri, Apr 06, 2001 at 08:41:32AM -0700, William R. Mussatto wrote:
How do you get it not to try an quote numbers? I realize this should be
obvious, but I can't find an exampl in the msql/mysql book.
It's magic. Or specifically it relies on Perl's internal magic.
Plus, of course, it wouldn't actually matter if it did in this case
since mysqld will happily convert the strings to numbers anyway.
Tim.
On Fri, 6 Apr 2001, Tim Bunce wrote:
Date: Fri, 6 Apr 2001 11:00:57 +0100
From: Tim Bunce [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED], [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
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
Sincerely,
William Mussatto, Senior Systems Engineer
CyberStrategies, Inc
ph. 909-920-9154 ext. 27
-
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: Mysql speed :)
On Fri, Apr 06, 2001 at 10:16:46AM -0400, Johnson, Gregert wrote:
What would really help would be to use multiple row inserts, i.e.
INSERT INTO speed1 VALUES (a,b,c),(d,e,f),(g,h,I),...
So, prepare a series of inserts, each with a few hundred (or even thousand) row
value sets.
In that case the benefit probably swings the other way and I wouldn't
bother with placeholders when generating insert statements for very
large numbers of rows. But it's obviously not portable and it's hard to
know which rows have failed if any do.
Tim.
--Greg Johnson
-Original Message-
From: Martin Hubert [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 9:52 AM
To: Tim Bunce; Heikki Tuuri
Cc: [EMAIL PROTECTED]; FileCopyMartin
Subject:RE: Mysql speed :)
But does that really change anything on the database server side ?
In other words is MySQL doing anything with respect to using prepared
statements ?
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 3:01 AM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do("insert into speed1 values ($j, $j, $j)");
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
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
-
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: Mysql speed :)
On Fri, 6 Apr 2001, Tim Bunce wrote: Date: Fri, 6 Apr 2001 17:25:27 +0100 From: Tim Bunce [EMAIL PROTECTED] To: "William R. Mussatto" [EMAIL PROTECTED] Cc: Tim Bunce [EMAIL PROTECTED], Heikki Tuuri [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Mysql speed :) On Fri, Apr 06, 2001 at 08:41:32AM -0700, William R. Mussatto wrote: How do you get it not to try an quote numbers? I realize this should be obvious, but I can't find an exampl in the msql/mysql book. It's magic. Or specifically it relies on Perl's internal magic. Plus, of course, it wouldn't actually matter if it did in this case since mysqld will happily convert the strings to numbers anyway. Tim. Ah. I was wondering about that. I was able to pass numeric/decimal types as strings but I thought that was becaues they were stored that way internally (vs. float). Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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: Mysql speed :)
What would really help would be to use multiple row inserts, i.e.
INSERT INTO speed1 VALUES (a,b,c),(d,e,f),(g,h,I),...
So, prepare a series of inserts, each with a few hundred (or even thousand) row value
sets.
--Greg Johnson
-Original Message-
From: Martin Hubert [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 9:52 AM
To: Tim Bunce; Heikki Tuuri
Cc: [EMAIL PROTECTED]; FileCopyMartin
Subject:RE: Mysql speed :)
But does that really change anything on the database server side ?
In other words is MySQL doing anything with respect to using prepared
statements ?
-Original Message-
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Friday, April 06, 2001 3:01 AM
To: Heikki Tuuri
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Mysql speed :)
On Thu, Apr 05, 2001 at 07:50:24PM +0300, Heikki Tuuri wrote:
for ($j = 0; $j 10; $j = $j + 1) {
$dbh-do(insert into speed1 values ($j, $j, $j));
}
That would run faster if you do a prepare with placeholders outside
the loop and then just use $sth-execute($j, $j, $j) inside.
That would save you the DBI statement handle creation/destruction
overhead that you're paying for each insert when using do().
Tim.
-
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
-
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
Mysql speed :)
Hello mysql, I've resenty tried to becnhmark mysql in really simple case. I need to select indexes really fast so I did "select * from pages where hash=11" there was a key on hash and the query returned only one row. The query was constant and server ad no other load. I've run this on 2CPU PIII-700 under 2.2.18aa2 and on one CPU PIII700 on 2.4.2 The results are about 3300 req/sec and the second one about 1800 req/sec. Unix domain sockets was used. I found the rather huge number of context swithches in first case: 2 0 0 3296 265244 104848 111472 0 0 0 0 103 27570 41 19 40 2 0 0 3296 265244 104848 111472 0 0 0 0 103 27690 38 21 41 0 0 0 3296 265344 104848 111472 0 0 0 0 104 26405 37 18 46 the second one looked better, but not much if you'll look at about 2 times speed difference: 1 0 0 24 58656 1480 298180 0 0 0 0 105 7946 84 16 0 1 0 0 24 58656 1480 298180 0 0 0 0 106 7942 88 12 0 2 0 0 24 58656 1480 298180 0 0 0 4 110 7968 82 18 0 1 0 0 24 58656 1480 298180 0 0 0 0 105 7966 81 19 0 1 0 0 24 58656 1480 298180 0 0 0 0 105 7965 81 19 0 1 0 0 24 58656 1480 298180 0 0 0 0 105 7948 83 17 0 1 0 0 24 58656 1480 298180 0 0 0 0 105 7964 85 15 0 I tried to run dumb query "select 10" and got about 7000 queries on second machine - which is 4 times faster then query which touches the table. Has anyone any ideas about if this speed is peek - I have heard people having 1 of inserts/sec into table with no indexes with mysql and so on so I was a bit surprised about this low perfomance, which is more likely to be not because of connection or pharsing speed as "select 10" works fast, and just the I/O needed should not take so much then everything should be in memory. The another story is - I've tried few time ago read speed of reiserfs - I created 100 of files, each ten bytes in size and I was able to read this file set in speed of 25000/sec - I mean open/read/close - so select speed of about 2K queries per second looks quite surprising, then most of this time is not because of communication but because of really accessing the table. -- Best regards, Peter mailto:[EMAIL PROTECTED] - 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: Mysql speed :)
Peter Zaitsev writes:
Hello mysql,
I've resenty tried to becnhmark mysql in really simple case.
I need to select indexes really fast so I did "select * from pages
where hash=11" there was a key on hash and the query returned only
one row. The query was constant and server ad no other load.
I've run this on 2CPU PIII-700 under 2.2.18aa2 and on one CPU
PIII700 on 2.4.2 The results are about 3300 req/sec and the second
one about 1800 req/sec. Unix domain sockets was used.
I found the rather huge number of context swithches in first case:
2 0 0 3296 265244 104848 111472 0 0 0 0 103 27570 41 19 40
2 0 0 3296 265244 104848 111472 0 0 0 0 103 27690 38 21 41
0 0 0 3296 265344 104848 111472 0 0 0 0 104 26405 37 18 46
the second one looked better, but not much if you'll look at about 2
times speed difference:
1 0 0 24 58656 1480 298180 0 0 0 0 105 7946 84 16 0
1 0 0 24 58656 1480 298180 0 0 0 0 106 7942 88 12 0
2 0 0 24 58656 1480 298180 0 0 0 4 110 7968 82 18 0
1 0 0 24 58656 1480 298180 0 0 0 0 105 7966 81 19 0
1 0 0 24 58656 1480 298180 0 0 0 0 105 7965 81 19 0
1 0 0 24 58656 1480 298180 0 0 0 0 105 7948 83 17 0
1 0 0 24 58656 1480 298180 0 0 0 0 105 7964 85 15 0
I tried to run dumb query "select 10" and got about 7000 queries on
second machine - which is 4 times faster then query which touches the
table.
Has anyone any ideas about if this speed is peek - I have heard
people having 1 of inserts/sec into table with no indexes with
mysql and so on so I was a bit surprised about this low perfomance,
which is more likely to be not because of connection or pharsing
speed as "select 10" works fast, and just the I/O needed should not
take so much then everything should be in memory.
The another story is - I've tried few time ago read speed of reiserfs
- I created 100 of files, each ten bytes in size and I was able
to read this file set in speed of 25000/sec - I mean open/read/close
- so select speed of about 2K queries per second looks quite
surprising, then most of this time is not because of communication
but because of really accessing the table.
--
Best regards,
Peter mailto:[EMAIL PROTECTED]
Hi!
In order to increase speed you shuuld apply our glibc 2.2 patches and
rebuild glibc and mysql.
Our Sasha Pachev even has some new additions to glibc 2.2, which he
will made available too..
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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: Mysql speed :)
Are the Mysql supplied statically linked binaries built using these optimised glibc
2.2 libraries, or are they just the stock standard 2.1.3 libraries?
--
Richard Ellerbrock
[EMAIL PROTECTED]
Sinisa Milivojevic [EMAIL PROTECTED] 2001/04/05 05:06:45
Peter Zaitsev writes:
Hello mysql,
I've resenty tried to becnhmark mysql in really simple case.
I need to select indexes really fast so I did "select * from pages
where hash=11" there was a key on hash and the query returned only
one row. The query was constant and server ad no other load.
I've run this on 2CPU PIII-700 under 2.2.18aa2 and on one CPU
PIII700 on 2.4.2 The results are about 3300 req/sec and the second
one about 1800 req/sec. Unix domain sockets was used.
I found the rather huge number of context swithches in first case:
2 0 0 3296 265244 104848 111472 0 0 0 0 103 27570 41 19 40
2 0 0 3296 265244 104848 111472 0 0 0 0 103 27690 38 21 41
0 0 0 3296 265344 104848 111472 0 0 0 0 104 26405 37 18 46
the second one looked better, but not much if you'll look at about 2
times speed difference:
1 0 0 24 58656 1480 298180 0 0 0 0 105 7946 84 16 0
1 0 0 24 58656 1480 298180 0 0 0 0 106 7942 88 12 0
2 0 0 24 58656 1480 298180 0 0 0 4 110 7968 82 18 0
1 0 0 24 58656 1480 298180 0 0 0 0 105 7966 81 19 0
1 0 0 24 58656 1480 298180 0 0 0 0 105 7965 81 19 0
1 0 0 24 58656 1480 298180 0 0 0 0 105 7948 83 17 0
1 0 0 24 58656 1480 298180 0 0 0 0 105 7964 85 15 0
I tried to run dumb query "select 10" and got about 7000 queries on
second machine - which is 4 times faster then query which touches the
table.
Has anyone any ideas about if this speed is peek - I have heard
people having 1 of inserts/sec into table with no indexes with
mysql and so on so I was a bit surprised about this low perfomance,
which is more likely to be not because of connection or pharsing
speed as "select 10" works fast, and just the I/O needed should not
take so much then everything should be in memory.
The another story is - I've tried few time ago read speed of reiserfs
- I created 100 of files, each ten bytes in size and I was able
to read this file set in speed of 25000/sec - I mean open/read/close
- so select speed of about 2K queries per second looks quite
surprising, then most of this time is not because of communication
but because of really accessing the table.
--
Best regards,
Peter mailto:[EMAIL PROTECTED]
Hi!
In order to increase speed you shuuld apply our glibc 2.2 patches and
rebuild glibc and mysql.
Our Sasha Pachev even has some new additions to glibc 2.2, which he
will made available too..
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
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
