Re: MySQL Performance with large data
The amount and type of data is less the issue than the amount and type of queries is :-) The machine you've described should be able to handle quite a bit of load, though, if well-tuned. On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you.
RE: MySQL Performance with large data
Thank you Johan. The table will be read only. There will be two steps - first to get the count using search conditions and then to get data from some columns based on those search conditions. The fields will be indexed as per search requirements. _ From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, November 24, 2009 9:56 PM To: Manish Ranjan (Stigasoft) Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance with large data The amount and type of data is less the issue than the amount and type of queries is :-) The machine you've described should be able to handle quite a bit of load, though, if well-tuned. On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you.
Re: MySQL Performance with large data
First off, for 4.000.000.000 records at 1867 byte per record, you're gonna need more storage than that (over 1.6 terabyte if I did my maths right) , unless you're using compressed tables - then your requirements will strongly depend on the actual data: text may easily compress to a factor ten, images (blobs?) almost not. Compressed tables will also speed up your I/O, in exchange for some more CPU load. On such a dataset, table scans are going to be geologically slow, so yes, good indexes will be your saviour :-) For speed, I'd also recommend that you get a RAID-10 setup. Go for a maximum amount of spindles, too - some form of SAN or locally-attached storage boxes with (relatively) small-capacity high-rpm disks. On Tue, Nov 24, 2009 at 5:39 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Thank you Johan. The table will be read only. There will be two steps - first to get the count using search conditions and then to get data from some columns based on those search conditions. The fields will be indexed as per search requirements. _ From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, November 24, 2009 9:56 PM To: Manish Ranjan (Stigasoft) Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance with large data The amount and type of data is less the issue than the amount and type of queries is :-) The machine you've described should be able to handle quite a bit of load, though, if well-tuned. On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you.
Re: MySQL Performance with large data
I second that RAID 10 with as many spindles as you can get recommendation.. for any kind of load, even read-only load, you are going to need it. Also, that 8G of RAM is paltry for the kind of dataset you propose. As already noted, the particulars will come down to the types and frequency of the queries (not to mention expected performance targets) but 4x64 CPUs churning that kind of data could really take advantage of a lot more RAM. - michael dykman On Tue, Nov 24, 2009 at 12:25 PM, Johan De Meersman vegiv...@tuxera.be wrote: First off, for 4.000.000.000 records at 1867 byte per record, you're gonna need more storage than that (over 1.6 terabyte if I did my maths right) , unless you're using compressed tables - then your requirements will strongly depend on the actual data: text may easily compress to a factor ten, images (blobs?) almost not. Compressed tables will also speed up your I/O, in exchange for some more CPU load. On such a dataset, table scans are going to be geologically slow, so yes, good indexes will be your saviour :-) For speed, I'd also recommend that you get a RAID-10 setup. Go for a maximum amount of spindles, too - some form of SAN or locally-attached storage boxes with (relatively) small-capacity high-rpm disks. On Tue, Nov 24, 2009 at 5:39 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Thank you Johan. The table will be read only. There will be two steps - first to get the count using search conditions and then to get data from some columns based on those search conditions. The fields will be indexed as per search requirements. _ From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, November 24, 2009 9:56 PM To: Manish Ranjan (Stigasoft) Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance with large data The amount and type of data is less the issue than the amount and type of queries is :-) The machine you've described should be able to handle quite a bit of load, though, if well-tuned. On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you. -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql performance issue. pls help..
hi all this is my innodb preference. i need to change anything for increasing the db performance.. innodb_additional_mem_pool_size -- 1048576 innodb_autoextend_increment -- 8 innodb_buffer_pool_awe_mem_mb -- 0 innodb_buffer_pool_size -- 8388608 innodb_checksums-- ON innodb_commit_concurrency -- 0 innodb_concurrency_tickets -- 500 innodb_data_file_path -- ibdata1:10M:autoextend innodb_data_home_dir-- innodb_doublewrite -- ON innodb_fast_shutdown-- 1 innodb_file_io_threads -- 4 innodb_file_per_table -- OFF innodb_flush_log_at_trx_commit -- 1 innodb_flush_method -- innodb_force_recovery -- 0 innodb_lock_wait_timeout-- 50 innodb_locks_unsafe_for_binlog -- OFF innodb_log_arch_dir -- innodb_log_archive -- OFF innodb_log_buffer_size -- 1048576 innodb_log_file_size-- 5242880 innodb_log_files_in_group -- 2 innodb_log_group_home_dir -- ./ innodb_max_dirty_pages_pct -- 90 innodb_max_purge_lag-- 0 innodb_mirrored_log_groups -- 1 innodb_open_files -- 300 innodb_support_xa -- ON innodb_sync_spin_loops -- 20 innodb_table_locks -- ON innodb_thread_concurrency -- 8 innodb_thread_sleep_delay -- 1 interactive_timeout -- 28800 thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Wed, Nov 18, 2009 at 11:50 AM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: HI experts I am facing performance issue for last couple of months. it taking more time to execute query.. developers created tables on myIsam and Innodb. i have a doubts whether we can use both storage same time.. i have 2 thinks in my minds to check this performance. 1. to check innodb and myisam db memory pools. 2. to check any index are required. based on the above to points i plan to investigate. so please help I am not familiar with mysql and first time iam working with developers. can any help to fix the problem. please assist me what i have to check now?. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com
Re: MySQL performance on LVM2
http://tldp.org/HOWTO/LVM-HOWTO/whatislvm.html On Tue, May 20, 2008 at 10:13 PM, Moon's Father [EMAIL PROTECTED] wrote: What is LVM? 2008/5/12 MarisRuskulis [EMAIL PROTECTED]: Hello! I'm wondering about MySQL LVM2 preformance, but cant found any comparisions. I know that there is some speed decrease with LVM, something about 30%. But how this decrease impacts overal MySQL performance? Now we are backuping replication slave server with mysqldump w full table locks, this takes some time. I think better solution is to use LVM snapshots, but this performance decrease really scares me. Has anyone some advices on this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- obed.org.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on LVM2
What is LVM? 2008/5/12 MarisRuskulis [EMAIL PROTECTED]: Hello! I'm wondering about MySQL LVM2 preformance, but cant found any comparisions. I know that there is some speed decrease with LVM, something about 30%. But how this decrease impacts overal MySQL performance? Now we are backuping replication slave server with mysqldump w full table locks, this takes some time. I think better solution is to use LVM snapshots, but this performance decrease really scares me. Has anyone some advices on this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: MySQL Performance Analysis tools
Bernd Jagla wrote: sar will give you some basic information about what happens on the system... (see e.g.: http://linux.die.net/man/1/sar)... Munin (http://munin.projects.linpro.no/) will generate graphs and stats over time for system usage (cpu, mem load, disk usage etc) and includes good support for mysql graphing throughput, queries, threads and slow queries. Example here: http://munin.ping.uio.no/ping.uio.no/dahl.ping.uio.no.html mark | MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? -Original Message- |From: thomas Armstrong [mailto:[EMAIL PROTECTED] |Sent: Monday, November 19, 2007 6:42 AM |To: mysql@lists.mysql.com |Subject: MySQL Performance Analysis tools | |Hi. | |Using MySQL on Linux, I'd like to analyze the performance and know how |resources (memory, threads) are used during a period of time. | |Do you know any tool to carry it out? Thank you very much. | |-- |MySQL General Mailing List |For list archives: http://lists.mysql.com/mysql |To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Performance Analysis tools
sar will give you some basic information about what happens on the system... (see e.g.: http://linux.die.net/man/1/sar)... -B |-Original Message- |From: thomas Armstrong [mailto:[EMAIL PROTECTED] |Sent: Monday, November 19, 2007 6:42 AM |To: mysql@lists.mysql.com |Subject: MySQL Performance Analysis tools | |Hi. | |Using MySQL on Linux, I'd like to analyze the performance and know how |resources (memory, threads) are used during a period of time. | |Do you know any tool to carry it out? Thank you very much. | |-- |MySQL General Mailing List |For list archives: http://lists.mysql.com/mysql |To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
Hi Daniel, We were using a software RAID-5 on top of hardware RAID-5 across 3 4-disk volume groups. (1 LUN from each array volume group built the software RAID-5). So we were able to lose 3 disks in a worst case scenario. It seems to me that neither RAID-1 or RAID-5 can lose more than one disk without losing data, please correct me if I'm wrong. Our data is 70% write/30% read, so the write latency is important. The filesystem is ZFS. Thanks again. Best Regards, Jason On 12/4/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 12/4/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi Daniel, Thank you very much for your help and advice. After some examination, we discovered a couple of things. It looks like our storage array layout was really bad for the IOPS MySQL was throwing at it, as a result the InnoDB transactions started to back-up under heavy load. Changing the array layout from RAID-5 to RAID-1 as well as moving the logs to their own spindles corrected the issue. Also, moving the InnoDB fsync log flushing interval from every commit to a 2 second interval helped dramatically. We found the storage was the problem by looking at SHOW INNODB STATUS while looking at the SCSI IOP latency. Does this sound reasonable to you? Disk IO is one of innodb's bottleneck anyway, but I doubt this could hurt performance as you suggested, making it unusable. You're the one with access to the system, and thus the only one who can test it and be sure ;) . Making a RAID 5 should increase read performance (if you calculate the best segment size), but the write operations would be not as fast as with a RAID 1, and you're risking data loss if more than one of your disks go away. I never trade security for speed, and if I were you I would check for another option. What's the most frequent operation (read/write) on your tables? Anyway, glad you solved your problem. Just out of curiosity, what is your filesystem? -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
On 12/4/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi Daniel, Thank you very much for your help and advice. After some examination, we discovered a couple of things. It looks like our storage array layout was really bad for the IOPS MySQL was throwing at it, as a result the InnoDB transactions started to back-up under heavy load. Changing the array layout from RAID-5 to RAID-1 as well as moving the logs to their own spindles corrected the issue. Also, moving the InnoDB fsync log flushing interval from every commit to a 2 second interval helped dramatically. We found the storage was the problem by looking at SHOW INNODB STATUS while looking at the SCSI IOP latency. Does this sound reasonable to you? Disk IO is one of innodb's bottleneck anyway, but I doubt this could hurt performance as you suggested, making it unusable. You're the one with access to the system, and thus the only one who can test it and be sure ;) . Making a RAID 5 should increase read performance (if you calculate the best segment size), but the write operations would be not as fast as with a RAID 1, and you're risking data loss if more than one of your disks go away. I never trade security for speed, and if I were you I would check for another option. What's the most frequent operation (read/write) on your tables? Anyway, glad you solved your problem. Just out of curiosity, what is your filesystem? -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
Hi Daniel, Thank you very much for your help and advice. After some examination, we discovered a couple of things. It looks like our storage array layout was really bad for the IOPS MySQL was throwing at it, as a result the InnoDB transactions started to back-up under heavy load. Changing the array layout from RAID-5 to RAID-1 as well as moving the logs to their own spindles corrected the issue. Also, moving the InnoDB fsync log flushing interval from every commit to a 2 second interval helped dramatically. We found the storage was the problem by looking at SHOW INNODB STATUS while looking at the SCSI IOP latency. Does this sound reasonable to you? Best Regards, Jason On 11/27/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 11/27/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. First, enable (if you don't have it already) logging, without any warnings or errors its kinda complicated to check for a real problem. From what you say, I can assume your server is probably eating memory on dead process or its trying to launch multiple threads to answer requests. Check the logs, check process (show processlist at mysql), check threads (ps on *ix), if there are dead process on the list, check your applications (web or standalone) and see if the connections are being closed correctly, decrease the wait_timeout and interactive_timeout variables to automatically clean this process, but be careful with those options, as they may kill your idle clients too fast. If there are many threads, check the variables that deal with thread launching, and your OS for limits on memory or cpu time. Also, while you're at it: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html http://dev.mysql.com/books/hpmysql-excerpts/ch06.html http://www.mysql.com/news-and-events/on-demand-webinars/mysql-performance-tuning.php Go for it. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
On 11/27/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. First, enable (if you don't have it already) logging, without any warnings or errors its kinda complicated to check for a real problem. From what you say, I can assume your server is probably eating memory on dead process or its trying to launch multiple threads to answer requests. Check the logs, check process (show processlist at mysql), check threads (ps on *ix), if there are dead process on the list, check your applications (web or standalone) and see if the connections are being closed correctly, decrease the wait_timeout and interactive_timeout variables to automatically clean this process, but be careful with those options, as they may kill your idle clients too fast. If there are many threads, check the variables that deal with thread launching, and your OS for limits on memory or cpu time. Also, while you're at it: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html http://dev.mysql.com/books/hpmysql-excerpts/ch06.html http://www.mysql.com/news-and-events/on-demand-webinars/mysql-performance-tuning.php Go for it. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
Jason, in addition to Daniel's suggestions, I'll throw this out there: I had a somewhat similar problem with a database I used to own, where a handful of very hard-hit tables would become progressively slower over time, despite the fact that (due to daily archiving and purging) they were not growing in size. For me, running OPTIMIZE operations periodically did the trick, keeping the tables performing fast. Daily wasn't sufficient, actually - I ended up optimizing the key tables every other hour, though that was probably more often than needed. I think the tables were becoming fragmented in memory, possibly along with the index data. This was with MyISAM, and I do not know whether performance would have improved with mysqld restarts, as we never really had occasion to restart mysqld except during major upgrades. HTH, Dan On 11/27/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 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]
Re: mysql performance / ndb 5.1 performance
What exactly is the performance problem you are seeing? Have you checked to make sure all your memory is being utilized? ie, not just grabbed by MySQL, but actually in use? -Sheeri On 5/24/06, Dan Trainor [EMAIL PROTECTED] wrote: Moritz Möller wrote: Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Moritz -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant Hi - Well, go ahead and do that and let us know how it turns out. There's a whole mailing list on cluster. Like I said, 5.1 (don't remember specifically which version) has file-based storage for cluster as an option. Good luck with that. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance / ndb 5.1 performance
Moritz Möller wrote: Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Hi Moritz! There is an alternative solution than NdbCluster, and that would be to set up a replicated environment, and have commodity hardware slaves supply the bulk of the SELECT operations, with the 8-core machine used as the master replication database. Your application server or web server would have to point SELECTs to the slaves for reporting purposes, and do writes to the master only. This is a cheap way to get scale-out performance from commodity hardware, and it is pretty customizable as far as the replicationi layout you would want... For instance, you could have your application server direct a certain category of queries to one slave, and another category to another slave, depending on traffic conditions. BTW, how many requests/sec are you averaging, and also, what is the percentage reads to writes in your database? You can get both answers from SHOW STATUS variables. Cheers, Jay -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql performance / ndb 5.1 performance
Hi Jay! Writes make up about 17% of the queries, and we average 4000 queries/sec. Querycache hit ratio is about 45% (QC seems to be a topic worth spending some time for... 45% does not look so good). We already tested replication, changing the database logic in the scripts to use the slaves until the first update operation, then use the master only (so the SELECT-after-UPDATE operations succeed). Main problem was that the slaves got out of sync after large updates (LOAD DATA INFILE for example), which we solved by doing large updates in small chunks (UPDATE LIMIT n while rows_matched n), but still, every 2-3 days the slaves got completely out of sync (duplicate key and other) :( Moritz -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 6:37 PM To: Moritz Möller Cc: 'Dan Trainor'; mysql@lists.mysql.com Subject: Re: mysql performance / ndb 5.1 performance Moritz Möller wrote: Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Hi Moritz! There is an alternative solution than NdbCluster, and that would be to set up a replicated environment, and have commodity hardware slaves supply the bulk of the SELECT operations, with the 8-core machine used as the master replication database. Your application server or web server would have to point SELECTs to the slaves for reporting purposes, and do writes to the master only. This is a cheap way to get scale-out performance from commodity hardware, and it is pretty customizable as far as the replicationi layout you would want... For instance, you could have your application server direct a certain category of queries to one slave, and another category to another slave, depending on traffic conditions. BTW, how many requests/sec are you averaging, and also, what is the percentage reads to writes in your database? You can get both answers from SHOW STATUS variables. Cheers, Jay -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
Re: mysql performance
Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql performance / ndb 5.1 performance
Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Moritz -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance / ndb 5.1 performance
Moritz Möller wrote: Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Moritz -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant Hi - Well, go ahead and do that and let us know how it turns out. There's a whole mailing list on cluster. Like I said, 5.1 (don't remember specifically which version) has file-based storage for cluster as an option. Good luck with that. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, The Server2 is not a slave. Seperate inserts were done on two different mysql servers. Thanks, Abdul. On Fri, 2006-04-28 at 12:01 -0400, Kishore Jalleda wrote: On 4/28/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? Thanks, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] Is the server2 a slave of server1, and is the same insert on server2 run directly on it or is itpart of a replicated query from server1. This came to my mind as u mentioned the two servers are geographically seperated .. Kishore Jalleda http://kjalleda.googlepages.com/projects This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, The insert queries are run from the localhost on both the machines. -Abdul On Fri, 2006-04-28 at 10:28 -0700, Atle Veka wrote: On Fri, 28 Apr 2006, Mohammed Abdul Azeem wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. Are these queries run from localhost or from a remote location? It's very unusual for an IDE based system to outperform SCSI. Atle - Flying Crocodile Inc, Unix Systems Administrator This email has been Scanned for Viruses! www.newbreak.com This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, The following is the hard disk specs for both the servers: server one: ( whose performance is good ) --- class: HD bus: IDE detached: 0 device: hda driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 - class: HD bus: IDE detached: 0 device: hdb driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 This has 2 SATA hard disks of 200 GB capacity. There is no RAID configured. server two: ( whose performance is poor ) -- class: HD bus: SCSI detached: 0 device: sda driver: ignore desc: Seagate ST3146707LC host: 0 id: 0 channel: 0 lun: 0 generic: sg0 - class: HD bus: SCSI detached: 0 device: sdb driver: ignore desc: Seagate ST3146707LC host: 0 id: 1 channel: 0 lun: 0 generic: sg1 This has 2 SCSI hard disks of capacity 146 GB each. There is no RAID configured. Thanks, Abdul. On Fri, 2006-04-28 at 12:52 +0200, living liquid | Christian Meisinger wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, I would like to make one correction. The server one has 2 IDE hard disks and not SATA hard disks. Thanks, Abdul. On Fri, 2006-04-28 at 16:33 +0530, Mohammed Abdul Azeem wrote: Hi, The following is the hard disk specs for both the servers: server one: ( whose performance is good ) --- class: HD bus: IDE detached: 0 device: hda driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 - class: HD bus: IDE detached: 0 device: hdb driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 This has 2 SATA hard disks of 200 GB capacity. There is no RAID configured. server two: ( whose performance is poor ) -- class: HD bus: SCSI detached: 0 device: sda driver: ignore desc: Seagate ST3146707LC host: 0 id: 0 channel: 0 lun: 0 generic: sg0 - class: HD bus: SCSI detached: 0 device: sdb driver: ignore desc: Seagate ST3146707LC host: 0 id: 1 channel: 0 lun: 0 generic: sg1 This has 2 SCSI hard disks of capacity 146 GB each. There is no RAID configured. Thanks, Abdul. On Fri, 2006-04-28 at 12:52 +0200, living liquid | Christian Meisinger wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? This email has been Scanned for Viruses! www.newbreak.com This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
On 4/28/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? Thanks, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Is the server2 a slave of server1, and is the same insert on server2 run directly on it or is itpart of a replicated query from server1. This came to my mind as u mentioned the two servers are geographically seperated .. Kishore Jalleda http://kjalleda.googlepages.com/projects
Re: ~Mysql performance~
On Fri, 28 Apr 2006, Mohammed Abdul Azeem wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. Are these queries run from localhost or from a remote location? It's very unusual for an IDE based system to outperform SCSI. Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance problems.
As others have suggested , turn your slow query log on in my.cnf , and set your long-query_time, and you can view your slow queries in the *.log file in your data dir, and then try to optimize them, you could also try mytop ( http://jeremy.zawodny.com/mysql/mytop/) , and check your queries in real time..., also check SHOW FULL PROCESSLIST to see what state the query's are in . Kishore Jalleda http://kjalleda.googlepages.com/projects On 3/29/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328
Re: mysql performance problems.
Is tat query is the problem ? Then turn on your slow queies and try optimizing those slow queries ? Post your queries and table description for further help :) --Praj On Wed, 29 Mar 2006 12:33:20 -0500 Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259 mysql/ 3055 mysql/snort 2184 mysql/snort_archive 1546 mysql/snort_archive/data.MYD 1546 mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74mysql/snort_archive/event.MYD 74mysql/snort/event.MYD 42mysql/snort_archive/data.MYI 42mysql/snort/data.MYI 40mysql/snort_archive/icmphdr.MYI 40mysql/snort/icmphdr.MYI 35mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:44 16:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348
Re: mysql performance problems.
Jacob, Raymond A Jr wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348 alert(s) to the Alert cache 2006-03-28, 16:10:02, Added 687 alert(s) to the Alert cache 2006-03-28, 16:15:00, Added 1168 alert(s) to the Alert cache 2006-03-28, 16:20:00, Added 1760 alert(s) to the Alert cache 2006-03-28, 16:25:00, Added 814 alert(s) to the Alert cache 2006-03-28, 16:30:01, Added
Re: mySQL Performance Problems - Help !!
my.cnf add this: skip-name-resolve under [mysqld] On 8/29/05, Callum McGillivray [EMAIL PROTECTED] wrote: Hi all, I'm pretty new to the list, so please be kind :) I'm having serious problems with our core mysql server. We are running a Dell Poweredge 2850 with dual Xeon 3.0 processors, RAID 5 and 1Gb memory. There are 3 main databases running on this machine, one is a freeradius database, one is for vpopmail and the other is for cacti (MRTG based tool). At the moment, we find the suddenly, our mail server or cacti server will establish several hundred connections to the database that just seem to be sit in an unauthenticated state. E.G. | 6452 | unauthenticated user | xxx.xxx.xxx.xxx:40923 | NULL | Connect | NULL | login | NULL | | 6453 | unauthenticated user | xxx.xxx.xxx.xxx:40924 | NULL | Connect | NULL | login | NULL | The server starts dragging it's heals and mail and cacti virtually stop altogether. There does not seem to be any unusual CPU / I/O or memory usage when this happens. Can anyone point me in the right direction here ? I'm more than happy to provide any configuration information that is required - but this is killing us and I need to know what is going on. We have run this server for several years (recently we upgraded the hardware without problems) and we have never experienced anything like this. I am at my wits end and not having had any formal training in mySQL servers, I simply don't know what I should be looking at next. Please - if anyone can give me any insight at all on this it would be MOST appreciated. Warm regards, Callum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Best Wishes, Xiaodong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL Performance Problems - Help !!
If you suddenly are spiking in unauthenticated connections, you may be the target of a network attack. This could be just a random probe, you may be a random target or someone may be targeting you. Although if someone were specifically targeting you, you would probably be down. I would check where the connections are coming from, especially if this amount of traffic is not typical. Perhaps you can set a filter on your router to limit the number of connections from that IP range or deny that range altogether. Normally you don't allow direct access to MySQL from the outside, access is usually done through SendMail/ Postifx or Apache (PHP, Perl, etc), but your requirements may be different. On Aug 29, 2005, at 4:58 AM, Callum McGillivray wrote: Hi all, I'm pretty new to the list, so please be kind :) I'm having serious problems with our core mysql server. We are running a Dell Poweredge 2850 with dual Xeon 3.0 processors, RAID 5 and 1Gb memory. There are 3 main databases running on this machine, one is a freeradius database, one is for vpopmail and the other is for cacti (MRTG based tool). At the moment, we find the suddenly, our mail server or cacti server will establish several hundred connections to the database that just seem to be sit in an unauthenticated state. E.G. | 6452 | unauthenticated user | xxx.xxx.xxx.xxx:40923 | NULL | Connect | NULL | login | NULL | | 6453 | unauthenticated user | xxx.xxx.xxx.xxx:40924 | NULL | Connect | NULL | login | NULL | The server starts dragging it's heals and mail and cacti virtually stop altogether. There does not seem to be any unusual CPU / I/O or memory usage when this happens. Can anyone point me in the right direction here ? I'm more than happy to provide any configuration information that is required - but this is killing us and I need to know what is going on. We have run this server for several years (recently we upgraded the hardware without problems) and we have never experienced anything like this. I am at my wits end and not having had any formal training in mySQL servers, I simply don't know what I should be looking at next. Please - if anyone can give me any insight at all on this it would be MOST appreciated. Warm regards, Callum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance
Carlos, Give us more details about our system: What are the table types you´re using? What are the configs in your my.cnf file? Ronan - Original Message - From: Carlos Augusto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 23, 2004 3:41 PM Subject: Mysql Performance Well that´s me again with a new problem. I am runnig another database with 7gb of data and this db can´t stop. All the time we have queries being inserted by a plain text file and all the time customers get information of this database. I obvserved that for a insert query is taking about 5 to 15 seconds for each insert. I saw some variables like: slow_query that output me a number of 1388 slow queries. I am new in mysql and database stuff and i have a task to improve mysql´s performance. I need help since in a simple Pentium 4 the same operations are almost 10 times higher(in time of inserting a querie) and this machine that is too slow for inserting is an dual xeon, 4gb ram and 500gb hard disk. I aprecciate if some one has a solution for improving this server performance. Thanks. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance
I do not believe that your problem is based on your hardware but ... Inserting data requires to reorganize your indexes. Please try to drop them (only for testing ;-) ) Hagen Carlos Augusto wrote: Well that´s me again with a new problem. I am runnig another database with 7gb of data and this db can´t stop. All the time we have queries being inserted by a plain text file and all the time customers get information of this database. I obvserved that for a insert query is taking about 5 to 15 seconds for each insert. I saw some variables like: slow_query that output me a number of 1388 slow queries. I am new in mysql and database stuff and i have a task to improve mysql´s performance. I need help since in a simple Pentium 4 the same operations are almost 10 times higher(in time of inserting a querie) and this machine that is too slow for inserting is an dual xeon, 4gb ram and 500gb hard disk. I aprecciate if some one has a solution for improving this server performance. Thanks. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance
I could see how the Pentium 4 may be faster for certain things. In some cases, older Pentiums with larger caches (i.e. 2MB) would outperform a Pentium 4. Rumor has it that MySQL loves level 2 cache, but don't tell PHP. But in this case, the Pentium 4 and Xeon I'm pretty sure both have 512K level 2 cache. However, the Xeon is designed as a server processor, meaning it can handle many tasks very well. Give the Xeon just one task and it's kind of mediocre. This is fairly evident with reviews comparing the Xeon to the AMD fx5X series. The AMD chip beats the Xeon pretty convincingly in single tasks. But the Xeon really shines when the system is doing multiple tasks and there may be a lot of context switching. The Pentium is like the AMD, it can do one task very well. The Xeon should perform much better then the Pentium under heavy load, multitasking loads. Also, I think the Xeon's interface to memory is DDR266, where the Pentium is DDR400. That can be a pretty significant speed difference when you are really pumping data around. What's the difference in hard drive speeds? 500GB doesn't really say much except that if it's just one drive, it's probably some sort of ATA with a slow rotation speed (i.e. 7200). An 18GB SCSI would probably outperform the 500GB ATA drive under heavy multitasking loads because of command queuing. Command queueing is just starting to become available in SATA drives. What kind of drive is in the Pentium? The cache on the drive matters too. So, that said, I'm assuming you didn't try the lowly Pentium 4 under the typical heavy load you expect. Now, since this is a MySQL discussion area, I think I should move away from hardware. You should serialize your insert queries if you haven't already. Meaning, run them one at a time instead of concurrently. And of course, before you do anything, make sure you've tweaked your MySQL configuration settings. On Nov 23, 2004, at 12:41 PM, Carlos Augusto wrote: Well that´s me again with a new problem. I am runnig another database with 7gb of data and this db can´t stop. All the time we have queries being inserted by a plain text file and all the time customers get information of this database. I obvserved that for a insert query is taking about 5 to 15 seconds for each insert. I saw some variables like: slow_query that output me a number of 1388 slow queries. I am new in mysql and database stuff and i have a task to improve mysql´s performance. I need help since in a simple Pentium 4 the same operations are almost 10 times higher(in time of inserting a querie) and this machine that is too slow for inserting is an dual xeon, 4gb ram and 500gb hard disk. I aprecciate if some one has a solution for improving this server performance. Thanks. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance
Brent, Would you be so kind to throw out some links to tweaking mysql to run to its full performance. I am googling right now for the answeres. Is there books you would recommend? THANKS On Tue, 2004-11-23 at 14:29 -0500, Brent Baisley wrote: I could see how the Pentium 4 may be faster for certain things. In some cases, older Pentiums with larger caches (i.e. 2MB) would outperform a Pentium 4. Rumor has it that MySQL loves level 2 cache, but don't tell PHP. But in this case, the Pentium 4 and Xeon I'm pretty sure both have 512K level 2 cache. However, the Xeon is designed as a server processor, meaning it can handle many tasks very well. Give the Xeon just one task and it's kind of mediocre. This is fairly evident with reviews comparing the Xeon to the AMD fx5X series. The AMD chip beats the Xeon pretty convincingly in single tasks. But the Xeon really shines when the system is doing multiple tasks and there may be a lot of context switching. The Pentium is like the AMD, it can do one task very well. The Xeon should perform much better then the Pentium under heavy load, multitasking loads. Also, I think the Xeon's interface to memory is DDR266, where the Pentium is DDR400. That can be a pretty significant speed difference when you are really pumping data around. What's the difference in hard drive speeds? 500GB doesn't really say much except that if it's just one drive, it's probably some sort of ATA with a slow rotation speed (i.e. 7200). An 18GB SCSI would probably outperform the 500GB ATA drive under heavy multitasking loads because of command queuing. Command queueing is just starting to become available in SATA drives. What kind of drive is in the Pentium? The cache on the drive matters too. So, that said, I'm assuming you didn't try the lowly Pentium 4 under the typical heavy load you expect. Now, since this is a MySQL discussion area, I think I should move away from hardware. You should serialize your insert queries if you haven't already. Meaning, run them one at a time instead of concurrently. And of course, before you do anything, make sure you've tweaked your MySQL configuration settings. On Nov 23, 2004, at 12:41 PM, Carlos Augusto wrote: Well that´s me again with a new problem. I am runnig another database with 7gb of data and this db can´t stop. All the time we have queries being inserted by a plain text file and all the time customers get information of this database. I obvserved that for a insert query is taking about 5 to 15 seconds for each insert. I saw some variables like: slow_query that output me a number of 1388 slow queries. I am new in mysql and database stuff and i have a task to improve mysql´s performance. I need help since in a simple Pentium 4 the same operations are almost 10 times higher(in time of inserting a querie) and this machine that is too slow for inserting is an dual xeon, 4gb ram and 500gb hard disk. I aprecciate if some one has a solution for improving this server performance. Thanks. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- - Vasiliy Boulytchev Colorado Information Technologies Inc. http://www.coinfotech.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance
No books to recommend, although there was a review of one on slashdot.org this morning, but you can start with the manual here: http://dev.mysql.com/doc/mysql/en/Server_parameters.html Other articles you may find helpful: http://www.f3n.de/doku/mysql/manual_10.html http://www.onlamp.com/pub/a/onlamp/2004/02/05/lamp_tuning.html http://www.databasejournal.com/features/mysql/article.php/3367871 On Nov 23, 2004, at 1:59 PM, Vasiliy Boulytchev wrote: Brent, Would you be so kind to throw out some links to tweaking mysql to run to its full performance. I am googling right now for the answeres. Is there books you would recommend? THANKS -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance
Vasiliy Boulytchev wrote: Brent, Would you be so kind to throw out some links to tweaking mysql to run to its full performance. I am googling right now for the answeres. Is there books you would recommend? THANKS High Performance Mysql (oreilly) MySQL enterprise solutions (wiley) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on FreeBSD compared to Linux
Hello Chris, I'm not familiar with super-smack, but it's compared to Apaches 'ab' which IMO is great for getting quick performance numbers but should in no way be trusted compared to a real world production environment. We run probably about 50+ dedicated mysql servers on various FreeBSD 4.X releases, and what we see again and again is that performance is not limited by CPU or memory; but by disk access time. My advice would be to stick with what you are most comfortable with, FreeBSD, and rather spend time on optimizing your database/code design. That's where you'll get the most gains from by far. :) I would also strongly advise you to upgrade to 15k SCSI drives as the speed improvements are well worth it. Regards, Atle - Flying Crocodile Inc, Junior Unix Systems Administrator On Fri, 21 May 2004, Chris Elsworth wrote: Forenote: I have no wish to start an OS debate. Hello, I'm in the fortunate position of having a dual 2.8GHz Xeon with 4G of ram and 4 10k SCSI disks (configured in RAID-10) to deploy as a new MySQL server. Since I'm a numbers freak, I've been running super-smack on it for the last few days to see how it stacks up. Tweaking various configs and kernel options, on any OS, obviously wins a few hundred/thousand queries per second, but I'm really quite surprised at one major difference. Optimisations and tweaking aside, FreeBSD 5.2.1-p6 on this hardware did well to achieve 17,000 queries per second, using super-smack's select-key.smack with the query cache turned on. Nothing I could do, and I spent days trying, got it much higher. Once I wiped this and tried Linux (both gentoo, with their patched-to-the-hilt 2.6.5 kernel, and Debian, with a stock 2.6.6 which had just been released by the time I installed) this figure jumped to 35,000 queries per second. Is FreeBSD really this crap for MySQL? I was quite horrified. FreeBSD 5 has a number of threading libraries, and I tried them all. LinuxThreads won (slightly, there wasn't much in it). I'm very much a FreeBSD fan and I'd quite like to keep FreeBSD on this machine before it goes live, but the performance pales in comparison to Linux. I had to do absolutely no tweaking to achieve 35,000 queries/sec in Linux. Has anyone else observed similar behaviour? Does anyone else have similar hardware with FreeBSD on? Have you fared any better? Thanks for any comments, -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on FreeBSD compared to Linux
On Sat, May 22, 2004 at 11:25:54PM -0500, mos wrote: Once I wiped this and tried Linux (both gentoo, with their patched-to-the-hilt 2.6.5 kernel, and Debian, with a stock 2.6.6 which had just been released by the time I installed) this figure jumped to 35,000 queries per second. First of all congratulations on getting queries that fast. :) I have a few questions if you have the time: 1) Are you using MyISAM tables? The test tables super-smack uses are in the test database and are indeed MyISAM. 2) How large is the table's row size? And the result size returned? Here's the create definition from super-smack (select-key.smack): CREATE TABLE http_auth ( username char(25) not null primary key, pass char(25), uid integer not null, gid integer not null ) If integers are 4 bytes I make that 58 bytes per row? And this is the query: SELECT * FROM http_auth WHERE username = '$word' So it fetches the entire row, 58 bytes. 3) Are you sorting the results? No. 4) What percentage are selects and updates? This particular test is 100% selects. super-smack also has an update-select.smack which does 1:1 updates and selects. 5) On average, how many rows are being returned for the query? 1 - the username is a unique key. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on FreeBSD compared to Linux
At 04:42 PM 5/21/2004, you wrote: Forenote: I have no wish to start an OS debate. Hello, Once I wiped this and tried Linux (both gentoo, with their patched-to-the-hilt 2.6.5 kernel, and Debian, with a stock 2.6.6 which had just been released by the time I installed) this figure jumped to 35,000 queries per second. First of all congratulations on getting queries that fast. :) I have a few questions if you have the time: 1) Are you using MyISAM tables? 2) How large is the table's row size? And the result size returned? 3) Are you sorting the results? 4) What percentage are selects and updates? 5) On average, how many rows are being returned for the query? The following question is open to anyone. Now I've been mulling over whether to get a dual processor machine at 4x the price of a single processor P4 3+ghz machine, or just get four P4 3+ghz machines (with hyperthreading) and replicate the data and use load balancing. I'll need to replicate the data eventually so why not use 4 machines instead of 1 expensive dual processor machine? The cost is about the same and I'll have redundancy. 99% of the activity will be reads and the writes can be batched back to the master database several minutes later. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on FreeBSD compared to Linux
mos wrote: At 04:42 PM 5/21/2004, you wrote: Forenote: I have no wish to start an OS debate. Hello, Once I wiped this and tried Linux (both gentoo, with their patched-to-the-hilt 2.6.5 kernel, and Debian, with a stock 2.6.6 which had just been released by the time I installed) this figure jumped to 35,000 queries per second. First of all congratulations on getting queries that fast. :) I have a few questions if you have the time: 1) Are you using MyISAM tables? 2) How large is the table's row size? And the result size returned? 3) Are you sorting the results? 4) What percentage are selects and updates? 5) On average, how many rows are being returned for the query? The following question is open to anyone. Now I've been mulling over whether to get a dual processor machine at 4x the price of a single processor P4 3+ghz machine, or just get four P4 3+ghz machines (with hyperthreading) and replicate the data and use load balancing. I'll need to replicate the data eventually so why not use 4 machines instead of 1 expensive dual processor machine? The cost is about the same and I'll have redundancy. 99% of the activity will be reads and the writes can be batched back to the master database several minutes later. For database stuff, I can strongly recommend giving AMD's gear a solid look-in. Their architectural differences really do make for excellent database performance (Athlon XP or their 64 bit product line). Best regards, Chris Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on FreeBSD compared to Linux
Chris Elsworth wrote: Forenote: I have no wish to start an OS debate. Hello, I'm in the fortunate position of having a dual 2.8GHz Xeon with 4G of ram and 4 10k SCSI disks (configured in RAID-10) to deploy as a new MySQL server. Since I'm a numbers freak, I've been running super-smack on it for the last few days to see how it stacks up. Tweaking various configs and kernel options, on any OS, obviously wins a few hundred/thousand queries per second, but I'm really quite surprised at one major difference. Optimisations and tweaking aside, FreeBSD 5.2.1-p6 on this hardware did well to achieve 17,000 queries per second, using super-smack's select-key.smack with the query cache turned on. Nothing I could do, and I spent days trying, got it much higher. Once I wiped this and tried Linux (both gentoo, with their patched-to-the-hilt 2.6.5 kernel, and Debian, with a stock 2.6.6 which had just been released by the time I installed) this figure jumped to 35,000 queries per second. Is FreeBSD really this crap for MySQL? I was quite horrified. FreeBSD 5 has a number of threading libraries, and I tried them all. LinuxThreads won (slightly, there wasn't much in it). I'm very much a FreeBSD fan and I'd quite like to keep FreeBSD on this machine before it goes live, but the performance pales in comparison to Linux. I had to do absolutely no tweaking to achieve 35,000 queries/sec in Linux. Has anyone else observed similar behaviour? Does anyone else have similar hardware with FreeBSD on? Have you fared any better? Thanks for any comments, Chris: It looks like FreeBSD was using only one CPU from your numbers. Try the test with only 1 thread and a lot of iterations to avoid the influence of overhead. I know very little about FreeBSD, but one thing I would check is if the kernel was configured to be SMP-cabaple/enabled. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on FreeBSD compared to Linux
On Fri, May 21, 2004 at 05:18:09PM -0600, Sasha Pachev wrote: It looks like FreeBSD was using only one CPU from your numbers. Try the test with only 1 thread and a lot of iterations to avoid the influence of overhead. I know very little about FreeBSD, but one thing I would check is if the kernel was configured to be SMP-cabaple/enabled. Both CPUs were definitely in use; since these are Xeons with HyperThreading, there's effectively 4 logical CPUs for the OS to use. The OS sees them all; if I only put one to use then top (over time) shows just 25% CPU in use which is correct. I generally run super-smack with 4 or 5 clients to exploit the entire CPUs. The super-smack results scaled roughly linearly up to 4 clients and then held fairly static as the number of clients grew beyond that, so I'm fairly sure all the CPUs are being used - just not as well as they should be. I didn't record firm numbers, I'd have get FreeBSD back on to get some firm results for that. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Tuning?
So your application tracks incoming HTTP-GETS. When you say that it's not able to capture all 1000 entries, what do you mean? Does an exception get thrown? Do some of the HTTP-GETs just not show in the database? You need to provide alot more information: Do all the HTTP-GETs happen on the same connection? How long do the HTTP-GETs take to process? 10 seconds? What hardware are you running on? CPU, disk, memory. Is the machine dedicated to MySQL? What's the MySQL CPU load on the above hardware during your test What table type (InnoDB, MyISAM, BDB, etc)? What tuning have you done to the my.cnf, and are you sure that MySQL is using that my.cnf (ie is it in the correct location)? - Original Message - From: mysql [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 15, 2004 9:23 AM Subject: MySQL Performance Tuning? Hi Gurus, I'm currently building an application which is expected to take very high loads. What the app does is essence is to 'log' and incoming entry into MySQL, do something then updates the 'log' entry. To test MySQL in handling high load, I used siege on another server to send 1000 HTTP GET requests to my php script which then does as described above. The results that I'm getting is not encouraging as it seems that MySQL is not able to capture the 1000 entries. I've tried doing some of the tuning from the net but so far to no avail. Does anyone know what is the critical tuning method needed for MySQL to be able to handle loads like this? Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Tuning?
Hi David, Thanks for you prompt reply. I'll try to answer your questions to the best I can currently. Please see my replies below. David Griffiths wrote: So your application tracks incoming HTTP-GETS. When you say that it's not able to capture all 1000 entries, what do you mean? Does an exception get thrown? Do some of the HTTP-GETs just not show in the database? All I can verify currently that all the 1000 entries reached the server fine (determined through viewing the apache logs). Whether some of the entries got an exception from MySQL I can't determine. Is there some MySQL logs like that of apache where I can look at transactions? You need to provide alot more information: Do all the HTTP-GETs happen on the same connection? If I get you correctly, all the HTTP-GETS happen on different connections. How long do the HTTP-GETs take to process? 10 seconds? takes less than a second as for testing, I've stripped the code to just log down into the database without even doing any processing What hardware are you running on? CPU, disk, memory. Is the machine dedicated to MySQL? Hmm CPU speed and harddisk, I can't remember but memory is 1GB. Also during the test, the server (Linux 9) is not doing anything that might hog up memory or cpu usage. What's the MySQL CPU load on the above hardware during your test From what I observed, it's nowhere near 50% What table type (InnoDB, MyISAM, BDB, etc)? I would think it is MyISAM currently What tuning have you done to the my.cnf, and are you sure that MySQL is using that my.cnf (ie is it in the correct location)? Tunings that I have tweaked cuurently are 1. join_buffer_size 131072 2. key_buffer_size 16773120 3. max_connections 300 Hope some of these info helps. Thanks! - Original Message - From: mysql [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 15, 2004 9:23 AM Subject: MySQL Performance Tuning? Hi Gurus, I'm currently building an application which is expected to take very high loads. What the app does is essence is to 'log' and incoming entry into MySQL, do something then updates the 'log' entry. To test MySQL in handling high load, I used siege on another server to send 1000 HTTP GET requests to my php script which then does as described above. The results that I'm getting is not encouraging as it seems that MySQL is not able to capture the 1000 entries. I've tried doing some of the tuning from the net but so far to no avail. Does anyone know what is the critical tuning method needed for MySQL to be able to handle loads like this? Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Tuning?
Hi! We are a hardware store(similiar to usa's home depot) here in venezuela. We have 14 stores that moves a very heavy load of traffic. Many of our retails runs in a home brew POS that uses mysql. Then sales are uploaded to a bigger in-store server and then move to the central office. The prices, promotions and offers are constantly replicated to the stores, so imagine it has a lot of traffic. For performance, the single most important factor seems to be a fast io hardware and a good properly configured os, instead of more ram and processor. We currently compile all our software, and run our db in linux boxes(you didn't specify a os) We compile our db using -O3 -march=i686 -mcpu=i686 -funroll-loops -fomit-frame-pointer -fno-rtti -fno-exceptions -felide-constructors Our servers are compiled static, it seems to be a little faster than a shared server, but your millage may actually vary. Our kernels are also compiled in a similar fashion. We specially add similar cflags and cxxflags to the above, modifing the Makefile in the kernel tree. Also a pair of tricks are also used inside the kernel, we rise the SEMNI value from 128 to 512 and the LOG_BUF_LEN from 16384 to 65536 in each server. We also tweak a few runtime kernel variables, chages in the sysctl.conf: added: # Improve file system performance vm.bdflush = 100 1200 128 512 500 6000 500 0 0 # Improve virtual memory performance vm.buffermem = 80 10 60 # Improve and better control swapping into the system vm.freepages = 383 766 1149 # Increase swap bandwidth system performance vm.kswapd = 1024 32 16 # Increase number of pages kernel reads in at once vm.page-cluster = 16 # Improve files memory mapping and generic caching vm.pagecache = 8 25 85 # Improve number of page tables keeps in a per-processor cache vm.pagetable_cache = 35 60 # Improve the number of open files fs.file-max = 16384 We also compile and use our own ssl binaries, we do not use the distros version. One thing to note is the fact that poor code will produce poor performance so check your code and your sql. We do not run mysql and apache on the same server, you shoudn't do that neither. What is your set up? OS? Are you using any speed-up technology like zend optimizer with your server? PHP can be a big resource eater, so find out where is the bottle neck, if it is mysql, calculate well your load and create a propper configuration in your my.cnf, add a good balance of harware and a few of the tweaks mentioned above. May i recommend Gehard Mourani's book on Securing and Optimizing RH Linux? it is available at tldp.org, for free under guides, it's intended for RH only, but it also works on suse and debian(and any other) if you know the way around your distro. Hope this works, regards On Thu, 2004-01-15 at 13:23, mysql wrote: Hi Gurus, I'm currently building an application which is expected to take very high loads. What the app does is essence is to 'log' and incoming entry into MySQL, do something then updates the 'log' entry. To test MySQL in handling high load, I used siege on another server to send 1000 HTTP GET requests to my php script which then does as described above. The results that I'm getting is not encouraging as it seems that MySQL is not able to capture the 1000 entries. I've tried doing some of the tuning from the net but so far to no avail. Does anyone know what is the critical tuning method needed for MySQL to be able to handle loads like this? Thank you very much! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 | ||geek by nature - linux by choice | |...| ... Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. Rich Cook
Re: MySQL Performance Tuning?
From the sounds of it, it's not clear if the database is the issue. Of the 1000 records, did the first 100 get in, then no other ones. Or were the missed-messages intermittent - some were missing in the middle? You need to figure out if the MySQL connections are throwing an exception. One thing I would check first is the maximum number of connections in your my.cnf file - maybe you haven't set it? It defaults to 100. If you are trying to get 1000 connections in a second, you will need at least 1000 connections. Add this to your my.cnf set-variable = max_connections=1000 David Hi David, Thanks for you prompt reply. I'll try to answer your questions to the best I can currently. Please see my replies below. David Griffiths wrote: So your application tracks incoming HTTP-GETS. When you say that it's not able to capture all 1000 entries, what do you mean? Does an exception get thrown? Do some of the HTTP-GETs just not show in the database? All I can verify currently that all the 1000 entries reached the server fine (determined through viewing the apache logs). Whether some of the entries got an exception from MySQL I can't determine. Is there some MySQL logs like that of apache where I can look at transactions? You need to provide alot more information: Do all the HTTP-GETs happen on the same connection? If I get you correctly, all the HTTP-GETS happen on different connections. How long do the HTTP-GETs take to process? 10 seconds? takes less than a second as for testing, I've stripped the code to just log down into the database without even doing any processing What hardware are you running on? CPU, disk, memory. Is the machine dedicated to MySQL? Hmm CPU speed and harddisk, I can't remember but memory is 1GB. Also during the test, the server (Linux 9) is not doing anything that might hog up memory or cpu usage. What's the MySQL CPU load on the above hardware during your test From what I observed, it's nowhere near 50% What table type (InnoDB, MyISAM, BDB, etc)? I would think it is MyISAM currently What tuning have you done to the my.cnf, and are you sure that MySQL is using that my.cnf (ie is it in the correct location)? Tunings that I have tweaked cuurently are 1. join_buffer_size 131072 2. key_buffer_size 16773120 3. max_connections 300 Hope some of these info helps. Thanks! - Original Message - From: mysql [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, January 15, 2004 9:23 AM Subject: MySQL Performance Tuning? Hi Gurus, I'm currently building an application which is expected to take very high loads. What the app does is essence is to 'log' and incoming entry into MySQL, do something then updates the 'log' entry. To test MySQL in handling high load, I used siege on another server to send 1000 HTTP GET requests to my php script which then does as described above. The results that I'm getting is not encouraging as it seems that MySQL is not able to capture the 1000 entries. I've tried doing some of the tuning from the net but so far to no avail. Does anyone know what is the critical tuning method needed for MySQL to be able to handle loads like this? Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
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]
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 questions
Hi. On Thu 2002-09-05 at 09:09:07 -0500, [EMAIL PROTECTED] wrote: For clarity sake, assume the following: Red Hat Linux 7.1 2.4.8 kernel MySQL 3.23.42 MyISAM databases 3GB RAM P3/700 x 4 15GB database spanned across ~200 tables Key_reads / Key_read_request = 0.00059875 Key_write / Key_write_requests = 0.81324723 1) Since the key_buffer variable defines the total amount of indexed data in memory, then the key_buffer size and index size of my tables should be releated. Total index size of all relevant tables is 440MB. Should the key_buffer size be , , = or a percentage of this number? Usually it makes no sense to make key_buffer greater than the index size, except if you have relatively fast-growing tables. There is no percentage to be recommended, because the ideal index size depends on your usage pattern. For example, for one of my databases (14GB data), the index files are 3GB. With a key_buffer of 500MB, the key efficiency is 97.48% (i.e. Key_reads / Key_read_request = 0.0252), because old rows are seldom read. Your current setting has a really suffiently large key_buffer (efficiency 99.94%). OTOH, if this is a MySQL-only machine, 3GB are plenty and 100MB more or less used do not really matter (regarding free memory), so I would simply set it to use about 400MB are forget about it. If memory is would be tight, I would usually set it so some guessed value (e.g. half of the index size, here: 200MB), and look what the efficiency is afterwards. Repeat, until you have found an effiency / memory usage ratio you feel comfortable with. 2) How does performance directly relate to the number of open tables? Yes, these open tables take memory, and a FLUSH TABLES would adequately free this memory, The memory blocked by open tables is only marginal, I think. With 200 tables, I see no reason to not set the table cache so that it allows all tables to be opened. but then the tables that are used most frequently would need to be reopened. No. The tables that are used less frequently would need to be reopened. MySQL would try hard to not close the most frequently opened tables. Therefore it is the same as with key_buffer above. If you want to trim this down, play around until you find a setting that has a reasonable rate of (re-)opening tables. Am I looking at memory vs. CPU in this case? Provided I have enough RAM, wouldn't it make sense to leave all the tables open? I don't think, that the main issue you deal with is memory. I may be wrong here, but I think the most restricting limit is the OS here, i.e. how many files may be open (up to 3 per table, IIRC) at one time and how your OS can cope with a lot of open files resp. opening lot of files. I'm sure I'll think of more later on. By the way, here are the pages I used to ramp up my performance knowledge: http://www.mysql.com/doc/en/SHOW_VARIABLES.html http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:19873 http://www.linux-mag.com/2001-12/mysql_01.html Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL performance questions
Benjamin Pflugmann wrote: OTOH, if this is a MySQL-only machine, 3GB are plenty and 100MB more or less used do not really matter (regarding free memory), so I would simply set it to use about 400MB are forget about it. Remember to actually benchmark your differences too if possible (within your applications), since its quite possible that OS-level disk caching is making up for less-than-perfect memory settings. People often forget that their OSs can do very efficient write-through caching ... :-) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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 questions
First of all, I forgive the rather lengthy post. Thanks for the repl(y|ies) Benjamin. Decreasing the key_buffer should be my first step. Back to the questions: 3) I'm somewhat at a loss for this one and perhaps the answer is more obvious than not. I have 257 total tables from my main DB and mysql. I figured this by a ls -al var/ | grep -c MYD. How can I possibly have 512 (which is also the table_cache value) open tables? | Open_tables | 512| | Open_files | 776| | Open_streams | 0 | | Opened_tables| 1499 | I also see Opened_tables is 1499, which is 3x the number of open tables. I would consider this number average and not think about increasing table_cache. OTOH, one of my slaves has this: | Open_tables | 256| | Open_files | 459| | Open_streams | 0 | | Opened_tables| 3532 | Where 256 is the table_cache limit. I'd consider the opened tables to be big, and would probably want to increase the table_cache size. 4) How can I reliably determine how much RAM MySQL is indeed using? From the manual: ps and other system status programs may report that mysqld uses a lot of memory ps shows each instance of MySQL using 91760k of RAM x ~40 processes = 3.6GB, which is how much real RAM I have. The rest of the swap is probably due to the rest of the system processes running. To further assistance here, here are my settings for the master, which is the DB that's swapping: First, we start with variables... back_log| 50 basedir | /usr/local/mysql/ binlog_cache_size | 32768 character_set | latin1 character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 esto nia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 concurrent_insert | ON connect_timeout | 5 datadir | /usr/local/mysql//var/ delay_key_write | ON delayed_insert_limit| 100 delayed_insert_timeout | 300 delayed_queue_size | 1000 flush | OFF flush_time | 0 have_bdb| NO have_gemini | NO have_innodb | NO have_isam | YES have_raid | NO have_ssl| NO init_file | interactive_timeout | 28800 join_buffer_size| 131072 key_buffer_size | 805302272 language| /usr/local/mysql/share/mysql/english/ large_files_support | ON locked_in_memory| OFF log | OFF log_update | OFF log_bin | ON log_slave_updates | OFF log_long_queries| OFF long_query_time | 10 low_priority_updates| OFF lower_case_table_names | 0 max_allowed_packet | 10484736 max_binlog_cache_size | 4294967295 max_binlog_size | 1073741824 max_connections | 150 max_connect_errors | 10 max_delayed_threads | 20 max_heap_table_size | 16777216 max_join_size | 4294967295 max_sort_length | 1024 max_user_connections| 0 max_tmp_tables | 32 max_write_lock_count| 4294967295 myisam_recover_options | 0 myisam_max_extra_sort_file_size | 256 myisam_max_sort_file_size | 2047 myisam_sort_buffer_size | 67108864 net_buffer_length | 16384 net_read_timeout| 30 net_retry_count | 10 net_write_timeout | 60 open_files_limit| 0 port| 3306 protocol_version| 10 record_buffer | 2093056 record_rnd_buffer | 2093056 query_buffer_size | 0 safe_show_database | OFF server_id | 1 slave_net_timeout | 3600 skip_locking| ON skip_networking | OFF skip_show_database | OFF slow_launch_time| 2 socket | /tmp/mysql.sock sort_buffer | 2097144 sql_mode| 0 table_cache | 512 table_type | MYISAM thread_cache_size | 8 thread_stack| 65536 transaction_isolation | READ-COMMITTED timezone| CDT tmp_table_size | 33554432 tmpdir | /tmp/ version | 3.23.42-log wait_timeout| 28800 Now we go
Re: MySQL performance questions
Hi again. :-) On Thu 2002-09-05 at 14:18:10 -0500, [EMAIL PROTECTED] wrote: [...] 3) I'm somewhat at a loss for this one and perhaps the answer is more obvious than not. I have 257 total tables from my main DB and mysql. I figured this by a ls -al var/ | grep -c MYD. How can I possibly have 512 (which is also the table_cache value) open tables? Because each concurrent access needs an own entry, e.g. self-joins and parallel accesses. See http://www.mysql.com/doc/en/Table_cache.html for more info. [...] | Open_tables | 512| | Opened_tables| 1499 | I also see Opened_tables is 1499, which is 3x the number of open tables. I would consider this number average and not think about increasing table_cache. Depends on the time period. According to your quote below, your server is running for about 23 days (uptime 2020009 secs). That makes 1 opened table every 2 hours. I wouldn't care about it, before this figure goes well beyond 1 every minute. [...] OTOH, one of my slaves has this: | Open_tables | 256| | Opened_tables| 3532 | Where 256 is the table_cache limit. I'd consider the opened tables to be big, and would probably want to increase the table_cache size. See above. 4) How can I reliably determine how much RAM MySQL is indeed using? From the manual: ps and other system status programs may report that mysqld uses a lot of memory ps shows each instance of MySQL using 91760k of RAM x ~40 processes = 3.6GB, which is how much real RAM I have. You are running Linux 2.4.x. Linux displays threads as processes, and with every thread the data of the whole process, i.e. it's just 91760k for all threads together. But, of course, the number 91760k seems a bit out of place, as your key cache alone already uses 400MB. Would you mind to quote some lines from ps? And some info about mem usage: http://www.mysql.com/doc/en/Memory_use.html [...] To further assistance here, here are my settings for the master, which is the DB that's swapping: [...] myisam_sort_buffer_size | 67108864 This is a per-connection buffer. Although it's only allocated when needed, you could run into troubles if several concurrent connections need to sort something. [...] record_buffer | 2093056 record_rnd_buffer | 2093056 sort_buffer | 2097144 There are also per-connection buffers. Not really something wrong with them, but note that if you happen to come to your connection maximum (250), these alone would eat up to 6MB*250 = 1.5GB. I would not change them now; just wanted that you aware of it. All other settings look sane to me (compared with one of my servers). Now we go to extended-status: [...] | Handler_delete | 15398143 | | Handler_read_first | 4561849| | Handler_read_key | 78715268 | | Handler_read_next| 2518057153 | | Handler_read_prev| 2759123| | Handler_read_rnd | 51014466 | | Handler_read_rnd_next| 639269479 | | Handler_update | 6824117| | Handler_write| 25044236 | These numbers give me the impression that your queries could benefit from some additional indexes. There seem to be a lot of ranged and full table reads. But - being ignorant of your real usage - I could be dead wrong, of course. | Key_blocks_used | 433327 | Btw, this means, as we already discussed, that only 433327KB of your key_cache are used at all. All numbers look sane to me. [...] And lastly, free.. total used free sharedbuffers cached Mem: 37031803694624 8556 0 12800 2158160 -/+ buffers/cache:15236642179516 Swap: 20964401582784 513656 That is plain strange. You have 2179516KB virtually free (i.e. Linux could free it, if it wanted), but Linux decided anyhow to swap out 1582784KB. I cannot help, but seems dead wrong to me. It could be due to the VM swapping problem I mentioned earlier. Also, it says that about 1.5GB (+1.5GB swap) are in real use, though considering the stat numbers from MySQL you provided above, I would not expect it to use more than about 800MB. Are you running other services on that machine? Could you provide the full output of ps? I cannot get rid of the feeling that we are missing something significant here (maybe I am blind at the moment ;). Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql performance problem
- Original Message - From: Supriya Shiyekar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 10:59 PM Subject: MySql performance problem Hi, We are having strange performance problem with mysql on Solaris.Our application makes JDBC calls to mysql database which resides locally on the machine. It takes 3 minutes to execute a piece of code(which involves select, insert and update queries) on a Windows machine but an hour on Solaris. Both databases(on windows and solaris) have exactly same database structure including indexes and table sizes. We are using mysql 3.23.44. Is this something to do with the memory allocated to mysql on Solaris machine ? Do we need to check any server parameters for mysql ? What is the setup of the two machines? (CPU/RAM/Disk/SCSI/IDE, etc), and what configuration parameters are you using now? Without this, you're asking us to guess, more information from you would get a better answer. -Mark - 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 problem
Hi Mark, Thanks for the reply. I have got more information. 1. We set only the port, basedir and datadir parameters for mysql on both Windows and Solaris machines. All other configuration parameters are default. 2. Setup for Windows Machine :- OS - Windows 2000 professional SP2 RAM - 196 MB CPU - Pentium III 600MHz Disk Drive - C:\- 14 GB No SCSI Setup for Solaris Server :- System Configuration: Sun Microsystems sun4u 8-slot Sun Enterprise E4500/E5500 System clock frequency: 84 MHz Memory size: 2048Mb = CPUs = Run Ecache CPUCPU Brd CPU Module MHz MBImpl. Mask --- --- --- - -- -- 0 0 0 336 4.0 US-II2.0 0 1 1 336 4.0 US-II2.0 2 4 0 336 4.0 US-II2.0 2 5 1 336 4.0 US-II2.0 = Memory Intrlv. Intrlv. Brd Bank MBStatus Condition Speed Factor With --- - --- -- - --- --- 0 01024 Active OK 60ns2-way A 2 01024 Active OK 60ns2-way A = IO Cards = Bus Freq Brd Type MHz Slot Name Model --- -- 1 SBus 25 0 fcaw/sd (block) FCW 1 SBus 25 1 fcaw/sd (block) FCW 1 SBus 25 2 SUNW,qfe SUNW,sbus-qfe 1 SBus 25 2 SUNW,qfe SUNW,sbus-qfe 1 SBus 25 2 SUNW,qfe SUNW,sbus-qfe 1 SBus 25 2 SUNW,qfe SUNW,sbus-qfe 1 SBus 25 3 SUNW,hme 1 SBus 25 3 SUNW,fas/sd (block) 1 SBus 2513 SUNW,socal/sf (scsi-3) 501-3060 No failures found in System === No System Faults found == Thanks for the help, Supriya. - Original Message - From: Mark Matthews [EMAIL PROTECTED] To: Supriya Shiyekar [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 7:02 AM Subject: Re: MySql performance problem - Original Message - From: Supriya Shiyekar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 10:59 PM Subject: MySql performance problem Hi, We are having strange performance problem with mysql on Solaris.Our application makes JDBC calls to mysql database which resides locally on the machine. It takes 3 minutes to execute a piece of code(which involves select, insert and update queries) on a Windows machine but an hour on Solaris. Both databases(on windows and solaris) have exactly same database structure including indexes and table sizes. We are using mysql 3.23.44. Is this something to do with the memory allocated to mysql on Solaris machine ? Do we need to check any server parameters for mysql ? What is the setup of the two machines? (CPU/RAM/Disk/SCSI/IDE, etc), and what configuration parameters are you using now? Without this, you're asking us to guess, more information from you would get a better answer. -Mark - 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 problem
Hi Mark, Thanks for the reply. I have got more information. 1. We set only the port, basedir and datadir parameters for mysql on both Windows and Solaris machines. All other configuration parameters are default. 2. Setup for Windows Machine :- OS - Windows 2000 professional SP2 RAM - 196 MB CPU - Pentium III 600MHz Disk Drive - C:\- 14 GB No SCSI Setup for Solaris Server :- System Configuration: Sun Microsystems sun4u 8-slot Sun Enterprise E4500/E5500 System clock frequency: 84 MHz Memory size: 2048Mb = CPUs = Run Ecache CPUCPU Brd CPU Module MHz MBImpl. Mask --- --- --- - -- -- 0 0 0 336 4.0 US-II2.0 0 1 1 336 4.0 US-II2.0 2 4 0 336 4.0 US-II2.0 2 5 1 336 4.0 US-II2.0 = Memory Intrlv. Intrlv. Brd Bank MBStatus Condition Speed Factor With --- - --- -- - --- --- 0 01024 Active OK 60ns2-way A 2 01024 Active OK 60ns2-way A = IO Cards = Bus Freq Brd Type MHz Slot Name Model --- -- 1 SBus 25 0 fcaw/sd (block) FCW 1 SBus 25 1 fcaw/sd (block) FCW 1 SBus 25 2 SUNW,qfe SUNW,sbus-qfe 1 SBus 25 2 SUNW,qfe SUNW,sbus-qfe 1 SBus 25 2 SUNW,qfe SUNW,sbus-qfe 1 SBus 25 2 SUNW,qfe SUNW,sbus-qfe 1 SBus 25 3 SUNW,hme 1 SBus 25 3 SUNW,fas/sd (block) 1 SBus 2513 SUNW,socal/sf (scsi-3) 501-3060 No failures found in System === No System Faults found == Thanks for the help, Supriya. - Original Message - From: Mark Matthews [EMAIL PROTECTED] To: Supriya Shiyekar [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 7:02 AM Subject: Re: MySql performance problem - Original Message - From: Supriya Shiyekar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 10:59 PM Subject: MySql performance problem Hi, We are having strange performance problem with mysql on Solaris.Our application makes JDBC calls to mysql database which resides locally on the machine. It takes 3 minutes to execute a piece of code(which involves select, insert and update queries) on a Windows machine but an hour on Solaris. Both databases(on windows and solaris) have exactly same database structure including indexes and table sizes. We are using mysql 3.23.44. Is this something to do with the memory allocated to mysql on Solaris machine ? Do we need to check any server parameters for mysql ? What is the setup of the two machines? (CPU/RAM/Disk/SCSI/IDE, etc), and what configuration parameters are you using now? Without this, you're asking us to guess, more information from you would get a better answer. -Mark - 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 issues - PLEASE ADVICE!!
[snip] I am having performance problems with my MySQL installation - what would be an appropriate channel for requesting help to such a problem? [/snip] Repost your original concern and I will see if I can help. Jay - 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 issues - PLEASE ADVICE!!
Hi, Don't give up.Try againso ... what's your problem? More details about your problem are welcome. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: webmaster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 07, 2002 5:13 PM Subject: MySQL performance issues - PLEASE ADVICE!! I am having performance problems with my MySQL installation - what would be an appropriate channel for requesting help to such a problem? I have posted to this list twice and another one as well. Volumes are very high on using MySQL/standard SQL questions, but not an single suggestion has been submitted to my request. Is the only valid alternative to go for an annual support deal with MySQL.org? My site can't afford that. Lots of enthusiastic 'community' members will be disappointed if it should go down because I cant afford proper support. Please respond if you have any suggestion where it might be wise to direct my request! All the best; Eivind :-) - 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 on Dual Processor machine
Hi Jay, There are other things that could be affecting the performance besides mysql. If both machines are idle. The long query should run about the same on the new machine. One thread will only run on one processor no matter how you compile mysql. For compiling MySQK on FreeBSD use the ports or see http://www.mysql.com/doc/F/r/FreeBSD.html . I would suspect other things first such as the my.cnf configuration (show variables) or has the kernel been optimised on the old box. Did you check kernel configurations and disk subsystems? Also the default process size on FreeBSD is 256Meg, so more memory won't help much unless you use it in my.cnf and the kernel configuration allows it! Hope this helps, Ken - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 30, 2002 2:04 PM Subject: MySQL Performance on Dual Processor machine Howdy all, We have a curious situation here, and I was wondering if any of you have encountered this before. We have MySQL installed on a FreeBSD machine (733mHz, 256Mb RAM). Compiled from source. We installed MySQL on another FreeBSD machine (dual 866mHz, 512Mb RAM). Compiled from source. The reason we went to the new machine is that we have a massive database that for practical purposes needed to live by itself. We moved this database from the first machine to the second machine. Most of the queries run slightly faster on the new machine, but one query in particular takes twice the time to run on the new machine. The query ; select c10.RecordID, count(*) as Quantity, sum(c10.Minutes) as Minutes from tblClass10 c10 left outer join tblANI a on c10.FromNumber = a.ExemptPhone where c10.RecordID = '100101' and a.ExemptPhone is null group by c10.RecordID; On the first machine runs in approximately 20 minutes, on the second machine runs in about 40-45 minutes. tblClass10 has 5.5 million records, tblANI has 3100 records. Out of tblClass10, the records matching the RecordID criteria '100101' is approximately 170,000 (today). We are thinking that it has something to do with the flags that were used to compile MySQL with reguards to dual processor machines. I am also up for any query optimization that would work too. I have tried INSERT ...SELECT the 100101 records into a new table and then running the query above (replacing tblClass10 with the new table), with the results being no faster. TFAYHIA! Jay Blanchard Applications Development nii communications, inc. 210-403-9100 x285 - 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 on Dual Processor machine
Hi, I don't have any experience with FreeBSD; however, I use MySQL on SMP servers with Linux. 1. due to MySQL architecture (1 thread per connection) you should not expect any performance inprovement on a dual processor for a SINGLE SQL query compared to an uniprocessor machine. This is what you get for most of your queries (the slight improvement is coming from CPU speed difference). Multiprocessor only helps when you run multiple queries in parrallel. 2. you should have the same kind of behaviour for your long query (slightly faster, not 2 times slower). Before suspecting your compilation flags, are you sure that your /etc/my.cnf is correctly setup ? Have you tried with the same configuration on both servers ? 3. If you want to optimize your query, you should try EXPLAIN command and make sure that your query is properly using indexes. Hope this helps -- Joseph Bueno Jay Blanchard wrote: Howdy all, We have a curious situation here, and I was wondering if any of you have encountered this before. We have MySQL installed on a FreeBSD machine (733mHz, 256Mb RAM). Compiled from source. We installed MySQL on another FreeBSD machine (dual 866mHz, 512Mb RAM). Compiled from source. The reason we went to the new machine is that we have a massive database that for practical purposes needed to live by itself. We moved this database from the first machine to the second machine. Most of the queries run slightly faster on the new machine, but one query in particular takes twice the time to run on the new machine. The query ; select c10.RecordID, count(*) as Quantity, sum(c10.Minutes) as Minutes from tblClass10 c10 left outer join tblANI a on c10.FromNumber = a.ExemptPhone where c10.RecordID = '100101' anda.ExemptPhone is null group by c10.RecordID; On the first machine runs in approximately 20 minutes, on the second machine runs in about 40-45 minutes. tblClass10 has 5.5 million records, tblANI has 3100 records. Out of tblClass10, the records matching the RecordID criteria '100101' is approximately 170,000 (today). We are thinking that it has something to do with the flags that were used to compile MySQL with reguards to dual processor machines. I am also up for any query optimization that would work too. I have tried INSERT ...SELECT the 100101 records into a new table and then running the query above (replacing tblClass10 with the new table), with the results being no faster. TFAYHIA! Jay Blanchard Applications Development nii communications, inc. 210-403-9100 x285 - 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 on Dual Processor machine
[snip] I would suspect other things first such as the my.cnf configuration (show variables) or has the kernel been optimised on the old box. Did you check kernel configurations and disk subsystems? Also the default process size on FreeBSD is 256Meg, so more memory won't help much unless you use it in my.cnf and the kernel configuration allows it! [/snip] Amazingly enough (I did not do the install of MySQL) there is no my.cnf on either machine (there are the default my-whatever.cnf files.). I belive that I should use the my-large.cnf as a starting point. Does anyone have any suggestions other than the default configuration for this file? Thanks! Jay Blanchard Applications Development nii communications, inc. 210-403-9100 x285 - 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 on Dual Processor machine
/etc/my.cnf isn't installed by default. Try my-huge.cnf (I think) Also, did you use mysql-max on the new one and mysql on the old (just wondering) This is what I use for a 1 Gig RAM single processor machine: [mysqld] set-variable = sort_buffer=2M set-variable = record_buffer=2M set-variable = table_cache=256 set-variable = key_buffer=256M set-variable = tmp_table_size=48M set-variable = interactive_timeout=7200 set-variable = wait_timeout=40 The last caveat - did you compile the freebsd kernel to actually use the second processor? Lastly, how did you copy the database over, sometimes the indexes don't copy. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 30, 2002 3:34 PM To: [EMAIL PROTECTED] Subject: RE: MySQL Performance on Dual Processor machine [snip] I would suspect other things first such as the my.cnf configuration (show variables) or has the kernel been optimised on the old box. Did you check kernel configurations and disk subsystems? Also the default process size on FreeBSD is 256Meg, so more memory won't help much unless you use it in my.cnf and the kernel configuration allows it! [/snip] Amazingly enough (I did not do the install of MySQL) there is no my.cnf on either machine (there are the default my-whatever.cnf files.). I belive that I should use the my-large.cnf as a starting point. Does anyone have any suggestions other than the default configuration for this file? Thanks! Jay Blanchard Applications Development nii communications, inc. 210-403-9100 x285 - 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 on Dual Processor machine
Just becareful not to use too much memory unless you raise the DMAX and such values (see /usr/src/sys/i386/conf/LINT ) if mysql runs out of memory things can get ugly! Also just run show variables to see the differences in the default configs. Also you did not say if both machines were the same version of FreeBSD and if both machine used the same type of SCSI harddisk and controller. CPU speed does not mean much during inserts, there is alot of disk writing. Ken - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 30, 2002 3:34 PM Subject: RE: MySQL Performance on Dual Processor machine [snip] I would suspect other things first such as the my.cnf configuration (show variables) or has the kernel been optimised on the old box. Did you check kernel configurations and disk subsystems? Also the default process size on FreeBSD is 256Meg, so more memory won't help much unless you use it in my.cnf and the kernel configuration allows it! [/snip] Amazingly enough (I did not do the install of MySQL) there is no my.cnf on either machine (there are the default my-whatever.cnf files.). I belive that I should use the my-large.cnf as a starting point. Does anyone have any suggestions other than the default configuration for this file? Thanks! Jay Blanchard Applications Development nii communications, inc. 210-403-9100 x285 - 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 on Dual Processor machine
Do keep in Mind that FreeBsd isn't 100 % with Smp .. you might have to compile linux threads in order to try and use the dual cpu's .. FreeBSD 5.0 should have the next Generation of SMP that should work great with mysql.. - Original Message - From: Ken Menzel [EMAIL PROTECTED] To: Jay Blanchard [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, April 30, 2002 5:09 PM Subject: Re: MySQL Performance on Dual Processor machine Just becareful not to use too much memory unless you raise the DMAX and such values (see /usr/src/sys/i386/conf/LINT ) if mysql runs out of memory things can get ugly! Also just run show variables to see the differences in the default configs. Also you did not say if both machines were the same version of FreeBSD and if both machine used the same type of SCSI harddisk and controller. CPU speed does not mean much during inserts, there is alot of disk writing. Ken - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 30, 2002 3:34 PM Subject: RE: MySQL Performance on Dual Processor machine [snip] I would suspect other things first such as the my.cnf configuration (show variables) or has the kernel been optimised on the old box. Did you check kernel configurations and disk subsystems? Also the default process size on FreeBSD is 256Meg, so more memory won't help much unless you use it in my.cnf and the kernel configuration allows it! [/snip] Amazingly enough (I did not do the install of MySQL) there is no my.cnf on either machine (there are the default my-whatever.cnf files.). I belive that I should use the my-large.cnf as a starting point. Does anyone have any suggestions other than the default configuration for this file? Thanks! Jay Blanchard Applications Development nii communications, inc. 210-403-9100 x285 - 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
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
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 on different OS-es
Ales It would be a good start to look at the OS on its own. Latest Linux versions are fast. Solaris is not the fastest but is rock solid. Win...well is windows. So this info will reflect on what you run. But how fast a system do you need? MySQL is very fast and so even on a low spec system you should not have problems. Simon -Original Message- From: Ales Vaupotic [mailto:[EMAIL PROTECTED]] Sent: 23 November 2001 12:22 To: MySQL list Subject: MySQL performance on different OS-es Hello, list! Has anyone made a test to find out under which OS MySQL performs best given the same hardware? How much is the difference? I am a new user and I have to choose a system. I am looking at Linux, Solaris or Win on x86. Thanks, Ales. - 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 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
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 and limit
On Fri, 2001-10-05 at 09:52, José León Serna wrote: Hello: I want to setup a machine with 3000 databases, each database will have 41 tables. Does MySQL support this configuration? What machine do you think will support this configuration? The purpose is to create a website with PHP+MySQL, is this possible? This should be no problem for MySQL. Everything is depending how to you use MySQL. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia ___/ www.mysql.com - 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 and limit
Tonu Samuel writes: I want to setup a machine with 3000 databases, each database will have 41 tables. This should be no problem for MySQL. Everything is depending how to you use MySQL. It seems to be pretty popular to have a large number of databases, but I guess it's mostly due to having a large number of users. In pthe project I'm working on now we have 3 databases, 2 of which are mysql and test. However, some of our tables will grow quite big (several gigabytes). Will MyISAM be the best table type for those, or under what circumstances will/won't it be? Also, what file system (for linux) do you recommend? ReiserFS or ext3 or something else? //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - 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 and limit
On Fri, 2001-10-05 at 12:13, Carl Troein wrote: It seems to be pretty popular to have a large number of databases, but I guess it's mostly due to having a large number of users. In pthe project I'm working on now we have 3 databases, 2 of which are mysql and test. However, some of our tables will grow quite big (several gigabytes). Will MyISAM be the best table type for those, or under what circumstances will/won't it be? Also, what file system (for linux) do you recommend? ReiserFS or ext3 or something else? There is no universal solution. HEAP tables are fast as they are only in memory, MyISAM tables are fast when used for reading, InnoDB is best on heavy use with concurrent writes. InnoDB and DBD are different in locking scheme, so depending on context DBD can beat InnoDB. Anyway usually we suggest to make decision between MySQL and InnoDB. BTW, this is unique in MySQL - you can have tables mixed to be transactional (InnoDB) and nontransactional (MyISAM) and use them mixed in same query. All other SQL-s (as much I know) have transactions on every table even if user don't need this. But usually tables contain different information, they have different usage and this affects things :) I do not know about ext3 much but rumours talk that it is just some kind of add-on on top of ext2. We have compared ext2 and ReiserFS when latter came out and ReiserFS was in some tests 30% faster in writes. Also ReiserFS can handle issues with directories containing 1 files much faster. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia ___/ www.mysql.com - 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 and limit
BTW, this is unique in MySQL - you can have tables mixed to be transactional (InnoDB) and nontransactional (MyISAM) and use them mixed in same query. All other SQL-s (as much I know) have transactions on Oracle's Global Temporary Tables don't write to redo or rollback, and on normal tables you can alter table tablename nologging to shut transaction logging off. Dave every table even if user don't need this. But usually tables contain different information, they have different usage and this affects things :) I do not know about ext3 much but rumours talk that it is just some kind of add-on on top of ext2. We have compared ext2 and ReiserFS when latter came out and ReiserFS was in some tests 30% faster in writes. Also ReiserFS can handle issues with directories containing 1 files much faster. -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia ___/ www.mysql.com - 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 and limit
On Fri, 2001-10-05 at 17:06, David Turner wrote: BTW, this is unique in MySQL - you can have tables mixed to be transactional (InnoDB) and nontransactional (MyISAM) and use them mixed in same query. All other SQL-s (as much I know) have transactions on Oracle's Global Temporary Tables don't write to redo or rollback, and on normal tables you can alter table tablename nologging to shut transaction logging off. Thank you for information. As you see I have not much Oracle knowledge :( -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia ___/ www.mysql.com - 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 and limit
No problem, MYSQL is great and I know far less about it than I do Oracle. Dave On Fri, Oct 05, 2001 at 06:08:07PM +0200, Tonu Samuel wrote: On Fri, 2001-10-05 at 17:06, David Turner wrote: BTW, this is unique in MySQL - you can have tables mixed to be transactional (InnoDB) and nontransactional (MyISAM) and use them mixed in same query. All other SQL-s (as much I know) have transactions on Oracle's Global Temporary Tables don't write to redo or rollback, and on normal tables you can alter table tablename nologging to shut transaction logging off. Thank you for information. As you see I have not much Oracle knowledge :( -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Tallinn, Estonia ___/ www.mysql.com - 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 Problem
On Thu, Sep 06, 2001 at 05:26:53PM +0200, Henning Schroeder wrote: Find all the queries that interact with this table. EXPLAIN them. Time them. *all* of them? there are lots. Well, perhaps not initially but you may want to have EXPLAINed a majority of the queries that are issued against your database and probably all the ones that occur often. as said before, it is the main user table. a user account is updated every time somebody request a page. and i need to join to that table very often to find out, when the corresponding user logged in last and what his name is. bad design? bad idea? what do you think? It does seem to be a point of contention. i could split these queries below into multiple queries, first getting the userid and then firing off another query to get the name (without a join). but i thought letting the database handle this should be faster. I guess only benchmarking will show if it's faster. Generally though I've found that many very quick queries is preferable to MySQL than one large, slow query, even if the many queries take longer (all added together) than the one large query. This assumes a web-backend type of application. select * from cookies left join users on cookies.uid=users.uid left join sessions on users.uid=sessions.uid where cookies.cookie=e3bd03382561eb3619b66fbea2af217d; select * from cookies left join users on cookies.uid=users.uid left join extended on users.uid=extended.uid where cookies.cookie=5226220e3b62cef71a13524ec7a413ac; (above queries have to be performed at the beginning of every webpage to find the current user. i don´t really think they are slow; they just lock because of something else.) i just tried the above query ten times. result: 7x 0.00sec, 1x0.01, 1x0.02, 1x0.04. does that spell slow? Yes. I wouldn't let queries with times like these near a busy production server I was responsible for unless I was positive they wouldn't and couldn't happen more than a handful of times a day. :-) Post the output of EXPLAIN for these queries. Along with the create defns for these other tables. Explanations snipped doesn´t look too bad for me. except that timing information at the end :-(. but that´s because the database is almost completely locked up again (processlist full of queries with Locked status). the table definition is quite long, i´ll append it to the end of the mail. Yes, I see what you mean. :-) Since uid is the primary key for cookies, users, sessions and extended, I wonder if the database could be made happier overall by breaking the queries down... select uid from cookies where cookie = 5226220e3b62cef71a13524ec7a413ac select * from users where uid=$uid select * from sessions where uid=$uid select * from extended where uid=$uid These might ease the bottlenecks. An update to sessions (for example) will only delay for the (relatively quick) third query instead of on the first bigger query. Conversely, only the third query will delay updates to sessions. I assume (and I'm guessing here) that the bottlenecks are being caused by regular updates to sessions and, to a lesser extent, extended. Perhaps some normalisation of sessions might help? Chances are I'm missing an important subtlety in your use of left join though, so feel free to flame (offlist)... :-) well, yes. i noticed that :-(. thank you for your good explanation though. but i think it´s probably not very fruitful to look for queries in the processlist that have locked status, because they are probably not the slow ones that caused the block. am i correct? Pretty much. While queries that spend too much time in a locked state aren't directly to blame (I blame the queries that lock the tables :-), blame isn't really at issue: either make the tables not locked so much or make the queries less dependent on the locked tables. :-) i also tried logging the queries that appear often with copying to temp table status and now have a nice set of them, though i don´t quite understand *why* the are copying. below are two: (the rows count is *way* to high, probably because the timestamps are ancient by the time i ran explain select) select count(*) from adviews where click=f and uid=7618 and cid=11 and datestamp=999782664 mysql explain select count(*) from adviews where click=f and uid=7618 and cid=11 and datestamp=999782664 ; +-+--+-+--+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+-+--+-+---+--++ | adviews | ref | uid,click,cid,datestamp | uid | 4 | const | 2365 | where used | +-+--+-+--+-+---+--++ 1 row in set (0.00 sec) index on all fields, optimized nightly.
Re: MySQL Performance Problem
On Thu, Sep 06, 2001 at 12:02:07AM +0200, Henning Schroeder wrote: All the updates to the table are of the style described above (one to three colums changed, row indexed by primary key). Well, with SELECTs it´s a different story: many queries join in different ways to the table. FYI, it´s the table where all user information is stored, so I need a join every time I need e.g. to find the name corresponding to an userid. Is that a bad idea? And how am I supposed to find the slow queries? Find all the queries that interact with this table. EXPLAIN them. Time them. If it was a contention problem, I could switch to another table type. Would that help? What do you think? Possibly, likely it would only make the symptoms a little less painful. select * from cookies left join users on cookies.uid=users.uid left join sessions on users.uid=sessions.uid where cookies.cookie=e3bd03382561eb3619b66fbea2af217d; select * from cookies left join users on cookies.uid=users.uid left join extended on users.uid=extended.uid where cookies.cookie=5226220e3b62cef71a13524ec7a413ac; (above queries have to be performed at the beginning of every webpage to find the current user. i don´t really think they are slow; they just lock because of something else.) i just tried the above query ten times. result: 7x 0.00sec, 1x0.01, 1x0.02, 1x0.04. does that spell slow? Yes. I wouldn't let queries with times like these near a busy production server I was responsible for unless I was positive they wouldn't and couldn't happen more than a handful of times a day. :-) Post the output of EXPLAIN for these queries. Along with the create defns for these other tables. update users set lastlogin=999697993, perstopre=f where uid=40651; update users set lastlogin=999698763, votescast=1514, prevvote=-8 where uid=54307; (the usual locked queries) When these are locked, not only are they waiting for the selects that came before them to finish, they are blocking selects behind them in the queue from executing simultaneously with selects ahead of them. Selects in the queue for the users table that join to other tables (and you have plenty of them) will block updates/inserts to those other tables. Imagine now that an insert arrives to one of these other tables and behind it comes another multi-table select, joining to other tables. On a busy database (thousands of queries per second), it doesn't take long for the whole database to get totally bogged down if a massive web of blocks happens like this. And mysqld doesn't always recover. Banning queries that take longer than 0.00 seconds at the mysql prompt (run them a few times - one or two 0.00s and it's suspect) is just a handy rule of thumb for measuring nastiness of queries. For proper measurements, one could benchmark queries - or one could stick a unique comment into every query and just watch processlists. You will come to recognise the common slow queries because you will see them in the processlists often! Wesley. - 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 Problem
hi wesley and the gang, thank you very much for your help so far. i optimized the indexes and added another one i obviously forgot before, and the database is smoother now. a bit at least, it now maxes out at 65q/s instead of 50... At 13:12 06.09.01, you wrote: idea? And how am I supposed to find the slow queries? Find all the queries that interact with this table. EXPLAIN them. Time them. *all* of them? there are lots. as said before, it is the main user table. a user account is updated every time somebody request a page. and i need to join to that table very often to find out, when the corresponding user logged in last and what his name is. bad design? bad idea? what do you think? i could split these queries below into multiple queries, first getting the userid and then firing off another query to get the name (without a join). but i thought letting the database handle this should be faster. select * from cookies left join users on cookies.uid=users.uid left join sessions on users.uid=sessions.uid where cookies.cookie=e3bd03382561eb3619b66fbea2af217d; select * from cookies left join users on cookies.uid=users.uid left join extended on users.uid=extended.uid where cookies.cookie=5226220e3b62cef71a13524ec7a413ac; (above queries have to be performed at the beginning of every webpage to find the current user. i don´t really think they are slow; they just lock because of something else.) i just tried the above query ten times. result: 7x 0.00sec, 1x0.01, 1x0.02, 1x0.04. does that spell slow? Yes. I wouldn't let queries with times like these near a busy production server I was responsible for unless I was positive they wouldn't and couldn't happen more than a handful of times a day. :-) Post the output of EXPLAIN for these queries. Along with the create defns for these other tables. mysql explain select * from cookies left join users on cookies.uid=users.uid left join extended on users.uid=extended.uid where cookies.cookie=ee33c9ec7950a1e5c39f37bf72d2de51; +--+---+---+-+-+---+--+---+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+-+-+---+--+---+ | cookies | const | PRIMARY | PRIMARY | 32 | const |1 | | | users| const | PRIMARY | PRIMARY | 4 | const |1 | | | extended | const | PRIMARY | PRIMARY | 3 | const |1 | | +--+---+---+-+-+---+--+---+ 3 rows in set (3.35 sec) mysql explain select * from cookies left join users on cookies.uid=users.uid left join sessions on users.uid=sessions.uid where cookies.cookie=5c50d7a7319ae6c8529f535aa5f19109; +--+---+---+-+-+---+--+---+ | table| type | possible_keys | key | key_len | ref | rows | Extra | +--+---+---+-+-+---+--+---+ | cookies | const | PRIMARY | PRIMARY | 32 | const |1 | | | users| const | PRIMARY | PRIMARY | 4 | const |1 | | | sessions | const | PRIMARY | PRIMARY | 4 | const |1 | | +--+---+---+-+-+---+--+---+ 3 rows in set (4.55 sec) doesn´t look too bad for me. except that timing information at the end :-(. but that´s because the database is almost completely locked up again (processlist full of queries with Locked status). the table definition is quite long, i´ll append it to the end of the mail. On a busy database (thousands of queries per second), it doesn't take long for the whole database to get totally bogged down if a massive web of blocks happens like this. And mysqld doesn't always recover. well, yes. i noticed that :-(. thank you for your good explanation though. but i think it´s probably not very fruitful to look for queries in the processlist that have locked status, because they are probably not the slow ones that caused the block. am i correct? Banning queries that take longer than 0.00 seconds at the mysql prompt (run them a few times - one or two 0.00s and it's suspect) is just a handy rule of thumb for measuring nastiness of queries. For proper measurements, one could benchmark queries - or one could stick a unique comment into every query and just watch processlists. You will come to recognise the common slow queries because you will see them in the processlists often! i also tried logging the queries that appear often with copying to temp table status and now have a nice set of them, though i don´t quite understand *why* the are copying. below are two: (the rows count is *way* to high, probably because the timestamps are ancient by the time i ran explain select) select count(*) from adviews where click=f and uid=7618 and cid=11 and datestamp=999782664 mysql explain
Re: MySQL Performance Problem
Henning Schroeder wrote: i also tried logging the queries that appear often with copying to temp table status and now have a nice set of them, though i don´t quite understand *why* the are copying. below are two: (the rows count is *way* to high, probably because the timestamps are ancient by the time i ran explain select) It is copying ALL of the results into the temp table. If you can (e.g. you know you will never want more than N records), add a LIMIT 0,N to the end of the SELECT so that when you have an old timestamp it will not hang the database. --Bill - 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 Problem
At 17:52 06.09.01, you wrote: It is copying ALL of the results into the temp table. If you can (e.g. you know you will never want more than N records), add a LIMIT 0,N to the end of the SELECT so that when you have an old timestamp it will not hang the database. as i use select count (*) from blah where blahblah; (actually i am just interested whether there is a row or not matching the criterion. under normal operations there should never be more than one match) i don´t understand how a limit 0,1 would help here. wouldn´t it just say that no more than one count(*) result should be returned (which never the case anyway because count(*) returns exactly one row)? henning - 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
