September 29, 2006

What to tune in MySQL Server after installation

Posted by peter |

My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.

Posted by peter @ 3:46 am :: tips  
Print This Post Print This Post book mark What to tune in MySQL Server after installation in del.icio.usdel.icio.us :: submit What to tune in MySQL Server after installation to digg.comdigg  
Comment RSS :: Trackback URI  
Related posts: :Percona RPMS for RedHat 5 / CentOS 5 x86_64::Innodb Performance Optimization Basics::Percona builds with Percona patchsets:
 

71 Comments »

  1. 1. sjc

    Thanks Peter, nice summary of these tuning settings.

    A question: I have a database using only MyISAM with about 6GB of data, 4GB of index files (largest index is nearly 1.7GB) running on a box with 4GB of memory. I’ve currently got the key_buffer_size set to 1.5GB, and I’m finding performance on the large tables can vary hugely, presumably dependent on whether the index for those tables is currently loaded or not. I’m working on a medium-term solution to get rid of those huge indexes entirely, but in the meantime can you suggest a suitable size for the key buffer? My current thinking is to *lower* the size of it in order to give the OS a better chance of caching all the files.

    Thanks,

    - s

    Comment :: September 29, 2006 @ 6:56 am

  2. 2. peter

    Surely performance will be very different if data is in memory or not. If you have some particular indexes which you need to have in memory you can use LOAD INDEX INTO CACHE to preload them. You can even have separate keycache for them to ensure they are not replaced by other data.

    Your 1.5GB key buffer looks reasonable but I can’t tell if it is optimal for your workload or 1GB will be better. Run some benchmarks and see yourself.

    Comment :: September 29, 2006 @ 7:05 am

  3. 3. Roland Bouman

    Hi Peter,

    What an excellent and valuable overview.

    I sometimes wonder if these are just the teasers and there’s still a box of well hidden secrets that you keep away for consulting gigs ;)

    Anyway, concerning innodb_flush_logs_at_trx_commit:

    “Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.”

    To be clear, we could loose *committed* data in these cases, right?

    Comment :: September 29, 2006 @ 10:43 am

  4. 4. peter

    Roland,

    I keep no secrets I do not need it. Normally the problem is not what information is unavailable – with OpenSource you can argue everything is open but applying this information to particular problem and finding bits and pieces relevant for particular case, so are analytical skills. It is impossible to remember everything these days :)

    About innodb_flush_logs_at_trx_commit – yes you’re right if you have it set to 0 or 2 you can loose commited data on the crash. The difference is it is MySQL crash for value 0 and full OS crash for value 2, which is why it is better.

    Comment :: September 29, 2006 @ 10:53 am

  5. 5. James Day

    Roland, as Peter says, no need to hide, the skill comes in knowing what to apply in a specific context and what to look for first.

    James Day

    Comment :: September 29, 2006 @ 12:31 pm

  6. 6. Ruslan Zakirov

    Is table_cache important for InnoDB setups?

    Comment :: September 29, 2006 @ 1:02 pm

  7. 7. peter

    James, Thanks for translation :)

    Ruslan – it is less of the issue for Innodb but still .frm needs to be read etc. So I prefer to keep it simple :)

    Comment :: September 29, 2006 @ 1:28 pm

  8. 8. Roland Bouman

    Hi Peter, James

    I trust you understand my remark was meant to be tongue-in-cheek.

    I’m just very much thrilled to see how Peter is openly sharing all this valuable knowledge, and in such a practical manner. I think it’s quite obvious that hiring his consultancy for performance issues is bound to lead to results – a good thing to know when you are hiring a consultant. Some (if not most) consultants might decide to be less detailed in offering such practical tips in fear of devaluating their skills.

    regards,

    Roland Bouman

    Comment :: September 30, 2006 @ 5:00 am

  9. 9. peter

    Roland,

    You’re right. I prefer to be open in sharing my knowledge/ideas when it comes to MySQL as well as other areas of expertise. I do not think I will drain me. I think the knowledge is more like the stream than like a lake, and sharing it does not decrease it. Furthermore I learn more by sharing.

    I also try to be very forthcoming in my pre-sales calls. I openly share what I think might be wrong based on information they provide during these 15 minutes. This allows customers to be confident I know what I’m doing and if this little tip was only what they needed to solve their problems I’m fine with having provided it for free.

    Comment :: September 30, 2006 @ 8:24 am

  10. 10. Jakiao

    You comment that setting key_buffer to 4G is not uncommon. What if you need to go over 4G? I have noticed that no matter what value you set it at, it will always restrict you to a maximum of 4G.

    Comment :: October 3, 2006 @ 2:10 am

  11. 11. Jakiao

    I should probably mention that I’ve noticed this limit on a Linux 2.6.18 x86_64.

    Comment :: October 3, 2006 @ 2:14 am

  12. 12. peter

    key_buffer has some internal implementation issues which limits its size to 4G. These are to be fixed in one of next MySQL versions but currently you should stick to this size.

    Comment :: October 3, 2006 @ 2:18 am

  13. 13. Mike

    Thanks again – this is excellent information! I’m curious, though, how one would go about setting some of these things on a system with lots of RAM (32G), but with the inherent per-process memory limitations of a 32-bit system. I work with a couple of servers with 8 P3 Xeon processors and 32G of RAM each. I know some buffers are allocated only once for all MySQL threads/processes, and others are per-thread or per-connection.

    Comment :: October 3, 2006 @ 11:06 am

  14. 14. peter

    Mike,

    I thought these beasts are all dead by now. But seriously if you are with 32bit and running Linux you can only allocate buffers up to some 2.5GB or so (depending on a lot of factors) – the rest will be only used as OS cache, which is less efficient. On Windows Innodb also can use AWE for caching which you can try to use.

    Comment :: October 3, 2006 @ 11:23 am

  15. 15. Jakiao

    Is there anyway I can compile my own MySQL binaries and change this 4GB limit? We won’t be able to use MySQL 5.x for many months, and utilizing RAM is key here. This 4GB limit seems to have no place other than to cause headaches.

    Thanks.

    Comment :: October 3, 2006 @ 3:46 pm

  16. 16. peter

    Jakiao,

    MySQL does not have a limit of memory it can use on 64bit systems, it applies to all versions. There is a limit however for particular key_buffer variable which should not be over 4GB. There are some code fixes needed to make it work so it is not simply question of recompiling.

    Comment :: October 4, 2006 @ 6:35 am

  17. 17. James Day

    Jaiko, consider multiple key buffers to get around the 4GB limit if you are using MySQL 4.1 or later.

    Comment :: October 4, 2006 @ 12:58 pm

  18. 18. peter

    Thanks James. I forgot about that one.

    I do not generally like using MyISAM in cases when more than 4GB of buffer may be required. Tables with such large indexes may take a while to recover.

    Comment :: October 4, 2006 @ 1:36 pm

  19. 19. Jakiao

    Hey all,

    I am attempting multiple key_cache’s, it’s just a matter of determining which tables to assign to the alternate cache.

    One thing I noticed is that the primary cache, when set to 4GB, usage never goes over 2GB. Once it reaches 2GB, it drops back to 1.8GB or so. After adding a second key_cache, I’ve notice it go past that 2GB mark.

    Thanks for taking the time to answer my questions. I appreciate it.

    Comment :: October 4, 2006 @ 9:08 pm

  20. 20. Caleb

    Great article. Thanks for it.

    I’ve got a situation that I’ve been trying to figure out for some time now and I haven’t really gotten anywhere with it, unfortunately…

    My VPS has 256mb ram and all of mysql resources are fine with the current setting I have, or at least appear to be – except my tmp_table_size. I’ve got 110mb assigned to it and still 50% or more of the tmp tables created are disk based. (as compared to my key_buffer allocation, for instance, which only has 16mb assigned to it but keeps the fill ratio only between 40-50%).

    My current settings:

    [mysqld]
    max_connections = 300
    max_user_connections = 300
    key_buffer = 16M
    myisam_sort_buffer_size = 16M
    read_rnd_buffer_size = 1M
    join_buffer_size = 2M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 1000
    thread_cache_size = 200
    interactive_timeout = 25
    wait_timeout = 30
    connect_timeout = 10
    max_allowed_packet = 2M
    max_connect_errors = 1000
    query_cache_limit = 1M
    query_cache_size = 12M
    query_cache_type = 1
    tmp_table_size = 110M
    safe-show-database

    Any light anyone can shed on this would be really awesome. :)

    Comment :: October 6, 2006 @ 12:44 pm

  21. 21. Shaheryar

    Great information Peter,

    I am facing a problem these days with MySQL. I have a table that has 3.3 million records (size is around 500 MB) that stores records of members. Now I need to update the records of this table by reading data from a file that contains 1 million email addresses and I need to update one field of those records whose email address is in the file. I created a script, reading 1 email address from file and trying to update the table on email field (that is not primary key but an index has been applied to it) and it takes 6-7 seconds in updating one record that means it will require days to update the whole table. I changed it to first select the primary key of the given email and then trying to update it but there was little difference in result. What settings you suggest for MySQL and what approach to update of the two I just described. Table type is MyISAM and MySQL version is 4.1.21-standard.

    Comment :: October 13, 2006 @ 1:45 am

  22. 22. peter

    Shaheryar,

    First I should ask you to ask unrelated questions at Forums: http://forum.mysqlperformanceblog.com/s/f/2/ rather than use post comments.

    In your case I would run EXPLAIN for SELECT statement similar to update you’re using, it really feels like it is not using index for some reason.

    Generally your method is fine. Depending on the data you have in the file you might be able to load it in the table and write single query which performs an update, but it will need to do similar operation internally anyway.

    Comment :: October 13, 2006 @ 5:20 am

  23. 23. Josh

    I am curious what you think about mixing table types. I have 2 web applications and they are integrated with a site. Now these web apps use MyISAM tables, but I would prefer to use transaction safe tables on the site itself and convert the few web app tables into InnoDB that I insert data into from the site. Then just allow the webapps to use MyISAM on the rest of their tables.

    Is it best to just stick with one table type performance wise?

    Comment :: October 15, 2006 @ 12:13 am

  24. 24. peter

    Josh, Same suggestion to you – please use forums for your questions instead of using comments for unrelated posts.

    Mixing table types is OK. I do it all the time. The thing you should care in terms of performance is mainly memory competition – you will need both allocate memory for OS cache and key_buffer for MyISAM tables and innodb_buffer_pool for Innodb tables and ballancing these might be complicated for performance fine tuning.

    Comment :: October 16, 2006 @ 5:29 am

  25. 25. Spud

    hi,
    i’ve some two forum tables with abot 700Mb each one, and they was type myisam. I was getting some lock problems and i decided to switch them to innodb, but server load growed from 3 to 20. I followed your steps but i got not any server load improvements.

    should i back to myisam? or is there any way to solve it?

    thanks ;)

    Comment :: October 19, 2006 @ 4:04 pm

  26. 26. peter

    Spud,

    Please ask your question at the forum http://forum.mysqlperformanceblog.com/s/f/2/ rather than here if you want to get an answer

    Comment :: October 20, 2006 @ 7:56 am

  27. 27. the_dbatrix

    This is great information thank you. I’m getting used to mySQL after many years in Oracle, and I’m curious as to what metrics can be reviewed to iteratively tune this parameters. For instance, INNODB_BUFFER_POOL_SIZE… how can I evaluate hit ratios to determine if this is undersized? Sure, I may see excessive IO in IOStat, but how do I know it’s from this, versus from disk sorts, or myISAM queries not using the query cache and key cache?

    I’ve noticed in the Administrator GUI tool you can see hit ratios for query cache and key cache in myISAM. Are there underlying sql commands I can run to get this to evaluate the efficiency of my variable settings?

    Thanks again, I’ve found reading through your blog quite illuminating.

    Comment :: October 23, 2006 @ 6:29 pm

  28. 28. peter

    You can run SHOW INNODB STATUS (it is also available in SHOW STATUS in MySQL 5.0+) – this will show you number of buffer pool pages reads/writes per second which is what you can use to differ Innodb IO from anything else.

    Comment :: October 24, 2006 @ 3:32 am

  29. 29. Arul

    Shaheryar,

    You can use the “start transaction + commit” pair to speed up your work. There are different commands for MyISAM and INNODB (can’t remember). Check it out and try it.

    My experience: I once had to load a 1.7 million record table into the DB. Initially the estimated time taken was a few hours. Then I tried the above pair. The upload was completed in 15 minutes! Assuming you are uploading a .sql file, MySQL reads each line and executes it and moves down to the next. With the “Start Transaction” and “Commit” around a chunk of query, SQL will not execute until it reaches the “commit”. So I figure that cuts down drastically on overhead processing.

    Comment :: October 25, 2006 @ 8:34 pm

  30. 30. jgabios

    also a good thing is to log your slow queries with:
    log_slow_queries=/var/log/mysqld.slow.log
    and
    long_query_time=1
    I set it to 1 second because my mysql in in web environment and if something takes more than a second, then the users will wait a lot for the page to load and never come back.

    Comment :: November 17, 2006 @ 3:15 am

  31. 31. Rohan

    Hi,

    I’m facing problem of slave servers are getting behind master. I’ve one slave and three slave. All slave are getting behind master.
    Server details
    Dual Xeon, 4 GB ram
    All servers are on same gig network.
    All my tables are MyIsam.

    Following is out of mytop

    MySQL on localhost (5.0.22-standard-log) up 12+23:09:57 [13:00:53]
    Queries: 598.6M qps: 560 Slow: 0.0 Se/In/Up/De(%): 03/00/00/00
    qps now: 4928 Slow qps: 0.0 Threads: 37 ( 34/ 2) 01/00/00/00
    Key Efficiency: 100.0% Bps in/out: 0.0/ 0.5 Now in/out: 42.0/18.7k

    qps varies from 3000 to 8000.

    My.cnf

    key_buffer = 4096M
    max_allowed_packet = 32M
    table_cache = 812
    sort_buffer_size = 1024M
    read_buffer_size = 512M
    myisam_sort_buffer_size = 1024M
    thread_cache = 16
    query_cache_size = 512M
    max_connections = 300
    max_user_connections = 300
    max_connect_errors = 99
    wait_timeout=10
    interactive_timeout = 10
    #set-variable=wait_timeout=3000
    # Try number of CPU’s*2 for thread_concurrency
    thread_concurrency = 8

    Please suggest which parameter to tweak. And where to look for the problem

    Waiting for your reply.

    Rohan

    Comment :: December 7, 2006 @ 12:31 am

  32. 32. peter

    Rohan,

    Please use forum.mysqlperformanceblog.com for such questions.
    In general I’m surprised it works at all, your sort_buffer_size and read_buffer_size are way off.

    Slaves can get behind master ether because write load is high or if you load slaves with reads too much.

    Comment :: December 7, 2006 @ 2:54 am

  33. 33. nustchen

    Deal Peter:
    Hi. First my english is very poor. Please forgive me for my grammar mistake.
    Your article is very useful for me. But I still want to know how can I tune “sort_buffer_size”.
    What status can I use to monitor the performance improvement when I tuned “sort_buffer_size”.

    Comment :: December 25, 2006 @ 10:45 pm

  34. 34. peter

    You would normally look at sort_merge_passes status variable. If this variable is increased sort_buffer was not enough to accommodate all data to perform the sort and file based sort was used. Note you do not always have to increase global sort_buffer value. In many cases setting per connection variable before running long queries would be good enough.

    Comment :: December 28, 2006 @ 5:49 am

  35. 35. Flasher

    Hi Peter,

    Its realy nice post.

    i want to create a table for templates and replace the type of files .tpl [for Smarty-Light]

    so which is the best type for my table ( MyISAM or InnoDB ) ?

    and Thanks..

    Comment :: December 29, 2006 @ 9:18 pm

  36. 36. peter

    Flasher,

    I did not exactly get your question. Also it is best to ask unrelated questions at forum.mysqlperformanceblog.com

    Comment :: December 30, 2006 @ 1:52 am

  37. 37. merlin

    I have a mysql server with 300+ workstations connecting to it and my server has only 4 GB. sometimes the server has 3000+ qps. on the average it has 125 qps.

    is 4GB enough since were adding more workstations that will be inserting records to this server? I was told that a standard mysql server must have at least 16 GB ram — true?

    Comment :: February 7, 2007 @ 8:53 am

  38. 38. peter

    Merlin,

    MySQL can run with very low amount of memory. 16GB is not required and it is surely not the lowest number.

    Database size and workload define how much memory you’ll need.

    Comment :: February 8, 2007 @ 6:20 am

  39. 39. merlin

    I have 65 tables now at 4.3 GB. One table has 6 million rows. Now we average at 300+ queries per second. Average threads at 150+

    How do I optimize my box?

    Comment :: February 8, 2007 @ 7:00 am

  40. 40. youtube videos

    It could be helpful to show example my.cnf files for sample configurations.

    Comment :: March 4, 2007 @ 1:46 pm

  41. 41. Strafverteidiger München

    James, Thanks for translation. very cool job.

    Comment :: April 19, 2007 @ 11:05 am

  42. 42. Ewin Barnett

    I am working building a simple table based on a very large sequential file of about 50 million titles that are in random order. I plan two indexes, one of just the title and one composed of only a date. Can anyone suggest if it would be better to use MyISAM or InnoDB?

    Comment :: May 13, 2007 @ 2:21 pm

  43. 43. youtube video izlesene

    James, Thanks for translation. very cool job.

    sometimes i got an error like this .. “mysql max_connection_user has a lot connection” .. what does it mean ?.. when i restart the httpd and mysql it will be resolve ..

    someone can help me about that .
    Thnks..

    Comment :: July 23, 2007 @ 7:46 pm

  44. 44. Blue System

    Hi every1,
    Database size and workload define how much memory you’ll need.
    any formula from this?? I really have problem on this now.
    pls help !!! extremely urgent.

    tqvm.

    Comment :: September 6, 2007 @ 2:22 am

  45. 45. meeero

    hello,

    thank you for all that information, i’ve used the defauls all the time on a mysql-server with heavy load… now i’ll see how the changes affect everyday-operation

    Comment :: October 4, 2007 @ 6:41 pm

  46. 46. Jitendra Thakor

    Hello every one
    Que:I create webapplication of company and i use mysql server
    but after some trasaction application sql server speed much down
    so i am not get reply sooon
    i send request through Ajax

    Comment :: October 19, 2007 @ 11:40 pm

  47. 47. Anil Kumar MS

    Hi friends,iam using MySQL5.0.

    I have a database with 2mb size.There are more than 30 tables.

    In a query, iam using joins to retrieve records from db.which joins 10 tables.Iam using windows 2003 server with 1GB RAM.Please specify the optimum settings for this hardware.Data will be bulk.

    Comment :: November 1, 2007 @ 12:32 am

  48. 48. ahmed

    If I have an insert/update and a delete statement in a mysql stored procedure,does it mean that the second statement(delete) is executed only after the inserts/updates are completed ? Or do they all run concurrently.If yes,how to make them run one after another?

    Comment :: January 8, 2008 @ 10:41 pm

  49. 49. Michael

    Anil, major optimum setting: move off of windows anything, to anything else..anything is an improvement. All the rest of us already know this. Why don’t you?

    Comment :: January 14, 2008 @ 7:49 am

  50. 50. ztej

    Hi Peter, this article actual a 2008 year, thanks

    Comment :: January 22, 2008 @ 12:10 pm

  51. 51. ramkiran

    how to determine the installed instance name?

    can any bosy help me in the form of a query

    Comment :: April 2, 2008 @ 4:40 am

  52. 52. Dane Jensen

    Great guide! One question, is there any sort of performance gain/penalty from using innodb_file_per_table?

    And one small correction, it should be ‘innodb_flush_log_at_trx_commit’ (singular log), rather than ‘innodb_flush_logs_at_trx_commit’ it seems.

    Comment :: June 20, 2008 @ 4:00 pm

  53. 53. Roel

    innodb_flush_logs_at_trx_commit should be innodb_flush_log_at_trx_commit (no s)

    Comment :: July 7, 2008 @ 6:09 pm

  54. 54. peter

    Thanks. Fixed :)

    Comment :: July 7, 2008 @ 7:41 pm

  55. 55. Sam Nicolosi

    I am a registered user on a couple of “blogs” (websites) that allow articles to be discussed (by registered users). And the users are given a opportunity to “edit” his/her own post during a 30 minute period following submission of same.
    One of the sites where I am encountering a problem is: commondreams.org

    Each time I try to EDIT one of my posts, I am allowed (once) to perform the edit, but I then immediately receive this message:

    “WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1]

    SELECT comment_content, comment_author_IP, comment_date_gmt FROM wp_comments
    WHERE comment_ID =

    You aren’t allowed to edit this comment, either because you didn’t write it
    or you passed the 30 minute time limit.”

    I am using a PC running Windows XP, and using either Firefox or Internet Explorer
    (the problem occurs with either browser).

    What is “my SQL syntax”?
    Where does it “reside”?
    How can I “fix it”?
    Is there some nifty “patch” that will fix it for me?
    I am not a “tech-type”, so please try not to answer me in “Computereese”

    Thanks!

    Comment :: July 27, 2008 @ 2:17 pm

  56. 56. Jitendra

    Hi, Peter,

    I am new to mysql setting level.

    Right now I am working in a PHP-Mysql base project in which we have one job table and one url master table.
    Job table contains 15000000 records and url table contains 25000 record. Every day we are crawling all the urls and if there is any new job on those url then we will insert that record in our job table. Show each and every day there are near 100000 new job record inserted into job table.

    Right now out both table type is ‘innodb’.

    But problem is right now our query takes two much time for duplicate checking and other type processes.

    Can you please tell me what are the settings required in mysql for incressing performance of my project.

    Also can you please tell me that the type of table ‘innodb’ is OK ro not..

    Below are the my server ‘SHOW STATUS’ output;

    Key_read_requests 9490927
    Key_reads 72073
    Key_write_requests 678314
    Key_writes 430602
    Open_tables 561
    Open_files 278

    Thanks !

    Comment :: September 5, 2008 @ 10:36 pm

  57. 57. top gazduire web

    Mysql syntax it’s very simple you juste learn some tricks.

    Comment :: September 14, 2008 @ 4:26 am

  58. 58. http://blog.colnect.com/

    Comment by “Sam Nicolosi” (no.81) is simply spam. WTF would some1 write such a long message in a place that’s completely irrelevant if not for the sole purpose of including a url?

    Anyway, very nice and helpful post. I’ve posted my question in your forum.
    This blog comes up on almost any mySQL config variable I search on Google :)

    Comment :: October 18, 2008 @ 4:55 pm

  59. 59. Jeremy

    Following some advice given here and in the mysql documentation, I’m setting up 3 keycaches. When I assign tables to a keycache, is that permanent or is that information lost when I restart the server or daemon? Also, is there a way to tell which keycache a table is assigned to currently?

    Comment :: November 18, 2008 @ 10:00 pm

  60. 60. yashesh

    Hi.

    I changed the parameter – innodb_log_file_size and many of mysql databases were giving lots of errors
    the

    mysqlcheck would give errors
    db1.civicrm_price_set_entity
    error : Incorrect information in file: ‘./db1/civicrm_price_set_entity.frm’

    mysql
    mysql> desc access;
    ERROR 1033 (HY000): Incorrect information in file: ‘./db1/access.frm’
    mysql> exit;

    mysqldump: Got error: 1033: Incorrect information in file: ‘./db1/access.frm’ when using LOCK TABLES

    Surprising thing is that of the above 3, only civicrm_price_set_entity is an innodb table. The other 2 are myisam.
    Finally after a lot of searcing i came across this very informative post –

    http://forums.mysql.com/read.php?34,177212,234851#msg-234851

    i’ll have to study a bit more deeper as to how to change the parameter innodb_log_file_size but it looks like it’s not as easy as
    editing the my.cnf file and restarting mysql.

    yashesh

    Comment :: January 30, 2009 @ 6:22 am

  61. 61. Baron Schwartz

    You have to move the old log files away. InnoDB is disabled in your server right now.

    Comment :: January 30, 2009 @ 6:42 am

  62. 62. kuber shrestha

    I am using Mysql server version 4.1.20
    and i in my server there are almost 10 databases but 2 databases running frequently. One has 40 tables and other has 120 tables. and they contains 133478 rows in each table.

    Mysql configuration is as follows:::

    [root@crm ~]$ vi /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [mysqld_safe]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    max_connections=255
    query_cache_size=8M
    table_cache=510
    tmp_table_size=13M
    thread_cache_size=12
    myisam_max_sort_file_size=100G
    myisam_max_extra_sort_file_size=100G
    myisam_sort_buffer_size=8M
    key_buffer_size=64M
    read_buffer_size=1M
    read_rnd_buffer_size=1M
    sort_buffer_size=203K
    innodb_additional_mem_pool_size=2M
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=1M
    innodb_buffer_pool_size=200M
    innodb_log_file_size=10M
    innodb_thread_concurrency=8

    My Problem is that, day by day i see that the number of slow queries is increasing on.

    mysql> \s
    ————–
    mysql Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3

    Connection id: 303745
    Current database: office_new
    Current user: root@localhost
    SSL: Not in use
    Current pager: stdout
    Using outfile: ”
    Using delimiter: ;
    Server version: 4.1.20
    Protocol version: 10
    Connection: Localhost via UNIX socket
    Server characterset: latin1
    Db characterset: latin1
    Client characterset: latin1
    Conn. characterset: latin1
    UNIX socket: /var/lib/mysql/mysql.sock
    Uptime: 72 days 19 hours 45 min 58 sec

    Threads: 1 Questions: 10163302 Slow queries: 149 Opens: 149457 Flush tables: 1 Open tables: 64 Queries per second avg: 1.615
    ————–

    Please suggest me how to increase the performance of my server???? There are almost 20-30 user’s online each time.

    Please suggest me

    My email address is kuberstha@subisu.net.np

    Comment :: June 22, 2009 @ 1:35 am

  63. 63. MySQL Newbie

    Hi Peter,

    These tips definately sound great from the number of responses that you have got ! I am not really a DBA but have been using oracle for 8+ years now, I am more of a BI engineer who knows to use SQL and write fairly complex queries.
    I am very intrested in learning MySQL and perfromance tunning in MySQL from a reporting standpoint, ie to optimise the database (datawarehouse) for reporting.
    Would you be kind enough to provide me with a starting point ? some website which starts with the basics of tunning ? (once again I am not a DBA but am a BI engineer)

    (I am able to google and get way too many links and get easily lost -:)

    Thanks in Advance !

    Comment :: June 29, 2009 @ 2:43 pm

  64. 64. MySQL Newbie

    For example, I found this link on google http://www.mysql.com/why-mysql/performance/
    But it has so many articles and documents within and so was not sure what would be the right start and best bet from a time line perspective.

    Thanks again !

    Comment :: June 29, 2009 @ 2:49 pm

  65. 65. Kuber Shrestha

    Please Anyone suggest me how to increase the performance of my MYSQL server. Day by day the number of slow queries are increasing on!….

    Threads: 1 Questions: 10163302 Slow queries: 149 Opens: 149457 Flush tables: 1 Open tables: 64 Queries per second avg: 1.615

    max_connections=255
    query_cache_size=8M
    table_cache=510
    tmp_table_size=13M
    thread_cache_size=12
    myisam_max_sort_file_size=100G
    myisam_max_extra_sort_file_size=100G
    myisam_sort_buffer_size=8M
    key_buffer_size=64M
    read_buffer_size=1M
    read_rnd_buffer_size=1M
    sort_buffer_size=203K
    innodb_additional_mem_pool_size=2M
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=1M
    innodb_buffer_pool_size=200M
    innodb_log_file_size=10M
    innodb_thread_concurrency=8

    Comment :: June 29, 2009 @ 10:16 pm

  66. 66. MySQL Newbie

    Kuber,
    If I may ask you a basic question please ?
    How do you get these stats ? I mean from where ? (I am a newbie-:)

    Hope some expert helps you with your question !

    Regards,

    Comment :: June 30, 2009 @ 11:32 am

  67. 67. Kuber Shrestha

    These are mysql configuration and it is found in my.ini or my.cnf which is the base of mysql configuration.
    in linux you can view this file in this location
    /etc/my.cnf

    Comment :: July 1, 2009 @ 12:37 am

  68. 68. MySQL Newbie

    Thanks Kuber !

    Comment :: July 2, 2009 @ 12:14 pm

  69. 69. Wagner Bianchi

    D’you consider “misc” like what, when treat about the functions of innodb_additional_mem_pool_size? I’ve used this variable to tune server to alocate dictionary cache…is this true?

    Comment :: September 9, 2009 @ 7:26 pm

  70. 70. john

    http://blog.taragana.com/index.php/archive/one-mysql-configuration-tip-that-can-dramatically-improve-mysql-performance/

    try this

    max_write_lock_count = 1

    Comment :: October 7, 2009 @ 12:51 pm

  71. 71. aftab

    im running my chat server at centos with 3.5 gb ram , i observered there are round about 100 queries per second can you please guess some important parameteres and their values for my server that can increase speed of mysql .. currently it is going slower and slower as the users increasing

    Comment :: October 13, 2009 @ 7:17 pm

 

Subscribe without commenting

Trackbacks/Pingbacks