Beginning with PHP Compile to support MySQL.
Usually, Compile PHP supports MySQL with --with-mysql. This method will use the MySQL Lib Client Bundle with PHP, which is the old version. There is also a new extension called MySQLi. If using MySQLi will not You can use the MySQL Lib Client that Bungle comes with. It will be a hit, so to start, I'd recommend Compile PHP as well --with-mysql = / usr / local / mysql (or if mysql is somewhere else, use a different path).
For ease of use, Thai language is usually setup in my.cnf that default-character-set = tis620. This method will cause MySQL to run around 20 - 30% slower, but that's fine. Because anyway I have to use Thai already
After putting the statement that default-character-set = tis620 into my.cnf, then I get the MySQL Client. It's a must because the Server's charset is tis620 but the client's Latin. Therefore, I have to setup another one which is skip-character-set-client-handshake with my.cnf. This will make the client work at the same server as the Charset.
skip-locking - this one, if remembered correctly, he changed his name to skip-external-locking Regarding the Replicate MySQL Server, I'm not sure if having a single Server will improve any performance or not. But not wearing it damaged
skip-thread-priority - This is to specify that thread threads must not be overtaken. MySQL will give each type of QUERY unequal. I don't remember anything more than anything. But removing this topic makes the queue system of MySQL not complicated to queue and work as FIFO instead.
skip-bdb - unused, skip it. If using bdb, then this comment line. For me, I only use MYISAM and INNODB.
skip-networking - This tells MySQL Server not to listen at INET SOCKET. Listen to UNIX SOCKET only. This one doesn't increase the speed much. But can reduce the chance of being attacked
log-slow-queries - This is used only when wanting to see which query is slow. Will be able to customize
Thread management - The most important thread -related variable of the thread is thread_cache. This variable will not destroy the thread of MySQL below this number. Normally, I keep guessing. Looking at the status of MySQL, I recommend looking at phpMyAdmin. It's convenient.
Threads cached 143
Threads connected 7
Threads created 532
Threads running 1
Threads cached - is the number of threads in the MySQL program. Now you can see 143 threads.
Threads connected - is the number of threads that are actually used
Threads running - These are threads that are looking for query results.
Threads created - is the number of threads that have been newly created since the Server started. If this value increases too quickly Increase the number of Thread_cache. The result is that MySQL will run a little faster because it won't waste time. Create and destroy Threads often.
MYISAM with memory The variable that we are interested in is
key_buffer = 32M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
- Key Buffer is the area for Cache. Key value of each Table. Whereas, there are 3 MySQL, Primary, INDEX and UNIQUE. Normally, if I use Table MyISAM a lot, this value should be very much. No need to use Key_Buffer. 16MB is recommended for 256 RAM and more when more RAM.
- Sort Buffer is the memory that MySQL uses for each connection to reserve for a table scan. Normally, if you will sort a field that is not a key, you will always use this part of the memory. Setup must start from 512K or more since it is the memory. There will be additional reservations for Connection, so you should not set the Setup too high because this will cause MySQL to run out of memory.
- Read Buffer is the memory that MySQL will use to store values read from a continuous table (that is, not Sort), not much need. Because normally we will have LIMIT read operations to show on the web already
- Read-Random Buffer Size is the memory that MySQL will use to store values from a discrete table (such as a Sort result) should be larger than the Read Buffer.
I don't really use MyISAM. I don't have many settings, so I will go to the tmp_table section 4.
In this context, we will talk about tmp_table . In complex queries, MySQL will create a result table in memory as a HEAP-TABLE. If the table is larger than a value, MySQL will copy that table. The disk is MyISAM TABLE. We will look at that value.
Before going further than that, let's talk about Complex Query first. In most cases, we talk about queries that use GROUP BY, UNIQUE, LIKE, and the uncertainty is SUB SELECT.
How to see how much memory has been swapped. Can be viewed from
Created tmp disk tables 14652
Created tmp tables 222220
When creating a tmp_table, MySQL will increase the value of Created tmp tables, and if a swap to Disk will increase the Created tmp disk tables. Normally, if you divide these two values by one hundred times, it should not exceed 5-10% depending on how the table you use is big, small How complicated is it?
There are 2 variables that will control the swap.
tmp_table_size = 32M
max_tmp_tables = 32
If tmp_table is bigger than tmp_table_size, it will swap to disk. Or if tmp_table is greater than max_tmp_tables, it will also swap to disk.
The normal tmp_table_size value is 32M and the max_tmp_tables value is 32. You shouldn't setup it higher than 2 times the normal value. But try to check how much your program has to Optimize Query or use the Cache method of the net result of the web page to help
In-depth key buffer
Key Buffer is the memory reserved by MySQL only. And use to share with every process (as mentioned before)
But we will talk about Key performance. Interesting values are
Key blocks unused 27683
Key blocks used 1312
Key read requests 1318393
Key reads 1344
The first pair will tell you how much use your Key Buffer is. Usually, the Key Blocks Unused is either not very large or 0 at all. For example, we have set too many Key_Buffer values.
Table Cache
For Table Cache, open the Handle of the Table. For quick access to data in the table But if you increase this value a lot You may have a problem that the File Descriptor is not enough. If I don't remember correctly, I suggest adding File Descriptor by editing the Kernel variable at / proc / sys / fs / file-max. Yes, by using the command
echo 392604> / proc / sys / fs / file-max
I'm not sure if using the ulimit command will work as well.
Come back to table_cache. Normally, if in a system that has a lot of tables, table_cache should cover all the underlying tables and about 50% of the remaining tables. But if possible, will cover it all, it is not wrong at all For my thing, set it to 1024.
How to see if the set is too small or not, see
Open tables 1024
Opened tables 1120
Where Open tables are the number of tables currently open and Opened tables are the total number of open tables Since the beginning of MySQL Server. If the Opened tables increase too quickly Suggest to increase the table_cache value
How to adjust table cache values by adding this line to my.cnf.
table_cache = 1024
The second pair, if you have (Key read requests - Key reads) * 100 / Key read requests, we will call the Key Hits Rate. For example, 99.9. Key Hits Rate is very good, usually around 95 - 99%. If less than this, suggest to add Key_Buffer. For Key Hits Rate, you have to think about it after working for MySQL for a few days, maybe 2-3 days.
Query Cache
Query Cache is a new feature available in MySQL version 4.x and up.
Query Cache is a simple operation. If the query is the same, MySQL will call from Cache instead of going to a new query.
Query Cache is not useful for every database structure. Query Cache is suitable for tables that do not update but has many records such as 50,000 records or more. Query Cache will only be used with Select. If your system is different from this Using a query cache may have the opposite effect.
How to enable Query Cache, insert this line in my.cnf.
query_cache_type = 1
query_cache_size = 32M
query_cache_type can have 3 values which are
0 - Query Cache Off
1 - Open Query Cache. You can instruct to not have Cache by using "SELECT SQL_NO_CACHE".
2 - On-Demand Style. You can instruct the MySQL Cache by using "SELECT SQL_CACHE".
Usually, if the table is updated, MySQL will immediately delete all of that table's cache and the query cache is case sensitive. Therefore,
SELECT * FROM a WHERE b = 1with
select * from a where b = 1
Will not be the same If we call the first and call the 2nd, the 2nd will not call from Cache.
So if
1. In your system, you have to write unplanned SQL, big case, small case. You will benefit from fewer Query Cache.
2. If your main table is constantly updated, you will receive fewer Query Cache benefits.
3. If your main table doesn't have many records, you will almost certainly not get any benefits from Query Cache.
Query Cache Performance
Normally, using the Query Cache is effective. There must be a SELECT SQL_NO_CACHE in the query that does not want MySQL to use Cache to improve performance. Let's look at the status of MySQL in the Query Cache.
Qcache free blocks 5184
Qcache free memory 17279864
Qcache hits 40606913
Qcache inserts 9779896
Qcache lowmem prunes 1096040
Qcache not cached 279265
Qcache queries in cache 10391
Qcache total blocks 26226
The important thing is Inserts and Hits. If this is Hits, about 4 times as much as Inserted. For me, it is not good. Probably about 10-20 times. It is expected that in my system there are tables that need too many updates, so Query Cache is not fully effective.
Comments
Post a Comment