Re: MySQL Performance Question
One reason you might be seeing a higher number of writes than reads is if MySQL is able to answer queries from the data it has cached in RAM, rather than having to read off disk. I would second Atle's opinion that this setup should be entirely possible with a single database daemon, especially the way you have it segmented into so many tables across so many databases - that will help reduce contention for locks among your many clients. A big reason I would vote for one (or as few as possible anyway) database daemon is sheer simplicity. Anytime you can make system design simpler, it will make for improved reliability, easier maintenance, easier upgrades, etc. It seems you've really done the numbers on planning your data size and growth - good show. You don't talk much about your hardware, except to note RAID 1. That will obviously be an important piece of the puzzle for serving so many clients and so much data. You might consider upgrading to a multi-disk RAID 1+0 array to improve speed, for example. With so many potential client connections to MySQL, you might also take a good hard look at multiprocessor machines if you haven't already. MySQL's site has a good section on optimization: http://dev.mysql.com/doc/refman/5.0/en/optimization.html Another great resource is Jeremy Zawodny's High Performance MySQL from O'Reilly. HTH, Dan Robinson, Eric wrote: All customer sites will use the same application, but each will have its own set of 3 databases. In believe the nature of the application confines users to brief, bursty selects and updates except possibly when they run reports. I have not specifically analyzed reporting, but I ran a 2-hour sampling today of a client site with 25 users during a period of typical workload. Here's the overall disk statistics: % Read Time:5.26 % Write Time: 5.00 Avg Bytes/Read: 2918 Avg Bytes/Write:6563 Avg Read Queue: .05 Avg Write Queue:.013 Avg Disk Secs/Read: .013 Avg Disk Secs/Write:.004 Avg Read Bytes/Sec: 15151 Avg Write Bytes/Sec:66904 Avg Disk Reads/Sec: 3 Avg Disk Writes/Sec:9 The numbers are very interesting. The system writes to disk 3 times more often than it reads, and the writes are more than double the size. Bytes written per second is 4 times higher than bytes read. Yet, on average, reads take longer than writes and they tend to stack up in the queue a little more, which could explain why % Read Time is slightly higher. This is a RAID 1 array. System has plenty of RAM and was not swapping. All in all, the application appears write-heavy, but I don't think anyone can hog all the disk I/O. So, your opinion is that one instance of MySQL with a lot of databases is just as efficient as multiple MySQL instances? (Note: I WILL have to run separate instances in some cases because some customers are using slightly different versions of the application.) --Eric -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Saturday, June 17, 2006 12:14 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken
RE: MySQL Performance Question
Dan, thanks for your comments. I think we agree on how things should be configured. I'll keep the number of daemons to a minimum. The sample data I reported earlier is from an existing production system. The new system that we are deploying will be in a 2-node HA Linux configuration. Each node will be: 2 x Dual-core Xeon 2.8GHz (4 processor cores, total). 8GB RAM RAID 10 array (300GB (usable) I prefer RAID 10 to RAID 0+1 because the former can survive the loss of 2 drives and read/write performance is about the same as RAID 0+1. Thanks again for your input. --Eric Robinson -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Monday, June 19, 2006 6:35 AM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question One reason you might be seeing a higher number of writes than reads is if MySQL is able to answer queries from the data it has cached in RAM, rather than having to read off disk. I would second Atle's opinion that this setup should be entirely possible with a single database daemon, especially the way you have it segmented into so many tables across so many databases - that will help reduce contention for locks among your many clients. A big reason I would vote for one (or as few as possible anyway) database daemon is sheer simplicity. Anytime you can make system design simpler, it will make for improved reliability, easier maintenance, easier upgrades, etc. It seems you've really done the numbers on planning your data size and growth - good show. You don't talk much about your hardware, except to note RAID 1. That will obviously be an important piece of the puzzle for serving so many clients and so much data. You might consider upgrading to a multi-disk RAID 1+0 array to improve speed, for example. With so many potential client connections to MySQL, you might also take a good hard look at multiprocessor machines if you haven't already. MySQL's site has a good section on optimization: http://dev.mysql.com/doc/refman/5.0/en/optimization.html Another great resource is Jeremy Zawodny's High Performance MySQL from O'Reilly. HTH, Dan Robinson, Eric wrote: All customer sites will use the same application, but each will have its own set of 3 databases. In believe the nature of the application confines users to brief, bursty selects and updates except possibly when they run reports. I have not specifically analyzed reporting, but I ran a 2-hour sampling today of a client site with 25 users during a period of typical workload. Here's the overall disk statistics: % Read Time: 5.26 % Write Time: 5.00 Avg Bytes/Read: 2918 Avg Bytes/Write: 6563 Avg Read Queue: .05 Avg Write Queue: .013 Avg Disk Secs/Read: .013 Avg Disk Secs/Write: .004 Avg Read Bytes/Sec: 15151 Avg Write Bytes/Sec: 66904 Avg Disk Reads/Sec: 3 Avg Disk Writes/Sec: 9 The numbers are very interesting. The system writes to disk 3 times more often than it reads, and the writes are more than double the size. Bytes written per second is 4 times higher than bytes read. Yet, on average, reads take longer than writes and they tend to stack up in the queue a little more, which could explain why % Read Time is slightly higher. This is a RAID 1 array. System has plenty of RAM and was not swapping. All in all, the application appears write-heavy, but I don't think anyone can hog all the disk I/O. So, your opinion is that one instance of MySQL with a lot of databases is just as efficient as multiple MySQL instances? (Note: I WILL have to run separate instances in some cases because some customers are using slightly different versions of the application.) --Eric -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Saturday, June 17, 2006 12:14 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB
Re: MySQL Performance Question
So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Performance Question
All customer sites will use the same application, but each will have its own set of 3 databases. In believe the nature of the application confines users to brief, bursty selects and updates except possibly when they run reports. I have not specifically analyzed reporting, but I ran a 2-hour sampling today of a client site with 25 users during a period of typical workload. Here's the overall disk statistics: % Read Time:5.26 % Write Time: 5.00 Avg Bytes/Read: 2918 Avg Bytes/Write:6563 Avg Read Queue: .05 Avg Write Queue:.013 Avg Disk Secs/Read: .013 Avg Disk Secs/Write:.004 Avg Read Bytes/Sec: 15151 Avg Write Bytes/Sec:66904 Avg Disk Reads/Sec: 3 Avg Disk Writes/Sec:9 The numbers are very interesting. The system writes to disk 3 times more often than it reads, and the writes are more than double the size. Bytes written per second is 4 times higher than bytes read. Yet, on average, reads take longer than writes and they tend to stack up in the queue a little more, which could explain why % Read Time is slightly higher. This is a RAID 1 array. System has plenty of RAM and was not swapping. All in all, the application appears write-heavy, but I don't think anyone can hog all the disk I/O. So, your opinion is that one instance of MySQL with a lot of databases is just as efficient as multiple MySQL instances? (Note: I WILL have to run separate instances in some cases because some customers are using slightly different versions of the application.) --Eric -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Saturday, June 17, 2006 12:14 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Performance Question
Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
42.54 12:00:01 HK all 57.51 0.00 28.40 14.09 12:10:00 HK all 50.69 0.00 26.55 22.76 Average: all 15.37 0.00 7.33 77.30 - Original Message - From: David Griffiths [EMAIL PROTECTED] To: Rainer Sip [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 3:11 AM Subject: Re: Mysql Performance Question You need to figure out what's slowing down your application. It could be expensive queries which in turn could be caused by missing indexes. It could be that the machine is too slow or the configuration of MySQL is sub-optimal. In this case, the machine looks fine. I can't comment on queries or indexes. Your tuning, I can. set-variable= key_buffer=512M This one is important; it caches data from the database in memory. How big is your database? Is it bigger than 512 meg? If so, is there free memory on the machine? If so, I'd recommend bumping it up as much as you can; it's faster to get data from memory than it is from disk. set-variable= sort_buffer=16M This is used to sort the results of queries; it's per-connection. I believe it is allocated on an as-needed basis. Regardless, are you doing a lot of ORDER BY/GROUP BY in your queries? This might be a little high. set-variable= table_cache=1024 How many tables do you have? This tells the database how many tables to keep open. It probably won't make much of a difference lowering it. set-variable= join_buffer=8M This one is used to join tables where no indexes exist. In otherwords, if you're using indexes, it won't be used. set-variable= record_buffer=8M This is used for reading in rows after a sort (from the sort_buffer). Again, per client. Do you need it? set-variable= query_cache_size=6M This one can be a waste of memory, or a huge bonus. Queries and their result sets are stored here. If you need to run the same query a second time, the database just pulls the results from the cache. There are a few issues, tho. If you have a query, SELECT * FROM table_1 WHERE condition_1 = 12 then the result of the query will be stored. But if the next statement is, UPDATE TABLE table_1... then the data in the cache relating to table_1 have to be unloaded, as the UPDATE statement could have invalidated all of it. But, if you have some stock queries that constantly read data from tables that, in the business logic of your application, are read-only (ie you rarely, if ever, update the data in them), then the query cache can be a big bonus. Another interesting note is the query_cache_type variable. You can set it to 2 in the my.cnf file; this means that in your select statements, you add a hint to tell the database to cache or not cache the results of the query. So if you know a query and it's results are very dynamic, then it's not much use to use the query cache and you can tell the database to not put it in the query cache. The SELECT statement would look like, 'SELECT SQL_CACHE * FROM table_1 WHERE...'. Here's the page: http://www.mysql.com/doc/en/Query_Cache.html Next, moving to InnoDB. I found that, properly tuned, InnoDB is almost as fast as MyISAM. You'll want to set the innodb_buffer_pool_size as large as possible (it's the MyISAM key_buffer) to cache as much data. You'll need to set up a tablespace; you can put them all over your disks. Finally, play with innodb_flush_method. I set it to O_DSYNC and got a substantial increase in performance. Search the MySQL list-archives for discussions on the options, or check out http://www.innodb.com Hope that helps, David - Original Message - From: Rainer Sip [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 9:48 AM Subject: Mysql Performance Question I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers, Rainer [client] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=512M set-variable
Re: Mysql Performance Question
At 12:32 PM 10/21/2003, you wrote: Thanks a lot guys. Haydies: Just out of wondering, are you using PHP and if so do you use mysql_pconnect rather then mysql_connect because that would really speed things up. I tired pconnect before but it didn't help but using up all the available memory. It speeds up things until the disks started swapping, which happened in a few hours after the server was up and running. Gabriel: Gabriel, You can also create a small RAM disk and put all of the PHP scripts and images on that drive. It will speed things up by 30%-50%. And you're right about pconnect. It doesn't speed things up. There are PHP debuggers available that have a profiler that will show you which of your PHP functions are slow. It could be only 1 or two functions that need optimizing. Also I insist on putting a LIMIT 100 or LIMIT 25 on my web queries to reduce the number of rows returned. This should speed things up quite a bit. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
Hi, I really doubt putting PHP files (or any site files) on a RAM disk will make any performance difference; and certainly not 30-50%. When the files/scripts are accessed, they are cached by the OS. So, in effect, they are automatically put in a sort of RAM disk. The disk is definitely NOT accessed each time the file is requested. This doesn't matter anyway (for the PHP), since he said he's using PHP Accelerator, which caches the compiled code in shared memory. And this does save the slight overhead of file reading calls (and the very costly and slow PHP parser/compiler :-)) -- except stat() or whatever to check the mtime. Matt - Original Message - From: mos Sent: Wednesday, October 22, 2003 1:44 PM Subject: Re: Mysql Performance Question At 12:32 PM 10/21/2003, you wrote: Thanks a lot guys. Haydies: Just out of wondering, are you using PHP and if so do you use mysql_pconnect rather then mysql_connect because that would really speed things up. I tired pconnect before but it didn't help but using up all the available memory. It speeds up things until the disks started swapping, which happened in a few hours after the server was up and running. Gabriel: Gabriel, You can also create a small RAM disk and put all of the PHP scripts and images on that drive. It will speed things up by 30%-50%. And you're right about pconnect. It doesn't speed things up. There are PHP debuggers available that have a profiler that will show you which of your PHP functions are slow. It could be only 1 or two functions that need optimizing. Also I insist on putting a LIMIT 100 or LIMIT 25 on my web queries to reduce the number of rows returned. This should speed things up quite a bit. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Performance Question
I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers, Rainer [client] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=1024 set-variable= sort_buffer=16M set-variable= join_buffer=8M set-variable= record_buffer=8M set-variable= thread_cache=512 set-variable= max_connections=500 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=512M set-variable= query_cache_size=6M #set-variable = max_connect_errors=1000 #set-variable = back_log=100 #log-bin skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=124M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=8M set-variable= write_buffer=4M [mysqlhotcopy] interactive-timeout
Re: Mysql Performance Question
On Tuesday, October 21, 2003, at 12:48 PM, Rainer Sip wrote: I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) Can you make changes to the site's software? Perhaps more aggressive caching would help. It looks like Xoops is using PHP, so you could try using something like php memcache (http://lucifer.intercosmos.net/) or Turck MMCache (http://sourceforge.net/projects/turck-mmcache) or any other PHP cache / accelerator product. In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. Will Xoops take advantage of InnoDB's transactional capabilities? The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Can you add more RAM? What type of disks are you using in the RAID setup? Is it hardware or software RAID? - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
On Wed, 22 Oct 2003 00:48:29 +0800, Rainer Sip [EMAIL PROTECTED] wrote: In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. I know you want to tune the parameters, but if you haven't already, I'd also recommend taking a quick survey of your most common queries, making sure there is no low-hanging fruit available in terms of tuning your queries/indices/etc. --bluejack, who knows very little about tuning the parameters. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
Thanks a lot guys. Haydies: Just out of wondering, are you using PHP and if so do you use mysql_pconnect rather then mysql_connect because that would really speed things up. I tired pconnect before but it didn't help but using up all the available memory. It speeds up things until the disks started swapping, which happened in a few hours after the server was up and running. Gabriel: Can you make changes to the site's software? Perhaps more aggressive caching would help. It looks like Xoops is using PHP, so you could try using something like php memcache (http://lucifer.intercosmos.net/) or Turck MMCache (http://sourceforge.net/projects/turck-mmcache) or any other PHP cache / accelerator product. I'm now using PHPA to cache the scripts. Will Xoops take advantage of InnoDB's transactional capabilities? I probably don't need the transactional capabilities of innodb. I'm just thinking the row level locking of innodb will bring some more speed to the server. Can you add more RAM? What type of disks are you using in the RAID setup? Is it hardware or software RAID? Due to limited budget I'm unable to add more memory in the short future (really don't want to replace 4 512MB modules with 1G modules). In fact there are always about 5-800MB of unused/buffered memory. The machine is equipped with hardware raid, and 4 19GB SCSI disks. Cheers, Rainer - Original Message - From: Rainer Sip [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 5:48 PM Subject: Mysql Performance Question I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers, Rainer [client] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=1024 set-variable= sort_buffer=16M set-variable= join_buffer=8M set-variable= record_buffer=8M set-variable= thread_cache=512 set-variable= max_connections=500 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=512M set-variable= query_cache_size=6M #set-variable = max_connect_errors=1000 #set-variable = back_log=100 #log-bin skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=124M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=8M set-variable= write_buffer=4M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
Rainer Sip wrote: I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. snip Rainer, The 12+ load avg. is HIGH. What is % idle when the load average is high? walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
You need to figure out what's slowing down your application. It could be expensive queries which in turn could be caused by missing indexes. It could be that the machine is too slow or the configuration of MySQL is sub-optimal. In this case, the machine looks fine. I can't comment on queries or indexes. Your tuning, I can. set-variable= key_buffer=512M This one is important; it caches data from the database in memory. How big is your database? Is it bigger than 512 meg? If so, is there free memory on the machine? If so, I'd recommend bumping it up as much as you can; it's faster to get data from memory than it is from disk. set-variable= sort_buffer=16M This is used to sort the results of queries; it's per-connection. I believe it is allocated on an as-needed basis. Regardless, are you doing a lot of ORDER BY/GROUP BY in your queries? This might be a little high. set-variable= table_cache=1024 How many tables do you have? This tells the database how many tables to keep open. It probably won't make much of a difference lowering it. set-variable= join_buffer=8M This one is used to join tables where no indexes exist. In otherwords, if you're using indexes, it won't be used. set-variable= record_buffer=8M This is used for reading in rows after a sort (from the sort_buffer). Again, per client. Do you need it? set-variable= query_cache_size=6M This one can be a waste of memory, or a huge bonus. Queries and their result sets are stored here. If you need to run the same query a second time, the database just pulls the results from the cache. There are a few issues, tho. If you have a query, SELECT * FROM table_1 WHERE condition_1 = 12 then the result of the query will be stored. But if the next statement is, UPDATE TABLE table_1... then the data in the cache relating to table_1 have to be unloaded, as the UPDATE statement could have invalidated all of it. But, if you have some stock queries that constantly read data from tables that, in the business logic of your application, are read-only (ie you rarely, if ever, update the data in them), then the query cache can be a big bonus. Another interesting note is the query_cache_type variable. You can set it to 2 in the my.cnf file; this means that in your select statements, you add a hint to tell the database to cache or not cache the results of the query. So if you know a query and it's results are very dynamic, then it's not much use to use the query cache and you can tell the database to not put it in the query cache. The SELECT statement would look like, 'SELECT SQL_CACHE * FROM table_1 WHERE...'. Here's the page: http://www.mysql.com/doc/en/Query_Cache.html Next, moving to InnoDB. I found that, properly tuned, InnoDB is almost as fast as MyISAM. You'll want to set the innodb_buffer_pool_size as large as possible (it's the MyISAM key_buffer) to cache as much data. You'll need to set up a tablespace; you can put them all over your disks. Finally, play with innodb_flush_method. I set it to O_DSYNC and got a substantial increase in performance. Search the MySQL list-archives for discussions on the options, or check out http://www.innodb.com Hope that helps, David - Original Message - From: Rainer Sip [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 9:48 AM Subject: Mysql Performance Question I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers, Rainer [client] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=1024 set-variable= sort_buffer=16M set-variable= join_buffer=8M set-variable= record_buffer=8M set-variable= thread_cache=512 set-variable= max_connections=500 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=512M set-variable= query_cache_size=6M #set-variable = max_connect_errors=1000 #set-variable = back_log=100 #log-bin skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick set-variable
Re: Mysql Performance Question
Hi Rainer, You might get more improvement by optimizing your application and queries than by tuning hardware or MySQL. :-) About switching to InnoDB, are you doing lots of writes that are causing locked tables? e.g. What's the ratio of Table_locks_immediate to Table_locks_waited in SHOW STATUS? If you aren't doing lots of UPDATEs or DELETEs that cause locking problems, InnoDB won't really give higher concurrency. Remember, INSERTs aren't usually a problem, since SELECTs can run at the same time with MyISAM as long as there's no free space in the data file. Anyway, about your my.cnf. I don't think it's as important as people make it that the key_buffer be so large. If it's unnecessarily big, it's taking away free memory that the OS could use to cache *file* data (key_buffer only caches indexes, not the data file). It's a lot faster to read indexes from disk (they may be still cached by the OS anyway if not by MySQL) than it is to read data rows! I don't know how big your indexes are and if only the same small portions are usually used or the whole thing? In other words, no matter the size of the indexes, how much of them is actually accessed? If it's less than key_buffer size, key_buffer is too big. e.g. If Key_blocks_read is not much more than Key_blocks_used, key_buffer is too big. Of course, Key_blocks_read will go up if a table is closed/flushed and indexes are reloaded, so this could be artificially high. Again, I don't know how much of your indexes are frequently used, but a key_buffer of 64-128M is probably plenty. If Key_reads divided by Key_read_requests is less than 0.005-0.01, it's definitely OK. Also, right now, MySQL uses a global mutex lock or something in the key buffer (e.g. exclusive lock even when *reading* keys!) which hurts MyISAM concurrency. I believe the new rewritten key cache system is implemented in version 4.1.1, which should be out within a month. Sounds like good news. Is your table_cache always full (Open_tables is 1024?) and Opened_tables status variable high? If so, you may want to increase the table_cache to 1536 or something. For sort, join, and record/read buffers, I don't know if the full amount is allocated right away, or as needed up to the limit. Anyone know? If it's all at once, the system may be allocating and releasing too much memory all the time. See http://jeremy.zawodny.com/blog/archives/34.html Why do you have thread_cache_size set to 512 when max_connections is only 500? :-) I think you should set thread_cache_size to a little less than the amount of clients that are usually connected. tmp_table_size seems a bit big. query_cache_size seems a bit small, assuming you have lots of queries that could be cached. In MySQL 4, skip-locking is the default; set-variable = syntax is deprecated; record_buffer is now read_buffer; and it's mysqld_safe instead of safe_mysqld. And thread_concurrency only applies to Solaris, BTW. Without knowing about your workload, you might try something like this for your my.cnf: [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-innodb max_connections=500 max_allowed_packet=1M key_buffer_size=96M sort_buffer_size=2M join_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=1M table_cache=1024 thread_cache_size=64 tmp_table_size=48M myisam_sort_buffer_size=512M query_cache_size=32M query_cache_limit=2M #max_connect_errors=1000 #back_log=100 #log-bin [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid open-files-limit=8192 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=256M set-variable = sort_buffer=384M set-variable = read_buffer=16M set-variable = write_buffer=16M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=384M set-variable = read_buffer=16M set-variable = write_buffer=16M [mysqlhotcopy] interactive-timeout Hope that helps. Matt - Original Message - From: Rainer Sip Sent: Tuesday, October 21, 2003 9:48 AM Subject: Mysql Performance Question I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers
Re: Mysql Performance Question
SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408517 xoops 192.168.0.1 baby_kingd10 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408519 xoops 192.168.0.1 baby_kingd10 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408581 xoops 192.168.0.1 baby_kingd10 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408604 xoops 192.168.0.1 baby_kingd10 Query SELECT uname FROM xoops_users WHERE uid = 793 408605 xoops 192.168.0.1 baby_kingd10 Query SELECT * FROM xoops_users WHERE uid=3089 408490 xoops 192.168.0.1 baby_kingd11 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408492 xoops 192.168.0.1 baby_kingd11 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408497 xoops 192.168.0.1 baby_kingd11 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408592 xoops 192.168.0.1 baby_kingd11 Query SELECT t.*, u.uname, u2.uname as last_poster, p.post_time as l 408599 xoops 192.168.0.1 baby_kingd11 Query SELECT t.*, u.uname, u2.uname as last_poster, p.post_time as l 408600 xoops 192.168.0.1 baby_kingd11 Query SELECT t.*, u.uname, u2.uname as last_poster, p.post_time as l 408602 xoops 192.168.0.1 baby_kingd11 Query SELECT * FROM xoops_users WHERE uid=3819 TOP -- 12:10:01 up 2 days, 8:52, 1 user, load average: 4.15, 6.79, 7.02 40 processes: 38 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 67.2% user 25.4% system0.0% nice 0.0% iowait 6.4% idle CPU1 states: 10.0% user 7.3% system0.0% nice 0.0% iowait 82.2% idle Mem: 2064500k av, 1130168k used, 934332k free, 0k shrd, 62876k buff 933640k actv, 49892k in_d, 26704k in_c Swap: 2040244k av, 36k used, 2040208k free 684320k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 3138 mysql 15 0 264M 264M 2232 S99.9 13.0 1792m 0 mysqld-max SAR -- 07:00:00 HK all 0.66 0.00 0.47 98.87 07:10:00 HK all 5.04 0.00 1.89 93.06 07:20:00 HK all 4.85 0.00 2.23 92.92 07:30:00 HK all 2.25 0.00 1.15 96.60 07:40:00 HK all 2.88 0.00 1.34 95.78 07:50:00 HK all 2.00 0.00 1.12 96.88 08:00:00 HK all 4.09 0.00 1.86 94.05 08:10:00 HK all 4.90 0.00 2.38 92.72 08:20:00 HK all 3.40 0.00 1.73 94.88 08:30:00 HK all 2.65 0.00 1.42 95.92 08:40:00 HK all 4.25 0.00 1.98 93.77 08:50:00 HK all 5.92 0.00 2.74 91.34 09:00:00 HK all 7.57 0.00 3.36 89.07 09:10:00 HK all 12.44 0.00 5.50 82.06 09:20:00 HK all 32.86 0.00 14.31 52.84 09:30:00 HK all 24.03 0.00 9.57 66.40 09:40:00 HK all 31.26 0.00 13.83 54.91 09:50:00 HK all 43.56 0.00 21.61 34.83 10:00:03 HK all 42.48 0.00 19.74 37.78 10:10:00 HK all 38.89 0.00 19.31 41.80 10:10:00 HK CPU %user %nice %system %idle 10:20:00 HK all 42.50 0.00 20.21 37.29 10:30:00 HK all 35.91 0.00 16.64 47.45 10:40:00 HK all 46.26 0.00 22.03 31.72 10:50:00 HK all 43.28 0.00 19.37 37.35 11:00:00 HK all 35.22 0.00 16.42 48.36 11:10:00 HK all 33.16 0.00 15.55 51.29 11:20:00 HK all 36.43 0.00 16.64 46.93 11:30:00 HK all 35.75 0.00 16.84 47.41 11:40:00 HK all 39.29 0.00 18.52 42.20 11:50:00 HK all 38.25 0.00 19.21 42.54 12:00:01 HK all 57.51 0.00 28.40 14.09 12:10:00 HK all 50.69 0.00 26.55 22.76 Average: all 15.37 0.00 7.33 77.30 - Original Message - From: David Griffiths [EMAIL PROTECTED] To: Rainer Sip [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 3:11 AM Subject: Re: Mysql Performance Question You need to figure out what's slowing down your application. It could be expensive queries which in turn could be caused by missing indexes. It could be that the machine is too slow or the configuration of MySQL is sub-optimal. In this case, the machine looks fine. I can't comment on queries or indexes. Your tuning, I can. set-variable= key_buffer=512M This one is important; it caches data from the database in memory. How big is your database? Is it bigger than 512
Re: MySQL performance question..
Keith C. Ivey [EMAIL PROTECTED] wrote: I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst, with a wd 512MB HD and I want to store George Bush in our MySQL database. As far as table definitions are concerned, should I use a BLOB or should I store him on disk and make a reference to the physical location in the MySQL table instead? One way would be to convert him to a text representation following RFC 1437 and store him in a HUMONGOUSTEXT column. There's an example of sending Dan Quayle by e-mail in the RFC: http://www.faqs.org/rfcs/rfc1437.html I suppose the P-133 is too slow to dereference human's DNA into a valid pointer. You may accidentaly cause new elections in U.S. if G.Bush is stored outside of rock-stable MySQL... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL performance question..
Hi, Can anyone help? I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst, with a wd 512MB HD and I want to store George Bush in our MySQL database. As far as table definitions are concerned, should I use a BLOB or should I store him on disk and make a reference to the physical location in the MySQL table instead? Will I run into any performance/storage issues when querying this data? Hope you can help, Andrew sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance question..
On 9 Jul 2003 at 23:14, Andrew Braithwaite wrote: I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst, with a wd 512MB HD and I want to store George Bush in our MySQL database. As far as table definitions are concerned, should I use a BLOB or should I store him on disk and make a reference to the physical location in the MySQL table instead? One way would be to convert him to a text representation following RFC 1437 and store him in a HUMONGOUSTEXT column. There's an example of sending Dan Quayle by e-mail in the RFC: http://www.faqs.org/rfcs/rfc1437.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql performance question
Hi Ken, Here are the values: kern.maxfiles: 24616 kern.maxfilesperproc: 22154 They are slightly less than 25716, is there any way to check if this upper limit becoming reached sometimes? Now there are question about innodb tables and 4.0.1. 1. What can be gained by switching to innodb inside 3.23 version, will the effect be significant? 2. Going to 4.0.1 with innodb - will it bring additional effect, what is more important - innodb or 4.0.1 or both? 3. How can I run both 3.23 and 4.0.1 on a single server, so I can play with 4.0.1 before transfering all databases to it? Starting both versions of daemons seems to be simple enough, but it seems the problem will start with the client part - how perl libraries (DBD) can deside to which mysql version they should connect? So can the mysql 3.23 clients libraries be used with 4.0.1, or what are the ways of solving it? Luck Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 15:14:39 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Your welcome Alexander! OK the value for table_cache is 8572 * 3 = 25716 file descriptors, how big is kern.maxfiles and kern.maxfilesproc? These need to be set above 26000 to allow all those tables to be opened! I know I have missed this sometime, or not reset them after a kernel compile. Also you don't need 4.0.1 for innodb tables, they work fine on 3.23.49 and LINUX_THREADS option from the ports package I think is only available for 3.23.xx versions. The only problems I have had with 4.0.1 have been self inflicted. There are some bugs in some of the newer features, 4.0.2 should be out soon. I have been kicking around the idea of using 4.0.1 in production as none of the bugs so far really affect my application. You can see the online change log for a list of changes and fixes since 4.0.1 at http://www.mysql.com/doc/N/e/News-4.0.2.html Good Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Ken Menzel [EMAIL PROTECTED] Cc: Simon Green [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 11:31 AM Subject: Re: Mysql performance question Thanks a lot for the advices. The value for table_cache is 8572, and I'm indeed accessing a lot of tables. So as I gather it the main effect should be expected from transfering to MySQL 4.0.1 compiled with LINUX_THREADS and using innodb tables, true? Now what can you say about reliability issue, aren't these products currently in the development stage and can they be used for the production server? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 10:07:22 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED], Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Hi Simon, A couple of things, unless you have compiled WITH_LINUX_THREADS from the /usr/ports/databases then adding more processors will be unlikely to help. The native threads lib on FreeBSD runs a threaded app on 1 processor currently, use the LINUX_THREADS option in the ports to get around this if you would like to add more processors. Do you have softupdates on also? Have you read 'man tuning'? Are you accessing alot of tables/DBs? If yes what is your table_cache value? What is in 'SHOW STATUS' and 'SHOW VARIABLES'? Also MySQL 4.0.1 has a query cache that is incredible! It works great. 6000 queries per minute is pretty good performance. Have you looked at using heap tables where possible? Another suggestion if everything is hitting the same table/database have you tried innodb tables? They work very well under high loads. I would study all the 'SHOW STATUS' output and see if you can spot anything in the manual page for each of the variables. Best of Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 8:30 AM Subject: RE: Mysql performance question Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green
Re: Mysql performance question
Good Morning Alexander! Looks like you have tuned your system pretty well. They are slightly less than 25716, is there any way to check if this upper limit becoming reached sometimes? sysctl kern.openfiles will tell you how many are open now but there is no way to know if the upper limit is being reached except inside of mysql you can look at 'show status' at see if the ratio of 'opened tables' to 'open tables' is high. If you have a much larger number of opened tables something is not right. But I suspect all is well with your setup. Also note the 'open files' will tell you how many files mysql has open. Now there are question about innodb tables and 4.0.1. 1. What can be gained by switching to innodb inside 3.23 version, will the effect be significant? I think the biggest advantage of the innodb tables (besides transaction capable) is the row level locking, which mainly benefits you if you are getting many 'table_locks_waited'. Innodb is very good at parallel operations. You can set the transaction level to a low value and avoid the speed hit from transactions as well. Check out Heikki's web page at www.innodb.com and http://www.innodb.com/bench.html Great stuff! Also this is the easiest thing to try, if you have innodb table space enabled on your server just type 'ALTER TABLE mytable TYPE=INNODB' do this for all you table (with that many tables I would do it with a script) and if you don't like it 'TABLE mytable TYPE=MYISAM' to change it back. 2. Going to 4.0.1 with innodb - will it bring additional effect, what is more important - innodb or 4.0.1 or both? This depends on your application, for instance the 'query cache' feature of 4.0.1 is amazing. If there are many reads of data that may be repeated the query cache can signifigantly increase performance. I would check out the online manual for a full list of features in 4.0.1. The main two for me have been the query cache and the ability for an index to be used with ORDER BY DESC. 3. How can I run both 3.23 and 4.0.1 on a single server, so I can play with 4.0.1 before transfering all databases to it? Starting both versions of daemons seems to be simple enough, but it seems the problem will start with the client part - how perl libraries (DBD) can deside to which mysql version they should connect? So can the mysql 3.23 clients libraries be used with 4.0.1, or what are the ways of solving it? Yes you can run two versions (but not on windows). You must assign the second version to another port, which will affect you apps if you want to use the second version the app must be told which one to connect to. I have not done this, but I think there is a recipe somewhere, maybe you could repost this question or take a look in the manual at mysql-multi (or in the local/bin directory). Best of luck to you also, - 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 performance question
Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 system. All mysql queries are rewritten so the INSERT/UPDATE's has been reduced or replaced by DELAYED as much as possible, to ease mysql locking. The mysql tables are indexed, all documentation from the main mysql site having been studied. And under the top load the mysql starts locking anyways. So what can be the best ways of helping it: - Changing mysql options, may be increasing key_buffer some more or what else? - Adding 2 more CPU's to the server (it can bear up to 4 CPU); - May be there can be some other ways? Thanks in advance! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 performance question
What disk drive have you got? We have found that this can help. Simon -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 13:14 To: [EMAIL PROTECTED] Subject: Mysql performance question Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 system. All mysql queries are rewritten so the INSERT/UPDATE's has been reduced or replaced by DELAYED as much as possible, to ease mysql locking. The mysql tables are indexed, all documentation from the main mysql site having been studied. And under the top load the mysql starts locking anyways. So what can be the best ways of helping it: - Changing mysql options, may be increasing key_buffer some more or what else? - Adding 2 more CPU's to the server (it can bear up to 4 CPU); - May be there can be some other ways? Thanks in advance! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 performance question
Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green [EMAIL PROTECTED] To: 'Varshavchick Alexander' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Mysql performance question What disk drive have you got? We have found that this can help. Simon -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 13:14 To: [EMAIL PROTECTED] Subject: Mysql performance question Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 system. All mysql queries are rewritten so the INSERT/UPDATE's has been reduced or replaced by DELAYED as much as possible, to ease mysql locking. The mysql tables are indexed, all documentation from the main mysql site having been studied. And under the top load the mysql starts locking anyways. So what can be the best ways of helping it: - Changing mysql options, may be increasing key_buffer some more or what else? - Adding 2 more CPU's to the server (it can bear up to 4 CPU); - May be there can be some other ways? Thanks in advance! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 performance question
Hi Simon, A couple of things, unless you have compiled WITH_LINUX_THREADS from the /usr/ports/databases then adding more processors will be unlikely to help. The native threads lib on FreeBSD runs a threaded app on 1 processor currently, use the LINUX_THREADS option in the ports to get around this if you would like to add more processors. Do you have softupdates on also? Have you read 'man tuning'? Are you accessing alot of tables/DBs? If yes what is your table_cache value? What is in 'SHOW STATUS' and 'SHOW VARIABLES'? Also MySQL 4.0.1 has a query cache that is incredible! It works great. 6000 queries per minute is pretty good performance. Have you looked at using heap tables where possible? Another suggestion if everything is hitting the same table/database have you tried innodb tables? They work very well under high loads. I would study all the 'SHOW STATUS' output and see if you can spot anything in the manual page for each of the variables. Best of Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 8:30 AM Subject: RE: Mysql performance question Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green [EMAIL PROTECTED] To: 'Varshavchick Alexander' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Mysql performance question What disk drive have you got? We have found that this can help. Simon -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 13:14 To: [EMAIL PROTECTED] Subject: Mysql performance question Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 system. All mysql queries are rewritten so the INSERT/UPDATE's has been reduced or replaced by DELAYED as much as possible, to ease mysql locking. The mysql tables are indexed, all documentation from the main mysql site having been studied. And under the top load the mysql starts locking anyways. So what can be the best ways of helping it: - Changing mysql options, may be increasing key_buffer some more or what else? - Adding 2 more CPU's to the server (it can bear up to 4 CPU); - May be there can be some other ways? Thanks in advance! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 performance question
Thanks a lot for the advices. The value for table_cache is 8572, and I'm indeed accessing a lot of tables. So as I gather it the main effect should be expected from transfering to MySQL 4.0.1 compiled with LINUX_THREADS and using innodb tables, true? Now what can you say about reliability issue, aren't these products currently in the development stage and can they be used for the production server? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 10:07:22 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED], Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Hi Simon, A couple of things, unless you have compiled WITH_LINUX_THREADS from the /usr/ports/databases then adding more processors will be unlikely to help. The native threads lib on FreeBSD runs a threaded app on 1 processor currently, use the LINUX_THREADS option in the ports to get around this if you would like to add more processors. Do you have softupdates on also? Have you read 'man tuning'? Are you accessing alot of tables/DBs? If yes what is your table_cache value? What is in 'SHOW STATUS' and 'SHOW VARIABLES'? Also MySQL 4.0.1 has a query cache that is incredible! It works great. 6000 queries per minute is pretty good performance. Have you looked at using heap tables where possible? Another suggestion if everything is hitting the same table/database have you tried innodb tables? They work very well under high loads. I would study all the 'SHOW STATUS' output and see if you can spot anything in the manual page for each of the variables. Best of Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 8:30 AM Subject: RE: Mysql performance question Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green [EMAIL PROTECTED] To: 'Varshavchick Alexander' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Mysql performance question What disk drive have you got? We have found that this can help. Simon -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 13:14 To: [EMAIL PROTECTED] Subject: Mysql performance question Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 system. All mysql queries are rewritten so the INSERT/UPDATE's has been reduced or replaced by DELAYED as much as possible, to ease mysql locking. The mysql tables are indexed, all documentation from the main mysql site having been studied. And under the top load the mysql starts locking anyways. So what can be the best ways of helping it: - Changing mysql options, may be increasing key_buffer some more or what else? - Adding 2 more CPU's to the server (it can bear up to 4 CPU); - May be there can be some other ways? Thanks in advance! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 performance question
Your welcome Alexander! OK the value for table_cache is 8572 * 3 = 25716 file descriptors, how big is kern.maxfiles and kern.maxfilesproc? These need to be set above 26000 to allow all those tables to be opened! I know I have missed this sometime, or not reset them after a kernel compile. Also you don't need 4.0.1 for innodb tables, they work fine on 3.23.49 and LINUX_THREADS option from the ports package I think is only available for 3.23.xx versions. The only problems I have had with 4.0.1 have been self inflicted. There are some bugs in some of the newer features, 4.0.2 should be out soon. I have been kicking around the idea of using 4.0.1 in production as none of the bugs so far really affect my application. You can see the online change log for a list of changes and fixes since 4.0.1 at http://www.mysql.com/doc/N/e/News-4.0.2.html Good Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Ken Menzel [EMAIL PROTECTED] Cc: Simon Green [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 11:31 AM Subject: Re: Mysql performance question Thanks a lot for the advices. The value for table_cache is 8572, and I'm indeed accessing a lot of tables. So as I gather it the main effect should be expected from transfering to MySQL 4.0.1 compiled with LINUX_THREADS and using innodb tables, true? Now what can you say about reliability issue, aren't these products currently in the development stage and can they be used for the production server? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 10:07:22 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED], Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Hi Simon, A couple of things, unless you have compiled WITH_LINUX_THREADS from the /usr/ports/databases then adding more processors will be unlikely to help. The native threads lib on FreeBSD runs a threaded app on 1 processor currently, use the LINUX_THREADS option in the ports to get around this if you would like to add more processors. Do you have softupdates on also? Have you read 'man tuning'? Are you accessing alot of tables/DBs? If yes what is your table_cache value? What is in 'SHOW STATUS' and 'SHOW VARIABLES'? Also MySQL 4.0.1 has a query cache that is incredible! It works great. 6000 queries per minute is pretty good performance. Have you looked at using heap tables where possible? Another suggestion if everything is hitting the same table/database have you tried innodb tables? They work very well under high loads. I would study all the 'SHOW STATUS' output and see if you can spot anything in the manual page for each of the variables. Best of Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 8:30 AM Subject: RE: Mysql performance question Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green [EMAIL PROTECTED] To: 'Varshavchick Alexander' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Mysql performance question What disk drive have you got? We have found that this can help. Simon -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 13:14 To: [EMAIL PROTECTED] Subject: Mysql performance question Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM
Re: MySQL Performance Question
On Mon, Oct 22, 2001 at 08:02:49AM +0100, Ronan Minogue wrote: Jeremy, Firstly thank you for the reply. On the MySql web site there are benchmark response times provided for the execution of queries on NT 4. e.g. reading 2,000,000 rows by index requiring 367 seconds. Are you aware of any comparable test on Linux? You can run the benchmarks yourself on Linux if you'd like. The benchmark software is part of the MySQL distribution. But those speeds are easily attainable on modern hardware. The numbers you actually see will depend heavily on your setup, of course, but I just ran a big select (450,000 rows) on a table with 150 million rows. I was done in about 50 seconds. I really haven't optimized for that case, since we never run queries like that. 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.41-max: up 46 days, processed 1,018,067,398 queries (253/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
MySQL Performance Question
Dear Sir/Madam. I have written a Management Information System that has a MySQL db running on a Linux server. There is quite a small number of tables. However these tables are growing quickly and the queries executed will require LEFT OUTER JOIN between tables. Example: A sample query over 4 tables using LEFT OUTER JOIN Table A 11000 rows Table B 15000 rows Table C 18000 rows Table D 2,500,000 rows Table D will over a year will reach values of 10 to 15 million records. Are you aware of any benchmarks / examples of expected response time?? Regards, Ronan - 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 Performance Question
On Sun, Oct 21, 2001 at 08:41:45PM +0100, Ronan Minogue wrote: Dear Sir/Madam. I have written a Management Information System that has a MySQL db running on a Linux server. There is quite a small number of tables. However these tables are growing quickly and the queries executed will require LEFT OUTER JOIN between tables. Example: A sample query over 4 tables using LEFT OUTER JOIN Table A 11000 rows Table B 15000 rows Table C 18000 rows Table D 2,500,000 rows Table D will over a year will reach values of 10 to 15 million records. Are you aware of any benchmarks / examples of expected response time?? Spend 20 minutes and fill table D with a lot of data and see how it performs. If it's not fast enough, look tweaking MySQL's settings (as described in the manual) for higher performance. With the detail you've given us so far, it's nearly impossible to predict the results. 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.41-max: up 45 days, processed 999,195,609 queries (252/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 performance question
Joshua, Comparing Oracle vs. MySQL myisam tables, MySQL will save disk space, and provide you with much faster queries, but you have to be aware of the table locking issues if you are doing updates/inserts/deletes mixed with reads. MySql documentation says that mysql enforces table level locking if no explicit locking is there.If I am using non transaction sensitive tables can I go ahead without providing any explicit locking?I am using myISAM. Regards, Ravi _ Joshua Chamas Chamas Enterprises Inc. NodeWorks free web link monitoring Huntington Beach, CA USA http://www.nodeworks.com 1-714-625-4051 - 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 Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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 performance question
VVM Ravikumar Sarma Chengalvala wrote: MySql documentation says that mysql enforces table level locking if no explicit locking is there.If I am using non transaction sensitive tables can I go ahead without providing any explicit locking?I am using myISAM. Yes. This is how I use MySQL too. --Josh _ Joshua Chamas Chamas Enterprises Inc. NodeWorks free web link monitoring Huntington Beach, CA USA http://www.nodeworks.com1-714-625-4051 - 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 performance question
VVM Ravikumar Sarma Chengalvala wrote: Joshua, Comparing Oracle vs. MySQL myisam tables, MySQL will save disk space, and provide you with much faster queries, but you have to be aware of the table locking issues if you are doing updates/inserts/deletes mixed with reads. MySql documentation says that mysql enforces table level locking if no explicit locking is there.If I am using non transaction sensitive tables can I go ahead without providing any explicit locking?I am using myISAM. Regards, Ravi You don't need to provide explicit lockin in most cases. But if you do not and mysql needs to make a lock for an update it will lock the table against other reads until the update is complete, it is quick, but if you are pushing the server with many updates and reads at the same time it can be very slow, or if you have updates that take a long time you can create a large queue of waiting readers. - 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 performance question
I would like to know the limitations of Mysql with extremely large tables. I need to store 20+ million records. Each record would contain only 4-6 fields and would not be longer than 128 bytes of information. The records could be divided among several tables, but at what size is the performance hindered enough to need to split records between several smaller tables? Any information leading to an optimal solution would be greatly appreciated. Thanks Shane Anderson Software Developer Web Accessibility in Mind - http://www.WebAIM.org - 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 performance question
Shane Anderson wrote: I would like to know the limitations of Mysql with extremely large tables. I need to store 20+ million records. Each record would contain only 4-6 fields and would not be longer than 128 bytes of information. The records could be divided among several tables, but at what size is the performance hindered enough to need to split records between several smaller tables? Any information leading to an optimal solution would be greatly appreciated. MySQL doesn't have a real limitation on the # of records, but I speak only from 1/2 million record table experience. But there is a point to that, I split my tables to have no more than that number because I don't want to take my system offline for very long if I want to add an index, or recover my database from a server crash. MERGE tables might allow you to break up your large tables nicely. But what I'm saying is true for any database, that if you have 20 million rows, your database maintenance window has to be a lot bigger, so while the query speed may be great on myisam with 20 millions rows, your DBA ( or you? ) may be none too happy when adding that new index. Comparing Oracle vs. MySQL myisam tables, MySQL will save disk space, and provide you with much faster queries, but you have to be aware of the table locking issues if you are doing updates/inserts/deletes mixed with reads. --Josh _ Joshua Chamas Chamas Enterprises Inc. NodeWorks free web link monitoring Huntington Beach, CA USA http://www.nodeworks.com1-714-625-4051 - 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 performance question
limitation about space and speed according to DBMS featuring. since MySQL still have not full SQL 92 feature (transaction, sub select... I think) make it eat a fewer space than other DBMS (oracle or informix) and it can speed up because it did not need to handle more condition. I did not know when MySQL support all today lack function in version 4, How many disk space and ram will require to handle more data? Sommai Fongnamthip At 18:52 21/5/2001 -0700, Joshua Chamas wrote: Shane Anderson wrote: I would like to know the limitations of Mysql with extremely large tables. I need to store 20+ million records. Each record would contain only 4-6 fields and would not be longer than 128 bytes of information. The records could be divided among several tables, but at what size is the performance hindered enough to need to split records between several smaller tables? Any information leading to an optimal solution would be greatly appreciated. MySQL doesn't have a real limitation on the # of records, but I speak only from 1/2 million record table experience. But there is a point to that, I split my tables to have no more than that number because I don't want to take my system offline for very long if I want to add an index, or recover my database from a server crash. MERGE tables might allow you to break up your large tables nicely. But what I'm saying is true for any database, that if you have 20 million rows, your database maintenance window has to be a lot bigger, so while the query speed may be great on myisam with 20 millions rows, your DBA ( or you? ) may be none too happy when adding that new index. Comparing Oracle vs. MySQL myisam tables, MySQL will save disk space, and provide you with much faster queries, but you have to be aware of the table locking issues if you are doing updates/inserts/deletes mixed with reads. --Josh _ Joshua Chamas Chamas Enterprises Inc. NodeWorks free web link monitoring Huntington Beach, CA USA http://www.nodeworks.com1-714-625-4051 - 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 performance question
I'm currently running MySQL with tables that contain ~10 million rows. Each row has 50 columns and the table has 27 indexes. The data size of what you're describing doesn't seem so big. There are some arithmatics in the MySQL manual on how much disk space is used per row. If the tables you have are more or less read-only, i.e. if they're loaded once and then are just being used, have a look at compressing them with myisamchk. My experiences with MERGE tables have not been satisfactory if you want to run queries on them. Although the total size of the indexes was the same as in a single table the response times were orders of magnitudes different. You should write some test scripts and test the performance of the queries you're going to run before you go down that path. Although I don't think that you'll hit the 2 GB file size limit for the data file you might keep an eye on that. At least for the stock Linux 2.2.x kernels the limit exists. RedHat started to provide the -enterprise kernels which contain the large file system extensions. Shane Anderson wrote: I would like to know the limitations of Mysql with extremely large tables. I need to store 20+ million records. Each record would contain only 4-6 fields and would not be longer than 128 bytes of information. The records could be divided among several tables, but at what size is the performance hindered enough to need to split records between several smaller tables? Any information leading to an optimal solution would be greatly appreciated. Thanks Shane Anderson Software Developer Web Accessibility in Mind - http://www.WebAIM.org - 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 -- Frank Schröder [EMAIL PROTECTED] AirFlash Engineering Europe www.airflash.com AirFlash Inc. - 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
