September 2, 2014

What to tune in MySQL Server after installation

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 for your initial MySQL performance tuning, 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 to optimize your MySQL database. 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.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Wagner Bianchi says:

    Normally, I have been adding skip-name-resolve on both sides, on both MySQL Servers involved on a remote connection.

    ERRATA on my last interaction:

    SHOW VARIABLES LIKE ‘key%’ will show you all variables involved in maintain key_buffer_size internal system and SHOW STATUS LIKE ‘key%’ will show you MySQL key_buffer_size status variables what is the snapshot of key_buffer behavior.

  2. benhur says:

    Daer sir,

    Kill 20509 unauthenticated user 89.x.x.x:2501 None Connect Reading from net —

    I have already added the skip-name-resolve, skip-host-cache, and skip-locking option to my.cnf. As a added measure I have already added the IP Address to the /etc/hosts and the privileges for the user and IP address has also been granted in the database already.

    still problem is there can any abody please help

    Please help.

    Regards,
    benhur

  3. Rasmus Møller says:

    Hello,

    our old MySQL 4.1 server has started to fail during a large SELECT INTO OUTFILE 4.5 mio records totalling 1.5GB with an “ORDER BY” statement at the end.

    If we test run without “ORDER BY” there is no error.
    If we select fewer records there is no error.

    We are p.t. not at liberty to upgrade Windows nor MySQL version nor are we allowed to change the SQL statement.

    Are there any MY.INI config parameters which should be tweaked?

  4. sjc says:

    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

  5. peter says:

    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.

  6. 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?

  7. peter says:

    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.

  8. James Day says:

    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

  9. Is table_cache important for InnoDB setups?

  10. peter says:

    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 :)

  11. 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

  12. peter says:

    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.

  13. Jakiao says:

    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.

  14. Jakiao says:

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

  15. peter says:

    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.

  16. Mike says:

    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.

  17. peter says:

    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.

  18. Jakiao says:

    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.

  19. peter says:

    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.

  20. James Day says:

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

  21. peter says:

    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.

  22. Jakiao says:

    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.

  23. Caleb says:

    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. :)

  24. Shaheryar says:

    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.

  25. peter says:

    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.

  26. Josh says:

    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?

  27. peter says:

    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.

  28. Spud says:

    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 ;)

  29. peter says:

    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

  30. 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.

  31. peter says:

    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.

  32. Arul says:

    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.

  33. jgabios says:

    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.

  34. Rohan says:

    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

  35. peter says:

    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.

  36. nustchen says:

    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”.

  37. peter says:

    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.

  38. Flasher says:

    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..

  39. peter says:

    Flasher,

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

  40. merlin says:

    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?

  41. peter says:

    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.

  42. merlin says:

    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?

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

  44. James, Thanks for translation. very cool job.

  45. Ewin Barnett says:

    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?

  46. 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..

  47. Blue System says:

    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.

  48. meeero says:

    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

  49. Jitendra Thakor says:

    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

  50. Anil Kumar MS says:

    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.

  51. ahmed says:

    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?

  52. Michael says:

    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?

  53. ztej says:

    Hi Peter, this article actual a 2008 year, thanks

  54. ramkiran says:

    how to determine the installed instance name?

    can any bosy help me in the form of a query

  55. Dane Jensen says:

    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.

  56. Roel says:

    innodb_flush_logs_at_trx_commit should be innodb_flush_log_at_trx_commit (no s)

  57. peter says:

    Thanks. Fixed :)

  58. Sam Nicolosi says:

    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!

  59. Jitendra says:

    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 !

  60. 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 :)

  61. Jeremy says:

    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?

  62. yashesh says:

    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

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

  64. kuber shrestha says:

    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

  65. MySQL Newbie says:

    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 !

  66. MySQL Newbie says:

    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 !

  67. Kuber Shrestha says:

    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

  68. MySQL Newbie says:

    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,

  69. Kuber Shrestha says:

    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

  70. MySQL Newbie says:

    Thanks Kuber !

  71. Wagner Bianchi says:

    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?

  72. john says:
  73. aftab says:

    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

  74. odeng says:

    Hi all,

    I really need your help on finding the solution for my problems. Fyi, i’m using 2 X quad core processor with 8gb of RAM. This server is a dedicated database server. So, my problems are, the mysql process always use 1 cpu core at 1 time and it used 100% of the cpu resource. I’am not sure why it happen like it. Below are my server ‘TOP’ status, as you can see cpu4 use 100% usage while others like at idle state.

    top – 18:27:53 up 103 days, 7:14, 3 users, load average: 1.10, 1.07, 1.02
    Tasks: 191 total, 1 running, 190 sleeping, 0 stopped, 0 zombie
    Cpu0 : 0.0% us, 0.3% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% si
    Cpu1 : 0.0% us, 0.0% sy, 0.0% ni, 99.3% id, 0.7% wa, 0.0% hi, 0.0% si
    Cpu2 : 0.3% us, 0.0% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% si
    Cpu3 : 0.0% us, 0.0% sy, 0.0% ni, 98.6% id, 1.4% wa, 0.0% hi, 0.0% si
    Cpu4 : 100.0% us, 0.0% sy, 0.0% ni, 0.0% id, 0.0% wa, 0.0% hi, 0.0% si
    Cpu5 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
    Cpu6 : 0.0% us, 0.0% sy, 0.0% ni, 100.0% id, 0.0% wa, 0.0% hi, 0.0% si
    Cpu7 : 0.0% us, 0.0% sy, 0.0% ni, 99.7% id, 0.3% wa, 0.0% hi, 0.0% si
    Mem: 8165752k total, 8077196k used, 88556k free, 493192k buffers
    Swap: 4096532k total, 276k used, 4096256k free, 6326856k cached

    PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND
    11395 mysql 16 0 100 39700:41 2.2 706m 172m 4764 S mysqld

    Below is my.cnf configuration file

    [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
    skip-locking
    key_buffer = 384M
    max_allowed_packet = 1M
    table_cache = 512
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 32M
    log_queries_not_using_indexes = On
    log_slow_queries=/var/lib/mysql/log-slow-queries.log

    innodb_data_home_dir = /var/lib/mysql/
    #innodb_data_file_path = ibdata1:100M:autoextend
    set-variable = innodb_buffer_pool_size=100M
    set-variable = innodb_additional_mem_pool_size=10M
    innodb_flush_log_at_trx_commit=1

    #skip-innodb
    skip-name-resolve
    # Try number of CPU’s*2 for thread_concurrency
    thread_concurrency = 16
    set-variable=max_connections=2000

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

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

    [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

    [mysqlhotcopy]
    interactive-timeout

  75. Odeng, the comments aren’t a place where we answer specific questions like that. If you really need help, we provide consulting on a paid basis. If you are looking for free help, you should post your question in a forum or mailing list.

  76. Barbon says:

    Hi, I have a question. Does the settings on the post above are up to date ? I didn’t see last update on the article.

    Think you

  77. cnizz says:

    Thanks this has been a big help. So on a dedicated MySQL server with 12 GB of RAM your saying the key_buffer_size should be set to about 3 GB? Right now its set at 512 MB…I’m slowly bumping it up by 512 MB every few days to gage performance. Please advise.

  78. Wagner Bianchi says:

    cnizz,

    I think that you must fit key_buffer_size memory space with all its tables indexes, as much as you can. SHOW VARIABLES LIKE ‘key%’ will show you all key_buffer_size status variables and than you’ll have an idea as you can proceed to configure key_buffer variable. Attention to a point: increase arbitrarily key_buffer variable will cause bad performance. Study and adjust it better as possible. Max 4GB, ok?

    Regards.

  79. benhur says:

    Daer sir,

    Kill 20509 unauthenticated user 89.x.x.x:2501 None Connect Reading from net —

    I have already added the skip-name-resolve, skip-host-cache, and skip-locking option to my.cnf. As a added measure I have already added the IP Address to the /etc/hosts and the privileges for the user and IP address has also been granted in the database already.

    still problem is there can any abody please help

    Please help.

    Regards,
    benhur

  80. Normally, I have been adding skip-name-resolve on both sides, on both MySQL Servers involved on a remote connection.

    ERRATA on my last interaction:

    SHOW VARIABLES LIKE ‘key%’ will show you all variables involved in maintain key_buffer_size internal system and SHOW STATUS LIKE ‘key%’ will show you MySQL key_buffer_size status variables what is the snapshot of key_buffer behavior.

  81. Be advised,
    when changing the values of variables
    innodb_log_file_size and innodb_log_buffer_size
    you must delete the file ib_logfile0 and probably ib_logfile1 too.
    otherwise it will not open the tables notifying: .frm file corrupted

  82. Budi Santoso says:

    I migrated our myISAM engine to InnoDB Engine and the performance is so poor after that and did some performance tuning based on above guidance. Now our MYSQL database is running faster and have a better performance.

    Thank for sharing

  83. Prinzhorn says:

    Thanks for your tips.

    BUT: Please add a big warning that just changing “innodb_log_file_size” is not enough (see other comments for more info)! Since this post still ranks very high it would be important. Otherwise people like me will update their my.cnf and certainly the production site goes down ;-)

  84. Tariq Masood says:

    Dear All

    I developed an application of attendance system in Java and MySQL.
    When I get the attendance register of five employees it is working fine and report is opened.
    But when I send the command for more than five employees then database is disconnected
    with my application. The error is given below.

    Because there are several queries with while loop in my application.

    I request to all please help me to resolve the issue.
    I shall be thankful to you.

    regards

    Tariq Masood

    SQLException: Communications link failure due to underlying exception:

    ** BEGIN NESTED EXCEPTION **

    java.net.SocketException
    MESSAGE: java.net.BindException: Address already in use: connect

    STACKTRACE:

    java.net.SocketException: java.net.BindException: Address already in use: connect
    at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:156)
    at com.mysql.jdbc.MysqlIO.(MysqlIO.java:276)
    at com.mysql.jdbc.Connection.createNewIO(Connection.java:2641)
    at com.mysql.jdbc.Connection.(Connection.java:1531)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
    at java.sql.DriverManager.getConnection(DriverManager.java:579)
    at java.sql.DriverManager.getConnection(DriverManager.java:243)
    at biopayroll.jdbcConnection.myStringGet(jdbcConnection.java:258)
    at biopayroll.rep_emp_attendance_register.get_gh_holiday(rep_emp_attendance_register.java:693)
    at biopayroll.rep_emp_attendance_register.jButton4ActionPerformed(rep_emp_attendance_register.java:311)
    at biopayroll.rep_emp_attendance_register.access$300(rep_emp_attendance_register.java:33)
    at biopayroll.rep_emp_attendance_register$4.actionPerformed(rep_emp_attendance_register.java:250)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6504)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6269)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4860)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2713)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:707)
    at java.awt.EventQueue.access$000(EventQueue.java:101)
    at java.awt.EventQueue$3.run(EventQueue.java:666)
    at java.awt.EventQueue$3.run(EventQueue.java:664)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:680)
    at java.awt.EventQueue$4.run(EventQueue.java:678)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:677)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

    ** END NESTED EXCEPTION **

  85. JMaldonado says:

    Hello everyone;

    I have a server with this configuration:
    2x Intel Xeon 2.53 GHz
    64GB RAM
    4x 80 GB SSD MLC
    CentOS 5 x 64 bits
    RAID-5 HW

    In this server is installing MySql 5.5.21 and the sorftware has the following settings:
    [mysqld]
    log-error=/var/log/mysqld.log
    init_connect=SET CHARACTER SET utf8
    datadir=/data/mysql
    socket=/data/mysql/mysql.sock

    [General]
    skip-name-resolve
    #skip-bdb
    default-time-zone=America/Caracas
    max_connections = 1000
    wait_timeout = 60
    thread_concurrency = 2
    max_allowed_packet = 1M

    [Slow Querys]
    #log-slow-queries = /var/log/mysqlslowq.log
    #long_query_time = 5
    #log-queries-not-using-indexes

    [Cache]
    query_cache_type = 1
    query_cache_size = 64M
    query_cache_limit = 64M
    thread_cache_size = 70
    table_cache = 2048
    table_open_cache = 512

    [Buffers]
    join_buffer_size = 32M
    key_buffer_size = 640M
    myisam_sort_buffer_size = 64M
    sort_buffer_size = 4M
    read_rnd_buffer_size = 12M
    read_buffer_size = 4M

    [Server]
    server-id = 1
    log-bin=/data/mysql/drbd
    [Server]
    server-id = 1
    log-bin=/data/mysql/drbd
    expire_logs_days = 5
    max_binlog_size = 100M

    [client]
    socket=/data/mysql/mysql.sock
    [Server]
    server-id = 1
    log-bin=/data/mysql/drbd
    expire_logs_days = 5
    max_binlog_size = 100M

    [client]
    socket=/data/mysql/mysql.sock

    The problem that we have is when conect to phpmyadmin and the system return to us that we have had 152 conections and this value is the max conection; when we have setting this parameters in 1000; so when I run the tuner script this one tell me that I have get the max connection (152) when I have set this parameter at 1000.

    Could any one gime me some advice or tell were is the wrong of theses settings?

    What I have to do to the system take correctly the settings?

    Thanks a lot for any comments.

  86. Michael says:

    I am working with MyISAM tables inserting millions of rows on Windows. MySQL writes to disk using very small I/O chunks. I am using a simple script inserting data into a table and monitoring disk I/O and I see that every I/O is 13 bytes. There is millions of small I/Os writtten to MYD file with sequentially increasing offset. Is there a way to increase the size of write I/O when insterting data into MyISAM tables? For example, instead of writing to disk every single row to write data by much larger blocks.

    Thank you
    Michael

  87. Aptitude says:

    innodb_buffer_pool_size was the actual thing I was searching for, search finally ends at your blog. Thanks a lot for such kind help. keep it up. thanks again

  88. Sorry for my bad english.
    I have a Managed VPS server (DV4 on MT).
    2GB Ram
    64-bit CentOS 6, MySQL 5.5 & PHP 5.4
    I run an application that takes an xml and inserts to the database are about 400,000 insertions per hour.
    In the next hour the system drop the database data and inserts again, this happens in the backend
    In the frontend of the website are connected near to 1000 users per hour, and mysql work is minimal.
    My experience with MySQL parameters is very low.
    But I need help tuning my MySQL to make it work better in the backend.
    I show my configuration and the results of mysqltuner.pl and tuning-primer.sh

    My.cnf————————————————-
    [mysqld]
    # Basic settings
    user = mysql
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    # Security settings
    local-infile = 0
    symbolic-links = 0
    # Memory and cache settings
    query_cache_type = 1
    query_cache_size = 98M
    query_cache_limit = 24M
    thread_cache_size = 24
    table_cache =12
    tmp_table_size = 256M
    max_heap_table_size = 48M
    join_buffer_size = 12M
    key_buffer_size = 460M
    max_connections = 200
    wait_timeout = 3600
    # Innodb settings
    innodb_buffer_pool_size = 256M
    innodb_additional_mem_pool_size = 64M
    innodb_log_buffer_size = 3M
    innodb_thread_concurrency = 6
    [mysqld_safe]
    # Basic safe settings
    log-error = /var/log/mysqld.log
    pid-file = /var/run/mysqld/mysqld.pid
    ### Log slow queries ###
    log-slow-queries = mysqld-slow.log
    # Duration a query must run (in seconds) to get logged
    long_query_time = 1
    ——————————————————

    mysqltuner.pl
    >> MySQLTuner 1.2.0 – Major Hayden
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with ‘–help’ for additional options and output filtering
    ——– General Statistics ————————————————–
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.54
    [OK] Operating on 64-bit architecture
    ——– Storage Engine Statistics ——————————————-
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 304)
    [--] Data in InnoDB tables: 2M (Tables: 173)
    [!!] Total fragmented tables: 191
    ——– Security Recommendations ——————————————-
    [OK] All database users have passwords assigned
    ——– Performance Metrics ————————————————-
    [--] Up for: 21h 8m 26s (5M q [75.629 qps], 41K conn, TX: 1B, RX: 28B)
    [--] Reads / Writes: 0% / 100%
    [--] Total buffers: 929.0M global + 14.6M per thread (200 max threads)
    [!!] Maximum possible memory usage: 3.8G (110% of installed RAM)
    [OK] Slow queries: 0% (12/5M)
    [OK] Highest usage of available connections: 4% (8/200)
    [OK] Key buffer size / total MyISAM indexes: 460.0M/410.3M
    [OK] Key buffer hit rate: 99.4% (64M cached / 372K reads)
    [OK] Query cache efficiency: 93.6% (469K cached / 501K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [!!] Joins performed without indexes: 263
    [OK] Temporary tables created on disk: 22% (1K on disk / 7K total)
    [OK] Thread cache hit rate: 99% (8 created / 41K connections)
    [!!] Table cache hit rate: 0% (12 open / 11K opened)
    [OK] Open file limit used: 2% (24/1K)
    [OK] Table locks acquired immediately: 99% (41K immediate / 41K locks)
    [OK] InnoDB data size / buffer pool: 2.8M/256.0M
    ——– Recommendations —————————————————–
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours – recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    *** MySQL’s maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 12.0M, or always use indexes with joins)
    table_cache (> 12)

    ———————————————————————
    tuning-primer.sh
    — MYSQL PERFORMANCE TUNING PRIMER –
    - By: Matthew Montgomery -
    MySQL Version 5.1.54 x86_64
    Uptime = 0 days 21 hrs 7 min 34 sec
    Avg. qps = 75
    Total Questions = 5753619
    Threads Connected = 2
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com…-variables.html
    Visit http://www.mysql.com…e/advisors.html
    for info about MySQL’s Enterprise Monitoring and Advisory Service
    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10.000000 sec.
    You have 12 out of 5753646 that take longer than 10.000000 sec. to complete
    Your long_query_time seems to be fine
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com…e-recovery.html
    WORKER THREADS
    Current thread_cache_size = 24
    Current threads_cached = 6
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    MAX CONNECTIONS
    Current max_connections = 200
    Current threads_connected = 2
    Historic max_used_connections = 8
    The number of used connections is 4% of the configured maximum.
    You are using less than 10% of your configured max_connections.
    Lowering max_connections could help to avoid an over-allocation of memory
    See “MEMORY USAGE” section to make sure you are not over-allocating
    INNODB STATUS
    Current InnoDB index space = 2 M
    Current InnoDB data space = 2 M
    Current InnoDB buffer pool free = 96 %
    Current innodb_buffer_pool_size = 256 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    MEMORY USAGE
    Max Memory Ever Allocated : 997 M
    Configured Max Per-thread Buffers : 2.85 G
    Configured Max Global Buffers : 881 M
    Configured Max Memory Limit : 3.71 G
    Physical Memory : 3.42 G
    Max memory limit exceeds 90% of physical memory
    KEY BUFFER
    Current MyISAM index space = 410 M
    Current key_buffer_size = 460 M
    Key cache miss rate is 1 : 171
    Key buffer free ratio = 81 %
    Your key_buffer_size seems to be fine
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 98 M
    Current query_cache_used = 25 M
    Current query_cache_limit = 24 M
    Current Query cache Memory fill ratio = 25.84 %
    Current query_cache_min_res_unit = 4 K
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won’t cache query results that are larger than query_cache_limit in size
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine
    JOINS
    Current join_buffer_size = 12.00 M
    You have had 263 queries where a join could not use an index properly
    join_buffer_size >= 4 M
    This is not advised
    You should enable “log-queries-not-using-indexes”
    Then look for non indexed joins in the slow query log.
    OPEN FILES LIMIT
    Current open_files_limit = 1024 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    TABLE CACHE
    Current table_open_cache = 12 tables
    Current table_definition_cache = 256 tables
    You have a total of 500 tables
    You have 12 open tables.
    Current table_cache hit rate is 0%
    , while 100% of your table cache is in use
    You should probably increase your table_cache
    You should probably increase your table_definition_cache value.
    TEMP TABLES
    Current max_heap_table_size = 48 M
    Current tmp_table_size = 256 M
    Of 5775 temp tables, 22% were created on disk
    Effective in-memory tmp_table_size is limited to max_heap_table_size.
    Created disk tmp tables ratio seems fine
    TABLE SCANS
    Current read_buffer_size = 128 K
    Current table scan ratio = 5654 : 1
    You have a high ratio of sequential access requests to SELECTs
    You may benefit from raising read_buffer_size and/or improving your use of indexes.
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 6979
    Your table locking seems to be fine

  89. Hi Alejandro,

    This post is actually 7 years old – and your question is very detailed. The best place to get advice for issues like yours is the Percona discussion forums – that is where you can ask for help from not only your peers but also Percona experts who participate on some of the posts. Here’s the url: http://www.percona.com/forums/

  90. Thanks.

Speak Your Mind

*