Tips For Improving The Performance Of Mysql
I really hope I can get some general advice and suggestions about how I can improve the performance of our mysql server.
We have a dedicated webhost running a number of quite highly used websites. They all use mysql quite heavily at times. Recently we've had complete mysql gridlock and had to restart the server. What are the steps I can take to improve performance?
This is what i've done so far.
1. Optimise queries - i've been checking the queries we are running and trying to cache the results in a session rather than generating all the time. I've also installed something called mytop which is basically the same as the standard unix top command, except for mysql processlist and shows you the queries currently running and the time elapsed.
2. I have run explain on my queries and put in indexes all over the place to try and make lookups quicker.
Things i'm considering doing
1. Reducing the amount of data in the database and perhaps rotating the tables slightly, our biggest tables are approaching 2 million records and linking these together is i'm sure causing some of the problem, especially when we have to run a SUM query on them, even with good indexes.
2. Moving the database off the webserver and onto its own dedicated mysql server.
3. Load balancing the database and somehow clustering more than one database server, but this would cause serious headaches so not seriously considering unless it was thought the best solution.
4. Upgrading mysql... We are currently running version 4.0.27, should we upgrade to 5? Will this give us performance boost?
5. Re-compiling... After a google search I discovered that potentially we could recompile mysql with a better options set and perhaps improve performance.
6. Buying a book on mysql optimisation Any suggestions?
View Complete Forum Thread with Replies
Related Forum Messages:
Any Tips For Improving This Query Time?
Is there anything I can do to improve execution time for this query? mysql> SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5; +---------------------------------------------------------+---------------------------------------------------------------+-------+ | webpageUrl | webpageName | count | +---------------------------------------------------------+---------------------------------------------------------------+-------+ | http://www.w3counter.com/ | W3Counter - Free Web Counter, Web Stats, Live Analytics | 2490 | | http://www.w3counter.com/stats/visitors/4071/day/1000/0 | Visitors Overview - www.pitbullmortgageschool.com/ | 1496 | | http://www.w3counter.com/stats/4071/v_daily | W3Counter - Visitors by Date - www.pitbullmortgageschool.com/ | 1130 | | http://www.w3counter.com/stats/ | Your Websites | 1124 | | http://www.w3counter.com/stats/websites | W3Counter - Websites | 800 | +---------------------------------------------------------+---------------------------------------------------------------+-------+ 5 rows in set (0.88 sec) mysql> explain SELECT webpageUrl, webpageName, COUNT(*) AS `count` FROM _1_log GROUP BY webpageUrl ORDER BY `count` DESC LIMIT 5; +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ | 1 | SIMPLE | _1_log | ALL | NULL | NULL | NULL | NULL | 29422 | Using temporary; Using filesort | +----+-------------+--------+------+---------------+------+---------+------+-------+---------------------------------+ 1 row in set (0.00 sec) It averages 0.5-3 seconds throughout the day (it's run often enough that it shows up in my slow query log multiple times an hour for exceeding 1 second). Adding a partial index on webpageUrl doesn't affect anything, and URLs can be quite long. Is there anything I can do to speed up that query? The table is updated too often to maintain separate url/count tables -- can't spare the resources.
View Replies !
Improving The Performance
Serve Spec: AMD 2000 RAM 1.5 GB Fedora 10 How can I modify my.cnf for a better performance. Current file looks like [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines and adjust the connectstring as needed. #ndbcluster #ndb-connectstring="nodeid=4;host=localhost:1186" [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [ndbd] # If you are running a MySQL Cluster storage daemon (ndbd) on this machine, # adjust its connection to the management daemon here. # Note: ndbd init script requires this to include nodeid! connect-string="nodeid=2;host=localhost:1186" [ndb_mgm] # connection string for MySQL Cluster management tool connect-string="host=localhost:1186"
View Replies !
Performance Tips
I'm working with 5.0.29 and have a model with 35 tables. When I click a table for modification or move the element into de model my computer lost performance and WB take a while to respond (enought to desesperate in daily use). Have a tips page to check posible reasons or tips for a better performance?. My pc have a Intel dual CPU E210 2GHz and 2MB RAM, then I thinks that is enougth for run a normal program. Please send me tips. System info from MySQL WM MySQL Workbench OSS for Windows version 5.0.29 Cairo Version: 1.5.12 Rendering Mode: GDI Rendering OpenGL Driver Version: Not Detected OS: Windows XP CPU: 2x Intel(R) Pentium(R) Dual CPU E2180 @ 2.00GHz, 2.0 GB RAM Video adapter info: Adapter type: NVIDIA GeForce 7300 GT Chip Type: GeForce 7300 GT BIOS String: Version 5.73.22.62.72 Video Memory: 262144 KB
View Replies !
Need Some Help In Improving Performance On A Query (was: Hi People)
I need some help in improving performance on a query which is taking too long to execute. I have 3 tables: table 'photo' stores information on photos and it has 2 important fields: - photo_id - title table 'item' stores information on generic items in the system (photos is an example). It has 3 important fields: - item_id - item_type (like 'photos') - submitter_id table 'user' stores information on users in the system. It has 2 important fields: - user_id - name I would like to get the number of photos that either have a title that contains 'a' or the submitter's name contains 'a' I tried the following: SELECT count(DISTINCT photo.photo_id) NumItems FROM (photo, item, user) WHERE (item.item_id = photo.photo_id AND item.item_type = 'photos' AND (photo.title LIKE '%a%' OR (user.name LIKE '%a%' AND user.user_id = item.submitter_id))); This query is taking way too long (sometimes up to 10 seconds Note that I have over 2,000,000 records in the 'user' table (I suspect the problem is there) I only have 2 recods in the 'photo' table I only have 2 records in the 'item' table
View Replies !
MySQL Tips And Tricks
If you have an interesting MySQL Tip or Trick, I would be interested in posting it. The following link (GPU Free Documentation License) contains advice on such standard features as "showing a process list" and "killing a particular" process, how to do transactions, interface with Perl, Java and C, remove duplicates entries in a table, rollback transactions, merge multiple tables into one, update foreign keys in a multi-user environment, create a UUID (same as GUID for MSSQL users), monitor connections with "tcpdump", create a C or C++ API, encrypt and decrypt data (des_decrypt, and des_encrypt), generate a virtual row count, monitor and show all "select, insert, create etc. statements" with binlogs, and using Spatial Extensions like SELECT X(GeomFromText('Point(5.02 7.9)')); with explanations. Code:
View Replies !
Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram mysql 4.1 with odbc 3.51 MYISAM windows 2003 server std edition date, account and invoice number are indexed Database size 18 million rows I am querying (selecting) columns of a date and an account our tester program that opens a socket to the Mysql database and does a select for the above n times each time the date and the account is randomized to minimize hits on records closeby. This program will perform over 1000 queries per second. At the end, the Mysql socket will be closed Even when the socket is closed each time, I still get 400 queries per second. When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results. Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.
View Replies !
Improving LIMIT X,Y Query
I have only 1m records in my database running on a laptop of speed 1.6GHz, memory 512MB ram, and Toshiba MK8032GAX hard disk. I use 'LIMIT x,10' for the query to utilise record paging. When the value of x is nearer to 0, the query speed is fast. Presently, 'LIMIT 0,10', 'LIMIT 50000,10' and 'LIMIT 100000,10' takes about 0sec, 11secs and 4mins respectively. 1) Is there anything I should do, in terms of sql statement or database design, to obtain the same speed for cases where x is in the middle or nearer to the end? 2) How fast is a simple select statement (not select count(*)) on a system like mine? 3) Suppose I left my original select .. limit query as is, what is the minimum hardware that I need to improve the speed?
View Replies !
Help Required Improving Query
the following query has been giving me problems. My users are reporting that credit is going down automatically and download counts seem to be overwritten with other downloads. I'm thinking using JOINS would be better, but I'm not sure which how best to structure it. MySQL UPDATE tb_members s, tb_userfield f,tb_downloads dl SET s.credit_available = credit_available - 1, s.credit_used = s.credit_used + 1, f.field24 = f.field24 - 1, dl.info_downloads_web = dl.info_downloads_web + 1, dl.info_downloads_today = dl.info_downloads_today + 1, dl.info_downloads_30days = dl.info_downloads_30days + 1, dl.info_last_download_time = NOW() WHERE s.userid = '".$ses_userid."' AND f.userid = '".$ses_userid."' AND dl.key='".$download_key."'"
View Replies !
5.X Optimization Tips
I have a unix box that has 32G of RAM. I want to optimize MySQL because right now it's pretty slow using the default settings. Do you have any configuration settings that you recommend to maximize MySQL [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 512M max_allowed_packet = 16M table_cache = 512 sort_buffer_size = 512M read_buffer_size = 8M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 512M thread_cache_size = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 16 # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 2G innodb_additional_mem_pool_size = 20M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M Edited 1 time(s). Last edit at 01/26/2009 03:46PM by John Doe.
View Replies !
Tips / Advice For Searching A Database
I'm building a search facility that'll be the main focus of my website, with the plan being that it'll be the first port of call for people to browse the products, similar to Amazon. Apart from doing a couple of LIKE commands, is there any advice people can offer and maybe some example queries that will return the most useful results to the user? I'll be searching the following fields: product_name image_name product_description
View Replies !
PHPmyadmin: Export Database: Tips?
Using PHPmyAdmin, is there any tips one can give me when exporting? I plan on using this data in a Textpattern or Drupal or Similar web app/cms/blog... what would be the best way to export the data for ease of importing into (for example) Textpattern... Drupal maybe?
View Replies !
Performance MySQL With ADO VB
I'm developing an VB application which uses the SAX parser and ADO VB to insert.update data in in MySQL. The perormance is very bad. If I use the same data and insert/update to a MS Access database it takes 14 minutes. But using MySQL it takes about 5 hours! Here's the piece of code which takes the most time. Does anyone have an explanation for this? Code:
View Replies !
MySQL Performance
We ran MySQL (4.0.15-standard, 64-bit, SuSe 8.0) benchmarks on 2 CPU and 4 CPU Opteron machines. Mostly we tested insertion times with many simultaneous connections (hundreds). To our surprise we did not find significant differences.Each of our servers (2 and 4 CPU machine) has one 1Gbit network connection. I have feelings that the machines are the network bounded, not the CPU bounded. Could it be true? How to verify it?
View Replies !
Problem With MYSQL Performance With ASP.NET
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram mysql 4.1 with odbc 3.51 MYISAM windows 2003 server std edition date, account and invoice number are indexed Database size 18 million rows I am querying (selecting) columns of a date and an account our tester program that opens a socket to the Mysql database and does a select for the above n times each time the date and the account is randomized to minimize hits on records closeby. This program will perform over 1000 queries per second. At the end, the Mysql socket will be closed When I enter a similar query manually a web interface, I get about 3 second response time. This program opens/closes a socket for each query Does anyone have any suggestions Your assistance would be greatly appreciated Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.
View Replies !
MySQL Performance Question..
I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst, with a wd 512MB HD and I want to store George Bush in our MySQL database. As far as table definitions are concerned, should I use a BLOB or should I store him on disk and make a reference to the physical location in the MySQL table instead? Will I run into any performance/storage issues when querying this data?
View Replies !
MySQL Tables Performance
I have a table where misc data are stored. Right now this table has about 30 columns, but for sure it will be more in the near future. So I wonder how the big number (50-100) of table's columns affect for MySQL DB performance. Maybe it's better to create more tables rather than more table's columns ??
View Replies !
Optimizing MySQL For Performance
I am going to be running mySQL on a RAID5 array and am wondering if I need to worry about splitting up the data and log files on to seperate channels on the RAID array. I have heard differing arguments. Some people say that you can install SQL on the same logial drive as long as you are running RAID5 and not worry about performance issues. Others say that you need to split everything up even if you are running RAID5. Do I need to split it up in to 2 channels and 2 logical drives or is one drive OK?
View Replies !
Analysis MySQL Performance
What is the best way to analayse the performance of an operational MySQL server? Are there any good stats programs out there that show more than the variabels listed by MySQL (And phpMyAdmin)? Are there any good tutorials about what figures to be alert for, and what should be ignored?In particular I'm facing a number of connections issue and need to isolate what is causing so many connections, and why so many stay in sleep mode.Database amostly use PEAR to connect to MySQL, are there any common pitfalls with PEAR and numerours connections?
View Replies !
MySQL Performance Monitor
Wanting to do some performance testing of MySQL on my Windows 2003 Server, and was wondering if there was anyway to setup some basic MySQL performance counters to monitor and log in perfmon? Things like queries per second, connections, traffic, etc? Doesn't have to be a permon counter, just something that can log usage values to a CSV at one second intervals
View Replies !
MySQL Performance Maintenance
can anyone please tell me how I keep the performance of the data within my MySQL database optimized? Ie, will the data over time become defragmented and therefore need to be optimized via (for example) an unload process, then a sort then a reload? Most of my selects will need to order the data in alphabetical order (I am storing business names and addresses and returning the data in business name order) and I was wonderring if I needed to perform some process to reorder and reload the data periodically to maintain performance. I am expecting approximately 8000 rows of data.
View Replies !
Ajax Performance With Mysql?
I'm trying to tweak the performance of a php script, but I don't understand how mysql handles connections, so I'm not sure if I should use standard sql statments, prepared statements or stored procedures. The php script is called by an ajax file, which will be executed very often. Right now each time the script is called I create a new connection (username, password, etc...) and at the end of the script I specifically close it (mysqli_close($con)).
View Replies !
Profile Performance On MySQL
I'm looking for recommendations on an app/function of mysql that will show a profile of the performance of my interaction with MySQL. I'm a newb to php/MySQL, but not to programming/db. I'm about to launch a site and I'd like to look at a profile of the queries I'm running and if they are using indexes properly/doing full table scans, and other performance related items. I've read up on the explain and benchmark functions, but they seem to require that the query is passed through them. I'd prefer something more global like MS SQL's profiler that captures all of the actions, time expended and execution plan. Then I can look through it and see if any of the actions are taking longer than x time and then investigate further. Does anything like this exist? FYI, I'm on a shared hosted db server.
View Replies !
MySQL General Query Log And Performance
I'm wondering how big impact on performance would have turning on the general query log. I read in manual it should be turned off in production environment to increase the speed but I didn't find any information about performance (how much cpu/disk usage it would take). Is it like 1% performance lost or is it bigger?
View Replies !
Mysql Too Many Connections Performance Issues
I have a website running on a linux/apache/mysql/php server. I receive about 8,000-10,000 visitors a day with about 200,000 to 300,000 page views. The server is a RedHat Linux server running PHP 5.x, MySQL 5.x, Apache 2.x We have been suffering from a number of performance issues. Our hosting company has set our max connections to 100, and we are using persistent connections in PHP. At times the mysqld process takes 100% of the CPU. We have also been suffering from mysql_pconnect(): Too many connections errors. What can I do to fix these issues? When I run a top on the server I see this ... PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 5567 mysql 16 0 169m 42m 4236 S 81.2 4.2 3078:09 mysqld 32539 apache 15 0 42676 25m 7708 S 1.0 2.5 0:03.40 httpd 32572 apache 15 0 42680 25m 7708 S 1.0 2.5 0:02.98 httpd 32608 apache 15 0 42680 25m 7704 S 1.0 2.5 0:00.72 httpd 32542 apache 15 0 42704 25m 7712 S 0.7 2.5 0:03.38 httpd 32561 apache 15 0 42732 25m 7712 S 0.7 2.5 0:02.61 httpd 32567 apache 15 0 42680 25m 7708 S 0.7 2.5 0:02.60 httpd 32591 apache ...................................................
View Replies !
MySQL Performance - Option Setting
I set OPTION=3 in my connection string as recommended by MySQL (http://dev.mysql.com/doc/refman/5.0...parameters.html) for a VB application. OPTION=3 is Don't Optimised Column Width (1) and Return Matching Rows (2). I found this to be very slow on a database with just 27,000 records. It took approx. 12 seconds to read 27,000 records. When I change the OPTION to 8 (Allow Big Results), it took just 3 seconds to read 27,000 records. BUT now my application cannot update records anymore and getting random errors on transactions. I try setting OPTION=11 (all 3 options added together) and the speed went back to being slow.
View Replies !
Mysql Performance On Large Joins (its Bad)
Mysql is unable to perform joins with large tables ON VARCHAR. these joins run fine when the queriers are performed on INT values. However int values are not practical in this instant because I am joining on product SKUs. sample SQL ----------------- SELECT a.sku AS this, b.sku AS that, b.price, b.SRP FROM table1 a INNER JOIN table2 b ON a.sku LIKE b.sku TABLE specs ------------------------- table1 14000 records table2 13000 records Running Mysql 4.1, PHP 4.3.
View Replies !
Performance On MySQL 5.1 / ODBC 5.1.4 Driver
Following procedure needs about 25 sec. to find the record I am asking for: Private Sub btnLesen_Click() sSQL = "SELECT * FROM tblAdressen WHERE AdressNr = 8" aRs.CursorLocation = adUseClient aRs.Open sSQL, oConn, adOpenKeyset, adLockOptimistic If aRs.RecordCount > 0 Then sevVorname = aRs!Vorname sevStrasse = aRs!Strasse sevOrt = aRs!Ort sevName = aRs!Nachname End If End Sub There are around 14'000 records in the table, Nr. 8 is the first one! I am trying to find the reason for the bad performance for days - without any success. Following Flags are checked for the connector: - return matched rows instead of affected rows - Allow big results - Use compression - Treat BIGINT columns as INT columns - Enable safe options - Don't prompt when connecting - Enable dynamic cursors - Ignore schema in Column specification - disable driver-provided cursor support - Don't use setlocale() - Don't cach results of forward-only cursors - Ingnore space after function names - Disable catalog support - Disable transaction support - Force use of forward-only cursors - Limit column sizs to signed 32-bit range The file "my.ini" I didn't change! I work with Win2000 & VB 6.0
View Replies !
Mysql Database Performance Counters.
I am testing an application using OpenSTA on Windows platform and it's database resides on the Linux server. I have installed a Mysql server version 5.0.45 on Centos5.2. I run the scripts through openSTA for generating load on my application. How to monitor the database performance counters in the case where the load is generated on the server. I want these performance counters to be available on windows performance monitor. Is there any way to do this?
View Replies !
Database Performance Counters For Mysql 5.0.45
I have installed mysql server V5.0.45 on CentOS 5.2. I want to know whether it installs database performance counters along with the software as Microsoft SQL server automatically installs the counters and those can be monitored using Windows Performance monitor tool.
View Replies !
Tool For Administrating MySQL, Especially Performance
Are there any administration consoles out there for MySQL that specifically with performance? I'm most used to using MS SQL, and inside the admin console there, there are tools like profiler where it will watch the database, show queries as they execute with timing, etc.. and then you can drill deeper into execution plans, etc.. I've searched for tools on the internet, but haven't found anything definitive. I use phpmyadmin now, and I've looked at Navicat, but didn't see any performance monitoring or inspection tools.
View Replies !
FreeBSD 5.4 And Poor Performance Using MySQL Administrator
It has been over 5 years since I have messed with FreeBSD and I have never messed with MySQL until now so forgive me if my issue is trivial and I thank everyone for the help. The hospital I work for is finally getting off of Micrisift IIS to server the Hospital website. i built a FreeBSD server with Apache 2.0.54 and PHP5 as well as MySQl 5.x.x. Running queries on the server locally is fast as lighting as well as is creating db's and tables. How ever if I use MySQL query or Administrator it takes what seems like like about a minute or more to finally connect. I believe I have ruled out all network issues as this server also sits in our corporate dmz by attempting to connect directly to the server via cross over cable with a laptop.
View Replies !
Slow Performance After Falling Back To MySQL 3
Yesterday I have upgraded my linux box with a pair of Xeon 1G CPU and reinstalled everything (it was a sinle Xeon 700). The default RH9 installation comes with MySQL 3.23.54. After the reinstallation I found the machine was not as responsive as before. (It's not very slow, but some lagging is noticed) I was running Mysql-max 4.0.11 and the performance was ok, except the slowness during the peak hours, which was regarded as normal. I would like to know the differences between Mysql 3 and 4. Is the new version making a significant jump in performance? I also noticed the system eats up memory faster than before. With the old setting the system never used up 60% of physical memory, even at extremely high loading (30+). Now it easily used up 70% of memory. Code:
View Replies !
Question About MySQL Performance On 2 And 4 CPU Opteron Machines
We ran MySQL (4.0.15-standard, 64-bit, SuSe 8.0) benchmarks on 2 CPU and 4 CPU Opteron machines. Mostly we tested insertion times with many simultaneous connections (hundreds). To our surprise we did not find significant differences. Each of our servers (2 and 4 CPU machine) has one 1Gbit network connection. I have feelings that the machines are the network bounded, not the CPU bounded. Could it be true? How to verify it?
View Replies !
Performance Issues In Iterative Concurrent Inserts In Mysql 4.0.18
I am using Mysql 4.0.18 and executing concurrent insert statement. I am executing this statement in the loop. For first few interation the perofrmace is quite good, the query executes in almost 5 to 6 second (Records being fetched and inserted are near about 80000 to 1,00,000). But as the no of iterations go on the execution time also increases. It goes upto 2 - 3 mins for a single query?
View Replies !
MySQL Performance: Less Rows With Comma Delimited Lists Or More Columns?
I'm storing results from tests students have taken. In the table where they'll be stored, I'm contemplating whether I should focus on keeping more columns and less rows, or if more rows would be better. I'm on a shared server, so size is of concern to me here as well. More rows would make it easier to reference the data as I'll be doing a lot of cross-referencing, but if I could choose between 1,000 rows for the data or 20,000 rows it seems that 20,000 would put me at a disadvantage for size and speed, even if there are only 3-4 SMALLINT columns per row. Are rows more expensive to a server than columns? Going for less rows would mean moving from SMALLINT to VARCHAR fields (as they'd now be comma delimited lists possibly greater than 64 values [so no set]), but would drastically cut down the rowsize.
View Replies !
IO Performance
I'm running a 5.0.51a mysql server on pentium M. My application is made of 30 myisam tables written every 2 seconds and a very few reads (1 evry hour) from the whole table. Each table is a kind of circular buffer (fixed number of line, current line). Each line of a table contains a medium blob whith fixed size (about 70k). I know the average output (Kb/sec) from the client to th server, says 200 Kb/sec. When trying to measure this flow with 2 invocations of "show global status like '%Bytes%'; followed by division by the elapsed time, I find between 1 and 2 times more (400 Kb/s). When I measure the output to the disk with the iostat command (iostat -d -k 10) I find 4 times more (800 Kb/s) and a lot of read Kb/s (100 kB/s). Is this normal or is there some tunning to do ? During this time, the iowait rate is very high (30%). Is this normal or is there some tunning to do ?
View Replies !
Best For Performance
It's a web application, accessed via internet, with around 100.000 clients and each client have around 10 users. This web app have a database of around 100 tables. Ok, the question is: Is it better to have each client with it's own catalog on the database (each catalog with around 100 tables) or is it better to use one catalog for all the clients (the tables would have a lot more records this way, but only one catalog)? I'm considering performance.
View Replies !
Which Is Better In Performance
country (id) country (1) America (2) Germany (3) Canada (4) China (5) Egypt (6) Russia (7) Korea (8) France (9) Brazil city (id) city (1) Berlin (2) New York (3) Cairo (4) Paris (5) Seoul (6) Peking (7) Chicago (8) Moscow (9) Denver I have two tables like the above. And I have one of these tables below. message1 (id) (city) message (1) (2) I love New York. (2) (5) Kumkang mountains are beautiful. (3) (4) I was born in France. (4) (6) Great Wall is great. (5) (7) Chicago is a big city. message2 (id) (city) (country) message (1) (2) (1) I love New York. (2) (5) (7) Kumkang mountains are beautiful. (3) (4) (8) I was born in France. (4) (6) (4) Great Wall is great. (5) (7) (1) Chicago is a big city. code1 select city, country from message1 left join city on city.id=message1.city left join country on country.id=city.country code2 select city, country from message2 left join city on city.id=message2.city left join ccountry on country.id=message2.country I think The codes above have a same result.(sorry, I didn't test it.)
View Replies !
Performance Of LIKE
I'm weighing the performance of materialised path vs. nested set algorithms, for storing a tree structure. The tree will have a lot of searches, a fair amount of inserts, but probably fairly few move-operations. The decisive factor seems to be the speed of a LIKE-query. I know, that regular expressions perform pretty well, as long as they are anchored. From this, I deduct, that LIKE must be able to perform equally good, since a path is indeed anchored. The questions is, if a LIKE will always result in a full scan, or if it can utilise indices? If it doesn't allow use of indices, would it be worth it, to use an equality comparison, on a substring (Which is essentially, what I'll be querying for) or am I just entirely missing the point?
View Replies !
Innodb Performance (CPU, RAM)?
I have a table with approx. 150,000,000 ++ rows. It should be approx 80 byte for each row. Now I'm planning to use it with mysql/innodb. Can you guys suggest anything about this. I have many questions... Can Innodb work well with such tables? Will queries (select/update) be slow? What hardware do you suggest, cpu/ram?
View Replies !
Balance Performance
My computer is AMD athlon 64 3400+ with 1.5G DDR. when i execute the following sql in mysql 5.XX. select c.filename, c.topic from articles c where c.topic in (select a.topic from articles a group by a.topic having count(a.topic)>1) order by topic; It eat up all the cpu power , and the mysql server stop responsing until end of this query. Even my apache stop responing too. How to fix this? How can i set the CPU limit of each query connection?
View Replies !
Mysqlcheck Performance
I launch the command line: "mysqlcheck --repair dbname" over a database with only one myisam table of more than 10.000.000 rows and 5 GB. The elapsed time to repair is about 1 hour! Is possible to increase speed?
View Replies !
Low Performance With Linux SMP
I use #mysql -V mysql Ver 12.21 Distrib 4.0.15, for suse linux (i686) with a SMP-kernel When I used this version with a kernel not SMP mysql was very very fast but now with the SMP-kernel the mysql is very very slow. I takes several minutes to make an INSERT on a table with near 50.000 Records Is there any problem with mysql an SMP? is a problem of the version I have installed?
View Replies !
Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table. Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.
View Replies !
Performance Needed
I have two tables. One for products and one for reviews. I have setup a detail page which displays the various fields out of the products table and then looks for all reviews in the reviews table which match the products key and display the average. All good so far. I want to have a page which displays a whole list of the products and show their rating next to them but I'm wondering if making it search the reviews table and calculate the average for 50 products in one go is going to cause a bit of a strain on the server? Is there another way I can do this, perhaps storing the precalculated average in the products table but how would I go about doing that?
View Replies !
How To Check Performance?
I have a web application which uses MySQL and I wanted to check out the performance of the database. I have been trying to use JMeter to test the performance of it but I was wondering if there is anything MySQL specific which can give me statistics on performance etc.
View Replies !
SELECT WHERE Performance On LIKE Vs =
I have a table: CREATE TABLE file (id VARCHAR(10) PRIMARY KEY, image MEDIUMBLOB); The table is filled with 4073 rows with images of 50-100k each. When I do a SELECT query using a WHERE-clause I get a huge difference in time before the result is delivered: SELECT image FROM file WHERE id=1234567801; 1 row in set (3.73 sec) SELECT image FROM file WHERE id LIKE 1234567801; 1 row in set (0.02 sec) Why is this difference in times?
View Replies !
(View) Performance
I have a huge table with product data from different shops in my database. There is a flag column 'import' witch flagt products with 1 for my PHP script. To select from one shop all product individually, work with it, and then update these product import flag to 0 in the database, seems to be the slowest way. I thougt about a view about all products of a shop with flag '1'. Code:
View Replies !
|
TRACKER
